Solved

ASP and  Pervasive stored procedures

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Learn by example how to specify CSS selectors for Selenium WebDriver test automation software.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

803 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