Solved

ASP and  Pervasive stored procedures

Posted on 2007-04-05
3
882 Views
Last Modified: 2013-12-25
I have a classic ASP web app that is connecting to a Pervasive 8.7 database.  I have no issues querying the database with straight SQL statements, however, I am cutting my teeth when it comes to using Stored Procedures in Pervasive.  

Here is my connect string:

strConnectSP = "Driver={Pervasive ODBC Client Interface};ServerName=server.domain.com;dbq=DBNAME;UID=*****;PWD=**********;"

'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
As a test, I thought that I'd get a recordset from a small table with 11 records.
The following stored proc works fine:

CREATE PROCEDURE up_get_MESSAGE_QUEUE()
RETURNS(CMPNY CHAR(3));  
BEGIN
  select CMPNY from MESSAGE_QUEUE;
END;

It returns 11 records.

'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
However, trying to get a count of the records in that same table is proving to be a bit of a challenge.
When coding the stored proc like this:

CREATE PROCEDURE up_get_MESSAGE_QUEUE()
AS BEGIN
  select count(*) as RecCount from MESSAGE_QUEUE;
END;

The browser returns:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

/test/index.asp, line 271


'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Also, when I code the stored proc as such:

CREATE PROCEDURE up_get_MESSAGE_QUEUE(out :outval INTEGER)
AS BEGIN
  select count(*) INTO :outval from MESSAGE_QUEUE;
END;

The browser returns:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface]Error in procedure argument 1.

/futurereqs/test/index.asp, line 269


'-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
sub TestSP()
  server.ScriptTimeout = 240
  dim adoConn
  dim strProc
  dim oRS

  set adoConn = server.CreateObject("ADODB.CONNECTION")
  adoConn.Open strConnectSP
  set oRS = server.CreateObject("ADODB.RecordSet")
 
  strProc = "CALL up_get_MESSAGE_QUEUE();"
  'set oRS = adoConn.execute(strProc)
  oRS.open strProc, adoConn                            ' <-----  line 269
      
  if not oRS.bof and not oRS.eof then                  ' <-----  line 271
    response.write "[" & oRS("RecCount") & "]"          
    'BuildHTMLTable oRS
  end if

  oRS.close
  set oRS = nothing
  adoConn.close
  set adoConn = nothing      
      
end sub
0
Comment
Question by:Preece
  • 2
3 Comments
 
LVL 12

Author Comment

by:Preece
ID: 18860875
I got it to work:

CREATE PROCEDURE up_get_MESSAGE_QUEUE()
RETURNS(RecCount integer);
BEGIN
  select count(*) from MESSAGE_QUEUE;
END;

Whenever returning a recordset, you must use the RETURNS clause.

http://www.pervasive.com/devtalk/topic.asp?TOPIC_ID=3462&FORUM_ID=14&CAT_ID=4&Topic_Title=Store+procedure&Forum_Title=Development

Preece
0
 
LVL 12

Author Comment

by:Preece
ID: 18860889
Moderator, please close this question!

Thanks,
Preece
0
 

Accepted Solution

by:
EE_AutoDeleter earned 0 total points
ID: 18998861
Preece,
Because you have presented a solution to your own problem which may be helpful to future searches, this question is now PAQed and your points have been refunded.

EE_AutoDeleter
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Read about why website design really matters in today's demanding market.
Viewers will get an overview of the benefits and risks of using Bitcoin to accept payments. What Bitcoin is: Legality: Risks: Benefits: Which businesses are best suited?: Other things you should know: How to get started:
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now