Insert data is not returned when using pre-prepared statements

[ewanhowell1]

Current Behavior

Pre-prepared statements return nothing

Expected Behavior

Pre-prepared statements return the insert data

Steps to Reproduce the Problem

console.log(await database.sql("INSERT INTO agents (email) VALUES ('test')"))
// { lastID: 9, changes: 1, totalChanges: 1, finalized: 1 }

const statement = database.prepare("INSERT INTO agents (email) VALUES ('test2')")
statement.run((err, res) => console.log(res))
// undefined

Environment

• Version: 0.0.56
• Platform: Win
• Node.js Version: v20.12.2

[gionatamettifogo]

Thanks Ewan, I will look into this and get back to you asap.

In the meanwhile, if you're fine with async code and wish to do a prepared statement, meaning eg. pass your parameters through sanification, etc. you can also use:

console.log(await database.sql`INSERT INTO agents (email) VALUES (${test})`)
// { lastID: 9, changes: 1, totalChanges: 1, finalized: 1 }

[gionatamettifogo]

An example of how the prepared statement is used...

first database.prepare( with ? where the parameters will be
... then bind( with the actual values will execute the query...

it('Statement.all', done => {
  const chinook = getChinookDatabase()
  expect(chinook).toBeDefined()
  const statement = chinook.prepare('SELECT * FROM tracks WHERE albumId = ?;', (err: Error, results: any) => {
    expect(err).toBeNull()
  })

  statement.all(3, (error, rows) => {
    expect(error).toBeNull()
    expect(rows).toBeDefined()
    expect(rows).toHaveLength(3)
    expect(rows).toBeInstanceOf(SQLiteCloudRowset)

    statement.all(4, (error, rows) => {
      expect(error).toBeNull()
      expect(rows).toBeDefined()
      expect(rows).toHaveLength(8)
      expect(rows).toBeInstanceOf(SQLiteCloudRowset)

      chinook.close(error => {
        expect(error).toBeNull()
        done()
      })
    })
  })
})

I will add some test code specifically for inserts and check for proper return values as you suggested

[gionatamettifogo]

Hi @ewanhowell1,

I changed the driver to report the number of changes as a result when calling a prepared statement's run method (previously these same values were returned as context and still are). Also wrote a quick test example to show how the statement is prepared and executed:

it('Statement.run - with insert results', done => {
  // create simple "people" database that we can write in...
  const database = getTestingDatabase(error => {
    expect(error).toBeNull()

    const statement = database.prepare('INSERT INTO people (name, hobby, age) VALUES (?, ?, ?); ')

    // @ts-ignore
    statement.run('John Wayne', 73, 'Horse Riding', (error, results) => {
      // this is an insert statement and the result will indicate number of changes, etc.
      // console.debug(`insert results: `, results)
      // -> insert results:  { lastID: 21, changes: 1, totalChanges: 21, finalized: 1 }

      expect(results.lastID).toBeGreaterThan(1)
      expect(results.changes).toBe(1)

      done()
    })
  })

Still, unless you have legacy code, the easiest way is await database.sql as suggested above.
Please let me know if I can be of help. Closing the issue, please reopen if the problem persists.

You will need to update the driver to the latest 1.0.122 version:
https://www.npmjs.com/package/@sqlitecloud/drivers/v/1.0.122