This week's giveaway is in the EJB and other Java EE Technologies forum.
We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line!
See this thread for details.
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 EJB 3 in Action this week in the EJB and other Java EE Technologies 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: 9317
    
109

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: 2476
    
    7

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: 29233
    
138

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?
     
    Similar Threads
    Struts-2 datetimepicker issue...
    Converting String to util.Date and then sql.Date
    Compare two custom date input fields each having 3 inputText fields using validator
    startdate,endDate validations how is it done?
    how to convert a string to date