Dynamic queries

[gonzotonka]

I'm trying to use a dynamic query to allow me to pass arguements into my code. I have 12 fields in our database that are set to either 1 or 2. If the field is set to 2 I use it as part of my query. Any example code involving dyanmic queries would be appreciated. Thanks!

 

[mra]

Hello Talker!

This is a procedure from our real world application?! It is used to get names for key values.

The procedure builds a dynamic query on the form "find first < table > no-lock where < key-col > = < key-val >".
It uses dynamic queries, dynamic buffers, and the buffer-field/buffer-value attributes.

The procedure is called with: run getName( output cName, "SELSKAB":U, iSelskab ).

Please note!!!!!
On HPUX11 version 9.0B the is a bug in the kernel. When you from a Windows client, fire many dynamic queries in a row,
the server process will terminate, taking every client connection with it. The problem is solved in 9.1.


I hope it helps.

Regards
Mike

Procedure getName:
/* The array aTabeller consists of "Tabelname,Key Col,Col name:[Col. name]:..."
   ARRAYSIZE is number of elements in the array.
  -------------------------------------------------------------------------------- */
&scoped-define ARRAYSIZE 9

def output param pNavn as char no-undo.
def input param  pType as char no-undo.
def input param  pKey as integer no-undo.

def var hBuf as handle no-undo.
def var hQry as handle no-undo.
def var hFelt as handle no-undo.

def var TabelNr  as integer no-undo.
def var Udtryk   as char no-undo.
def var Kolonner as char no-undo.
def var KolIdx   as integer no-undo.
def var cDelimiter as char no-undo.

def var aTabeller as char extent {&ARRAYSIZE} initial
      [ "selskab,selskabsnr,forkortelse:navn":U,
        "vej,vejnr,vejnavn:bynavn":U,
        "institution,institutionsnr,navn":U,
        "postdistrikt,integer(postnr),bynavn":U,
        "grund_omraade,grund_omraadenr,navn":U,
        "person,personnr,fornavn:efternavn":U,
        "kunde,kundenr,navn":U,
        "skema,skemanr,skema_navn":U,
        "skema_tur,skema_turnr,tur_navn":U
      ].

/* Contains delimitrers to insert between columns
   The array must contain as many row as aTabeller
  ---------------------------------------------------------------------- */
def var aDelimiter as char extent {&ARRAYSIZE} initial
      [ ":":U,
        ",":U,
        "":U,
        "":U,
        "":U,
        " ":U,
        "":U,
        "":U,
        "":U
      ].


do TabelNr = 1 to {&ARRAYSIZE}:
  if entry( 1, aTabeller[TabelNr] ) = pType then
    leave.
end.

/* Is the array excausted
*/
if TabelNr > {&ARRAYSIZE} then do:
  pNavn = "UKENDT TYPE":U.
  return.
end.

create buffer hBuf for table entry( 1, aTabeller[TabelNr] ).
create query hQry.

Udtryk = substitute( "for each &1 no-lock where &2 = &3":U,
                     entry( 1, aTabeller[TabelNr] ),
                     entry( 2, aTabeller[TabelNr] ),
                     string( pKey ) ).

hQry:set-buffers( hBuf ).
hQry:query-prepare( Udtryk ).
hQry:query-open.
hQry:get-first.

if hQry:num-results = 0 then do:
  pNavn = "FINDES IKKE":U.
  return.
end.


/* Find the names of columns and the delimiter to but between.
  ------------------------------------------------------------- */
Kolonner = entry( 3, aTabeller[TabelNr] ).
cDelimiter = aDelimiter[TabelNr].

do KolIdx = 1 to num-entries( Kolonner, ':':U ):
  hFelt = hBuf:buffer-field( entry( KolIdx, Kolonner, ':':U ) ).
  if pNavn = "":U then
    pNavn = hFelt:buffer-value.
  else
    pNavn = pNavn + cDelimiter + hFelt:buffer-value.
end.

hQry:query-close.

delete object hBuf.
delete object hQry.


end procedure.

[Edited by progresstalk on 21 Nov 2000 at 09:02 AM]