This week's book giveaway is in the Mac OS forum.
We're giving away four copies of a choice of "Take Control of Upgrading to Yosemite" or "Take Control of Automating Your Mac" and have Joe Kissell on-line!
See this thread for details.
The moose likes JDBC and the fly likes MySql array (workarounds?) Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "MySql array (workarounds?)" Watch "MySql array (workarounds?)" New topic
Author

MySql array (workarounds?)

jay vas
Ranch Hand

Joined: Aug 30, 2005
Posts: 407
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30586
    
154

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61310
    
  66

"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


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
 
GeeCON Prague 2014
 
subject: MySql array (workarounds?)