Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    13

    Unanswered: Stored Procedure

    I am trying to write a stored procedure for the first time. I am using pervasive 9.0. I am tying together a secure desktop messaging system with a web application. I will be passing the data that matches what the desktop app needs to fill in the tables.

    The issue is that the desktop app uses tow tables to do messaging. One table (PDSMSGC) that stores the relevant information about the message: sender, recipient, notes, phone number, etc. The other table (MEMOS) stores the message text and references back to the "PDSMSGC" table (The memos table in the desktop app is used for more than just the messaging system.)

    So both tables have unique IDs that are referenced in both tables. For example:
    MEMOS.ParentID = PDSMSGC.MessageID
    PDSMSGC.MemoID = MEMOS.MemoID

    here are the column definitions:




    My question is: Does this seem like a properly formed set of commands and can you think of a better way to do this?

    Here is what I have written so far:

    CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);

    BEGIN

    DECLARE :NewMemoID UINTEGER;

    DECLARE :NewMessageID UINTEGER;

    INSERT INTO PDSMSGC (ToOper, MsgFrom, LinkID, PrimaryID, Phone, MemoID, DateTaken, TimeTaken, TakeOper, BeenRead) VALUES (:RecipientID, :PatientName, '2', :PatientID, :PatientPhone, :PatientID, CurDate(), CurTime(), 'WEB', '5');

    INSERT INTO MEMOS (ParentID, FieldName, MemoText) VALUES (:PatientID, 'Xholder', :MemoText);

    SELECT :NewMemoID = MemoID FROM MEMOS WHERE MEMOS.ParentID = :PatientID AND MEMOS.FieldName = 'Xholder' AND MEMOS.MemoText = :MemoText;

    SELECT :NewMessageID = MessageID FROM PDSMSGC WHERE PDSMSGC.MsgFrom = :PatientName AND PDSMSGC.PrimaryID = :PatientID AND PDSMSGC.MemoID = :PatientID AND PDSMSGC.TimeTaken = CurDate() AND PDSMSGC.TakeOper = 'WEB' AND PDSMSGC.BeenRead = '5';

    UPDATE MEMOS SET ParentID = :NewMessageID, FieldName = 'pmc:MemoID', MemoText = :MemoText WHERE MEMOS.MemoID = :NewMemoID;

    UPDATE PDSMSGC SET ToOper = :RecipientID, MsgFrom = :PatientName, MemoID = :NewMemoID, BeenRead = '0' WHERE PDSMSGC.MessageID = :NewMessageID;

    END;

  2. #2
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Without actually trying it, I would say that it looks pretty good. I don't see where you are setting the MessageID in the PDSMSGC table though.
    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
    Feb 2008
    Posts
    13
    Mirtheil,

    Thanks for looking, the MessageID is set to autonumber in PDSMSGC and MemoID is autonumber for MEMOS.

    I tried it but I get a "Invalid data type(s) argument when I try to create this procedure.

    Thank you

    Bob

  4. #4
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    Can you post the exact error you get when you try to create it?
    Also, are you sure about the autonumber type? Usually Pervasive shows Identity fields as Identity rather than UINTEGER.
    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
    Feb 2008
    Posts
    13

  6. #6
    Join Date
    Feb 2008
    Posts
    13
    Well they aren't autonumber yet, I am writing this procedure for when the desktop programmers change the definitions.

    I guess that may be why it isn't working. Because I am not creating the IDs in the insert?


  7. #7
    Join Date
    Dec 2001
    Posts
    1,109
    Provided Answers: 4
    One more thing, you have:
    CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR, in :PatientID UINTEGER, in :PatientName CHAR, in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);
    Shouldn't it be:
    CREATE PROCEDURE usp_webmessgaing (in :RecipientID CHAR(3), in :PatientID UINTEGER, in :PatientName CHAR(30), in :MemoText LONGVARCHAR, in :PatientPhone DECIMAL);
    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.

  8. #8
    Join Date
    Feb 2008
    Posts
    13
    I will try that. Thanks

Posting Permissions

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