• 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
  • Bear Bibeault
  • Ron McLeod
  • Jeanne Boyarsky
  • Paul Clapham
Sheriffs:
  • Tim Cooke
  • Liutauras Vilda
  • Junilu Lacar
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • fred rosenberger
  • salvin francis
Bartenders:
  • Piet Souris
  • Frits Walraven
  • Carey Brown

Error in executeQuery() on callableStatement

 
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Please somebody help me. I am unable to figureout what the problem is.
I am trying to run a Stored-Procedure and get the results in the resultSet.
I am getting the following error
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated.
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(Unknown Source)
at TracerFTP.FileWriteHelper.writeToFile(FileWriteHelper.java:34)
at TracerFTP.FileWriteHelper.main(FileWriteHelper.java:14)

Here is my code


SQLConnection = getSQLServConnection();
String sql = "{call pr_TRM_WeeklyTimeToMetrix}";
CallableStatement stproc_stmt = SQLConnection.prepareCall(sql);
stproc_stmt.executeQuery();
rs = stproc_stmt.getResultSet();


private Connection getSQLServConnection(){
Connection connection = null;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
String url = "jdbc dbc:TestDB";
connection = DriverManager.getConnection(url,"userid", "password");
} catch(Exception e){
e.printStackTrace();
)
return connection;
}


Here is my storedProcedure. I am able to see the results fine when I run the stored procedure seperately.


CREATE PROCEDURE dbo.pr_TRM_WeeklyTimeToMetrix
AS

/* Setup tblReporting */

CREATE TABLE #tblReporting (
Customer_Number VARCHAR(9),
Team_ID VARCHAR(10),
Employee_ID VARCHAR(12),
First_Name VARCHAR(12),
Last_Name VARCHAR(12)
)


INSERT INTO #tblReporting
SELECT DISTINCT tbl_Account_Team.Customer_Number, Team_Resource.Team_ID,
Resource.Employee_ID, Resource.First_Name, Resource.Last_Name
FROM tbl_Account_Team
INNER JOIN (Team_Resource
INNER JOIN Resource
ON Team_Resource.Employee_ID = Resource.Employee_ID)
ON tbl_Account_Team.Team_ID = Team_Resource.Team_ID
WHERE tbl_Account_Team.Customer_Number LIKE 320



/* Run query to return data */
SELECT Event_Instance.Application_ID,
Timesheet_History.Task_ID,
Timesheet_History.Employee_ID,
Timesheet_History.Week_Of,
Timesheet_History.Sunday,
Timesheet_History.Monday,
Timesheet_History.Tuesday,
Timesheet_History.Wednesday,
Timesheet_History.Thursday,
Timesheet_History.Friday,
Timesheet_History.Saturday,
Timesheet_History.Status_ID,
Timesheet_History.Labor_Code
FROM ((((((Timesheet_History LEFT JOIN Event_Instance ON Timesheet_History.Event_ID = Event_Instance.Event_ID)
LEFT JOIN tbl_Corporate_Job ON Timesheet_History.Corporate_Job_ID = tbl_Corporate_Job.Corporate_Job_ID)
LEFT JOIN Team ON Timesheet_History.Team_ID = Team.Team_ID)
LEFT JOIN Resource ON Timesheet_History.Employee_ID = Resource.Employee_ID)
LEFT JOIN Task_Detail ON (Timesheet_History.Event_ID = Task_Detail.Event_ID)
AND (Timesheet_History.Task_ID = Task_Detail.Task_ID)
LEFT JOIN Account ON tbl_Corporate_Job.Owning_Account = Account.Customer_Num)
LEFT JOIN Application ON Event_Instance.Application_ID = Application.Application_ID)
WHERE (--(Timesheet_History.Employee_ID like @UserID) AND
(Timesheet_History.Week_Of=CAST(DATEADD(wk, DATEDIFF(wk,0,getdate()), -8) AS smalldatetime)));
GO

Thank You,
 
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Without going into your code much "String or binary data would be truncated" usually implies that you are passing a value to a column that is larger (in number of characters) then the column can hold. I would compare the table column sizes with the data you are trying to insert. 9-12 is pretty small for text data and IDs are often stored as integers (although not required by any means)
 
Surya Indukuri
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Scott,

I am just doing a select. I am not doing any inserts or updates.
 
Marshal
Posts: 25682
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Originally posted by Surya Indukuri:
I am just doing a select. I am not doing any inserts or updates.

But the stored procedure you posted has an INSERT INTO statement in it.
 
Scott Selikoff
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Took the words right out of my mouth.
 
Surya Indukuri
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not using that Insert
 
Paul Clapham
Marshal
Posts: 25682
69
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You say you are using that stored procedure, of which you posted the code. It contains a CREATE TABLE, an INSERT, and a SELECT. When you use the stored procedure you are using all three of those statements, one after another. So you can't say things like "I am not using that Insert". You are using that Insert. And that is what the error message is talking about.

It looks to me as if the SELECT statement doesn't use the results of the INSERT statement. But that doesn't prevent the INSERT from being run before its results are ignored. If you don't need it then why don't you just take it out of the stored procedure? Or just use the SELECT as a query and not use a stored procedure at all?
 
Surya Indukuri
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you, I am in the process of changing it to a query. Let me see if that works.

Thanks you for your immediate response again.
 
Surya Indukuri
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank You Paul, It worked.
 
Scott Selikoff
author
Posts: 4173
29
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, thank you Paul for defending what I had said.
 
expectation is the root of all heartache - shakespeare. tiny ad:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
    Bookmark Topic Watch Topic
  • New Topic