f



How to pass variables to DBParameterized #values:# ?

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
0
mstram
11/12/2009 11:00:56 PM
comp.lang.smalltalk.dolphin 3769 articles. 0 followers. Post Follow

4 Replies
310 Views

Similar Articles

[PageSpeed] 23

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
0
Christopher
11/12/2009 11:53:31 PM
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
0
Andy
11/12/2009 11:56:57 PM
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.

Christopher J. Demers wrote:
> 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
0
mstram
11/13/2009 12:58:28 AM
Andy,

.. Thanks for that great explanation !

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

Mike

Andy Bower wrote:
> 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
0
mstram
11/13/2009 4:55:04 PM
Reply: