Oracle sql command

[K4sh]

[Solved] Oracle sql command

Hello there,

I'm trying to send a sql request to an Oracle base using progress 9.
With OE the following code can compile without any problem. Problem is that it's not working with Progress 9. I always get an error.
I need to send a sql request as the table been accessed is highly populated.

IF DBTYPE("grh") = "ORACLE" THEN DO:
    DEFINE VARIABLE     tt_1    AS HANDLE       NO-UNDO. /* Handle pour la copie de la table tbinterf dans table tempo */
    DEFINE VARIABLE     sqlreq  AS CHARACTER    NO-UNDO.
    ASSIGN sqlreq = "SELECT * FROM tbinterf /*+ INDEX (tbinterf I-typlotident) */ "
                  + "WHERE tbinterf.typinterf = 'CHRONOS_VALPLAN' "
                  + "AND tbinterf.nolot < 3 "
                  + "AND tbinterf.identifiant >= 'VAL|" + p_matr + "|" + STRING(YEAR(p_deb),"9999") + "/" + STRING(MONTH(p_deb),"99") + "/" + STRING(DAY(p_deb),"99") + "' "
                  + "AND tbinterf.identifiant <= 'VAL|" + p_matr + "|" + STRING(YEAR(p_fin),"9999") + "/" + STRING(MONTH(p_fin),"99") + "/" + STRING(DAY(p_fin),"99") + "' " 
             tt_1 = TEMP-TABLE p_tbinterf:HANDLE.
    RUN STORED-PROC grh.send-sql-statement LOAD-RESULT-INTO tt_1 NO-ERROR  (sqlreq).
END.

It seems that the load-into-result is not accepted.
Any help would be greatly appreciated.

 

[Stefan]

So you need to read the results from the proc-text-buffer manually. It looks something like so:

DEFINE VARIABLE iph AS INTEGER NO-UNDO.
DEFINE VARIABLE cSQLbuffer AS CHARACTER NO-UNDO

RUN STORED-PROC qrh.send-sql-statement iph = PROC-HANDLE (sqlreq).

FOR EACH qrh.proc-text-buffer WHERE PROC-HANDLE = iph:
   cSQLbuffer = REPLACE(REPLACE(qrh.proc-text-buffer.proc-text, "''":U, CHR(1)), CHR(1), "'":U).
   /* now assign results of csqlbuffer to your temp-table */  
   CREATE tt_1.
   ASSIGN
      tt_1.field1 = ENTRY( 1, cSQLbuffer, CHR(1) )
      tt_1.field2 = ENTRY( 2, cSQLbuffer, CHR(1) )
      .
END.

Note that the data returns is not delimited very nicely (at least on SQL Server it isn't), so we add CHR(1) as a delimiter in the SELECT statement passed:

SELECT field_1 + CHR(1) + field_2 FROM ... WHERE...

 

[K4sh]

Thank you Stefan for your reply.
I guessed that it would be very hard for me to parse each field of the long char returned.
As you said it's not well delimited. In fact it's impossible to delimiter each of the table field.
So i will give it a try tomorrow.
It seems that you replace each space with a chr(1) character. Will it work with character fields that may contain space inside ?

 

[K4sh]

Just gave it a try and it's not working. I guess my statement has somthing to do with the request failure.
Here is what i tried into the statement :

ASSIGN sqlreq = "SELECT seqinterf" + chr(1) + "nolot FROM tbinterf /*+ INDEX (tbinterf I-typlotident) */ "
              + "WHERE tbinterf.typinterf = 'CHRONOS_VALPLAN' "
              + "AND tbinterf.nolot < 3 "
              + "AND tbinterf.identifiant >= 'VAL|" + p_matr + "|" + STRING(YEAR(p_deb),"9999") + "/" + STRING(MONTH(p_deb),"99") + "/" + STRING(DAY(p_deb),"99") + "' "
              + "AND tbinterf.identifiant <= 'VAL|" + p_matr + "|" + STRING(YEAR(p_fin),"9999") + "/" + STRING(MONTH(p_fin),"99") + "/" + STRING(DAY(p_fin),"99") + "' "

It seems that the delimiter is not well inserted inside statement so that the entries are always outside range of the list.

Any idea ?

 

[K4sh]

Ok finally i got it to work with your idea.
for anyone that would like any help here is my code :

    ASSIGN sqlReq = "SELECT seqinterf||CHR(1)||typinterf||CHR(1)||nolot||CHR(1)||identifiant||" /* All the fields of my table */
                  + "CHR(1)||contenu1||CHR(1)||contenu2||CHR(1)||contenu3||CHR(1)||contenu4 "
                  + "FROM tbinterf /*+ INDEX (tbinterf I-typlotident) */ "
                  + "WHERE tbinterf.typinterf = 'CHRONOS_VALPLAN' " ...
                   ...
    RUN STORED-PROC grh.send-sql-statement sqlhnd = PROC-HANDLE NO-ERROR (sqlReq).
    FOR EACH grh.proc-text-buffer WHERE PROC-HANDLE = sqlHnd :
        CREATE p_tbinterf.
        ASSIGN sqlBuf = proc-text-buffer.proc-text
               p_tbinterf.field1   = DEC(TRIM(ENTRY(1,sqlBuf,CHR(1))))
               p_tbinterf.field2   = TRIM(ENTRY(2,sqlBuf,CHR(1)))
               p_tbinterf.field3   = INTEGER(TRIM(ENTRY(3,sqlBuf,CHR(1))))
               p_tbinterf.field4   = TRIM(ENTRY(4,sqlBuf,CHR(1)))
               p_tbinterf.field5   = TRIM(ENTRY(5,sqlBuf,CHR(1)))
               p_tbinterf.field6   = TRIM(ENTRY(6,sqlBuf,CHR(1)))
               p_tbinterf.field7   = TRIM(ENTRY(7,sqlBuf,CHR(1)))
               p_tbinterf.field8   = TRIM(ENTRY(8,sqlBuf,CHR(1))).
    END.

It works like a charm.