• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Ron McLeod
  • Liutauras Vilda
  • Bear Bibeault
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Devaka Cooray
Saloon Keepers:
  • Tim Moores
  • Tim Holloway
  • Piet Souris
  • salvin francis
  • Stephan van Hulst
Bartenders:
  • Frits Walraven
  • Carey Brown
  • Jj Roberts

how to display a SQL query ?

 
Ranch Hand
Posts: 563
Google Web Toolkit Eclipse IDE 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
 
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.
 
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.
 
Java Cowboy
Posts: 16084
88
Android Scala IntelliJ IDE Spring Java
  • 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: 563
Google Web Toolkit Eclipse IDE 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 ]
 
reply
    Bookmark Topic Watch Topic
  • New Topic