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 chars \ or / removed from String when added to database into field type VARCHAR? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "chars \ or / removed from String when added to database into field type VARCHAR?" Watch "chars \ or / removed from String when added to database into field type VARCHAR?" New topic
Author

chars \ or / removed from String when added to database into field type VARCHAR?

Annemarie McKeown
Ranch Hand

Joined: Nov 07, 2003
Posts: 47
I have a string which is basically the url to a particaluar file - eg
C:\jakarta-tomcat-3.3.1a\jakarta-tomcat-3.3.1a\webapps\test\uploads\general_info.txt
When I want to add this string into my mySQL database field type VARCHAR,
the characters \ or / are removed and the string contained in the database becomes:
C:jakarta-tomcat-3.3.1ajakarta-tomcat-3.3.1awebappstestuploadsgeneral_info.txt

Why is this? I need to keep the \ chars in because I will use it for reference for user to download in the future?
What do I need to do to keep them in?
Probably something simple like changing the field type?
Some help please.
David Peterson
author
Ranch Hand

Joined: Oct 14, 2001
Posts: 154
Can you post the code that adds the string to the database? It's hard to know what the problem is without seeing the code.
Annemarie McKeown
Ranch Hand

Joined: Nov 07, 2003
Posts: 47
The following gets the String from the previous page:
String location = request.getParameter("location");
ie content is: C:\jakarta-tomcat-3.3.1a\JSP\uploads\file.txt
And prints out on the process page the correct string as inputted.
The following adds the String to the database into field location of type VARCHAR(200):
....
stmt.executeUpdate("insert into fileuploads (location) values('"+location+"')");
When it gets added to the database the entry is C:jakarta-tomcat-3.3.1aJSPuploadsfile.txt
Is it something to do with \ is an escape character in Java?
Do you know how to help?
Annemarie
Christopher Zinn
Greenhorn

Joined: Nov 03, 2003
Posts: 13
Yes, the \ is getting escaped (more likely by your JDBC driver / SQL Server implementation). To get around this, use a PreparedStatement instead of a Statement. Your code would look something like this:

The PreparedStatement method is also much safer; it prevents people from
doing nasty things like entering "';drop table fileuploads" as there filename.
- Chris
Annemarie McKeown
Ranch Hand

Joined: Nov 07, 2003
Posts: 47
I have implemented your code but I get the following error -
java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'insert into fileuploads (location) values('C:\\jakarta-tomcat-3"
seems like it cuts off the rest of the String...doesn't get the ); at the end of the insert statement??
My code is as yours was...
PreparedStatement ps = myConn.prepareStatement("(insert into fileuploads (location) values(?)");
ps.setString(1, location);
ps.executeUpdate();
Have I implemented this wrong? Its getting part of the String location, but not all of it.
the next char in that string would be a "." would it be anything to do with that - I wouldn't think so.
Can you help me fix this?
[ March 01, 2004: Message edited by: Annemarie McKeown ]
[ March 01, 2004: Message edited by: Annemarie McKeown ]
Christopher Zinn
Greenhorn

Joined: Nov 03, 2003
Posts: 13
You are correct, the code should read:

The ( before the insert was a mistake when I typed it in.
 
Consider Paul's rocket mass heater.
 
subject: chars \ or / removed from String when added to database into field type VARCHAR?
 
Similar Threads
Classes12.zip
http://localhost:8080 shows page not diaplyed in browser.
Only 256 chars stored in DB field
Invoking servlets in tomcat
Special character handling