How to pass variables to DBParameterized #values:# ?

[mstram]

I've been playing with Dbconnect to talk to mysql, and have it
basically working, but can't figure out the syntax on how to pass a
variable to the insert statement.

Have tried :

** c1, c2 are varchar(20)) ***

n1 := 'Mike'
n2 := 'Smith'

ps := d prepare: 'insert into t1 (c1,c2) values (?, ?)'

--- crashes to debugger *
ps values:#(#n1 #n2)

-- 2) crash to debugger
ps values:#('#n1 #n2')

ps values:#('#n1' '#n2')

ps values:#(n1 n2)

ps exec

[Christopher J. Demers]

mstram wrote:
> I've been playing with Dbconnect to talk to mysql, and have it
> basically working, but can't figure out the syntax on how to pass a
> variable to the insert statement.
>
> Have tried :
>
> ** c1, c2 are varchar(20)) ***
>
> n1 := 'Mike'
> n2 := 'Smith'
>
> ps := d prepare: 'insert into t1 (c1,c2) values (?, ?)'
>
> --- crashes to debugger *
> ps values:#(#n1 #n2)
>

...

You need to set the column types ( with paramCols: ). You also need to
pass a collection of values.

Try:
ps values: (Array with: n1 with: n2).

This link has a good example:
http://www.mimuw.edu.pl/~sl/teaching/00_01/Delfin_EC/DatabaseConnection/DatabaseConnection.htm

I found the DBConnection a little clunky to work with using pre-compiled
statements. These days I prefer to use the ADO classes for database stuff.

Chris

[Andy Bower]

Mike,

> I've been playing with Dbconnect to talk to mysql, and have it
> basically working, but can't figure out the syntax on how to pass a
> variable to the insert statement.

To be honest, I've forgotten most of what I knew about SQL but here goes.

>
> Have tried :
>
> ** c1, c2 are varchar(20)) ***
>
> n1 := 'Mike'
> n2 := 'Smith'
>
> ps := d prepare: 'insert into t1 (c1,c2) values (?, ?)'

So far so good, I think.
The first thing to do is to read the Preparing SQL for Later Execution
section in the docs. It can be found here:

http://www.object-arts.com/docs/preparingsqlforlaterexecution.htm

Notice the part about using paramCols: to tell the prepared expression
the types of the missing columns. This is the bit that I think you are
missing. As the docs say, the easiest way to parameterize the columns is
to query the types from the database itself. In your case this should work:

pcols := (d columns: 't1') select: [:col |
col name='c1' or: [col name='c2']].
ps paramCols: pcols.

>
> --- crashes to debugger *
> ps values:#(#n1 #n2)
>
> -- 2) crash to debugger
> ps values:#('#n1 #n2')
>
> ps values:#('#n1' '#n2')

The above three are wrong and make me think that perhaps you need to
revise what Symbols (#xxxx) are in Smalltalk and how they are used. The
following is almost correct...

>
> ps values:#(n1 n2)

except that Smalltalk doesn't allow you to create an Array at runtime
using variables and the #(a b) form. This is for compile-time (literal)
arrays only. What you need to do is dynamically create the array like this:

array := Array with: n1 with: n2.
ps values: array.

Of course you can skip the intermediate step and use:

ps values: (Array with: n1 with: n2).

> ps exec

Then executing the statement like this should work.

Note: The (Array with: n1 with: n2) form will only work when there is an
appropriate #with:with: method defined for Array. Take a look at the
class side of Array in the class browser. You will see that the most
parameters you can pass in this way is 5 using
#with:with:with:with:with:. If you need more than this then you can
dynamically build an OrderedCollection and convert it to an Array.

ps values: (OrderedCollection new
add: n1;
add: n2;
add: n3;
add: n4; ...
add: nX;
yourself) asArray.

I hope this helps.

Best regards

Andy Bower
Object Arts Ltd

[mstram]

Chris,

Thanks, I saw that link, but the values passed were hard coded :

p values: #(11111 'TOM' 'N' nil nil nil 'PROF' '2267' ).
p exec.

[mstram]

Andy,

. Thanks for that great explanation !

The Array with ... was the missing piece !
(maybe this example should be added to the documentation :) )

Mike