Dynamic Query which might contain " or ' quote marks

Cecil

19+ years progress programming and still learning.
dynamic query - Forum - OpenEdge General - Progress Community

Following on from the above thread.

Has this been resolved?
I tried to replace a single quote mark ' with ~', did not work.
I tried to replace the ' with ~047, that too did not work.

Basically, how do you handle query strings which might contain a single or double quote?

i.e.
Fish 'n Chips
or
Mitre10 "MEGA" Store

Code:
searchTerm = 'Mitre10 "MEGA" Store'.
searchString = SUBSTIUTE("name eq &1", QUOTER(searchTerm)  ).
queryStringWhere = SUBSTITUTE("where &1", searchString ).
 

ForEachInvoiceDelete

Active Member
We had a similar issue with Irish employee's. O'Meara still haunts me to this day.

My idea of sacking him to resolve it didn't go down well either.

Can you escape them on the input?
 

LarryD

Active Member
Can you try using a back slash to escape the single or double quotes?.... but that may or may not be *nix only.
 

TomBascom

Curmudgeon
What version of Progress?

I need a complete copy & paste example to work with, possibly using sample data in a temp-table. And I am much too lazy to type one up myself ;)

My off the cuff idea this morning is to try doubling the quotes. IOW, "this is text with a "" in the middle of it".
 

Cecil

19+ years progress programming and still learning.
What version of Progress?

I need a complete copy & paste example to work with, possibly using sample data in a temp-table. And I am much too lazy to type one up myself ;)

My off the cuff idea this morning is to try doubling the quotes. IOW, "this is text with a "" in the middle of it".

OE11.7 64bit Windows.

Well, this is embarrassing... my cut down demo dynamic query is working, sort of...

The code works, but with unexpected results??? (Sorry I can't seam to be able to resize the image )

1532130371107.png



Code:
DEFINE TEMP-TABLE ttLocation NO-UNDO
    FIELD LOCATIONID   AS CHARACTER
    FIELD LocationName AS CHARACTER.
   
DEFINE TEMP-TABLE ttCustomer NO-UNDO
    FIELD CustomerID   AS CHARACTER
    FIELD CustomerName AS CHARACTER.   
   
FUNCTION getDynaQuery RETURN CHARACTER(
    INPUT bufferTableName AS CHARACTER,
    INPUT returnFieldName AS CHARACTER,
    INPUT queryWhere      AS CHARACTER):
   
    DEFINE VARIABLE tableBuffer     AS HANDLE      NO-UNDO.
    DEFINE VARIABLE fullWhereString AS CHARACTER   NO-UNDO.
    DEFINE VARIABLE dynaReturnValue AS CHARACTER   NO-UNDO INITIAL "NOT FOUND!".
   
   
    ASSIGN
        fullWhereString = SUBSTITUTE('WHERE &1', queryWhere).
       
        MESSAGE fullWhereString .
   
    CREATE BUFFER tableBuffer FOR TABLE bufferTableName.
   
    tableBuffer:FIND-FIRST( fullWhereString, NO-LOCK ) NO-ERROR.

    IF tableBuffer:AVAILABLE THEN
        dynaReturnValue = tableBuffer:BUFFER-FIELD(returnFieldName):STRING-VALUE().      
   
    RETURN dynaReturnValue.
   
    FINALLY:
       
        DELETE OBJECT tableBuffer.
   
    END.
   
END FUNCTION.

PROCEDURE createMockTTRecords:

    /** create some mock records. **/

    DEFINE BUFFER  ttLocation FOR TEMP-TABLE ttLocation.
    DEFINE BUFFER  ttCustomer FOR TEMP-TABLE ttCustomer.
   
    CREATE  ttLocation.
   
    ASSIGN
        ttLocation.LOCATIONID   = HEX-ENCODE(GENERATE-UUID)   
        ttLocation.LocationName = "The ~"BIG~" apple" .
       
    CREATE    ttCustomer.
   
    ASSIGN
        ttCustomer.CustomerID   = HEX-ENCODE(GENERATE-UUID)   
        ttCustomer.CustomerName = "D'Arcy, D'Lilah" .       
           
END PROCEDURE.

DEFINE VARIABLE queryWhereString AS CHARACTER   NO-UNDO.

RUN createMockTTRecords IN THIS-PROCEDURE.

queryWhereString = SUBSTITUTE('ttLocation.LocationName EQ &1', QUOTER("The ~"BIG~" apple") ).

DISPLAY getDynaQuery(INPUT 'ttLocation',        /** Buffer Name**/
                     INPUT 'LOCATIONID',        /** Field to Return**/
                     INPUT queryWhereString)   /** query Condition.**/
                     FORMAT "x(60)"

                    
queryWhereString = SUBSTITUTE('ttCustomer.CustomerName EQ &1', QUOTER("D'Arcy, D'Lilah") ).

DISPLAY getDynaQuery(INPUT 'ttCustomer',        /** Buffer Name**/
                     INPUT 'CustomerID',        /** Field to Return**/
                     INPUT queryWhereString)   /** query Condition.**/
                     FORMAT "x(60)".
 

Cecil

19+ years progress programming and still learning.
FOUND IT! Missing a full stop after the first DISPLAY statement.
 
Top