Highlighted
Absent Member.
Absent Member.
845 views

[archive] sqlstate error 22005 when calling stored procedure

[Migrated content. Thread originally posted on 13 October 2010]

hello,

When i'm calling a sql stored procedurce from my program i keep getting the following sqlstate error : 22005 (Error in assignment )

I think it has something to do with the output parameter i'm using but i can't figure out what's wrong.
Or maybe i do something wrong in my stored procedure?


This is my code in cobol.


//working storage
01 test-id pic 999.

***** SQL ***************************
EXEC SQL INCLUDE SQLCA END-EXEC.

EXEC SQL BEGIN DECLARE SECTION END-EXEC.

01 c1-afspraak.
05 c1-datum PIC S9(8) comp-5.
05 c1-lesgever PIC x(10).
05 c1-resource PIC x(10).
05 c1-soort PIC S9(2).
05 c1-begintijd PIC x(4).
05 c1-eindtijd PIC x(4).
05 c1-opm PIC x(100).
05 c1-plaats PIC S9(2).
05 c1-cat PIC x(3).
05 c1-afspraak-teller PIC S9(2).


01 c2-afspraak_detail.
05 c2-soort PIC S9(2).
05 c2-var1 pic x(100).

01 c-newid PIC S9(18) COMP-3.

01 sp_query pic x(500).

EXEC SQL END DECLARE SECTION END-EXEC.

//event
Screen-eur-PB-OK-Ev-Cmd-Clicked.
move 20101010 to c1-datum
move "Romario" to c1-lesgever
move "TXA210" to c1-resource
move 5 to c1-soort, c2-soort
move "1200" to c1-begintijd
move "1250" to c1-eindtijd
move "dit is een testje" to c1-opm
move 1 to c1-plaats
move "BE" to c1-cat
move 1 to c1-afspraak-teller
move "Jefke" to c2-var1
move 1 to c-newid


move '{call sp_insertAFSpraak(? out,?,?,?,?,?,?,?,?,?,?,?)}'
to sp_query
exec sql prepare mycall from :sp_query end-exec
exec sql
execute mycall using :c-newid
, :c1-datum
, :c1-lesgever
, :c1-resource
, :c1-soort
, :c1-begintijd
, :c1-eindtijd
, :c1-opm
, :c1-plaats
, :c1-cat
, :c1-afspraak-teller
, :c2-var1

end-exec


move c-newid to test-id

.


//And here is also my stored procedure in mssql

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER proc [dbo].[sp_insertAFspraak]
@id bigint output,
@Datum int,
@lesgever char(10),
@resource char(10),
@soort smallint,
@begintijd char(4),
@eindtijd char(4),
@opm char(100),
@plaats smallint,
@cat char(3),
@afspraak_teller smallint,
@var1 char(100)

as
begin
insert into afspraak(datum,lesgever,resource,soort,begintijd,eindtijd,opm,plaats,cat,afspraak_teller)
values(@datum,@lesgever,@resource,@soort,@begintijd,@eindtijd,@opm,@plaats,@cat,@afspraak_teller)

set @id = scope_identity()

insert into afspraak_detail(afspraak_id,soort,var1)
values(@id,@soort,@var1)
end



What i'm trying to do here is to insert the record and export the "ID" back into my cobol program, so i can use it further in my program.


I hope someone can help me?

Regards

Danny
0 Likes
1 Reply
Highlighted
Absent Member.
Absent Member.

RE: [archive] sqlstate error 22005 when calling stored procedure

problem solved.

I had to use the asqlsrvr.dll instead of the esqllib.dll

see :

AcuSQL User's Guide
Version 8.1.2
4.2 Running Your Application with Microsoft SQL Server


Thanks to Randy Zack and Piet Henskens

w
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.