Insert Into....but for other reasons...
Ok, another day and another problem.
I'm attempting to write to a MS Access database.... the Connection works because I am able to read in a recordset and populate a widget listbox. However I'm having an issue with concatenation...
eg:
stem.executeUpdate("Insert into table (strName) Values (strVarName));
The strVarName is a Sring variable that is passed into my setter as a String but when the line executes and I manually query the database nothing appears. However on the other hand if I in close like this 'strVarName' and then execute the line again the strVarName literal is added to the database.
What am I doing wrong here.... I've tried a number of concatenations all with the same end result.
Jim
[756 byte] By [
KTM96a] at [2007-11-27 4:07:05]
What you need to learn to do is use java.sql.PreparedStatement, not merely java.sql.Statement. Statement has issues, and experienced JDBC programmers prefer to use PreparedStatement. http://java.sun.com/docs/books/tutorial/jdbc/basics/prepared.html
With Statement you need to form your SQL query correctly and that means enclosing the values in single quotes (' ' ) if needed like in case of strings or chars, but not in case of numeric fields.
In your case, when it doesn't work, it's because your query would be something like:
insert into table ( mytable ) values ( thisIsTheStringIGet )
where as it should be
insert into table ( mytable ) values ( 'thisIsTheStringIGet' )
I find it very, very helpful to print out the query I'm running in my code for debugging; helps you locate syntactical errors and also lets you copy paste and run the SQL query directly.
Like mentioned above, with PreparedStatement, you'd be using functions that are similar ( the opposite, in fact ) to those of the ResultSet. Methods like setString() and setInt(). What these methods do, is set the value of 'placeholders' that you have in your PreparedStatements. You don't need to mess around with the single quotes etc, since you're specifically calling the correct function for that datatype and asking it to put the value you want in a correct manner into the query.
> Yes, PerparedStatement will save you headaches when> trying to insert strings with embedded quotes, not to> mention inserting dates and times...Lions and Tigers and BLOBs. Yawmark (< Oh, my!)~
> >stem.executeUpdate("Insert into table (strName)
> Values (strVarName));
>
>
> try this :
>
> stem.executeUpdate("Insert into table (' "+strName+"
> ') Values (' "+strVarName+" '));
>
>
> should work , this is how we need to concantenate
Don't use this. It works but is a mess and prone to sql injection attacks. Learn the prepared statement.
Manuel Leiria
>Don't use this. It works but is a mess and prone to sql injection attacks. Learn the prepared statement.
>Manuel Leiria
im in complete agreement with manuel, it is not a good way.
better to opt for prepared statement , but if you insist on using statement , this is the way
Krishna Kanth
