Error with BigDecimal used as stored procedure parameter

I have the following stored procedure:
create procedure MyNumericTestProc
(
@.param1 numeric(13,2) output
)
as
begin
if (@.param1 is NULL)
begin
set @.param1 = 5.25
end
set @.param1 = @.param1 + 0.01
select @.param1
end
I call it using the MS SQL Server JDBC Driver (SP3):
public class TestMyNumericTestProc
{
public static void main(String[] args)
{
try
{
testMyNumericTestProcUsingJDBC();
}
catch (ClassNotFoundexception e)
{
}
}
public void testMyNumericTestProcUsingJDBC() throws
ClassNotFoundException
{
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
try {
conn = DriverManager.getConnection(
"jdbc:microsoft:sqlserver://MySystem\\MySQL2000Server:MyPort;databaseName=My
Database",
"username",
"password");
cs = conn.prepareCall("{call MyNumericTestProc(?)}");
BigDecimal d = new BigDecimal("300.10");
int scale = 3;
System.out.println("Input Value = " + d.toString());
System.out.println("Input Value Scale = " + d.scale());
System.out.println("Input Parameter Scale = " + scale);
// Set a BigDecimal inout parameter and execute call
cs.setObject(1, d, Types.DECIMAL);
cs.registerOutParameter(1, Types.DECIMAL, scale);
boolean csResult = cs.execute();
// Obtain result set
rs = cs.getResultSet();
rs.next();
d = rs.getBigDecimal(1);
System.out.println("ResultSet Value = " + d.toString());
System.out.println("ResultSet Scale = " + d.scale());
// Obtain value of the output parameter as object
Object obj = cs.getObject(1);
System.out.println("Output Param Value (as Object) = " +
((BigDecimal) obj).toString());
System.out.println("Output Param Scale (as Object) = " +
((BigDecimal) obj).scale());
// Obtain value of the output parameter as BigDeciaml
d = cs.getBigDecimal(1);
System.out.println("Output Param Value (as BigDecimal) = " +
d.toString());
System.out.println("Output Param Scale (as BigDecimal) = " +
d.scale());
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (cs != null) {
try { cs.close(); }
catch (SQLException e) {;}
}
if (conn != null) {
try { conn.close(); }
catch (SQLException e) {;}
}
}
}
}
The output of executing this class is as follows:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 30.02
ResultSet Scale = 2
Output Param Value (as Object) = 30.020
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 30.020
Output Param Scale (as BigDecimal) = 3
Note that I use BigDecimal as the parameter type (which is the recommended
type fr DECIMAL and NUMERIC).
Given the stored procedure, I would have expected the value 300.11 as the
value of the output
parameter and within the result set.
It appears there is an error when the scale of the input value and the scale
specified by the output
parameter do not match, and the input parameter is a BigDecimal.
Is this a problem within the realm of the MS JDBC Driver? If it is not how
do I determine where the
error is occuring?
Try running your code against another driver. If it works, then it's
probably a MS JDBC Driver problem. And it will work.
Alin.
|||I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
what one would expect:
Input Value = 300.10
Input Value Scale = 2
Input Parameter Scale = 3
ResultSet Value = 300.11
ResultSet Scale = 2
Output Param Value (as Object) = 300.110
Output Param Scale (as Object) = 3
Output Param Value (as BigDecimal) = 300.110
Output Param Scale (as BigDecimal) = 3
Now that I have determined this is a problem in the SQL Server JDBC Driver,
where do I file an error/bug report so that Microsoft is aware of the issue
(and possibly an idea of when the problem may be fixed)?
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
> Try running your code against another driver. If it works, then it's
> probably a MS JDBC Driver problem. And it will work.
> Alin.
>
|||Fred Foozle wrote:
> Now that I have determined this is a problem in the SQL Server JDBC
Driver,
> where do I file an error/bug report so that Microsoft is aware of the
issue
> (and possibly an idea of when the problem may be fixed)?
Microsoft engineers read this newsgroup, so they should be able to
either direct you to such a place or create a bug report themselves.
But I wouldn't wait for the bug to be fixed; MS only releases a new
JDBC driver version with a new SP and they usually fix a very limited
number of bugs; check the changelogs of their previous versions to see
what I mean.
Alin.
|||Hello Fred,
I have been able to reproduce the issue as reported. I filed a bug on it
and forwarded it to development.
Thanks,
Kamil
Kamil Sykora
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/securXity.
| Reply-To: "Fred Foozle" <ffoozle@.hotmail.com>
| From: "Fred Foozle" <ffoozle@.hotmail.com>
| Subject: Re: Error with BigDecimal used as stored procedure parameter
| Date: Thu, 7 Apr 2005 14:32:46 -0400
|
| I revised the code to use the JDBC/ODBC Driver and re-ran. The output was
| what one would expect:
|
| Input Value = 300.10
| Input Value Scale = 2
| Input Parameter Scale = 3
| ResultSet Value = 300.11
| ResultSet Scale = 2
| Output Param Value (as Object) = 300.110
| Output Param Scale (as Object) = 3
| Output Param Value (as BigDecimal) = 300.110
| Output Param Scale (as BigDecimal) = 3
|
|
| Now that I have determined this is a problem in the SQL Server JDBC
Driver,
| where do I file an error/bug report so that Microsoft is aware of the
issue
| (and possibly an idea of when the problem may be fixed)?
|
|
|
| "Alin Sinpalean" <alin@.earthling.net> wrote in message
| news:1112824157.261614.198520@.f14g2000cwb.googlegr oups.com...
| > Try running your code against another driver. If it works, then it's
| > probably a MS JDBC Driver problem. And it will work.
| >
| > Alin.
| >
|
|
|

Error while using OUTPUT clause - The multi-part identifier could not be bound

I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into:

CREATE TABLE Parent(

ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

ParentName VARCHAR(50) NOT NULL)

GO

CREATE TABLE Child(

ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

ParentID INT NOT NULL REFERENCES Parent(ParentID),

ChildName VARCHAR(50) NOT NULL)

GO

INSERT INTO Parent(ParentName) VALUES('Parent 1')

INSERT INTO Parent(ParentName) VALUES('Parent 2')

GO

INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1')

INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2')

GO

At this stage, there Child table looks like:

ChildID	ParentID	ChildName
1	1	Child 1
2	1	Child 2

What I want to do is copy Parent 1’s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query:

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

INSERT INTO Child(ParentID, ChildName)

OUTPUT c.ChildID, inserted.ChildID INTO @.LinkTable

SELECT 2, c.ChildName

FROM Child c

WHERE c.ParentID = 1

SELECT * FROM @.LinkTable

In the end I was expecting Child table to look like:

ChildID	ParentID	ChildName
1	1	Child 1
2	1	Child 2
3	2	Child 1
4	2	Child 2

and OUTPUT clause to return me:

FromChildID	ToChildID
1	3	Child record with ID 3 was created using ID of 1.
2	4	Child record with ID 4 was created using ID of 2.

But infact I’m getting following error:

Msg 4104, Level 16, State 1, Line 9

The multi-part identifier "c.ChildID" could not be bound.

Any ideas on how to fix the OUTPUT clause in the query to return me the expected output?

Thanks

Yogesh

This is not possible because the INSERT statement doesn’t have a FROM clause. The UPDATE and DELETE however has the non-standard TSQL specific FROM clause as part of the DML statement itself so you can reference those tables in the OUTPUT clause. You can only do INSERT….VALUES or INSERT..SELECT or INSERT…EXECUTE. So you can only reference the inserted table. And the INSERT DML itself cannot reference other tables (except you can now use CTE in SQL Server 2005 and reference it as target table of insert which is non-standard also).

See the link below for the OUTPUT clause usage (also documents where you can use from_table_name in the OUTPUT clause column list):

http://msdn2.microsoft.com/en-us/ms177564(SQL.90).aspx

In your example, you need to reference the inserted table columns:

INSERT INTO Child(ParentID, ChildName)

OUTPUT inserted.ParentID, inserted.ChildID INTO @.LinkTable

SELECT 2, c.ChildName

FROM Child c

WHERE c.ParentID = 1

|||

Thanks a bunch Umachandar for your prompt reply. I really need the output as I explained earlier. I played with CTE as per your suggestion for a while but I could not get records inserted into my @.LinkTable table variable as I wanted. Finally as a last option I tried with cursors, and got the source and target child id output.

I’m not terribly worried about poor performance of cursor because this stored procedure will be called only once in 6 months when no one else but admin of my application is logged in. What are your thoughts on using this in production app given these circumstances?

DECLARE

@.ChildID int,

@.ChildName varchar(50)

DECLARE child_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT ChildID, ChildName

FROM Child

WHERE ParentID = 1

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

OPEN child_cur

FETCH child_cur INTO @.ChildID, @.ChildName

WHILE @.@.fetch_status = 0

BEGIN

INSERT INTO Child(ParentID, ChildName)

OUTPUT @.ChildID, inserted.ChildID INTO @.LinkTable

VALUES (2, @.ChildName)

FETCH child_cur INTO @.ChildID, @.ChildName

END

CLOSE child_cur

DEALLOCATE child_cur

SELECT * FROM @.LinkTable

This code gave my expected output:

FromChildID	ToChildID
1	3
2	4

|||

I didn't mean to imply that using a CTE as target for the insert statement will work. It is just not possible to reference other tables in the OUTPUT clause of INSERT DML. Your code looks fine except that I don't see the need for OUTPUT clause at all. You can just do below:

DECLARE

@.ChildID int,

@.ChildName varchar(50)

DECLARE child_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT ChildID, ChildName

FROM Child

WHERE ParentID = 1

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

OPEN child_cur

WHILE (1=1)

BEGIN

FETCH child_cur INTO @.ChildID, @.ChildName

IF @.@.FETCH_STATUS < 0 BREAK

INSERT INTO Child(ParentID, ChildName) VALUES (2, @.ChildName)

INSERT INTO @.LinkTable VALUES (@.ChildID, SCOPE_IDENTITY())

END

CLOSE child_cur

DEALLOCATE child_cur

SELECT * FROM @.LinkTable

Alternatively, if the ChildName is unique per parent (your schema doesn't seem to imply if this is the case) then you can simply get the link table values by doing following query:

SELECT c1.ChildID AS FromChildID, c2.ChildID AS ToChildID

FROM Child as c1

JOIN Child as c2

ON c2.ChildName = c1.ChildName

WHERE c2.ParentID = 2

AND c1.ParentID = 1

So by putting the necessary constraints on your data model you can answer most questions efficiently.

|||

I have similer problem, I need to return with OUTPUT clause field from the SELECT clause in insert statement.
If I'll do it as suggested using CURSOR, it going to take a lot of time.

CREATE TABLE [dbo].[ProductsMapping](

[OldProductID] [int] NOT NULL,

[NewProductID] [int] NOT NULL,

[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsNEQ] [bit] NOT NULL,

[IsNew] [bit] NOT NULL,

CONSTRAINT [PK_ProductsMapping] PRIMARY KEY NONCLUSTERED

(

[OldProductID] ASC

,[NewProductID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Products](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED

(

[ProductID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO ProductsMapping

(OldProductID

,NewProductID

,ProductName

,IsNEQ

,IsNew)

SELECT o.ProductID OldProductID

,isnull(n.ProductID,o.ProductID) NewProductID

,o.ProductName

,(isnull(n.ProductID,0)-o.ProductID) IsNEQ

,isnull(n.ProductID,0) IsNew

FROM LINKEDSRV.DB.dbo.Products o

LEFT OUTER JOIN Products n ON n.ProductName = o.ProductName

DECLARE @.NewProducts TABLE (

[OldProductID] [int] NOT NULL,

[NewProductID] [int] NOT NULL,

[ProductName] [varchar](50))

DECLARE @.OldProductID int

DECLARE @.ProductName varchar(50)

DECLARE NewProduct_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT w.ProductName

,w.OldProductID

FROM ProductsMapping w

WHERE w.IsNew=1

OPEN NewProduct_cur

FETCH NewProduct_cur INTO @.ProductName, @.OldProductID

WHILE @.@.fetch_status = 0

BEGIN

INSERT INTO Products(ProductName)

OUTPUT INSERTED.ProductID AS NewProductID

,INSERTED.ProductName AS ProductName

,@.OldProductID AS OldProductID

INTO @.NewProducts

VALUES (@.ProductName)

END

CLOSE NewProduct_cur

DEALLOCATE NewProduct_cur

UPDATE ProductsMapping

SET NewProductID=t.NewProductID

FROM ProductsMapping w

INNER JOIN @.NewProducts t ON t.OldProductID=w.OldProductID

AND IsNew=1

I also tried CTE and it doesn't work.

Do you have any suggestion how to do it with out cursor?

THNX

Jermy

Error while using OUTPUT clause - The multi-part identifier could not be bound

I was trying to copy child records of one parent record into another, and wanted to report back new child record id and corresponding child record id that was used to create it. I ran into run-time error with OUTPUT clause. Following is a script that will duplicate the situation I ran into:

CREATE TABLE Parent(

ParentID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

ParentName VARCHAR(50) NOT NULL)

GO

CREATE TABLE Child(

ChildID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,

ParentID INT NOT NULL REFERENCES Parent(ParentID),

ChildName VARCHAR(50) NOT NULL)

GO

INSERT INTO Parent(ParentName) VALUES('Parent 1')

INSERT INTO Parent(ParentName) VALUES('Parent 2')

GO

INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 1')

INSERT INTO Child(ParentID, ChildName) VALUES(1, 'Child 2')

GO

At this stage, there Child table looks like:

ChildID	ParentID	ChildName
1	1	Child 1
2	1	Child 2

What I want to do is copy Parent 1’s children to Parent 2, and report back which source ChildID that was used to create the new child records. So I wrote the query:

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

INSERT INTO Child(ParentID, ChildName)

OUTPUT c.ChildID, inserted.ChildID INTO @.LinkTable

SELECT 2, c.ChildName

FROM Child c

WHERE c.ParentID = 1

SELECT * FROM @.LinkTable

In the end I was expecting Child table to look like:

ChildID	ParentID	ChildName
1	1	Child 1
2	1	Child 2
3	2	Child 1
4	2	Child 2

and OUTPUT clause to return me:

FromChildID	ToChildID
1	3	Child record with ID 3 was created using ID of 1.
2	4	Child record with ID 4 was created using ID of 2.

But infact I’m getting following error:

Msg 4104, Level 16, State 1, Line 9

The multi-part identifier "c.ChildID" could not be bound.

Any ideas on how to fix the OUTPUT clause in the query to return me the expected output?

Thanks

Yogesh

This is not possible because the INSERT statement doesn’t have a FROM clause. The UPDATE and DELETE however has the non-standard TSQL specific FROM clause as part of the DML statement itself so you can reference those tables in the OUTPUT clause. You can only do INSERT….VALUES or INSERT..SELECT or INSERT…EXECUTE. So you can only reference the inserted table. And the INSERT DML itself cannot reference other tables (except you can now use CTE in SQL Server 2005 and reference it as target table of insert which is non-standard also).

See the link below for the OUTPUT clause usage (also documents where you can use from_table_name in the OUTPUT clause column list):

http://msdn2.microsoft.com/en-us/ms177564(SQL.90).aspx

In your example, you need to reference the inserted table columns:

INSERT INTO Child(ParentID, ChildName)

OUTPUT inserted.ParentID, inserted.ChildID INTO @.LinkTable

SELECT 2, c.ChildName

FROM Child c

WHERE c.ParentID = 1

|||

Thanks a bunch Umachandar for your prompt reply. I really need the output as I explained earlier. I played with CTE as per your suggestion for a while but I could not get records inserted into my @.LinkTable table variable as I wanted. Finally as a last option I tried with cursors, and got the source and target child id output.

I’m not terribly worried about poor performance of cursor because this stored procedure will be called only once in 6 months when no one else but admin of my application is logged in. What are your thoughts on using this in production app given these circumstances?

DECLARE

@.ChildID int,

@.ChildName varchar(50)

DECLARE child_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT ChildID, ChildName

FROM Child

WHERE ParentID = 1

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

OPEN child_cur

FETCH child_cur INTO @.ChildID, @.ChildName

WHILE @.@.fetch_status = 0

BEGIN

INSERT INTO Child(ParentID, ChildName)

OUTPUT @.ChildID, inserted.ChildID INTO @.LinkTable

VALUES (2, @.ChildName)

FETCH child_cur INTO @.ChildID, @.ChildName

END

CLOSE child_cur

DEALLOCATE child_cur

SELECT * FROM @.LinkTable

This code gave my expected output:

FromChildID	ToChildID
1	3
2	4

|||

I didn't mean to imply that using a CTE as target for the insert statement will work. It is just not possible to reference other tables in the OUTPUT clause of INSERT DML. Your code looks fine except that I don't see the need for OUTPUT clause at all. You can just do below:

DECLARE

@.ChildID int,

@.ChildName varchar(50)

DECLARE child_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT ChildID, ChildName

FROM Child

WHERE ParentID = 1

DECLARE @.LinkTable TABLE (FromChildID INT, ToChildID INT)

OPEN child_cur

WHILE (1=1)

BEGIN

FETCH child_cur INTO @.ChildID, @.ChildName

IF @.@.FETCH_STATUS < 0 BREAK

INSERT INTO Child(ParentID, ChildName) VALUES (2, @.ChildName)

INSERT INTO @.LinkTable VALUES (@.ChildID, SCOPE_IDENTITY())

END

CLOSE child_cur

DEALLOCATE child_cur

SELECT * FROM @.LinkTable

Alternatively, if the ChildName is unique per parent (your schema doesn't seem to imply if this is the case) then you can simply get the link table values by doing following query:

SELECT c1.ChildID AS FromChildID, c2.ChildID AS ToChildID

FROM Child as c1

JOIN Child as c2

ON c2.ChildName = c1.ChildName

WHERE c2.ParentID = 2

AND c1.ParentID = 1

So by putting the necessary constraints on your data model you can answer most questions efficiently.

|||

I have similer problem, I need to return with OUTPUT clause field from the SELECT clause in insert statement.
If I'll do it as suggested using CURSOR, it going to take a lot of time.

CREATE TABLE [dbo].[ProductsMapping](

[OldProductID] [int] NOT NULL,

[NewProductID] [int] NOT NULL,

[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

[IsNEQ] [bit] NOT NULL,

[IsNew] [bit] NOT NULL,

CONSTRAINT [PK_ProductsMapping] PRIMARY KEY NONCLUSTERED

(

[OldProductID] ASC

,[NewProductID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

CREATE TABLE [dbo].[Products](

[ProductID] [int] IDENTITY(1,1) NOT NULL,

[ProductName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

CONSTRAINT [PK_Products] PRIMARY KEY NONCLUSTERED

(

[ProductID] ASC

)WITH FILLFACTOR = 90 ON [PRIMARY]

) ON [PRIMARY]

INSERT INTO ProductsMapping

(OldProductID

,NewProductID

,ProductName

,IsNEQ

,IsNew)

SELECT o.ProductID OldProductID

,isnull(n.ProductID,o.ProductID) NewProductID

,o.ProductName

,(isnull(n.ProductID,0)-o.ProductID) IsNEQ

,isnull(n.ProductID,0) IsNew

FROM LINKEDSRV.DB.dbo.Products o

LEFT OUTER JOIN Products n ON n.ProductName = o.ProductName

DECLARE @.NewProducts TABLE (

[OldProductID] [int] NOT NULL,

[NewProductID] [int] NOT NULL,

[ProductName] [varchar](50))

DECLARE @.OldProductID int

DECLARE @.ProductName varchar(50)

DECLARE NewProduct_cur CURSOR FORWARD_ONLY READ_ONLY FOR

SELECT w.ProductName

,w.OldProductID

FROM ProductsMapping w

WHERE w.IsNew=1

OPEN NewProduct_cur

FETCH NewProduct_cur INTO @.ProductName, @.OldProductID

WHILE @.@.fetch_status = 0

BEGIN

INSERT INTO Products(ProductName)

OUTPUT INSERTED.ProductID AS NewProductID

,INSERTED.ProductName AS ProductName

,@.OldProductID AS OldProductID

INTO @.NewProducts

VALUES (@.ProductName)

END

CLOSE NewProduct_cur

DEALLOCATE NewProduct_cur

UPDATE ProductsMapping

SET NewProductID=t.NewProductID

FROM ProductsMapping w

INNER JOIN @.NewProducts t ON t.OldProductID=w.OldProductID

AND IsNew=1

I also tried CTE and it doesn't work.

Do you have any suggestion how to do it with out cursor?

THNX

Jermy