• 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

can you put multiple words in a database by using one statement

 
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello everyone
just a quick question.
my string holds the words hello, how, are, you
I want all those values to go into one column name (FIRSTNAMES).
now can i insert them into a sql database by using one preparedstatement?
thanks for your time
ben
 
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Sure,
Assuming you have a Connection named con:

This is also assuming the field will allow that many characters. If not, you'll get an SQLException.
[ January 09, 2003: Message edited by: Eric Fletcher ]
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello thanks for the reply
but i want the words to into the same column name but diffrent fields. is this possible?
ben
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by ben riches:

but i want the words to into the same column name but diffrent fields. is this possible?


hmmm, I'm not sure what you mean, a column IS a field in a DB. It sounds to me like you want to be able to store them in the same place but be able to seperate them if you need to? The best way to do that would probably be to store it in one field with a delimiter and parse it when you select it from the database. The other recourse you have would be to parse it first and store it in different fields in the DB, like FIRSTNAME1, FIRSTNAME2, etc. , but there is no way I know of to have duplicate fields names in any DB.
Sorry if I haven't answered your question, I'm still not 100% sure what you're asking.
E
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello eric cheers for you help
But i will try and explain a bit more agian.
lets say i have a column name called FirstName,
then i want to put 4 values in that column but in diffrent rows but the 4 values are strored in a string.
is this possible?
I hope you understand a bit more.
thanks
ben
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
OK! I got it now!
Yes that is possible, in fact that is one of the primary intended uses of PreparedStatement, to optimize performance of repeated updates. In your case, you'll have to tokenize the String and insert the values into the DB, you could do it like so:

That should insert four new rows in the DB with the different words in the string as the respective values for FIRSTNAMES.
HTH,
E
[ January 10, 2003: Message edited by: Eric Fletcher ]
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello eric thanks for your replys
that last solution worked brilliantly
but how would i put two column names in (FIRSTNAME, LASTNAME)VALUES(?, ?);
and set there values up the same way as before?
thanks for your time eric
ben
[ January 10, 2003: Message edited by: ben riches ]
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Then just use the stmt.setXXX(number, value) syntax for each question mark in the values section, i.e.:

etc, etc, as you go down the list of needed fields.
Check out the javadoc for PreparedStatement, it will likely answer a lot of questions for you.
PreparedStatement
HTH,
E
[ January 10, 2003: Message edited by: Eric Fletcher ]
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hello again
I am having problems with how to use the setXXX method
And also i want LASTNAMES to have 4 diffrent values from FIRSTNAMES is this possible?
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by ben riches:
Hello again
I am having problems with how to use the setXXX method


The method itself is not "setXXX", there are several different setter methods for PreparedStatement, so I was just using "setXXX" as generic notation. For different datatypes, substitute the datatype for the "XXX", i.e.
stmt.setString(1, "String");
or
stmt.setInt(1, 4);
In your case you only need to worry about String.
Sorry to confuse you, be sure to look at the javadoc on the link I posted before, it lists all the methods for PreparedStatement.

Originally posted by ben riches:

And also i want LASTNAMES to have 4 diffrent values from FIRSTNAMES is this possible?


I'm not sure what you mean...
You can change the query to add another value, then just set the value as we did before, one value will be for the firstname, the second value will be for the second name. If you have them in seperate comma-delimited strings, you will probably have to parse them into seperate collections(i.e. ArrayLists), then loop through the collections setting the values and doing the inserts, just like we did when we tokenized the string before. Is that what you mean?
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
yes eric that is what i wanted to know
thanks for yr time
ben
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No problem.
Good luck.
E
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello again eric right here i go
i have a string with 30 values inside it is it possible to get the first 10 values and put them in a preparedstatement then get the next 10 values and then the next 10?
for example
INSERT INTO table_name (FIRSTNAME, MIDDLE NAME, LASTNAME) VALUES (first 10 values, second 10 values, third 10 values)
thanks again
ben
[ January 13, 2003: Message edited by: ben riches ]
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have to set the values for each update individually, so what you suggest wouldn't work. Most likely you would have to parse out the values into seperate ArrayLists, then do something like:

This is also assuming that the ArrayLists are the same size and filled respectively, i.e. the firstname at index 0 is matched with the middle and last names at index 0 in the other Lists.
I will say the way the data is set up makes your life tougher when it comes to mapping it to a DB record. Is there a reason you set up the data this way, or is this simply a learning exercise?
 
ben riches
Ranch Hand
Posts: 126
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello eric
yes there is a reason i have to set it up like this
it is because i have to get every first name out of a csv file and then every lastname. I have done everything else with my code, It was just the prepared statement i was having problems with.
thanks eric
p.s
if one of the arraylists has a empty string in it would it enter a blank space to the database?
 
Eric Fletcher
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by ben riches:
if one of the arraylists has a empty string in it would it enter a blank space to the database?


Depends on which DBMS you are using. I know in Oracle an empty string is treated as a null(which is a gross violation of the SQL standard, but I digress). I'm most familiar with Oracle, so I'm not sure(and would actually be surprised) if that is the case for other DBMSs.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic