|
Need help:
I am trying to call a DB2 stored procedure that requires parameters: 4x int,
date, varchar, int. I use VB6 & oledb.
I'm using statements:
cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, '2002-04-22', '0000000',
351)}"
and
cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, {d '2002-04-22'},
'0000000', 351)}"
and I get following error:
[DB2/NT] SQL0171N The data type, length or value of argument "4" of routine
"PROC" is incorrect. SQLSTATE=42815
DB2 v 7.2 is running on Win2003
conn.ConnectString = "Provider=IBMDADB2.1;Password=" & Text6.Text & ";User
ID=" & Text5.Text & ";Data Source=" & Text4.Text
How are dates supposed to be passed to procedures?
Please, help
Martini | |
Share:
|
"martini" <martini_k (małpa) pf.pl> wrote in message news:<41********@news.vogel.pl>... Need help: I am trying to call a DB2 stored procedure that requires parameters: 4x int, date, varchar, int. I use VB6 & oledb. I'm using statements: cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, '2002-04-22', '0000000', 351)}" and cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, {d '2002-04-22'}, '0000000', 351)}" and I get following error:
[DB2/NT] SQL0171N The data type, length or value of argument "4" of routine "PROC" is incorrect. SQLSTATE=42815 DB2 v 7.2 is running on Win2003
It says 'argument "4"'.
What is your CREATE PROCEDURE statement for db2admin.proc? | | |
Uzytkownik "Tokunaga T." <to*****@jp.ibm.com> napisal w wiadomosci
news:81**************************@posting.google.c om...
[...] It says 'argument "4"'. What is your CREATE PROCEDURE statement for db2admin.proc?
CREATE PROCEDURE DB2ADMIN.PROC ( IN idbloczek int,
IN iddostawamandatow int,
IN idrodzajmandatu int,
IN idstatusmandatu int,
IN datawystawienia date,
IN numermandatu varchar(10),
IN kwota int )
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
INSERT INTO
DB2ADMIN.MANDAT
(
IDBLOCZEK,
IDDOSTAWAMANDATU,
IDRODZAJMANDATU,
IDSTATUSMANDATU,
DATAWYSTAWIENIA,
DATAWPROWADZENIA,
NUMERMANDATU,
KWOTA,
REJESTRACJAWSTEPNA
)
VALUES
(
sp_wstawmandat.idbloczek,
sp_wstawmandat.iddostawamandatow,
sp_wstawmandat.idrodzajmandatu,
sp_wstawmandat.idstatusmandatu,
sp_wstawmandat.datawystawienia,
current timestamp,
sp_wstawmandat.numermandatu,
sp_wstawmandat.kwota,
1
);
[...]
END P1
Simple, isn't it? But doesn't work :(
Any ideas? TIA,
Martini | | |
Użytkownik "martini" <martini_k (małpa) pf.pl> napisał w wiadomości
news:41********@news.vogel.pl... Need help: I am trying to call a DB2 stored procedure that requires parameters: 4x
int, date, varchar, int. I use VB6 & oledb. I'm using statements: cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, '2002-04-22',
'0000000', 351)}" and cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, {d '2002-04-22'}, '0000000', 351)}" and I get following error:
[DB2/NT] SQL0171N The data type, length or value of argument "4" of
routine "PROC" is incorrect. SQLSTATE=42815 DB2 v 7.2 is running on Win2003
conn.ConnectString = "Provider=IBMDADB2.1;Password=" & Text6.Text & ";User ID=" & Text5.Text & ";Data Source=" & Text4.Text
How are dates supposed to be passed to procedures?
Can anyone help? Please, it's very important for me.
Martini | | |
The error message exactly explains the problem. You are passing 4
integers, 2 chars, and an integer.
From a command prompt, with a connection to any database, try:
db2 "values('2002-04-22')"
db2 "values('04-22-2002')
db2 "values(date('2002-04-22'))"
db2 "values(date('04-22-2002'))"
to see the difference (UDB 8.2 Linux).
Phil Sherman
martini wrote: Need help: I am trying to call a DB2 stored procedure that requires parameters: 4x int, date, varchar, int. I use VB6 & oledb. I'm using statements: cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, '2002-04-22', '0000000', 351)}" and cmd.CommandText = "{call db2admin.proc(1, 0, 3, 3, {d '2002-04-22'}, '0000000', 351)}" and I get following error:
[DB2/NT] SQL0171N The data type, length or value of argument "4" of routine "PROC" is incorrect. SQLSTATE=42815 DB2 v 7.2 is running on Win2003
conn.ConnectString = "Provider=IBMDADB2.1;Password=" & Text6.Text & ";User ID=" & Text5.Text & ";Data Source=" & Text4.Text
How are dates supposed to be passed to procedures?
Please, help
Martini
| | |
"Philip Sherman" <ps******@ameritech.net> wrote in message
news:sX*******************@newssvr19.news.prodigy. com... The error message exactly explains the problem. You are passing 4 integers, 2 chars, and an integer.
From a command prompt, with a connection to any database, try: db2 "values('2002-04-22')" db2 "values('04-22-2002') db2 "values(date('2002-04-22'))" db2 "values(date('04-22-2002'))" to see the difference (UDB 8.2 Linux).
Phil Sherman
[...]
I tried. From command prompt works fine:
------------------------------ Command
Entered ------------------------------
call db2admin.proc(1, 0, 3, 3, '2004-05-22', '0000000', 350);
----------------------------------------------------------------------------
-
"PROC" RETURN_STATUS: "0"
but:
------------------------------ Command
Entered ------------------------------
call db2admin.proc(1, 0, 3, 3, date('2004-05-22'), '0000001', 350);
----------------------------------------------------------------------------
-
returns:
SQL0104N An unexpected token "(" was found following "<identifier>".
Expected tokens may include: ")". SQLSTATE=42601
From command prompt there's no problem with executing the procedure. Problem
appears when i try to execute it from VB6 via oledb :(
Of course both values('2002-04-22') and values(date('2002-04-22')) works!
But how to insert it into VB code?
Martini | | |
Your procedure requires a UDB date passed as a parmaeter. I believe
you've run into a problem where VB and oledb can't generate a UDB date.
If you must keep this procedure as-is; try setting up a second
procedure to invoke the first. The second should expect a string instead
of a date and should convert the string to a date the invoke the first.
If you can change the procedure; you can do the date conversion inside
it. Using the same name for the procedures should create an "overloaded"
procedure that will accept a date or a string for the fourth parameter.
Phil Sherman
martini wrote: "Philip Sherman" <ps******@ameritech.net> wrote in message news:sX*******************@newssvr19.news.prodigy. com...
The error message exactly explains the problem. You are passing 4 integers, 2 chars, and an integer.
From a command prompt, with a connection to any database, try: db2 "values('2002-04-22')" db2 "values('04-22-2002') db2 "values(date('2002-04-22'))" db2 "values(date('04-22-2002'))" to see the difference (UDB 8.2 Linux).
Phil Sherman
[...]
I tried. From command prompt works fine: ------------------------------ Command Entered ------------------------------ call db2admin.proc(1, 0, 3, 3, '2004-05-22', '0000000', 350); ---------------------------------------------------------------------------- - "PROC" RETURN_STATUS: "0"
but: ------------------------------ Command Entered ------------------------------ call db2admin.proc(1, 0, 3, 3, date('2004-05-22'), '0000001', 350); ---------------------------------------------------------------------------- - returns: SQL0104N An unexpected token "(" was found following "<identifier>". Expected tokens may include: ")". SQLSTATE=42601
From command prompt there's no problem with executing the procedure. Problem appears when i try to execute it from VB6 via oledb :( Of course both values('2002-04-22') and values(date('2002-04-22')) works! But how to insert it into VB code?
Martini | | |
Uzytkownik "Philip Sherman" <ps******@ameritech.net> napisal w wiadomosci
news:mG*****************@newssvr33.news.prodigy.co m... Your procedure requires a UDB date passed as a parmaeter. I believe you've run into a problem where VB and oledb can't generate a UDB date.
If you must keep this procedure as-is; try setting up a second procedure to invoke the first. The second should expect a string instead of a date and should convert the string to a date the invoke the first. If you can change the procedure; you can do the date conversion inside it. Using the same name for the procedures should create an "overloaded" procedure that will accept a date or a string for the fourth parameter.
Phil Sherman
Thanks a lot. It works! ;)
Martini | | This discussion thread is closed Replies have been disabled for this discussion. Similar topics
2 posts
views
Thread by zlatko |
last post: by
|
11 posts
views
Thread by Bă§TăRĐ |
last post: by
|
1 post
views
Thread by jjone99 |
last post: by
|
2 posts
views
Thread by xAvailx |
last post: by
|
7 posts
views
Thread by Anthony Robinson |
last post: by
|
5 posts
views
Thread by Rob Wire |
last post: by
|
28 posts
views
Thread by mooreit |
last post: by
|
1 post
views
Thread by xFiver |
last post: by
|
3 posts
views
Thread by Sirisha |
last post: by
|
1 post
views
Thread by John Cazaly |
last post: by
| | | | | | | | | | |