how to make data validation in dynamic query

Posted by ravinandhan on 07-Jan-2015 12:46

can any one help me doing a validation on record level locking using dynamic query with code

Posted by Youssif Shanshiry on 07-Jan-2015 13:02

Would the following caller.p and callee.p help you do what you need?

1.  

/***   1. caller.p. This procedure calls callee.p to get the availability,

locking status, current-changed status of a record given its table

name, a field name, a comparison operation and a comparison value:   ***/

DEFINE VARIABLE cTableName AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cFieldName AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cOperation AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cValue AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cResult AS CHARACTER  NO-UNDO.

ASSIGN

   cTableName = "Customer"

   cFieldName = "CustNum"

   cOperation = "EQ"

   cValue     = "11".

RUN Callee.p(  

   INPUT cTableName,

   INPUT cFieldName,

   INPUT cOperation,

   INPUT cValue,

   OUTPUT cResult

   ).

MESSAGE cResult

   VIEW-AS ALERT-BOX INFO BUTTONS OK.

2.

/***

2. callee.p: This procedure builds a dynamic query based on the

received information and returns the availability, locking and

current-changed status of the specified record:

***/

/* Define input and output parameters */

DEFINE INPUT PARAMETER  pcTableName AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcFieldName AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcOperation AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcValue     AS CHARACTER  NO-UNDO.

DEFINE OUTPUT PARAMETER pcResult    AS CHARACTER  NO-UNDO.

/* Define local variables */

DEFINE VARIABLE cLegalOperations AS CHARACTER  NO-UNDO.

DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO.

DEFINE VARIABLE hTableBuffer AS HANDLE     NO-UNDO.

DEFINE VARIABLE cQueryString AS CHARACTER  NO-UNDO.

/* Check that the Criterion is a legal Comparison Operations */

ASSIGN

   cLegalOperations = "EQ,GE,GT,LE,LT,NE".

IF LOOKUP ( pcOperation , cLegalOperations, ',' ) = 0 THEN DO:

   ASSIGN

       pcResult = pcOperation + " is not a legal Comparison

Operations.".

   RETURN.

END.

/*  Construct the query string */

ASSIGN

   cQueryString = "FOR EACH " +

                  pcTableName +

                  " NO-LOCK " +

                  " WHERE "   +

                  pcFieldName + " " +

                  pcOperation + " " +

                  QUOTER(pcValue).

/*  Create the Dynamic Query and open it for the given buffer */

CREATE BUFFER hTableBuffer FOR TABLE pcTableName.

CREATE QUERY hQuery.

hQuery:SET-BUFFERS(hTableBuffer).

hQuery:QUERY-PREPARE(cQueryString).

hQuery:QUERY-OPEN.

IF hQuery:QUERY-OFF-END THEN DO:

   ASSIGN

       pcResult = "There are no records in " + pcTableName + "

meeting the speci.fied Criterion.".

   RETURN.

END.

hQuery:GET-FIRST(SHARE-LOCK, NO-WAIT).

ASSIGN

   pcResult = " The record specified is: ".

IF hTableBuffer:AVAILABLE  THEN DO:

   pcResult = pcResult + " AVAILABLE ".

   IF hTableBuffer:LOCKED  THEN

       pcResult = pcResult + " LOCKED ".

   IF hTableBuffer:CURRENT-CHANGED  THEN

   pcResult = pcResult + " CURRENT-CHANGED ".

END.

ELSE

   pcResult = pcResult + " NOT AVAILABLE ".

RETURN.

Thank you for using Progress.

Best Wishes,

Youssif H. Shanshiry

Principal Technical Support Engineer

Progress Software Corporation

Telephone:  781-280-3028

All Replies

Posted by James Palmer on 07-Jan-2015 12:59

Sorry your question doesn't make sense to me. What are you trying to achieve and what Progress version?

James Palmer | Application Developer
Tel: 01253 785103

[collapse]From: ravinandhan
Sent: ‎07/‎01/‎2015 18:49
To: TU.OE.Development@community.progress.com
Subject: [Technical Users - OE Development] how to make data validation in dynamic query

Thread created by ravinandhan

can any one help me doing a validation on record level locking using dynamic query with code

Stop receiving emails on this subject.

Flag this post as spam/abuse.




This email has been scanned for email related threats and delivered safely by Mimecast.
For more information please visit http://www.mimecast.com
[/collapse]

Posted by Youssif Shanshiry on 07-Jan-2015 13:02

Would the following caller.p and callee.p help you do what you need?

1.  

/***   1. caller.p. This procedure calls callee.p to get the availability,

locking status, current-changed status of a record given its table

name, a field name, a comparison operation and a comparison value:   ***/

DEFINE VARIABLE cTableName AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cFieldName AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cOperation AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cValue AS CHARACTER  NO-UNDO.

DEFINE VARIABLE cResult AS CHARACTER  NO-UNDO.

ASSIGN

   cTableName = "Customer"

   cFieldName = "CustNum"

   cOperation = "EQ"

   cValue     = "11".

RUN Callee.p(  

   INPUT cTableName,

   INPUT cFieldName,

   INPUT cOperation,

   INPUT cValue,

   OUTPUT cResult

   ).

MESSAGE cResult

   VIEW-AS ALERT-BOX INFO BUTTONS OK.

2.

/***

2. callee.p: This procedure builds a dynamic query based on the

received information and returns the availability, locking and

current-changed status of the specified record:

***/

/* Define input and output parameters */

DEFINE INPUT PARAMETER  pcTableName AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcFieldName AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcOperation AS CHARACTER  NO-UNDO.

DEFINE INPUT PARAMETER  pcValue     AS CHARACTER  NO-UNDO.

DEFINE OUTPUT PARAMETER pcResult    AS CHARACTER  NO-UNDO.

/* Define local variables */

DEFINE VARIABLE cLegalOperations AS CHARACTER  NO-UNDO.

DEFINE VARIABLE hQuery AS HANDLE     NO-UNDO.

DEFINE VARIABLE hTableBuffer AS HANDLE     NO-UNDO.

DEFINE VARIABLE cQueryString AS CHARACTER  NO-UNDO.

/* Check that the Criterion is a legal Comparison Operations */

ASSIGN

   cLegalOperations = "EQ,GE,GT,LE,LT,NE".

IF LOOKUP ( pcOperation , cLegalOperations, ',' ) = 0 THEN DO:

   ASSIGN

       pcResult = pcOperation + " is not a legal Comparison

Operations.".

   RETURN.

END.

/*  Construct the query string */

ASSIGN

   cQueryString = "FOR EACH " +

                  pcTableName +

                  " NO-LOCK " +

                  " WHERE "   +

                  pcFieldName + " " +

                  pcOperation + " " +

                  QUOTER(pcValue).

/*  Create the Dynamic Query and open it for the given buffer */

CREATE BUFFER hTableBuffer FOR TABLE pcTableName.

CREATE QUERY hQuery.

hQuery:SET-BUFFERS(hTableBuffer).

hQuery:QUERY-PREPARE(cQueryString).

hQuery:QUERY-OPEN.

IF hQuery:QUERY-OFF-END THEN DO:

   ASSIGN

       pcResult = "There are no records in " + pcTableName + "

meeting the speci.fied Criterion.".

   RETURN.

END.

hQuery:GET-FIRST(SHARE-LOCK, NO-WAIT).

ASSIGN

   pcResult = " The record specified is: ".

IF hTableBuffer:AVAILABLE  THEN DO:

   pcResult = pcResult + " AVAILABLE ".

   IF hTableBuffer:LOCKED  THEN

       pcResult = pcResult + " LOCKED ".

   IF hTableBuffer:CURRENT-CHANGED  THEN

   pcResult = pcResult + " CURRENT-CHANGED ".

END.

ELSE

   pcResult = pcResult + " NOT AVAILABLE ".

RETURN.

Thank you for using Progress.

Best Wishes,

Youssif H. Shanshiry

Principal Technical Support Engineer

Progress Software Corporation

Telephone:  781-280-3028

Posted by ravinandhan on 07-Jan-2015 13:08

thank you shanshiry for support

Posted by ravinandhan on 07-Jan-2015 13:15

Hi james ,thanking you from you suggestion

This thread is closed