Problem with execute to make a stored procedure

[Cecil Westerhof]

I have to maintain a piece of code. One of the things that is done is a
create procedure:
CREATE PROCEDURE PROC_22_
RETURNS(IEXTENSION INTEGER,CNTICALLINGNBR INTEGER,CNTIDIALEDNBR
INTEGER,TOTAL INTEGER) AS
DECLARE VARIABLE SORTORDER INTEGER;
BEGIN
SORTORDER = 0;
FOR SELECT ICALLINGNBR AS IEXTENSION
, SUM(CNTICALLINGNBR) AS CNTICALLINGNBR
, SUM(CNTIDIALEDNBR) AS CNTIDIALEDNBR
, (SUM(CNTICALLINGNBR) + SUM(CNTIDIALEDNBR)) AS TOTAL
FROM SV_22_
GROUP BY ICALLINGNBR
ORDER BY 4 DESC
INTO :IEXTENSION ,:CNTICALLINGNBR ,:CNTIDIALEDNBR ,:TOTAL
DO
BEGIN
IF((SORTORDER >= 0) AND (SORTORDER < 10)) THEN
BEGIN
SUSPEND;
END
SORTORDER = SORTORDER + 1;
END
END

When this code is execute, I get an error.
Number -2147217904
HelpContext 0
Description The command required parameters

I tried it with:
oDbConn.Execute strStoredProc, adExecuteNoRecords
and
oDbConn.Execute strStoredProc, , adExecuteNoRecords

Anybody an idea what could be the problem?

[Aaron Bertrand [MVP]]

CREATE PROCEDURE PROC_22_

> RETURNS

You seem to be confusing the functionality of procedures with that of
functions.

[Cecil Westerhoff]

On Fri, 27 Feb 2004 14:08:02 -0500, Aaron Bertrand [MVP] wrote:

> CREATE PROCEDURE PROC_22_

>> RETURNS

>
> You seem to be confusing the functionality of procedures with that of
> functions.

As said, it is only code from someone else that I have to maintain. But
that is not the problem, because if I use the exact same code in IBConsole
it is executed without a problem. So there goes something wrong in
ASP/ADO.

[Bob Barrows]

Cecil Westerhoff wrote:

> On Fri, 27 Feb 2004 14:08:02 -0500, Aaron Bertrand [MVP] wrote:
>

>> CREATE PROCEDURE PROC_22_

>>> RETURNS

>>
>> You seem to be confusing the functionality of procedures with that of
>> functions.

>
> As said, it is only code from someone else that I have to maintain.
> But that is not the problem, because if I use the exact same code in
> IBConsole it is executed without a problem. So there goes something
> wrong in ASP/ADO.

What is IBConsole? it sounds as if you are not dealing with a MS SQL Server
database.

Hmm, now that I look more closely at the code within the procedure, it is
obviously not Transact-SQL (the brand of SQL used by MS SQL Server). Always
tell us the type and version of database you are using. It is always
relevant.

As for your ADO code, this is the correct syntax:

> oDbConn.Execute strStoredProc, , adExecuteNoRecords

Since it is saying a parameter is required, your strStoredProc variable may
not contain the the sql statement you think it contains. Do a:

Response.Write strStoredProc

and verify that the statement in the variable is correct when written to the
browser window.

If it is correct, you should be able to copy and paste it from the browser
window into IBConsole and execute it without modification.

If it is not what you expect it to be, then you need to look at the vbscript
code used to build the statement. If you can't figure out what is wrong with
the vbscript, post the result of the Response.Write and the vbscript code so
we can take a look at it.

If this does not provide your solution, you may need to ask this question on
a newsgroup devoted to whatever database you are using.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows]

Cecil Westerhof wrote:

> The statement I got from writing to the browser window. Executing into
> IBConsole works okay. So that is the problem. In the Console it
> executes without a problem, but not from ASP.
>
>
>
> The code first creates two views and then the stored procedure. It
> creates the views with two seperate executes and with the thirth the
> stored procedure should be created. When I copy paste the code it
> goes allright. Could the problem be that it is done in three steps?
> In IBConsole it is done in one step.
>

I don't know. Jet (Access) does not allow batched queries. Maybe Firebird
doesn't support them as well. Perhaps IBConsole is doing things behind the
scenes to allow you to batch your queries, things that ADO does not know to
do. Are you able to run other batched queries? Is this the only set of
queries that gives you a problem when run as a batch?

My next step in this debug process would be to run each query individually
in an attempt to discover which one was creating the problem. If they run
fine individually, then you have your answer: execute them individually.

Perhaps you need to get a different/newer OLEDB provider, or ODBC driver. A
Google search using the keywords Firebird and ADO reveals some links to
sites offering OLEDB providers for Firebird:
[url]http://groups.google.com/groups?q=ADO+Firebird&hl=en&lr=&ie=UTF-8&oe=UTF-8&start=0&sa=N[/url]


Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Cecil Westerhof]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u07DjE3$DHA.3536@tk2msftngp13.phx.gbl...

> I don't know. Jet (Access) does not allow batched queries. Maybe Firebird
> doesn't support them as well. Perhaps IBConsole is doing things behind the
> scenes to allow you to batch your queries, things that ADO does not know

to

> do. Are you able to run other batched queries? Is this the only set of
> queries that gives you a problem when run as a batch?

I checked the code and this is the only place where a stored procedure is
used. It looks like it that the original coder made something that he
thought would work, but never bothered to check it.

> Perhaps you need to get a different/newer OLEDB provider, or ODBC driver.

A

> Google search using the keywords Firebird and ADO reveals some links to
> sites offering OLEDB providers for Firebird:
>

[url]http://groups.google.com/groups?q=ADO+Firebird&hl=en&lr=&ie=UTF-8&oe=UTF-8&start=0&sa=N[/url]

Maybe that is the best idea. The only problem could be that the application
has to be installed by many customberes and this would increase the price.
But that is someone's else decision.

Thanks for the help.

[Cecil Westerhof]

"Bob Barrows" <reb01501@NOyahoo.SPAMcom> wrote in message
news:u07DjE3$DHA.3536@tk2msftngp13.phx.gbl...

> Perhaps you need to get a different/newer OLEDB provider, or ODBC driver.

A

> Google search using the keywords Firebird and ADO reveals some links to
> sites offering OLEDB providers for Firebird:
>

[url]http://groups.google.com/groups?q=ADO+Firebird&hl=en&lr=&ie=UTF-8&oe=UTF-8&start=0&sa=N[/url]

We installed another driver and now it works.
Thanks.