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;

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,

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

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.

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?

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);

I will try that. Thanks