` Printed Icetips Article
Icetips Article

SQL Related Articles: Generic procedure to process SQL
2004-09-11 -- Shankarj
Newsgroups: softvelocity.clarion.databasedrivers

EDITOR NOTE:  There is a lot of code in this article and it may not format and align
properly.  

I normally have a dummy table defined for this and a common procedure to
process all prop:sql statements.

Dummy Table : ACQTBL with 24 CSTRING(256) fields - to be opened in the Frame

Prototype :

SQLQuery               FUNCTION(STRING, <*QUEUE>, <*?>, <*?>, <*?>, <*?>, <*?>,
<*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>, <*?>,
<*?>, <*?>, <*?>, <*?>, <*?>, <*?>),BYTE,PROC   !Execute SQL Statement

Procedure (Global) :

SQLQuery             PROCEDURE  (INP:Query, OUT:RQ, OUT:C01, OUT:C02, OUT:C03,
OUT:C04, OUT:C05, OUT:C06, OUT:C07, OUT:C08, OUT:C09, OUT:C10, OUT:C11,
OUT:C12, OUT:C13, OUT:C14, OUT:C15, OUT:C16, OUT:C17, OUT:C18, OUT:C19,
OUT:C20, OUT:C21, OUT:C22, OUT:C23, OUT:C24) ! Declare Procedure
QueryView       VIEW(ACQTBL)
                   PROJECT(C01)
                   PROJECT(C02)
                   PROJECT(C03)
                   PROJECT(C04)
                   PROJECT(C05)
                   PROJECT(C06)
                   PROJECT(C07)
                   PROJECT(C08)
                   PROJECT(C09)
                   PROJECT(C10)
                   PROJECT(C11)
                   PROJECT(C12)
                   PROJECT(C13)
                   PROJECT(C14)
                   PROJECT(C15)
                   PROJECT(C16)
                   PROJECT(C17)
                   PROJECT(C18)
                   PROJECT(C19)
                   PROJECT(C20)
                   PROJECT(C21)
                   PROJECT(C22)
                   PROJECT(C23)
                   PROJECT(C24)
                END

ExecOK          BYTE(0)
ResultQ         &QUEUE
Recs            ULONG(0)

QString         CSTRING(8192)   ! 8K Limit ???

NoRetVal        BYTE(0)         ! NO Return Values (True/False)
BindVars        BYTE(0)         ! Binded Variables Exist (True/False)
BindVarQ        QUEUE           ! Binded Variables
No                BYTE          ! No
Name              STRING(18)    ! Name
                END

  CODE
  ExecOK = False

  FREE(BindVarQ) ; BindVars = False ; NoRetVal = False

  IF (OMITTED(2) AND OMITTED(3) AND OMITTED(4)) THEN NoRetVal = True.   
   ! No Return Values - Possible an UPDATE/DELETE statement
  IF NOT OMITTED(2) THEN ResultQ &= OUT:RQ.                             
   ! If Result Queue Exists - Reference Queue

  IF INP:Query = ''
    BEEP ; MESSAGE('Missing Query Statement')
  ELSE
!~! Parse Query String for EMBEDDED Variables to be BOUND
     IF INSTRING('CALL ',UPPER(INP:Query),1,1)  
       ! Check if Stored Procedure is Called
       QString = CLIP(INP:Query)
       S# = 0 ;  L# = LEN(CLIP(QString))
       LOOP C# = 1 TO L#
         IF S# AND INLIST(QString[C#],',',' ',')') 
           ! End of Binded Variable
           BindVarQ.No   = RECORDS(BindVarQ) + 1
           BindVarQ.Name = QString[(S#+1) : (C#-1)]
           IF NOT OMITTED(2+BindVarQ.No)   
              ! Bound Variables MUST be at the Beginning of OUTPUT Variables
             EXECUTE BindVarQ.No
               BIND(CLIP(BindVarQ.Name),OUT:C01)
               BIND(CLIP(BindVarQ.Name),OUT:C02)
               BIND(CLIP(BindVarQ.Name),OUT:C03)
               BIND(CLIP(BindVarQ.Name),OUT:C04)
               BIND(CLIP(BindVarQ.Name),OUT:C05)
               BIND(CLIP(BindVarQ.Name),OUT:C06)
               BIND(CLIP(BindVarQ.Name),OUT:C07)
               BIND(CLIP(BindVarQ.Name),OUT:C08)
               BIND(CLIP(BindVarQ.Name),OUT:C09)
               BIND(CLIP(BindVarQ.Name),OUT:C10)
               BIND(CLIP(BindVarQ.Name),OUT:C11)
               BIND(CLIP(BindVarQ.Name),OUT:C12)
               BIND(CLIP(BindVarQ.Name),OUT:C13)
               BIND(CLIP(BindVarQ.Name),OUT:C14)
               BIND(CLIP(BindVarQ.Name),OUT:C15)
               BIND(CLIP(BindVarQ.Name),OUT:C16)
               BIND(CLIP(BindVarQ.Name),OUT:C17)
               BIND(CLIP(BindVarQ.Name),OUT:C18)
               BIND(CLIP(BindVarQ.Name),OUT:C19)
               BIND(CLIP(BindVarQ.Name),OUT:C20)
               BIND(CLIP(BindVarQ.Name),OUT:C21)
               BIND(CLIP(BindVarQ.Name),OUT:C22)
               BIND(CLIP(BindVarQ.Name),OUT:C23)
               BIND(CLIP(BindVarQ.Name),OUT:C24)
             END
           END

           ADD(BindVarQ,+BindVarQ.No)
           IF ERRORCODE()
             BEEP 
             MESSAGE('BindVarQ : ' & ERROR()) 
             BREAK
           END
           S# = 0
         END
         IF QString[C#] = '&'                      
           ! Start of Bound Variable
           IF S#
             BEEP
             MESSAGE('Improper Use of BINDED Variables')
             BREAK
           ELSE
             S# = C#
           END
         END
       END

       BindVars = RECORDS(BindVarQ)
     END

!~! Assume FILE is Open
     Access:ACQTBL.Open()
     IF ERRORCODE()
       MESSAGE('Error : ' & ERROR() & ' [' & ERRORCODE() &|
               ']|' & 'File Error: ' & FILEERROR() &|
               ' [' & FILEERRORCODE() & ']','OPEN TABLE')
     ELSE
       OPEN(QueryView)
       IF ERRORCODE() = 90
          IF FILEERRORCODE()
             MESSAGE('Error : ' & ERROR() & ' [' & ERRORCODE() &|
                     ']|' & 'File Error : ' & FILEERROR() &|
                     ' [' & FILEERRORCODE() & ']','OPEN VIEW')
          END
       ELSIF ERRORCODE()
          MESSAGE('Error : ' & ERROR() & ' [' & ERRORCODE() &|
                  ']|' & 'File : ' & ERRORFILE(),'OPEN VIEW')
       ELSE
         QueryView{PROP:SQL} = CLIP(INP:Query)
!~!*TEMP*
         ErrCode# = ERRORCODE()
         IF ERRORCODE() = 90 AND FILEERRORCODE() = 37000
            ErrCode# = 0
         END
!~!*TEMP*
!~!           IF ERRORCODE() = 90
         IF ErrCode# = 90
           IF FILEERRORCODE()
              MESSAGE('Error : ' & ERROR() & ' [' & ERRORCODE() &|
                      ']|' & 'File Error : ' & FILEERROR() &|
                      ' [' & FILEERRORCODE() & ']|' &|
                      CLIP(INP:Query),'EXECUTE QUERY')
           END
         ELSIF ErrCode#
            MESSAGE('Error : ' & ERROR() & ' [' & ERRORCODE() &|
                    ']|' & 'File: ' & ERRORFILE() & '|' &|
                    CLIP(INP:Query),'EXECUTE QUERY')
         ELSE
           IF BindVars OR NoRetVal
              ExecOK = True
           ELSE
            Recs = 0
            LOOP
              NEXT(QueryView)   ! Retrieve Records
              Recs += 1
              IF NOT ERRORCODE()
                 ExecOK = True
                 IF NOT OMITTED(2) THEN CLEAR(ResultQ). ! Clear Result Queue Buffer
                 IF NOT OMITTED(3)  THEN OUT:C01 = AQRY:C01.
                 IF NOT OMITTED(4)  THEN OUT:C02 = AQRY:C02.
                 IF NOT OMITTED(5)  THEN OUT:C03 = AQRY:C03.
                 IF NOT OMITTED(6)  THEN OUT:C04 = AQRY:C04.
                 IF NOT OMITTED(7)  THEN OUT:C05 = AQRY:C05.
                 IF NOT OMITTED(8)  THEN OUT:C06 = AQRY:C06.
                 IF NOT OMITTED(9)  THEN OUT:C07 = AQRY:C07.
                 IF NOT OMITTED(10) THEN OUT:C08 = AQRY:C08.
                 IF NOT OMITTED(11) THEN OUT:C09 = AQRY:C09.
                 IF NOT OMITTED(12) THEN OUT:C10 = AQRY:C10.
                 IF NOT OMITTED(13) THEN OUT:C11 = AQRY:C11.
                 IF NOT OMITTED(14) THEN OUT:C12 = AQRY:C12.
                 IF NOT OMITTED(15) THEN OUT:C13 = AQRY:C13.
                 IF NOT OMITTED(16) THEN OUT:C14 = AQRY:C14.
                 IF NOT OMITTED(17) THEN OUT:C15 = AQRY:C15.
                 IF NOT OMITTED(18) THEN OUT:C16 = AQRY:C16.
                 IF NOT OMITTED(19) THEN OUT:C17 = AQRY:C17.
                 IF NOT OMITTED(20) THEN OUT:C18 = AQRY:C18.
                 IF NOT OMITTED(21) THEN OUT:C19 = AQRY:C19.
                 IF NOT OMITTED(22) THEN OUT:C20 = AQRY:C20.
                 IF NOT OMITTED(23) THEN OUT:C21 = AQRY:C21.
                 IF NOT OMITTED(24) THEN OUT:C22 = AQRY:C22.
                 IF NOT OMITTED(25) THEN OUT:C23 = AQRY:C23.
                 IF NOT OMITTED(26) THEN OUT:C24 = AQRY:C24.

                IF NOT OMITTED(2) ! Result Queue
                  ADD(ResultQ)
                  IF ERRORCODE()
                     MESSAGE('Error : ' & ERROR() &|
                             ' [' & ERRORCODE() & ']','ADD TO QUEUE')
                  END
                END
              ELSE
                IF OMITTED(2) ! NO Result Queue
                  IF ERRORCODE() <> 33
                    IF ERRORCODE() = 90
                      CASE FILEERRORCODE()
                      OF ''          
                        ! Ignore NO File Error
                      OF '24000'     
                        ! Ignore Cursor State Error - 
                        ! Statement Executes BUT Error Returned
                      OF 'S1010'     
                        ! Ignore Function Sequencing Error - 
                        ! Statement Executes BUT Error Returned
                      ELSE
                        MESSAGE('Error : ' & ERROR() &|
                                ' [' & ERRORCODE() & ']|' & 'File Error : ' &|
                                FILEERROR() & ' [' & FILEERRORCODE() &|
                                ']','NEXT VIEW')
                      END
                    ELSE
                      MESSAGE('Error : ' & ERROR() & ' [' &|
                              ERRORCODE() & ']|' &|
                              'File : ' & ERRORFILE(),'NEXT VIEW')
                    END
                  END
                ELSE
                  BREAK
                END
              END
              IF OMITTED(2) THEN BREAK. ! NO Result Queue
            END
          END
        END
      END
    END
  END

  CLOSE(QueryView)

!  CLOSE(ACQTBL)
  Access:ACQTBL.Close()

  IF BindVars
    LOOP C# = 1 TO BindVars
      GET(BindVarQ, C#)
      IF BindVarQ.Name THEN UNBIND(CLIP(BindVarQ.Name)).
    END
  END

  FREE(BindVarQ)

  IF NOT OMITTED(2) THEN ResultQ &= NULL.

  RETURN ExecOK

Usage :

1)  SQLQuery('SELECT Code, Name FROM ACLDGR ORDER BY Code', LedgerQ, LQ:Code, LQ:Name)
     -- loads a queue

2)  IF SQLQuery('SELECT YrNo, Name FROM ACYEAR WHERE YrNo = ' & APLD:YrNo, ,
AFYR:YrNo, AFYR:Name) .....
     -- normal query

3)  SQLQuery('SELECT GETDATE()', , ServerDateNTime)
     Dt1# = DEFORMAT(ServerDateNTime[01:10],@D10-)
     Tm1# = DEFORMAT(ServerDateNTime[12:19],@T04)
     -- what you want to do

HTH

On Fri, Sep 10 2004 3:10 pm, "Shaun Mumford"  said:
>OK, created a table to take the results and works fine.
>
>Thanks
>
>  SHaun
>
>"Shaun Mumford"  wrote in message
>news:A921.1094811983.47574@discuss.softvelocity.com...
>> Just been looking at this more.
>>
>> My PROP:SQL knowledge is rather weak.
>>
>> I can run the select in SQL Query analyser and it works great. I just
>can't
>> see how you get it into Clarion. If I had a file declared and did a select
>> then I would have somewhere to do a NEXT and get the data. In this case
>that
>> is not the situation.
>>
>> How do I get the SQL to run and then retreive the result ?
>>
>> Thanks
>>
>>   Shaun
>>
>> "ShankarJ" wrote in message
>> news:1_135_2737@discuss.softvelocity.com...
>> > You could also use a PROP:SQL to get the Server Date/Time by a 'SELECT
>> > getdate()' statement, deformat the returned string into date and time
>and
>> > assign it to the columns BUT you will be a few seconds off by the time
>the
>> row
>> > is written on the database.
>> >
>> > HTH
>> >
>> > On Thu, Sep 9 2004 9:26 pm, "Shaun Mumford" said:
>> > >Thanks, had suspected that was the case, just hoped could get around
>it.
>> > >
>> > >Cheers,, Shaun
>> > >
>> > >"Arnor Baldvinsson" wrote in message
>> > >news:4140896a.5063991@news.softvelocity.com...
>> > >> Hi Shaun,
>> > >>
>> > >> On 9 Sep 2004 10:21:10 -0400, "Shaun Mumford" 
>> > >> wrote:
>> > >>
>> > >> >  Aud:AUDDateTime_DATE = TODAY()
>> > >> >  Aud:AUDDateTime_TIME = CLOCK()
>> > >>
>> > >> You can create a trigger in the db to fill in the date/time fields.
>> > >> Here is something I used where I needed this same kind of
>> > >> functionality:
>> > >>
>> > >> CREATE TRIGGER TradeNotes_Insert_Trigger
>> > >>   BEFORE INSERT
>> > >>   ON Office.TradeNotes
>> > >> REFERENCING NEW AS newTRDN
>> > >> FOR EACH ROW
>> > >> BEGIN
>> > >>   declare @Loc_NewUser     VarChar(50);
>> > >>   set @Loc_NewUser = (select IsNull(newTRDN.NoteEnteredBy,''));
>> > >>   if @Loc_NewUser = '' then
>> > >>     SET newTRDN.NoteDate      = GetDate(*);
>> > >>     SET newTRDN.NoteTime      = GetDate(*);
>> > >>     SET newTRDN.NoteEnteredBy = (select current user);
>> > >>   end if;
>> > >> END
>> > >>
>> > >> THis is ASA 9.0 not MSSQL.  I'm sure that MSSQL has some similar
>> > >> functions to get the date/time.  Note that in ASA it's exactly the
>> > >> same syntax to get the date and time!  You can also use a Now(*)
>> > >> function.
>> > >>
>> > >> Best regards,
>> > >>
>> > >> Arnór Baldvinsson
>> > >> Icetips Software
Printed May 8, 2024, 10:35 am
This article has been viewed/printed 35265 times.
Google search has resulted in 344 hits on this article since January 25, 2004.