SelectSQL issues

  1. ‹ Older
  2. 7 weeks ago

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

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

  4. Markus R

    Feb 10 Pre-Release Testers, Xojo Pro Europe / Germany / Lower Saxon...
    Edited 7 weeks ago

    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.

  5. 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?

  6. 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?

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

  8. 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 ?!?

  9. Markus R

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

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

  10. Jeff T

    Feb 11 Pre-Release Testers Midlands of England, Europe

    its really SelectSQL and not SQLSelect

    Change for the sake of change. Pointless.

  11. 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?

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

  13. Derk J

    Feb 11 Pre-Release Testers, Xojo Pro

    Are you missing "End Try"?

  14. 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 !!!

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

  16. 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?

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

  18. Jeff T

    Feb 12 Pre-Release Testers Midlands of England, Europe

    the powers that be have decided that exceptions are the way forward.

    I get it, but my Gud it makes the code expand:
    Every damn thing gets wrapped in cotton wool
    And if you forget to do that - unhandled exceptions and / or crashes.

    This will not help newbies.. it will just make their apps crash instead of acting odd. (Yes I know thats a subtle distinction..)
    Whereas not checking an error condition at least allows for a little 'thats odd, let me look into it' time for your end users.

  19. Norman P

    Feb 12 Pre-Release Testers, Xojo Pro outside LMAO
    Edited 7 weeks ago

    Exception handling is measurably slower than checking error codes
    I wrote a blog post about this
    Its meant to be used when things are exceptional, things you didnt expect might occur like nil object or a file cant be opened that exists, not as a general purpose error handling mechanism

    I kind of worry that the "default" things people wil resort to is a single try catch surrounding all the code in every method "just to handle every error" or the single "exception" at the bottom of the method to catch every error
    Java ran into this early on and it made for some really awful code

  20. Markus R

    Feb 12 Pre-Release Testers, Xojo Pro Europe / Germany / Lower Saxon...
    Edited 7 weeks ago

    about error handling
    i like this way simplified with a boolean result

    if db.Open()=True then
     if db.Query("select * ...",Recordset)=True Then
       ...
     endif
     db.Close
    else
     Message "can't open database"
    ShowErrorList
    endif

    if a method catch a error it put it into a list and you can show this list when you want.
    at vb6 i wrapped much of ms methods this way because you can't use a single method without surrounding error handling.

    btw can xojo editor hide this try catch surroundings?

or Sign Up to reply!