substitute for ? placeholders in the SQL statement

#1

Hello, I am trying to execute an SQL command, that will sum all the price of a given month. But I can’t figure out how to get the results in expo.

This works:

tx.executeSql(
   "select sum(price) from notes where strftime('%m', created_at) = '12';",
   [],
   (_, ResultSet) => {
       console.log('monthSum success', ResultSet)
   },
   (_, error) => {
       console.log('monthSum error', error)
   }
)

However, when I provide a value for the month, I get an error.

tx.executeSql(
   "select sum(price) from notes where strftime('%m', created_at) = '?';",
   [12],
   (_, ResultSet) => {
       console.log('monthSum success', ResultSet)
   },
   (_, error) => {
       console.log('monthSum error', error)
   }
)

monthSum error [Error: Cannot bind argument at index 1 because the index is out of range. The statement has 0 parameters.]

I have tried removing quotes from the ? as well, which returns null as the sum.

tx.executeSql(
   "select sum(price) from notes where strftime('%m', created_at) = ?;",
   [12],
   (_, ResultSet) => {
       console.log('monthSum success', ResultSet)
   },
   (_, error) => {
       console.log('monthSum error', error)
   }
)

I have also tried casting the provided value as text, but this also returns null as the sum.

tx.executeSql(
   `select sum(price) from notes where strftime('%m', created_at) = CAST(? as TEXT);`,
   [12],
   (_, ResultSet) => {
       console.log('monthSum success', ResultSet)
   },
   (_, error) => {
       console.log('monthSum error', error)
   }
)

However, this works too:

tx.executeSql(
    `select sum(price) from notes where strftime('%m', created_at) = CAST(${12} as TEXT);`,
    [],
    (_, ResultSet) => {
        console.log('monthSum success', ResultSet)
    },
    (_, error) => {
        console.log('monthSum error', error)
    }
)

Can someone please tell me where I am wrong?

1 Like
closed #2

This topic was automatically closed 15 days after the last reply. New replies are no longer allowed.