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

  1. 4 weeks ago

    Martin T

    Oct 20 Pre-Release Testers Germany
    Edited 4 weeks ago

    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:

    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
    );

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

    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

    The API 2.0 equivalent looks like this:

    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

    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.

    Sorry John I obviously misunderstood what you wrote.

    This bug is marked as fixed.

  2. Julian S

    Oct 20 Pre-Release Testers, Xojo Pro UK

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

  3. Martin T

    Oct 20 Pre-Release Testers Germany
    Edited 4 weeks ago

    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.

  4. Julian S

    Oct 20 Pre-Release Testers, Xojo Pro UK
    Edited 4 weeks ago

    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)

  5. Karen A

    Oct 20 Pre-Release Testers

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

    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

  6. Martin T

    Oct 20 Pre-Release Testers Germany
    Edited 4 weeks ago

    I've created Feedback Case #57974. Should this really be a bug, then a dot release must come.

  7. Oliver O

    Oct 20 Pre-Release Testers, Xojo Pro https://udemy.seminar.pro

    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.

  8. Karen A

    Oct 20 Pre-Release Testers

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

    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... <sigh>

    - Karen

  9. Ivan T

    Oct 20 Pre-Release Testers

    @Karen A 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.

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

    @Karen A even if the overhead added does not cause any issues for Xojo Inc when it it is eating it's own dog food...

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

  10. Julian S

    Oct 20 Pre-Release Testers, Xojo Pro UK
    Edited 4 weeks ago

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

    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

    SQL syntax for prepared statements does not support multi-statements (that is, multiple statements within a single string separated by ; characters).

  11. Wayne G

    Oct 20 Pre-Release Testers, Xojo Pro Auckland, New Zealand

    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.

  12. Rick A

    Oct 20 Pre-Release Testers (Brazil. GMT-3:00)
    Edited 4 weeks ago

    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.

  13. Rick A

    Oct 20 Pre-Release Testers (Brazil. GMT-3:00)
    Edited 4 weeks ago

    @MartinTrippensee The API 2.0 code creates only the table "Aliase". Why?

    Just out of curiosity. What happens when:

    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
  14. Karen A

    Oct 20 Pre-Release Testers

    @Wayne G I don't see this as a bug, it is just how prepared statements protect us from SQL injection.

    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.

  15. Alberto D

    Oct 20 Pre-Release Testers

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

  16. Alberto D

    Oct 20 Pre-Release Testers

    @Rick A Just out of curiosity. What happens when:

    MyDB.sqlite with only Aliase table.

  17. Wayne G

    Oct 20 Pre-Release Testers, Xojo Pro Auckland, New Zealand

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

    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.

  18. Maximilian T

    Oct 20 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    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).

  19. John A

    Oct 21 Pre-Release Testers Las Vegas, Nevada

    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.

  20. Maximilian T

    Oct 21 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    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.

  21. Newer ›

or Sign Up to reply!