Embedded SQL in AutoScript


In kosmos/BOS you can use SELECT statements to call data from the database
that you will use to a form, to a report or to a script file.
This article shows the code for creating and running a query.
You must close your query inside two commands, start_sql and end_sql.

       start_sql “qname “KOSMOS”
               SELECT column_name(s)
               FROM table_name
       end_sql
       q = QueryByName("qname")
       call TExecute(q)

or (for an unamed query)

       start_sql “* “KOSMOS”               
            SELECT column_name(s)                
            FROM table_name
     
      
end_sql       
       q = QueryByName("")       
       call TExecute(q) 

   
See the example for a parameter query.

In this example the users will be prompted to type the name of a town when they run the query

      city = Input(“ Enter Parameter Value ”;” Which Town ? ”;””)
      param = Input(“ bar title ”;” prompt ”;””)

       start_sql “CUSTOMER_LIST “KOSMOS”
               SELECT CUSTOMER.NAME, CUSTOMER.PHONE, CUSTOMER.TOWN,
                CUSTOMER.ADDRESS
               FROM CUSTOMER
               WHERE CUSTOMER.CITY = :PP
 
       end_sql
       q = QueryByName("CUSTOMER_LIST")
       call TSetParam(q,"PP",param)
       call TExecute(q)