MySql array (workarounds?)

[jay vas]

Hi Guys : I wanted to insert a hashtable of name value pairs into mysql.

I know one way of doing this is to do

insert into myTable values (1,'jay'), (2,'jim') , etc...

but Id rather have a mysql stored procedure handle the creation
of this statement (so that
I can avoid having sql in my java code).

Any ideas for a work around ?

Ideally, Id like to insert as a String and have an SP handle the parsing...

call SP_param_insert('1=jay;2=jim;')

[Jeanne Boyarsky]

Jay,
If you really want to use a stored procedure, you could pass two arrays to the stored proc: one with the names and one with the values. Not sure if mySql supports this though.

I think it would be clearer to do this in Java though. You could use a PreparedStatement:
nsert into myTable values (?, ?)

If you use JDBC's batch update, you can still do all the updates in one server roundtrip. Also, you can get the SQL out of the Java code by a putting it in a property file.

[Bear Bibeault]

"jay v",

There aren't many rules that you need to worry about here on the Ranch, but one that we take very seriously regards the use of proper names. Please take a look at the JavaRanch Naming Policy and adjust your display name to match it.

In particular, your display name must be a first and a last name separated by a space character, and must not be obviously fictitious.

Thanks!
bear
JavaRanch Sheriff