Solved

ASP and  Pervasive stored procedures

Posted on 2007-04-05
3
894 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Color can increase conversions, create feelings of warmth or even incite people to get behind a cause. If you want your website to really impact site visitors, then it is vital to consider the impact color has on them.
Although a lot of people devote their energy toward marketing for specific industries, there are some basic principles that can be applied to any sector imaginable. We’ll look at four steps to take and examine how those steps were put into action fo…
This video teaches users how to migrate an existing Wordpress website to a new domain.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 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