• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

MySql array (workarounds?)

 
Ranch Hand
Posts: 407
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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;')
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
"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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic