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 How to handle comma in the text field? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to handle comma in the text field?" Watch "How to handle comma in the text field?" New topic
Author

How to handle comma in the text field?

Rekha Pai
Ranch Hand

Joined: Oct 30, 2008
Posts: 35
Hi,

While entering an insert query in pgsql database using JDBC connection,

I get SQL error when I use text for a database field text with commas included.

How can I resolve this issue? Please, help.

Regards,
Rekha

Suppose database table privilege_leave has some fields like (name, fromdate, todate, leavetotal, reason).

The fields are
name varchar(50)
fromdate date
todate date
leavetotal double
reason text

If I write the query taking the html form elements :

insertQuery = " insert into privilege_leave values(\'"+name+"\',\'"+fdate+"\',\'"+tdate+"\',"+total+",\'"+reason+"\')";

If the reason variable contains a comma within, it gives error.

Regards,
Rekha
Jaikiran Pai
Marshal

Joined: Jul 20, 2005
Posts: 10288
    
168

What does your query look like? Have you enclosed the value in single quotes?

[My Blog] [JavaRanch Journal]
hem raj
Greenhorn

Joined: Apr 07, 2010
Posts: 11
put your text value between single quotes....


eg.. insert into table(fieid) values('some text,text');
Rekha Pai
Ranch Hand

Joined: Oct 30, 2008
Posts: 35
Suppose database table privilege_leave has some fields like (name, fromdate, todate, leavetotal, reason).

The fields are
name varchar(50)
fromdate date
todate date
leavetotal double
reason text

If I write the query taking the html form elements :

insertQuery = " insert into privilege_leave values(\'"+name+"\',\'"+fdate+"\',\'"+tdate+"\',"+total+",\'"+reason+"\')";

If the reason variable contains a comma within, it gives error.

Regards,
Rekha
Sandeep Sanaboyina
Ranch Hand

Joined: Dec 14, 2009
Posts: 72
Why don't you use a PreparedStatement. It will solve your problem and it's also a proper way than preparing a string as you did.


They say you have to be the first, the best or different. I say, is it too much to ask for all three.
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2516
    
  10

Sandeep Sanaboyina wrote:Why don't you use a PreparedStatement. It will solve your problem and it's also a proper way than preparing a string as you did.
+1. That is the best advise one can give in this situation.


OCUP UML fundamental and ITIL foundation
youtube channel
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31057
    
232

You should absolutely use a PreparedStatement:



It solves three problems you have:
  • 1) allows commas
  • 2) lets you call stmt.setDate() to insert the dates rather than formatting them in a database specific way
  • 3) Prevents SQL Injection. Entering a comma is what a normal user will do. A hacker will enter ' sql here; ' and try to attack your database. PreparedStatements prevent that too


  • [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    Rekha Pai
    Ranch Hand

    Joined: Oct 30, 2008
    Posts: 35
    Hi,

    Thanks a lot for your valuable suggestion!!

    Regards,
    Rekha
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: How to handle comma in the text field?