[quote=459455:@Wayne Golding]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.[/quote]
Except API 2.0 has no way to execute SQL scripts as one can with API 1.0 because ExecuteSQL ALWAYS uses prepared statements even without parameters… I have to believe that is a bug. I can’t believe such as significant loss of functionality was intentional!
[quote=459492:@Karen Atkocius]Except API 2.0 has no way to execute SQL scripts as one can with API 1.0 because ExecuteSQL ALWAYS uses prepared statements even without parameters… I have to believe that is a bug. I can’t believe such as significant loss of functionality was intentional!
-Kraren[/quote]
They can introduce ExecuteScriptSQL for this situation like Python Sqlite3.
[quote=459458:@John A Knight, Jr]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.[/quote]
Don’t worry about that, this code works:
[code]Var db As New SQLiteDatabase
db.DatabaseFile = SpecialFolder.Desktop.Child(“MyDB.sqlite”)
Try
db.CreateDatabase
db.BeginTransaction
Var multiple() As String = kDatabaseScheme.Split(";")
For i As Integer = multiple.FirstRowIndex To multiple.LastRowIndex
db.ExecuteSQL(multiple(i))
Next
db.CommitTransaction
Catch error As DatabaseException
MessageBox("Database not created. Error: " + error.Message)
Return
End Try[/code]
This creates MyDB.sqlite with both tables.
For me:
Xojo should introduce ExecuteScriptSQL when we want to have multiple-statements (yes, we know what we are doing)
Xojo should raise an exemption when we use ExecuteSQL and we try to use multiple-statements and not just use the first statement and silently fail with the rest
EDIT: Note, the code I posted is not to be taken as a workaround or an “easy-fix” for this issue, it was just a test to see if the BeginTransaction and CommitTransaction worked (don’t know if I misunderstood what John posted)
Not really. You can’t split SQL code easily like this as the “;” might be part of some value. You’d have to come up with some sort of escaping scheme. Also the prepared statements get created over and over again as Bob explained in his blog, which hurts performance.
Well, no problem I’ll just wait with my code transformation for Xojo to introduce some way. Or maybe I’ll look at Christian’s SQL-Plugin.
[quote=459495:@Maximilian Tyrtania]Not really. You can’t split SQL code easily like this as the “;” might be part of some value. You’d have to come up with some sort of escaping scheme. Also the prepared statements get created over and over again as Bob explained in his blog, which hurts performance.
Well, no problem I’ll just wait with my code transformation for Xojo to introduce some way. Or maybe I’ll look at Christian’s SQL-Plugin.[/quote]
I was referring to what John posted about wrapped in a single BEGIN/END TRANSACTION for version/feature updates as in, you can have a Begin, then multiple commands and End transaction and it works. Using the original example was just a fast way to test and post some code. What doesn’t work is having multiple-statements on a single command.
[quote=459498:@Rick Araujo]Blind things like this must be avoided as hell, and seeing such workarounds gives me stomach aches.
While the fix don’t come out, the only less bad workaround is using the API 1.0 SQLExecute() that still works.
[/quote]
Yes, my code is not a workaround and never should be used. You should use API 1.0 SQLExecute for this type of situation (that works like always under 2019r2), you can use API 2.0 ExecuteSQL for everything else until the next release of Xojo.
The problem here is that all the prepared statements mombo jumbo is done INTERNALLY as part of the framework. So the expected behavior of something called ExecuteSQL is that actually executes a valid SQL script, even if it contains multiple statements.
[quote=459501:@Rick Araujo]If they don’t, thousands of people could start to have damaged databases, bad calculations, sums, balances… silently.
A snowball of problems causing $ loss day by day.
So… Not dreaming, and they are aware of this.[/quote]
I can use anything. The data and $ loss occurs to newbies and people that doesn’t follow this conversations. That’s why a fix is URGENT… and simple for THIS only case. They just need to modify the call signatures and behaviors in their framework:
// Var SQL, param1, param2 as String
// ExecuteSQL(SQL, param1), ExecuteSQL(SQL, param1, param2 ) Prepared statement with params
Public Sub ExecuteSQL(SQLStatement as String, FirstParam as Variant, ParamArray ExtraParams as Variant)
// Code
End Sub
// Var SQL As String, params() As Variant
// ExecuteSQL(SQL, params) Prepared statement with array of params
Public Sub ExecuteSQL(SQLStatement as String, values() as Variant)
// Code
End Sub
// Var SQL as String
// ExecuteSQL(SQL) Direct, not a prepared statement execution, no params
Public Sub ExecuteSQL(SQLStatement as String)
// Code
End Sub
[quote=459507:@Rick Araujo]I can use anything. The data and $ loss occurs to newbies and people that doesn’t follow this conversations. That’s why a fix is URGENT… and simple for THIS only case. They just need to modify the call signatures and behaviors:
[code]
// Var SQL, param1, param2 as String
// ExecuteSQL(SQL, param1), ExecuteSQL(SQL, param1, param2 ) Prepared statement with params
Public Sub ExecuteSQL(SQLStatement as String, FirstParam as Variant, ParamArray ExtraParams() as Variant)
// Code
End Sub
// Var SQL As String, params() As Variant
// ExecuteSQL(SQL, params) Prepared statement with array of params
Public Sub ExecuteSQL(SQLStatement as String, values() as Variant)
// Code
End Sub
// Var SQL as String
// ExecuteSQL(SQL) Direct, not a prepared statement execution, no params
Public Sub ExecuteSQL(SQLStatement as String)
// Code
End Sub
[/code][/quote]
That could be more confusing. Mainly, if they are going to do some extra in the framework, it should be done the right way, not just a half baked solution. This potentially dangerous problem could be avoided if internally:
-No params, no PS
-Multiple statements, parse them an make multiple SQL executions.
ExecuteSQL(PreparedSQLString, param1),
ExecuteSQL(PreparedSQLString, param1, param2 )
ExecuteSQL(PreparedSQLString, arrayOfParams)
ExecuteSQL(SQLString) <--- This part, right now impossible with the current signatures, will fix the bug
ExecuteSQL(stmt as string, params() as variant)
ExecuteSQL(stmt as string, paramarry params as variant)
Then just see if params.ubound < 0 and if it is do not use a prepared statement
If you DO try to pass in a multi-statment script that tries to use a prepared statment its going to fail anyway
The DB engine will reject it
That however only deals with one issue
They still have the performance issues because they crate a new prepared statement each time and thats exactly counter to at least one reason to use a prepared statement on many db’s
Normally you would create it once then reuse it (say in a loop) to insert a lot of data without recreating the prepared statement each time
Basically you end up just not using this new API 2.0 mechanism
BTW If DB.SelectSQL also uses a prepared statement under the hood when no parameters are passed (don’t know if that is true), that too should be changed not to…
As i mentioned above, one often does selects without direct user input (they make non textual choices via selecting option or when doing canned reports).
We should not have to deal with the extra overhead when it is not needed… and while it’s a good thing to make it easy for us to be “safe”, we also need the option to be able to get maximum performance when needed without that being made too hard…
This case is a good example of the problem. Another (though less used) one is the deprication of the stringB methods IMO.
The point above about parentalism is well taken… Forcing us to ALWAYS use prepared statements is a step too far IMO and would help insure than Xojo will be seen ONLY as a beginner tool and not “powerful” enough for “serious” use.
This type of overprotectiveness is one reason I had an issue with the xojo framework, and that thinking looks like it is being carried over to API 2.0… and IMO is a huge mistake.
The renaming and 0 vs one 1 indexes got the most attention, but the overprotectiveness is IMO by far the biggest issue for the long term use of Xojo
how about adding a parameter to all these api 2 calls , usePreparedStatements as Boolean = true
so that it is safe for beginners, and experimented users can bypass it ?
