Win a copy of The Java Performance Companion this week in the Performance forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Update query

 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Use a PreparedStatement.
 
Damanjit Kaur
Ranch Hand
Posts: 346
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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
Posts: 346
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok. I didn't knew about this removal of escape character at the time of parameter binding.
 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
Java won't support Operator overloading why?Can you tell me the reason ?
Regards\Sirish Reddy.,
 
Sirish Reddy
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
Java won't support Operator overloading why?Can you tell me the reason ?
Regards\Sirish Reddy.,
 
Arjunkumar Shastry
Ranch Hand
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
We are discussing about jTDS here.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 986
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 29
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I somehow remember that varchar2 supports upto 2000 characters. Anything bigger might need CLOBs.
 
David Demner
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2588
11
C++ Linux Netbeans IDE
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic