how to display a SQL query ?

[Celinio Fernandes]

Hi,
I am using JDBC to insert data into a table.
A bit like this:
Can someone tell me what is wrong with this insert statement.
String sql = "INSERT INTO IT_REQUEST (REQUESTID, EMPLOYEEID REQUESTDATE, PRIORITY, TYPE, DESCRIPTION, STARTDATE, COMPLETED, STATUS, MISEMPLOYEE, ACTIVE, CREATEDDATE, CREATEDBY, MODIFIEDDATE, MODIFIEDBY, DELETEINDICATOR, MODIFIEDINDICATOR, EMPNAME, EMPEMAIL) VALUES ( '"+ requestIdInt +"', '"+ zero +"', '"+ reqDate +"', '"+ zero +"', '"+ reqType +"', '"+ reqDesc +"','"+ reqDate +"', '"+ n +"', '"+ open +"', '"+ assignto +"', '"+ y +"', '"+ reqDate +"', '"+ vgm +"', '"+ reqDate +"', '"+ vgm +"', '"+ n +"', '"+ n +"', '"+ empName +"', '"+ empEmail +"')";

ps = con.prepareStatement(sql);

ps.setInt(1,8);
ps.setString(2,blabla);
ps.setString(3,"bla")
etc

I sometimes get the error :"value too large for column"
In order to quickly determine which value is too large,
I would like to display the full SQL insert request so I can run it in Toad.

Does anyone know how to display / get that query ?

Thanks ver much

[David O'Meara]

There are a few options, but if you're using PreparedStatements the best thing I found for debugging them is the DebuggableStatement. You can find a link to it if you search this forum.

[David O'Meara]

like this.

[Anand Karia]

Hi Max longbeach!

What i understand by your question is that while typing data you make some of fields longer than its defined limit.

Like if your emp_name column size is 10 and you are inserting data that is more then it size 10 then it shows such kind a error.

So what i suggest you to know exactly which field is longer, you can find the same through following statement.

System.out.println("EmpName= "+txtEmpName.getText().length());

So you better make number of output like this and check it with your table structure you will find the solution.

Good Luck.

[Jesper de Jong]

String sql = "INSERT INTO IT_REQUEST (REQUESTID, EMPLOYEEID REQUESTDATE, PRIORITY, TYPE, DESCRIPTION, STARTDATE, COMPLETED, STATUS, MISEMPLOYEE, ACTIVE, CREATEDDATE, CREATEDBY, MODIFIEDDATE, MODIFIEDBY, DELETEINDICATOR, MODIFIEDINDICATOR, EMPNAME, EMPEMAIL) VALUES ( '"+ requestIdInt +"', '"+ zero +"', '"+ reqDate +"', '"+ zero +"', '"+ reqType +"', '"+ reqDesc +"','"+ reqDate +"', '"+ n +"', '"+ open +"', '"+ assignto +"', '"+ y +"', '"+ reqDate +"', '"+ vgm +"', '"+ reqDate +"', '"+ vgm +"', '"+ n +"', '"+ n +"', '"+ empName +"', '"+ empEmail +"')";

ps = con.prepareStatement(sql);

It looks like you don't understand how to use PreparedStatment properly.

Instead of concatenating all the values in the SQL itself, you should use question marks and use the set...() methods of PreparedStatement to set the parameters:

String sql = "INSERT INTO IT_REQUEST (REQUESTID, EMPLOYEEID REQUESTDATE, PRIORITY, TYPE, DESCRIPTION, STARTDATE, COMPLETED, STATUS, MISEMPLOYEE, ACTIVE, CREATEDDATE, CREATEDBY, MODIFIEDDATE, MODIFIEDBY, DELETEINDICATOR, MODIFIEDINDICATOR, EMPNAME, EMPEMAIL) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

ps = con.prepareStatement(sql);

ps.setString(1, requestIdInt);
ps.setString(2, zero);
ps.setString(3, reqDate);
// ...etc.
[ September 02, 2005: Message edited by: Jesper de Jong ]

[Celinio Fernandes]

hi,
I copy-pasted this preparedStatement from an example I found, but i did not notice it was a bad example.
But anyways my question is about tracing an SQL query.
I am going to use the DebuggableStatement package to do so.
Thank you very much for your help.
Especially David O'Meara , you gave me a great link
[ September 02, 2005: Message edited by: Max longbeach ]