Batch execution with values

[snow-drop]

Good day. In this link for batch execution, is there any way I could include values to query statements? Based on the method signature, it only accepts statements. If this function is not yet implemented, I think we could use something like this:

execute([
{ statement: statement1, values: ['value1'] },
{ statement: statement2, values: ['value2'] }
])

Also, in this signature,

execute({statements:"fooStatements"}) => Promise<{changes:{changes:number},message:string}>

isfooStatements an array or a couple of strings combined?

Thanks!

[jepiqueau]

@snow-drop fooStatements is a string and the execute command allow to execute a set of raw statements which are regroup in a string. so the way to achieve passing values is made as follows:

            const row: Array<Array<any>> = [["Whiteley","Whiteley.com",30],["Jones","Jones.com",44]];
            let sqlcmd: string = `
            BEGIN TRANSACTION;
            DELETE FROM users;
            INSERT INTO users (name,email,age) VALUES ("${row[0][0]}","${row[0][1]}",${row[0][2]});
            INSERT INTO users (name,email,age) VALUES ("${row[1][0]}","${row[1][1]}",${row[1][2]});
            COMMIT TRANSACTION;
            `;

hope this clarify

[snow-drop]

I see. I asked it because some libraries I used in databases uses a separates the values from the statements so that it can escaped the content of value whenever the value contains a maliciously crafted SQL query or a character/word belongs to the reserve words. Anyway, thank you very much for the clarification. Your example code made it crystal clear. ;)

[oxylius]

Hi there, is there a implementation planned to allow execute a batch of prepared statements? Like @snow-drop mentioned it would be much better to prevent SQL-Injections and it would be better to let a library create and evaluate those prepared statements.

[jepiqueau]

@oxylius first thank for using the plugin, I thought my answer to @snow-drop was clear, but if you come back you are thinking at something different. so can you specify yours thoughts much deeper so i can understand what you are after. Can you give me an example of library which do the job, so after it i will evaluate the necessary work.

[jepiqueau]

@oxylius @snow-drop Are the batch of prepared statements include only INSERT or UPDATE statements ?

[jepiqueau]

@oxylius @snow-drop i got this working on my dev for electron. Is it what you are looking for ?

        let set: Array<any>  = [
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Simpson","Tom","Simpson@example.com",69,"4405060708"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Jones","David","Jones@example.com",42,"4404030201"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Whiteley","Dave","Whiteley@example.com",45,"4405162732"]
          },
          { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
            values:["Brown","John","Brown@example.com",35,"4405243853"]
          },
          { statement:"UPDATE users SET age = ? , MobileNumber = ? WHERE id = ?;",
            values:[51,"4404030202",2]
          }
        ];
        result = await this._SQLiteService.executeSet(set);
        console.log("result.changes.changes ",result.changes.changes)
        if(result.changes.changes != 5) {
           console.log("error");
        } else {
          console.log("success");
       }

[oxylius]

Hi @jepiqueau Thank you for your fast reply. I've been testing this and it looks like it is working, thank you for clarification. I think the main problem is the reuse of typings capSQLiteOptions and capSQLiteResult in all the plugin methods. So it's hard to find out, what a method allows/accepts as parameters. I'm writing this from the view of the plain plugin user, i didn't look too much under the hood so i don't know how easy that would be to change that 🙈

My case right now is, that i have an ionic app with cordova and i switched to capacitor and i'm looking to replace the cordova plugins. On cordova based ionic app i used ionic-native/sqlite backed by cordova-sqlite-storage. As you might see, the types there are also not very good, but the documentation has more examples, that make the usage more obvious eg. SQL transactions

Probably the best would be more descriptive type definitions on the plugin methods, maybe related with issue #4 . A quick fix would be enhancing the Readme.md with more examples

Thank you very much for your work and time on this plugin!

[jepiqueau]

@oxylius @snow-drop the new release of the plugin 2.2.0-2 allow you to run batch execution with values. A new command has been added executeSet for that purpose to use it

            // Execute a Set of raw SQL Statements
            let set: Array<any>  = [
              { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
                values:["Blackberry","Peter","Blackberry@example.com",69,"4405060708"]
              },
              { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
                values:["Jones","Helen","HelenJones@example.com",42,"4404030201"]
              },
              { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
                values:["Davison","Bill","Davison@example.com",45,"4405162732"]
              },
              { statement:"INSERT INTO users (name,FirstName,email,age,MobileNumber) VALUES (?,?,?,?,?);",
                values:["Brown","John","Brown@example.com",35,"4405243853"]
              },
              { statement:"UPDATE users SET age = ? , MobileNumber = ? WHERE id = ?;",
                values:[51,"4404030237",2] in the code 
              }
        ];
        result = await this._sqlite.executeSet({set:set});
        console.log("result.changes.changes ",result.changes.changes)
        if(result.changes.changes != 5) resolve(false);

For the documentation, i do not know why you are looking at capSQLiteOptions and capSQLiteResult in the code, look at the readme for each command you have the entry parameters and the output result described as well as some ways of using the methods. You have also some applications referenced demonstrating the use of the methods and also an application starter. We can for sure always improve the documentation. Hope you will enjoy the new feature