SelectSQL issues

Hi,

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

[code]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”)[/code]

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

Your Order By clause seems wrong.

Var Qry As String = "SELECT * FROM Suppliers ORDER BY LastName ASC"

This compiles…

[code]Var OrdersDatabase As SQLiteDatabase
Var rs As RowSet

Try

rs = OrdersDatabase.SelectSQL(“SELECT * FROM Suppliers ORDER BY LastName ASC”)

Catch e

End[/code]

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

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

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

Thanks

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.

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

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

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

[code]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[/code]

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 Lefebvre What are your thoughts about my issue ?

Thanks

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

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

Change for the sake of change. Pointless.

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?

[quote=475003:@Gilles Plante]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.[/quote]
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.

Are you missing “End Try”?

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:

[code]Try

rs = OrdersDatabase.SelectSQL(“SELECT * FROM Suppliers ORDER ASC BY LastName”)[/code]

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

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.

@Bob Keeney : 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?

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.

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.

Forum for Xojo Programming Language and IDE. Copyright © 2021 Xojo, Inc.