OK, I think I found the cause of the problem. The SQL module in 8.1 does things quite differently than in 8.0...

There's a common Connection class that all of the database drivers inherit. Its code is called when you use sprintf() style bindings and under the covers converts a query() to a big_query(), which returns a Result object which it then converts back to the array of mappings that you expect query() to return. That's where the problem occurred.

The Result object is designed for interacting with databases that return results in a streaming manner. There was an interaction with they way SQLite works (there's no "execute query" function per-se, so you step through the results and it executes the query as part of the stepping process). The code in Connection that converts a Result into an array looked to see if there were any fields in the result, and if none were present, it just stopped. That resulted in the query never being executed for those that may not return any colums (such as inserts, updates and deletes).

I changed this method so that it fetches the rows before checking for the columns. A result without fields will likely not return any rows, so this shouldn't be a problem for databases that have already executed the query by this point.

On a side note, I'm concerned that the conversion of query() to big_query() for bindings that already provide query() may be a performance regression... for example, the SQLite binding generates the result array in C, which should be at least somewhat faster than the Pike code used by Connection. I also suspect that there could be result differences since the code paths are so drastically different.

Is there a reason for query() not to use the driver provided query() when it exists?

Bill March 19, 2018 11:57 AM, "Matthew Clarke" <pclar7@yahoo.co.uk (mailto:%22Matthew%20Clarke%22%20pclar7@yahoo.co.uk)> wrote: Hi No I'm afraid that update didn't fix the problem though it doesn't crash the system now - just silently fails to insert anything into the table. int main() { Sql.Sql conn = Sql.Sql("sqlite://mydb.db"); conn->query("create table t(id int)"); array arr = ({ 14, 3, 7, 9, 100, 20, 5, 12 }); foreach (arr, int x) { conn->query("insert into t(id) values(%d)", x); //conn->query("insert into t(id) values(:a)", ([":a":x])); } array arr2 = conn->query("select * from t"); write("num rows=%dn", sizeof(arr2)); return 0; } The second of those query() calls (commented out) works but not the sprintf-style version. Regards, Matthew On Sunday, 18 March 2018, 04:02:13 GMT, H. William Welliver III <william@welliver.org (mailto:william@welliver.org)> wrote: Hey… sorry for the delay, the week got away from me. As I was looking into this, I noticed that grubba may have fixed the problem earlier today (not sure if he told you directly). If a current checkout doesn’t resolve the problem for you, let me know. Bill On Mar 11, 2018, at 1:37 PM, Matthew Clarke <pclar7@yahoo.co.uk (mailto:pclar7@yahoo.co.uk)> wrote: Hi Bill I posted again but I'm not sure that went through either. So if I email you directly... Here's the example: int main() { Sql.Sql conn = Sql.Sql("sqlite://mydb.db"); conn->query("create table t(id int)"); array arr = ({ 14, 3, 7, 9, 100, 20, 5, 12 }); foreach (arr, int x) { conn->query("insert into t(id) values(%d)", x); } return 0; } Works on 8.0.498 but not on 8.1.12. On 8.1.12 you need to use the other version of the query() method: conn->query("insert into t(id) values(:var)", ([":var":x])); Thanks & regards, Matthew On Sunday, 11 March 2018, 15:36:02 GMT, H. William Welliver III <william@welliver.org (mailto:william@welliver.org)> wrote: Hi… I’m not sure if you meant to include code examples, but they didn’t come through, and I don’t see your post on nabble. Can you resend it? Some changes were made to the sqlite binding to bring them in line with the way the other database bindings wok. It’s possible the change broke something but I’ll have to see what you’re doing to know for sure :) Bill > On Mar 11, 2018, at 7:26 AM, larcky <pclar7@yahoo.co.uk (mailto:pclar7@yahoo.co.uk)> wrote: > > Hi > This works perfectly in stable 8.0.498 but crashes in 8.1.12. > > > > To get it to work in 8.1.12, you have to use the other version of the > 'query' method: > > > Would it be possible to get the 'sprintf' variant working again? > Thanks, larcky > > > > > -- > Sent from: http://pike.1058338.n5.nabble.com/Pike-User-f4601254.html (http://pike.1058338.n5.nabble.com/Pike-User-f4601254.html) >