SQLExecute (API 1.0) vs. ExecuteSQL (API 2.0) - API 2.0 Bug?

Hello,

I just tried to port a very simple database app to API 2.0 and already the new (renamed) method “Database.ExecuteSQL” doesn’t seem to work anymore like previous Xojo version (< 2019.02). I have a database setup string that is a constant (kDatabaseScheme) in my project. Let’s start with this example:

[code]CREATE TABLE Aliase
(
ID INTEGER PRIMARY KEY,
IndividualRecordID INTEGER NOT NULL,
ConnectedPersonID INTEGER NOT NULL
);

CREATE TABLE Associations
(
ID INTEGER PRIMARY KEY,
IndividualRecordID INTEGER NOT NULL,
AssociatedPerson INTEGER
);[/code]

The following code uses API 1.0 and runs without errors in Xojo 2019.1.1:

[code]Dim db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“MyDB.sqlite”)

If db.CreateDatabaseFile Then
db.SQLExecute(kDatabaseScheme)
Else
MsgBox("Database error: " + db.ErrorMessage)
End If[/code]

The API 2.0 equivalent looks like this:

[code]Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“MyDB.sqlite”)

Try
db.CreateDatabase
db.ExecuteSQL(kDatabaseScheme)
Catch error As DatabaseException
MessageBox("Database not created. Error: " + error.Message)
Return
End Try[/code]

The API 1.0 code creates the database with both tables. The API 2.0 code creates only the table “Aliase”. Why? Is this a bug?

If I now have to create each table individually in the code, this is by no means a work relief for me.

Its probably due to the move to everything being prepared statements and not being able to run multi-statements.

Oh man, if the new method works fundamentally different than the old one, Xojo has to communicate that very clearly. We need a tool we can rely on.

I’d throw up a feedback on that, the framework should fall back to a non-prepared request if there are no params (if this is actually what is going on)

I create in memory DBs like that all the time from multi-statement SQL strings stored in constants! If you are right that could be a big issue for a more than few.

  • karen

I’ve created <https://xojo.com/issue/57974>. Should this really be a bug, then a dot release must come.

I ‘m pretty sure this added layer of logic, trying to hide complexity fron the programmer, results in decrease of speed and increase of erratic behavior on different kinds of databases.

In scenarios where speed is valued higher than security we would need an sqlexecute statement which passes sql statements straightaway to the database server.

I find this change maybe the most useless one in api 2.0.

And I hate this kind of paternalism. Let alone that tons of old code is now invalid, aka “deprecated” - which is bs wording for: you can throw your valid code into the trash now.

There aways seems to be tradeoffs between speed of execution, coding convenience , readability and ‘safety’. How all 4 are balanced is important… C is at one extreme… one which I don’t want to be!!!

But when writing a specific app one can judge when more overhead is not significant. For a general purpose tool for which the uses are only limited by our abilities and imagination, efficiency overall needs to be of much greater concern … even if the overhead added does not cause any issues for Xojo Inc when it it is eating it’s own dog food…

I liked how API 1.0 balanced those things much better than how the Xojo framework did, and so far over what I’ve seen from API 2.0.

Some of the changes/deprecations in API 2.0 seem not to be very concerned about introducing more overhead as well as more coding on our part in some cases to get things done, so potentially slowing some operations down in the name of making it harder to shoot oneself in the foot or looking more elegant.

The thing is I thought the balance between those things in AP 1.0 was more or less right in general (a few things could have been improved ). The Xojo framework went WAY too far in terms of parentalism… API 2.0 is bit less that way but but I suspect they will tend to make it more so with time…

  • Karen

The semicolon is a part of the SQL standard, having an implementation in 2019 that cant recognize it, well…

As they said many times in the forum, they are NOT adopting api 2 for now

It can’t be done for mysql according to the docs, nothing to do with xojo so its probably similar for sqlite:

https://dev.mysql.com/doc/refman/5.6/en/sql-syntax-prepared-statements.html

I don’t see this as a bug, it is just how prepared statements protect us from SQL injection. However raising an exception where the sql string includes a semicolon just seems rational. After all it is the binding data that needs to be sanitized.

To be fair this is a design time issue rather than run time. Once we have our constants (or sql strings) correct we should be ok.

it’s a bug. It can’t silently fail.

And…

It must Execute(SQLString) as always, accepting multiple statements when there’s no parameters, with just the string with the statements.

Just out of curiosity. What happens when:

[code]Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“MyDB.sqlite”)

Try
db.CreateDatabase
db.BeginTransaction
db.ExecuteSQL(kDatabaseScheme)
db.CommitTransaction
Catch error As DatabaseException
Var errMsg As String = error.Message
db.RollbackTransaction
MessageBox("Database not created. Error: " + errMsg)
Return
End Try[/code]

We should have way to execute multi-statement SQL because it is very convenient for things like creating in-memory DBs.

I create the table structure in another tool and export the SQL as text and then just feed that text to DB.SQLExecute (which is now deprecated)… To use DB.ExecuteSQL I would need to parse that output myself… Which make no sense…

This most definitely is a bug. As you yourself point out only parameters need to be sanitized, so there is no point to using using a prepared statement when there are no parameters… With no parameters DB.ExecuteSQL should have exactly the same behavior as DB.SQLExecute.

Also it has been reported that using prepared statements when they are not needed (no parameters) results in significantly slower performance.

BTW not even all Selects ( never mind just executing SQL) need prepared statements … those that don’t use direct user input (which I did a lot for my biggest app) do not and have no need for the overhead.

As it is now DB.ExecuteSQL makes API 2.0 less capable than API 1.0 in executing SQL commands because it always uses prepared statements… I can’t believe that was intended.

In any case they need to fix this.

Don’t know if they will make ExecuteSQL work with multiple statements or maybe introduce ExecuteScriptSQL

[quote=459444:@Rick Araujo]Just out of curiosity. What happens when:
[/quote]
MyDB.sqlite with only Aliase table.

I don’t disagree, I just don’t see this as a Xojo bug as it is a feature of prepared statements.

However a Xojo raised exception from the framework when a statement includes a ; would seem to be appropriate given that this is essentially an illegal character for preparing SQL statements.

I wonder if this affects DDL statements and databases that don’t support those in transactions only (so all except Postgres and I believe Oracle).

Major pain if multiple statements in one SQL ‘script’ won’t execute.

I have a few update scripts in constants that automatically update the database to add tables, indexes, and columns, wrapped in a single BEGIN/END TRANSACTION for version/feature updates.

Just tried it with Postgres. Got a “Can’t execute multiple statements within a prepared statement” (my translation) database exception.

This is totally wrong. Xojo should not execute stuff within prepared statements without me asking for it.