Results 1 to 8 of 8

Thread: Like an idiot

  1. #1
    Join Date
    Aug 2004
    Posts
    51

    Unanswered: Like an idiot

    Can anyone take time to point out the syntax for returning a recordset from a stored procedure in Pervasive syntax?

    I couldn't find a straight example anywhere in their docs.

    like, what's wrong with this?

    CREATE PROCEDURE TestProc()
    BEGIN
    SELECT * FROM SOMETABLE;
    END;

    It runs but returns no records.

    Thanks,

    Kimball Johnson

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    I found this example pretty easily:
    Code:
    CREATE PROCEDURE DATERETURNPROC(IN :PDATE DATE)  
    RETURNS( 
    ID INTEGER, 
    Name CHAR(7), 
    Section CHAR(3), 
    Max_Size USMALLINT, 
    Start_Date DATE, 
    Start_Time TIME, 
    Finish_Time TIME, 
    Building_Name CHAR(25), 
    Room_Number UINTEGER, 
    Faculty_ID UBIGINT 
    ); 
    BEGIN  
    SELECT ID, Name, Section, Max_Size, Start_Date, Start_Time, Finish_Time, Building_Name, Room_Number, Faculty_ID FROM CLASS WHERE START_DATE = :PDATE; 
    END; 
    
    
    CALL DATERETURNPROC('2001-06-05')
    at http://www.pervasive.com/library/doc...ntaxref16.html.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  3. #3
    Join Date
    Aug 2004
    Posts
    51

    Me too.

    I found that one to begin with.

    However, it appeared to me that it was populating parameters. Therefore my question.

    So what you're saying is that the syntax for returning records requires that there be a 'Returns' expression that lists each of the column names?

    After seeing that example I was unable to find any text confirming that suspicion.

    Would you mind taking the time to be explicit regarding the details?

    Thanks,

    Kimball Johnson

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    RETURNS is required to return data from a Stored Procedure unless you use an INOUT/OUT parameter. The RETURNS clause is not parameters. It returns a resultset. It does require you to list each column defintion being returned.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  5. #5
    Join Date
    Aug 2004
    Posts
    51

    Thank you!

    Ok,

    So could you help me with this next one:

    What's the syntax for calling a stored procedure with an out paramter from the Data Manager query tool?

    like:

    CREATE PROCEDURE ClientList(OUT :intErrorCode INTEGER)
    RETURNS(
    ...list
    )
    BEGIN
    SET :intErrorCode = 1;
    END:

    then (in data manager):

    CALL ClientList(:err)

    or something...

    I mean, it won't let you declare the err variable outside the signature, but it does want you to use the ":" prefix. So it knows something.

    grrrr!

    (by the way, I always search the documentation six ways before asking.)

    Thanks,

    Kimball

  6. #6
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Query Manager wasn't designed to handle OUT parameters. You'll need to create a wrapper Stored Procedure to use OUT parameters within the PCC.. For example:
    CREATE PROCEDURE Wrapper ();
    BEGIN
    DECLARE :err INTEGER
    CALL ClientList(:err);
    PRINT :err;
    END;
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

  7. #7
    Join Date
    Aug 2004
    Posts
    51

    Thumbs up Well of course!

    Why didn't I think of that?

    Probably don't have enough respect for the PCC thing.

    Seems pretty hokey after living with Microsoft stuff.

    Like, what's with the stored procedure editor not being expandable so you can see the whole procedure at once?

    And why not have a save button?

    And why does it always have to crash with the error 'cannot find the stupid database again' every time you try to save after it detected a previous error?

    and and and.

    But you've been a big help! Thanks!

  8. #8
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Make sure you report any problems (especially crashes) to Pervasive. Don't use Dev Talk to report support issues though, it's not an "official" support area. If it's truly a defect and you don't need a fix, you can submit issues here http://www.pervasive.com/support/submit_defect.asp.
    Mirtheil Software
    Certified Pervasive Developer
    Certified Pervasive Technician
    Custom Btrieve/VB development
    http://www.mirtheil.com
    I do not answer questions by email. Please post on the forum.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •