one more question about inserting data

Candy Bortniker

Ranch Hand

Posts: 123

posted 14 years ago

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:

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

Posts: 276

posted 14 years ago

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

Posts: 123

posted 14 years ago

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.

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

Posts: 287

posted 14 years ago

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!