SelectSQL issues

  1. last week

    Gilles P

    Feb 10 Pre-Release Testers, Xojo Pro Québec, Québec, Canada

    Hi,

    working with version 2019r3.1, this code is giving me a hard time

    Var rs As RowSet
    
    Var i As Integer = 1
    Var Mid As Integer 
    
    Var Qry As String = "SELECT * FROM Suppliers ORDER ASC BY LastName"
    
    Try
      
      rs = OrdersDatabase.SelectSQL("SELECT * FROM Suppliers ORDER ASC BY LastName")

    SelectSQL don't compile, throwing this error

    SetEmailsAdds.YesButton.Action, line 14
    There is more than one method with this name but this does not match any of the available signatures.
    rs = OrdersDatabase.SelectSQL("SELECT * FROM Suppliers ORDER ASC BY LastName")

    The documentation states that the second parameter is optional, but the compiler acts as its mandatory, this is what I get at the bottom when the cursor its over ExecuteSQL:

    (14, 26) Database.SelectSQL(sql As String, values() As Variant) As RowSet

    Then is it a bug, a code 16 or what else ?

    Many thanks

    Finally found out what was wrong in my code.

    Again my project is made after the Sample application Eddie's Electronics Web app.

    In the project there is a class called OrdersDatabase. I added a button to the WebToolbar to set emails for tests purposes. This button calls a WebDialog, and if I click on Yes, then it ptoceeds. Part of the code is:

    Try
      
      rs = OrdersDatabase.SelectSQL("SELECT * FROM Suppliers ORDER ASC BY LastName")

    The code was in the button Action method. I moved the code in a new method for class OrdersDatabase, and still got a compile error, but different and this time informative. This is not not the exact message but it was something like "don't invoke a method on the class but on an instance of the class". Voilà !

    The instantiated object is saved in a property of Session called Orders. I edited the call in the Yes button to:

    Session.Orders.SetTestEmails

    instead of

    OrdersDatabase.SetTestEmails

    In the SetTestEmaiils() method I used:

    rs = Self.SelectSQL(Qry)

    In the end it looks like the compiler was not able to provide the right error message. Can't tell if it's a bug or a complex situation that went the compiler bananas. Anyway the issue is solved.

    Thanks to all of you !!!

  2. Wayne G

    Feb 10 Pre-Release Testers, Xojo Pro, MVP Auckland, New Zealand

    Your Order By clause seems wrong.

    Var Qry As String = "SELECT * FROM Suppliers ORDER BY LastName ASC"
  3. Rick A

    Feb 10 Pre-Release Testers (Brazil. UTC-3:00)

    This compiles...

    Var OrdersDatabase As SQLiteDatabase
    Var rs As RowSet
    
    Try
      
      rs = OrdersDatabase.SelectSQL("SELECT * FROM Suppliers ORDER BY LastName ASC")
      
    Catch e
      
    End
  4. Jay M

    Feb 10 Pre-Release Testers, Xojo Pro NC, USA

    @Wayne G Your Order By clause seems wrong.

    While this is true, it wouldn't cause the error he's getting, which is during compilation, not execution.

    Gilles, did you paste some of your code into Xojo from someplace else? If so, try highlighting all the code in that method then right-clicking and choose "Clean invisible ascii characters".

  5. Gilles P

    Feb 10 Pre-Release Testers, Xojo Pro Québec, Québec, Canada

    I tried removing ASC from the query, even completely removing the ORDER BY clause, still the same.

    @Jay M Tried your suggestion, but I still get the compilation error.

    Thanks

  6. Markus R

    Feb 10 Pre-Release Testers, Xojo Pro Europe / Germany
    Edited last week

    do you work in an older project version? try save as ..
    try remark rows until compilation error gone.
    try clear caches in options/building.
    if you not need older xojo ide uninstall it.

  7. Maximilian T

    Feb 11 Pre-Release Testers, Xojo Pro Europe, Germany, Berlin

    You don't, by any chance, have some extension method SelectSQL extends db as SQLiteDatabase returning rowset?

  8. Jeff T

    Feb 11 Pre-Release Testers Midlands of England, Europe

    I don't see a Var statement for OrdersDatabase
    Where is it set up, and as what type?

  9. Gilles P

    Feb 11 Pre-Release Testers, Xojo Pro Québec, Québec, Canada

    Here is a few more information.

    I am working from a copy of Eddie's Electronics Web version that I rework for a test.

    The database is a class in the project whose superclass is SQLiteDatabase and name is OrdersDatabase. The object is set from a call to OrdersDatabase.OpenDatabase (modified for my needs):

    Dim orders As New OrdersDatabase
    orders.DatabaseFile = source
    If Not orders.Connect Then
      msg = "Could not connect to database for this user: " + orders.ErrorMessage
      #If TargetDesktop Then
        MsgBox(msg)
      #ElseIf TargetWeb Then
        App.AppendToDBErrorLog(msg)
        App.AppendToDBErrorLog("DBFile: " + source.NativePath)
        System.DebugLog(msg)
      #EndIf
      Return Nil
    End If
    
    Return orders

    The source is a FolderItem set to a SQLiteDatabase file.

    In the documentation about Database.SelectSQL (API 2.0):

    Database.SelectSQL(SQLStatement as String [,ParamArray values() as Variant]) As RowSet
    Database.SelectSQL(SQLStatement as String [,values() as Variant]) As RowSet

    Those are the two signatures for the method, see the square brackets, compare to what I get in the IDE:

    (14, 26) Database.SelectSQL(sql As String, values() As Variant) As RowSet

    In the IDE values as Variant is not optional, and my guess is where the issue is from.

    @Paul L What are your thoughts about my issue ?

    Thanks

  10. Gilles P

    Feb 11 Pre-Release Testers, Xojo Pro Québec, Québec, Canada

    I tried a Sample application under Database, the Web one, and it works, no compilation error on SelectSQL ?!?

  11. Markus R

    Feb 11 Pre-Release Testers, Xojo Pro Europe / Germany

    its really SelectSQL and not SQLSelect. me thought web api 2.0 is still work in progress.

  12. Jeff T

    Feb 11 Pre-Release Testers Midlands of England, Europe

    its really SelectSQL and not SQLSelect

    Change for the sake of change. Pointless.

  13. Paul L

    Feb 11 Xojo Inc, Third Party Store

    @Gilles P @Paul L What are your thoughts about my issue ?

    I see no obvious reason why it would not work, at least based on what you've posted here. Perhaps you could share a sample project for others to try?

  14. Jay M

    Feb 11 Pre-Release Testers, Xojo Pro NC, USA

    @Gilles P Those are the two signatures for the method, see the square brackets, compare to what I get in the IDE:

    (14, 26) Database.SelectSQL(sql As String, values() As Variant) As RowSet
    In the IDE values as Variant is not optional, and my guess is where the issue is from.

    The IDE won't show which parameters are optional, and it won't show alternate signatures, just the first one. It's a limitation of the IDE. But that doesn't mean it won't work.

    As Paul said, we need to see your project (or a trimmed-down version of it) to determine why it's not working, because everything you've showed us so far should work correctly.

  15. Derk J

    Feb 11 Pre-Release Testers, Xojo Pro

    Are you missing "End Try"?

  16. Gilles P

    Feb 11 Pre-Release Testers, Xojo Pro Answer Québec, Québec, Canada

    Finally found out what was wrong in my code.

    Again my project is made after the Sample application Eddie's Electronics Web app.

    In the project there is a class called OrdersDatabase. I added a button to the WebToolbar to set emails for tests purposes. This button calls a WebDialog, and if I click on Yes, then it ptoceeds. Part of the code is:

    Try
      
      rs = OrdersDatabase.SelectSQL("SELECT * FROM Suppliers ORDER ASC BY LastName")

    The code was in the button Action method. I moved the code in a new method for class OrdersDatabase, and still got a compile error, but different and this time informative. This is not not the exact message but it was something like "don't invoke a method on the class but on an instance of the class". Voilà !

    The instantiated object is saved in a property of Session called Orders. I edited the call in the Yes button to:

    Session.Orders.SetTestEmails

    instead of

    OrdersDatabase.SetTestEmails

    In the SetTestEmaiils() method I used:

    rs = Self.SelectSQL(Qry)

    In the end it looks like the compiler was not able to provide the right error message. Can't tell if it's a bug or a complex situation that went the compiler bananas. Anyway the issue is solved.

    Thanks to all of you !!!

  17. Bob K

    Feb 11 Pre-Release Testers, Xojo Pro Kansas City

    @Jeff T Change for the sake of change. Pointless.

    In this case it's not pointless. In the classic API SQLSelect it's up to the user to check for the database error. Many people simply ignored, or where unaware of the error bit, so you had weird workarounds on checking for Nil Recordset's when it was really a db error due to malformed SQL statements.

    The API 2.0 method SelectSQL throws an exception when there is a database error and you simply cannot ignore it. People coming from other languages expect this. So I think this is one of the best changes in API 2.0. I would have preferred a less confusing name (SelectSQL and SQLSelect are too similar) or a different database class that threw exceptions with the same method names. But that ship has long sailed.

    All of our classic API projects used an overloaded SQLSelect and SQLExecute methods that, wait for it, checked for the error automatically and raises a BKS_DatabaseException that contains the SQL Error and maybe the SQL statement itself. So API 2.0 is now doing what we've been doing for years.

  18. 7 days ago

    Beatrix W

    Feb 12 Pre-Release Testers, Third Party Store Europe (Germany)

    @Bob K : you really should read the DailyWTF about the perversions you can do with exceptions. Handling exceptions has a greater complexity than handling errors. As Xojo is now being modelled on Excel users who might grasp the "on error resume next": do you really think that those users can do exception handling?

  19. 6 days ago

    Bob K

    Feb 12 Pre-Release Testers, Xojo Pro Kansas City

    Trust me, I completely understand the complexities of dealing with exceptions and how it affects less experienced developers. I'm just saying that when it comes to the database classes this is a welcome change (from my perspective) because I've fixed so many damn projects in the past 20 years that make the exact same fundamental mistake (not checking for an error). In reality it's not an exception but a normal operation but the powers that be have decided that exceptions are the way forward.

  20. Newer ›

or Sign Up to reply!