File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes one more question about inserting data Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "one more question about inserting data" Watch "one more question about inserting data" New topic
Author

one more question about inserting data

Candy Bortniker
Ranch Hand

Joined: Mar 17, 2003
Posts: 123
I am trying to take data from a GUI and enter it into the table in my database. This is working except for some formatting problems. I am able to get the string info just fine but when I try to get the float values and dates all I get is zeros.
Here's what I have:

PurchasePrice and PurchaseDate are what I'm having trouble with.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Hi Candy,
If you would be so kind as to provide the data types for the "Symbol", "SharesBought", "PurchasePrice" and "PurchaseDate" columns (in the "Purchase" [database] table), I may be able to help you.
Also, if you could tell me what database (and JDBC driver) you are using, that would also help!
Cheers,
Avi.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
It looks like you are passing the data as all strings, and this would not work if your database has them defined as numbers.
Also, if you are using Oracle, usually the TO_DATE function is used to convert the string to a date. Although you could pass the default database date formatted which was selected at installation time.
Give database and metadata info to clarify problem.
Dan
Candy Bortniker
Ranch Hand

Joined: Mar 17, 2003
Posts: 123
Symbol is Varchar, Shares and PurchasePrice are floats and PurchaseDate is Date. The driver I'm using (I think it is the driver) is MYSQL. Or that is the database and I don't know what the driver is.
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1134

Hi Candy,
As Daniel has mentioned, you should probably convert the variables in your java code to data types that map correctly to the database table columns.
In any case, in SQL, single quotes delimit string literals only -- numeric literals should not be enclosed in single quotes, so you need to remove the single quotes (in your SQL string) enclosing the values for "Shares" and "PurchasePrice".
Regarding the "PurchaseDate" column, you may be able to use the 'escape syntax' as defined in the JDBC specification. More details are available here:
http://java.sun.com/j2se/1.4.1/docs/guide/jdbc/getstart/statement.html#999472
Note that if you use the 'escape syntax', then your "PurchaseDate" string format needs to be 'yyyy-mm-dd', example:
2003-04-30
which is April 30, 2003
I don't work with MySQL -- I work with Oracle -- so the below code is merely to demonstrate how (I think) you should modify your code:
[Of-course, this code is untested!]

Note that I believe you should be able to find more information in the documentation for MySQL.
Hope this helps.
Good Luck,
Avi.
DC Dalton
Ranch Hand

Joined: May 28, 2001
Posts: 287
Hey Candy... MySQL will ALWAYS return zeros on any data that is non numeric or surrounded by 's going into a numeric field. Ive been arguing with it for years when I make the same mistake. I would parse & validate the numerics before slamming them into the table & MAKE SURE there are no ticks around them...MySQL doesnt like that!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: one more question about inserting data