aspose 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
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: 10102
    
165

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: 2500
    
    8

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
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

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
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: How to handle comma in the text field?