Win a copy of Pro Spring MVC with WebFlux: Web Development in Spring Framework 5 and Spring Boot 2 this week in the Spring forum!
  • 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
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

one more question about inserting data

 
Ranch Hand
Posts: 123
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 276
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1143
1
Eclipse IDE Oracle Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 287
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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!
 
Get me the mayor's office! I need to tell him about this tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic