• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to display a SQL query ?

 
Celinio Fernandes
Ranch Hand
Posts: 549
Eclipse IDE Google Web Toolkit Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Rancher
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
like this.
 
Anand Karia
Ranch Hand
Posts: 156
Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Java Cowboy
Saloon Keeper
Posts: 15627
46
Android IntelliJ IDE Java Scala Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 549
Eclipse IDE Google Web Toolkit Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic