ExecuteSQL parameter array data types

Targets Web
1

I have created a dictionary with key value mappings, and an empty array (e.g. MyArray) as variant.
I believe all values in dictionaries are stored as Variant.

I therefore check the VarType of each value e.g. MyDict.Value(key).

If VarType is 3 (Integer), I add to the array as follows:
MyArray.Add(MyDict.Value(key).IntegerValue)

If it is 8 (String), I add to the array as follows:
MyArray.Add(MyDict.Value(key).StringValue)

Then I pass MyArray into ExecuteSQL as follows:
MyDatabase.ExecuteSQL(sql, MyArray)

However, when trying to run the query, the web app session interrupt message appears so it seems like the database can’t parse the data. I think it has something to do with the StringValue stored in MyArray. If I test it by storing a random String in place of MyArray.Add(MyDict.Value(key).StringValue) e.g. MyArray.Add("testString"), the query runs perfectly fine.

2

First, since you are working with Variants in both the array and Dictionary, the casting is unnecessary unless you are trying to cast to something else. For example, if the Variant holds an Integer but you want to return it as a String.

What is the query, and code, you are attempting, and against what kind of database?

3

Good point.

Database is MS SQL Server

sql string is:

sql = "INSERT INTO [MyDB].[MySchema].[MyTable] (" + sqlCols + ") "
sql = sql + "VALUES (" + sqlValues + ")"

where sqlCols is a comma-separated string of all columns and sqlValues is comma-separated string all corresponding values
sqlValues contains ‘?’ for every value that is passed in as a parameter in ExecuteSQL

4

In your test, assign “testString” to a String variable, then add that to the array.

Assuming that fails, next try assigning it to a Text variable. I suspect that will work and you’ve uncovered a bug.

var s as string = "testString"
var t as text = "testString"

// Uncomment one of these
'MyArray.Add( s )
'MyArray.Add( t )
5

This works fine.

So I then tried assigning the following to the string variable and adding it to the array but it fails (session interrupt message appears again)

testString = MyDict.Value(key).StringValue
MyArray.Add(testString)
6

What’s the value of the string?

7

it was implemented in a loop so takes almost 50 different values.
I have looked through each value in the dictionary and they are all strings. Some are empty strings while some others are numeric values casted as string e.g. ‘54232’

8

What happens if you test with an empty string?

9

Thanks - looks like we have found the issue.
Empty string breaks it.

So I did the following

Var testString As String
testString = ""

and inside my loop, I add it to my array.
MyArray.Add(testString)

It fails when trying to ExecuteSQL. By simply replacing empty string in testString above with a non-empty string, it works. I take it that this is a bug?

10

I think so.

11

lol, i made a bug report about ms sql 2019 express and empty strings in xojo a few days ago.
https://tracker.xojo.com/xojoinc/xojo/-/issues/69746

1 Like
12

Hopefully it gets fixed soon.
In the meantime, I probably have to use Chr(0), which is interpreted as empty string by the database