MSSQLServerDatabase SelectSQL parameters returning Nil RowSet

Targets Web
1

I have spent quite a bit of time trying to debug my code as after performing a SelectSQL on my MSSQLServerDatabase, it always returns a Nil RowSet if I pass in parameters, whether that be directly or an array of type Variant.

MSSQLServerDatabase — Xojo documentation

I have setup a very simple SELECT statement and tried passing a single parameter into SelectSQL to choose the column to select. Even this is returning a Nil RowSet. Therefore, I think it’s a bug. Can someone please test?

sql = "SELECT ? "
sql = sql + "FROM [MyTable] "

MySQLServerDB.SelectSQL(sql, "[Col1]")

passing parameters into ExecuteSQL works fine.

2

You cannot use parameters for table or column names, only for values. Such as:

SELECT * FROM MyTable WHERE MyID=?"

Think about how this works - Xojo substitutes the parameter as a string or a literal, depending on its type. If the parameter is a String, it encloses it in single quotes (or whatever the string delimiter is for the db). If it is a number, then it uses the string version of the number, without quotes. So, in your example, the resulting query comes out like this:

SELECT '[Col1]' FROM [MyTable]
3

Also, if you were catching the error exception, it would probably give you a clue as to what went wrong.

4

Ok perhaps my example was a bad one, but anyway I learnt something here regarding not being able to use column names so thank you (one to note for future).
My real query parameters are indeed filters in the WHERE clause exactly like you have shown. Another thing is that is doesn’t raise an exception so that’s why I couldn’t pick it up easily. It simply returns a RowSet as Nil value. An exception is only raised if I try doing something with the RowSet afterwards as it contains no data.

5

It’s not that it contains no data, but the fact that it is Nil. An empty rowset is not Nil but contains no data, which is what you get if the SQL was valid but didn’t return any records.

Would need more details to be sure, but this could be a bug. A SelectSQL that does not raise an exception should not return a Nil rowset.

6

In API 1.0 nil recordset meant your SQL query was bad.
In API 2.0 you should be getting a DatabaseException for bad queries. This is probably a bug of not raising an exception when it should.