Solved

ASP and  Pervasive stored procedures

Posted on 2007-04-05
3
889 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Forms that calculate points for wordpress 10 51
Html CheckBox obtain Its Value 5 27
Load data upon clicking a button 8 35
Database ERD 4 29
Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
This tutorial walks through the best practices in adding a local business to Google Maps including how to properly search for duplicates, marker placement, and inputing business details. Login to your Google Account, then search for "Google Mapmaker…
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…

860 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