db.ExecuteSQL: Parameters cannot be arrays

Do you use this:

I am sorry, I wasted the past hour to try to replicate this using the Documentation example I based my code from withour been able to get some (generic) code to share instead of mine.

But, the code of the trouble comes from what the screen shot shows, after all. If you tried to use this syntax, you know if it worked or if you had to change your mind.

sql_parms must be an array of variant

You are correct. However, now I have an error near SET !

So, now the question is… How do I write an INSERT:

sql = "INSERT Customer SET City=?

all examples I saw used SELECT or UPDATE

insert into customer (city, …) values (?, …)

set is for update
like for example with an id as primary key

update customer set city=?… where id=?

The documentation says:

Here’s the same example but using parameters which protects you against a SQL injection attack:

That is why I used ‘=?’ (and because I have a Description text field that may have troubleshooting characters (‘,’ for example, but I may be wrong).

the docs are correct. this it the way to avoid injection attack

you have to create your sql string with ? for every parameter and use an array (variant) or a list (sequence of values) that will be used for every ? in strict order

1 Like

Yes, I think that is what I have done, but Xojo does not plays nice with me.

Here’s my code:

Var sql_Cmd      As String
Var sql_Parms(8) As Variant

sql_Cmd = "INSERT INTO Episode SET Title=?, Length=?, Season_Nbr=?, " +_
"Episode_Nbr=?, Year=?, Country=?, Director=?, Description=?"

sql_Parms(0) = TF_Episode_Title.Text
sql_Parms(1) = TF_Episode_Length.Text
sql_Parms(2) = TF_Episode_Season_Nbr.Text
sql_Parms(3) = TF_Episode_Nbr.Text
sql_Parms(4) = TF_Episode_Released_Year.Text
sql_Parms(5) = TF_Episode_Country.Text
sql_Parms(6) = TF_Episode_Director.Text
sql_Parms(7) = TA_Episode_Description.Text

  gSeries_db.ExecuteSQL(sql_Cmd, sql_Parms)

INSERT INTO Episode (Title, Length, Season_Nbr, Episode_Nbr, Year, Country, Director, Description) VALUES (?, ?, ?, ?, ?, ?, ?, ?)

I think I need some sleep. I check my TextFields (TF_) names and I come back here then.

I suggest to use TF_xxxxx.Text.Trim otherwise extraspaces could be added and you could have problems in some query since ‘abc’ is different from ’ abc ’

Something to note… you’re sending an array of strings here, but some of the fields look like they probably contain numbers. Those items should be converted as they’re put into the array, otherwise they’ll have the wrong types when sent to the engine.

For testing, I pasted the same text (“Texte”) from the Clipboard.

I even removes the comas from the sql_cmd string. I have a furious need to send that laptop thru the window and take some drink to flood my anger.

I am totally exhausted…

Hi Greg,

You are right; there is even a time value (length of the video). But the fields are declared as strings, so this does not matter.

Here’s what the debugger says for ExecuteSQL:

If that were my Column names, I would get an unknow Column Name (or so) error.

I’ve posted the right syntax for INSERT

I have troubles to understand here. What about avoiding injection ?
Or what you post deals with injection ?

use this

OK, will try.

This worked fine. THANK YOU !