Solved

ASP and  Pervasive stored procedures

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Read about achieving the basic levels of HRIS security in the workplace.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will get a basic understanding of what section 508 compliance can entail, learn about skip navigation links, alt text, transcripts, and font size controls.

705 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

20 Experts available now in Live!

Get 1:1 Help Now