|
P: n/a
|
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 this Question
|
P: n/a
|
"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? | |
|
P: n/a
|
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 | |
|
P: n/a
|
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 | |
|
P: n/a
|
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
| |
|
P: n/a
|
"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 | |
|
P: n/a
|
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
| |
|
P: n/a
|
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. | |  Question stats - viewed: 7481
- replies: 7
- date asked: Nov 12 '05
|
