Passing an array of params to SQLExecute

Databases
1

I have a little problem. I’m trying to create a generic database routine that I can pass different dictionaries of data to and it will handle the database submissions for me. This is for iOS by the way.

I have my SQL that looks like this:

INSERT OR REPLACE INTO users (ID, cloud_id, username, ipad_pin, title, firstname, lastname, status, created, modified, archived, access_group_id, expiry, useexpiry) VALUES ( (SELECT ID FROM users WHERE cloud_id = ?1), ?2, ?3, ?4, ?5, ?6, ?7, ?8, ?9, ?10, ?11, ?12, ?13, ?14);

This string is compiled based on a list of fields that are passed to the function. So the number of ?X can change.

I build an array of all the params I wish to pass to the DB (arrParams() as Auto) and execute:

App.datasrc.SQLExecute(sql, arrParams)

I then receive the error:

InvalidArgumentException
Arrays are not valid SQLite types

I notice on the Eddies Electronics example you are passing the params like this:

App.EEDB.SQLExecute(sql, FirstName, LastName, Address, City, State, Zip, Phone, Email, Taxable, ID)

So it is not using an array of values and instead passing each one as a separate variable. I can’t however do this as I don’t know what data I will have to submit or how many items it will be, so I can only submit it as an array.

The documentation for iOS says:

SQLExecute(sqlstatement As Text, ParamArray values() As Auto)

So I’m assuming I am able to submit the params as an array? Or am I missing something? Is the documentation wrong and am I going to have to do this differently?

Any help appreciated, or advice on another way I can do this that works.

2

I’ve managed to do this now using string replacement, however I’d still be interested to know if the documentation is incorrect for the SQLExecute statement.

3

Came across this after trying to do the same thing myself. The problem you’re having is that the parameter array isn’t an array you can dynamically create and pass in. When you’re passing in arrParams you’re saying use this array for the first variable not use the contents of this array for the paramarray.

I’ve added Feedback Request to hopefully get some method of being able to specify which variable value you’re adding. <https://xojo.com/issue/40319> Not holding my breath for it.

The way it works in iOS right now basically mean I can throw ActiveRecord out and start over. If you make the extrapolation that the new database frameworks will act the same way AR is dead in its current incarnation.

4

The method is declared as:

SQLExecute(sqlstatement As Text, ParamArray values() As Auto)

values is not an array, it is a parameter array, which means you can pass a variable amount of variables as arguments and within the method they are then treated as an array.

The easiest would be if the Xojo engineers would overload the method in one of the next releases with this:

SQLExecute(sqlstatement As Text, values() As Auto)

Listbox.AddRow works the same: two overloaded methods, one taking a ParamArray, one an Array.

5

You said it way better than I did. :slight_smile:

6

I have a window where the user can update one or more than one fields and then click on the “save Changes” button. I did an array which is filled conditionnally if there’s been a change in the field which is empty at the beginning. I have a routine which first checks if the data is ok (example a date is valid , etc) then I call this routine. Hope it helps.

dim q As string
dim i, j as integer
dim w as string
dim query(-1) as string
MessageTextArea.text = “”

query.Append "UPDATE horaire_main set "
query.Append " date_modified= NOW() "
query.Append " id_admin= ‘3’ "

if FindLocDepCC1.IsSelected then // on vrifie s’il y a une succ de slectionne
query.append (" id_location_dept = ‘" + str (FindLocDepCC1.FindLocDepId) + "’ ")
end if

if FindLphCCSide1.IsSelected then // on vrifie s’il y a un pharmacien de slectionn
query.append (" id_user = ‘" +str( FindLphCCSide1.FindLphID) + "’ ")
end if

if len(ModDateTextField.text) = 10 then
query.append ( " schedule_date = ‘" +ModDateTextField.text + "’ ")
end if

if len(ModTimeInTextField.text) = 5 then
query.append ( " schedule_time_in = ‘" +ModTimeInTextField.text + ":00’ ")
end if

if len(ModTimeOutTextField.text) = 5 then
query.append ( " schedule_time_out = ‘" +ModTimeOutTextField.text + ":00’ ")
end if

if ModStatusPopUp.ListIndex > 0 then
query.append ( " status = ‘" + ModStatusPopUp.Text + "’ ")
end if

if len(ModRemarqueTextField.text) > 0 then
w=ModRemarqueTextField.text.ConvertEncoding(Encodings.utf8)
w = w.ReplaceAll("’","’’")
query.append ( " remark = ‘" + w + "’ ")
end if

if len(ModRemarqueAdminTextField.text) > 0 then
w=ModRemarqueAdminTextField.text.ConvertEncoding(Encodings.utf8)
w = w.ReplaceAll("’","’’")
query.append ( " remark_admin= ‘" + w + "’ ")
end if

query.append ( " WHERE ID = ‘" + IdTextField.text +"’ ;")
j = ubound(query)

if j > 2 then
q = query(0) + query(1)
for i = 2 to (j-1)
q = q +" , " + query(i)
next
q = q + query(j)
if app.MyScheduleDB.Connect then
app.MyScheduleDB.SQLExecute(" set names utf8 ;")
app.MyScheduleDB.SQLExecute(q)
if app.MyScheduleDB.error then
MessageTextArea.text = MessageTextArea.text + str(app.MyScheduleDB.ErrorCode) + " " + app.MyScheduleDB.ErrorMessage
else
app.MyScheduleDB.Commit
MessageTextArea.text = MessageTextArea.text + " Modification russie dans Horaire_main ! " + chr(13)
end if
RefreshInfo(me.IdTextField.text)
end if
end if

Jean-Maurice Vandergoten

7

I may have discovered a rather ugly (and, I hope, very temporary) solution to the problem of passing an array to the SQLExecute method.

Some background: I am working on a little iOS app. Need to collect some simple info. I create the sql on the fly. At the same time I create an array of values (vals()).

The sql looks like:
insert into unit (garage,owner,permit,unitid,unitnum) values (?,?,?,?,?) // it’s in a variable called sq

My call to SqlExecute looks like this:
self.db.SQLExecute(sq,vals(0),vals(1),vals(2),vals(3),vals(4),vals(5),vals(6),vals(7),vals(8),vals(9),vals(10),vals(11),vals(12))

What I did is simply append nils to the end of the array until I reached 12 (12 because I was running out of screen space).

I have now succeeded in inserting two rows. So it seems to work. Obviously, this is not an ideal solution, but it will let me move forward.

Please, Xojo people, let us pass an array do sqlExecute.

-Bob Gordon

8

make the string yourself, concatening what you need and pass that string to sqlexecute
you can even make a method with your array as input, and the good formatted string ready for sqlexecute as output.

9

Jean-Yves,

Making a string (or text) is not the issue. There are a number of advantages to using the parameter approach (some of which do not apply to the iOS environment).

  1. It’s more secure.
  2. One does not have to deal with embedded apostrophes (e.g. Bob’s Bakery).
10

I offered an elegant solution to this here:

https://forum.xojo.com/conversation/post/242151

It will let you pass an array of any size.