aspose file tools*
The moose likes JDBC and the fly likes Error in executeQuery() on callableStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error in executeQuery() on callableStatement" Watch "Error in executeQuery() on callableStatement" New topic
Author

Error in executeQuery() on callableStatement

Surya Indukuri
Greenhorn

Joined: Dec 05, 2005
Posts: 13
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,
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

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)


My Blog: Down Home Country Coding with Scott Selikoff
Surya Indukuri
Greenhorn

Joined: Dec 05, 2005
Posts: 13
Scott,

I am just doing a select. I am not doing any inserts or updates.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Took the words right out of my mouth.
Surya Indukuri
Greenhorn

Joined: Dec 05, 2005
Posts: 13
I am not using that Insert
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18570
    
    8

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

Joined: Dec 05, 2005
Posts: 13
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

Joined: Dec 05, 2005
Posts: 13
Thank You Paul, It worked.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3704
    
    5

Yes, thank you Paul for defending what I had said.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error in executeQuery() on callableStatement