aspose file tools*
The moose likes JDBC and the fly likes Update query Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Update query" Watch "Update query" New topic
Author

Update query

Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
While updating the rows,I am using the query in JDBC:

....
//Get the updatedStrings,str1 and str2.then
Statement st = con.createStatement();
st.executeUpdate("UPDATE T20 SET C123 = "+"'"+str1+"'"+"WHERE C1 = '"+str2+"'");
Here in query,str1 sometimes contains the characters ' or ". So the I will get Incorrect Syntax exception.
How to handle these characters? Using \' or \" in query?


Namma Suvarna Karnataka
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Use a PreparedStatement.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Damanjit Kaur
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
but perhaps PreparedStatement also may not work as there also one needs to use setString, while passing the String.

Another way might be to process the String first using StringBuffer and insert /' for ' and /" for " whenever there is occurrence of ' or ".
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


but perhaps PreparedStatement also may not work as there also one needs to use setString, while passing the String.

Why do you believe it would not work? One of the reasons you use a PreparedStatement is parameter binding which removes the need to escape characters in SQL.
Damanjit Kaur
Ranch Hand

Joined: Oct 18, 2004
Posts: 346
Ok. I didn't knew about this removal of escape character at the time of parameter binding.
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
Thanks.The column value which I m trying to modify is of type "text" and not varchar in SQL 2000.I m getting an error "DBMS returned unspecified error".PreparedStatement.setString(int,str) says:
"The driver converts this to an SQL VARCHAR or LONGVARCHAR value (depending on the argument's size relative to the driver's limits on VARCHAR values) when it sends it to the database. "
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
When updated column value is of small length,say 500 characters,it works but gives the above error when length exceeds 20,000 characters.But when I use just Statement instead of PreparedStatement then it updates.
Thanks.
[ April 04, 2005: Message edited by: Arjunkumar Shastry ]
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

The error message tells you what's causeing this. Not mich you can do about it I'm afraid. You might try a different driver (google for jTDS) - if you are using Microsoft's own driver, I'd do this anyway. Its not production-ready, despite what Microsoft might attest.
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
Thanks.As datatype is of text,I think I will try using first,rs.getCharacterStream(),update it and then ps.setCharacterStream(int,reader,length).I m not sure whether this will work.As discussed,
a)If I use,PreparedStatement for column values of bigger length doesn't work.
b) If I use Statement,I need to do \' conversion.Not sure how this will work if value contains ".
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
As Microsift Driver behaving "weird" I downloaded jTDS.In a program I was able to load the driver but during database connection,I get the following error:
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded
My Database URL is following:
String connectionStr = "jdbc:jtds:sqlserver://"+serverName+":1433/"+dataBase+";user = "+login+";password = "+pwd;
I did a search on web,but could not find info about SSPI library.
Thanks

Sirish Reddy
Greenhorn

Joined: Sep 16, 2004
Posts: 7
Hi!
Java won't support Operator overloading why?Can you tell me the reason ?
Regards\Sirish Reddy.,
Sirish Reddy
Greenhorn

Joined: Sep 16, 2004
Posts: 7
Hi!
Java won't support Operator overloading why?Can you tell me the reason ?
Regards\Sirish Reddy.,
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
We are discussing about jTDS here.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

SSPI is the Security Support Provider Interface I think. jTDS (and MS's driver) don't include this, since it is native code, which makes me think there is something wrong with your SQL Server setup or install. jTDS seems to be looking for native code which isn't there.
Arjunkumar Shastry
Ranch Hand

Joined: Feb 28, 2005
Posts: 986
I am back to using MicrosoftJdbc Driver.It really behaves in odd fashion.Now it seems to be working without any change in configuration of DB or java code. I am using preparedStatment.setCharacterStream(int,Reader).
It works well when Reader contains less than 2000 characters.Otherwise it gives an error.
Thanks

[ April 06, 2005: Message edited by: Arjunkumar Shastry ]
Kash Mhai
Greenhorn

Joined: Apr 01, 2005
Posts: 29
I somehow remember that varchar2 supports upto 2000 characters. Anything bigger might need CLOBs.


-------------<br />There are wheels within wheels...<br />P.G Wodehouse
David Demner
Greenhorn

Joined: Oct 10, 2001
Posts: 5
Hi,

You get the SSPI error if you don't replace "Username" in your Microsoft driver connection URL with "User" for the jTDS connection URL.

David
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2510
    
  10

More info on solving the SPPI error and jTDS:

This person has documented the solution:
Change your connect string to:
jdbc:jtds:sqlserver://khrcs2:1622/Mail_List;instance=bkupexec;user=XXX;password=YYY

or set user/pwd in con = DriverManager.getConnection(dbURL,"XXX","YYY");


To make windows authentication work, You need to put the ntlmauth.dll (included in the jTDS zip, subdir SSO) on your system:

from README.SSO:

In order for Single Sign On to work, jTDS must be able to load the native
SPPI library (ntlmauth.dll). Place this DLL anywhere in the system path
(defined by the PATH system variable) and you're all set.
Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Update query