date formatting to use in stored procedures

martini

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

Nov 12 '05 #1

Tokunaga T.

"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?

Nov 12 '05 #2

martini

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

Nov 12 '05 #3

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

Nov 12 '05 #4

Philip Sherman

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

Nov 12 '05 #5

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

Nov 12 '05 #6

Philip Sherman

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

Nov 12 '05 #7

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

Nov 12 '05 #8