> Schema  > Products  > SRTransport  > Documentation  > Sql

 
  SQL in DataScript

Twelve SQL function calls allow you to send arbitrary SQL statements direct to your relational database. You can enhance your transports with custom database operations.

SRTransport automatically generates various kinds of SQL statements (insert, update, delete...), but if you need more control than this offers, you can send your own SQL statements and retrieve tabular results, using the functions described in this document.

You can open any number of database connections to any number of database servers. SRTransport may open two connections automatically, one for the data source and one for the data destination.

src_channel - the database channel for the SOURCE (may be null)
dst_channel - the database channel for the DESTINATION (may be null)
channel - same as dst_channel if not null, otherwise same as src_channel

Example #1 - Creating a database channel

   declare sqlChannel object
   set sqlChannel = SybaseCtChannel ( connectionInfo tuple (
      server "ALEXIS", database "test",
      user 'sa', password 'sap' ))

Example #2 - Submitting SQL and dumping results

   call Sql(sqlChannel,'SELECT/INSERT ...')

Example #3 - Retrieving all rows

   call SqlExecute (sqlChannel,'SELECT/INSERT ...')
   declare rows list = SqlRows (sqlChannel)

Example #4 - Retrieving rows one at a time

   call SqlExecute (sqlChannel,'SELECT * FROM person')
   print SqlTemplateRow (sqlChannel)
   while yes {
      set row = SqlRow(sqlChannel)
      if row = null
         break
   }
   print "That SQL was: ", SqlStatement(sqlChannel)

Example #5 - Controlling connections

   if SqlConnect(sqlChannel) = no
      print "Sorry, we can't connect."
   call SqlDisconnect (sqlChannel)
   if SqlConnected(sqlChannel) = no
      print "No, we are not connected."
   print "Our connection info is: "
   print sqlChannel.connectionInfo

Sql()

  procedure Sql ( sqlChannel object, sql string )

Executes the SQL on the sqlChannel and dumps the results. Returns success or failure. Automatically connects the channel to the server if not already connected.

SqlExecute()

  procedure SqlExecute ( sqlChannel object, sql string )

Executes the SQL on the sqlChannel, and leaves the results (if any) pending. Returns yes if successful. Automatically connects the channel to the server if not already connected.

SqlDump()

  procedure SqlDump ( sqlChannel object )

Dumps any pending results to standard output. No results are pending after this call. Called immediately after SqlExecute(). 

  Example:
      call SqlExecute (sqlChannel,"SELECT ...")
      call SqlDump(sqlChannel)

SqlStatement()

  procedure SqlStatement ( sqlChannel object )

Returns the last SQL statement (a string) that was executed on the channel.

SqlValue()

  procedure SqlValue ( sqlChannel object, sql string )

Executes the SQL on the sqlChannel and returns the first value (typically the only value) in the result. All remaining results are discarded. 

  Example:
    print SqlValue(sqlChannel, "SELECT getdate()")

Automatically connects the channel to the server if not already connected.

execsql()

  procedure execsql("SELECT/INSERT...")

execsql() is an old function call similar to SqlValue(). execsql() assumes the channel is in a variable called 'channel', which is true during a transport run.

SqlTemplateRow()

  procedure SqlTemplateRow ( sqlChannel object )

Returns a template row for any pending results. The row contains contains fields with no (null) values. Returns null if there are no results pending.

The purpose is to allow your code to know what fields will be returned in each row (and their types), before you receive the first row.

SqlRow()

  procedure SqlRow ( sqlChannel object )

Returns the next row (a tuple) in the result. Returns NULL if there are no more rows in the result set.

SqlRows()

  procedure SqlRows ( sqlChannel object )

Returns a list of all rows pending in the result. Returns an empty list if there are no rows in the result. Returns a null list if there are no results pending.

SqlConnect()

  procedure SqlConnect ( sqlChannel object )

Returns yes if the sqlChannel can connect to, or is already connected to a database server. Functions Sql(), SqlExecute() and SqlValue() automatically connect if not already connected.

SqlConnected()

  procedure SqlConnected ( sqlChannel object )

Returns yes if sqlChannel is currently connected to a database server.

SqlDisconnect()

  procedure SqlDisconnect ( sqlChannel object )

Disconnects sqlChannel from the database server.