SQABasic "DatabaseFunctions" Script

 MODULE DESCRIPTION:

      Routines to work with data bases via DDE ComponentFunctions.
      These are not normally called directly.  These are called by the DDE
      as it processes DDE test tables containing commands for database functions.

      This functions connects to a data base through ODBC, so you have to have a data source
      pointing to that data base defined, using the appropriate driver.

      They uses SQABasic SQLRequest function to connect, using a basic connection string,
      concatenating the values of 'datasource', 'user' and 'password' parameters. If you
      need a more complex connection string, you have to specify it in the data source (this
      will be implemented in future versions).

      To use Database utilities outside of the DDE, use and consult the
      documentation on DatabaseUtilities.


Action Commands Global Variables Routine Details

User Dependencies:

(stuff the developer's library/script $INCLUDES at compile time.)
(Note: The order of items may matter and may be different for your code.)

Internal Dependencies:

(stuff this library needs at compile time.)

DDE Action Commands

 SUPPORTED ACTION COMMANDS:

      CopyDBTableToFile       'copies DB table to file
      CopyDBTableColumnToFile 'copies one or more DB table column(s) to file
      ExecSQLQuery            'executes the SQL Query
      GetDBTableColumnCount   'returns the column count on a given table
      GetDBTableRowCount      'returns the row count on a given table
      GetDBValue              'returns the value of a table attribute
      VerifyDBValue           'verifies the value of a table attribute
      VerifyDatabaseValue     'same as VerifyDBValue
      VerifyDBNULLValue       'verifies if the value of a table attribute is NULL
      VerifyDatabaseNULLValue 'same as VerifyDBNullValue
      DeleteDBTableRecords    'deletes records from a table in a database




Globals

    (none)

Routine Details



   Sub VerifyDBValue ()

 DESCRIPTION:

      Verifies the case-sensitive value of a table field from a data base.

      To return the value, it executes a SQL query sentence using an ODBC
      data source to connect to the data base.

      A table and field name are required. You also have to specify data source name,
      and optionally user id and password (this is because some drivers
      don't need a user/password).

      To do: if user id and/or password are not specified, don't include
      them in the connection string. This would allow the possibility
      to specify them in the data source definition.

      If the query returns more than one record, the returned value
      is the corresponding to the first one, unless the field name
      be something like COUNT(*) or SUM(InvoiceTot).

      A query condition can be included, usually it is. It can be any
      valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver
      you are using. For example: "CliBal > 100000 and CliCat = 'C'"


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      table's field to verify
       6      table name
       7      OPTIONAL query condition
       8      Data source name
       9      OPTIONAL user id
       10     OPTIONAL password
       11     expected value
       12     OPTIONAL prompt

 ERRORS:

      none

 Orig Author: Alejandro Blengio
 Orig   Date: OCT 17, 2000
 History:

      OCT 17, 2000    Original Release
      JUN 16, 2003    (bnat)  Modified the routine to read the prompt value from step table.




   Sub VerifyDBNULLValue ()

      Verifies if the value of a table field from a data base is NULL.

      Sometimes you have to check if a field's value is NULL. This
      NULL value is different from any other (even zero for numerics
      and null string for characters), so if you try to check it
      using VerifyDBValue() function, if will fail.

      A table and field name are required. You also have to specify data source name,
      and optionally user id and password (this is because some drivers
      don't need a user/password).

      To do: if user id and/or password are not specified, don't include
      them in the connection string. This would allow the possibility
      to specify them in the data source definition.


      A query condition can be included, usually it is. It can be any
      valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver
      you are using. For example: "CliBal > 100000 and CliCat = 'C'"


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      table's field to verify
       6      table name
       7      OPTIONAL query condition
       8      Data source name
       9      OPTIONAL user id
       10     OPTIONAL password
       11     OPTIONAL prompt


 Orig Author: Alejandro Blengio
 Orig   Date: OCT 17, 2000
 History:

      OCT 17, 2000    Original Release
      JUN 16, 2003    (bnat)  Modified the routine to read the prompt value from step table.




   Sub DeleteDBTableRecords ()

 DESCRIPTION:

      Deletes records in a table of a data base.

      A table name is required. You also have to specify data source name,
      and optionally user id and password (this is because some drivers
      don't need a user/password).

      To do: if user id and/or password are not specified, don't include
      them in the connection string. This would allow the possibility
      to specify them in the data source definition.

      A query condition can be included, usually it is. It can be any
      valid expression allowed by the WHERE clause of the SELECT sentence of the SQL driver
      you are using. For example: "CliBal > 100000 and CliCat = 'C'"


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      table name
       6      OPTIONAL query condition
       7      data source name
       8      OPTIONAL user
       9      OPTIONAL password
      10      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Alejandro Blengio
 Orig   Date: OCT 17, 2000
 History:

      OCT 17, 2000    Original Release
      JUN 16, 2003    (bnat)  Modified the routine to read the prompt value from step table.




   Sub ExecSQLQuery ()

 DESCRIPTION:

      Executes the query provided in a ^queryStr against the database.

      A data source name [DSN] and Query String are required. You also have to specify
      a variable to get the status code.  And optionally you can specify user id, password
      and prompt.  (this is because some drivers don't need a user/password).

      To do: if user id and/or password are not specified, don't include
      them in the connection string. This would allow the possibility
      to specify them in the data source definition.


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required data source name
       6      Required queryStr
       7      Required sqlStatus
       8      OPTIONAL UserID
       9      OPTIONAL Pwd
      10      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: MAY 21, 2003

 History:

      MAY 21, 2003    Original Release




   Sub GetDBTableColumnCount ()

 DESCRIPTION:

      This command retuns the total column count in the Table specified.  It executes a
      SQL query for a given table using an ODBC data source to connect to the data base.

      A table and the two return variables to strore the total column count and SQL status
      code are required. You also have to specify data source name, and optionally user id
      and password (this is because some drivers don't need a user/password).

      To do: if user id and/or password are not specified, don't include them in the connection
      string. This would allow the possibility to specify them in the data source definition.


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required data source name
       6      Required table name
       7      Required columnCount
       8      Required sqlStatus
       9      OPTIONAL UserID
      10      OPTIONAL Pwd
      11      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: MAY 22, 2003

 History:

      MAY 22, 2003    Original Release




   Sub GetDBValue ()

 DESCRIPTION:

      This command executes the query defined in the ^queryStr and retuns the single column value.
      It executes a SQL query on the database table using an ODBC data source suppied by the user.

      A ^queryStr and the two return variables to strore the result of the query and SQL status
      code are required. You also have to specify data source name, and optionally user id
      and password (this is because some drivers don't need a user/password).

      To do: if user id and/or password are not specified, don't include them in the connection
      string. This would allow the possibility to specify them in the data source definition.

      If the query returns more than one record, the returned value is the corresponding to the
      first one, unless the field name be something like COUNT(*) or SUM(InvoiceTot).



 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required Data source name
       6      Required queryStr
       7      Required dbValue
       8      Required sqlStatus
       9      OPTIONAL user id
      10      OPTIONAL password
      11      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: MAY 23, 2003


 History:

      MAY 23, 2003    Original Release




   Sub GetDBTableRowCount ()

 DESCRIPTION:

      This command retuns the total row count in the Table specified.  It executes a
      SQL query for a given table using an ODBC data source to connect to the data base.

      A table and the two return variables to strore the total row count and SQL status
      code are required. You also have to specify data source name, and optionally user id
      and password (this is because some drivers don't need a user/password).

      To do: if user id and/or password are not specified, don't include them in the connection
      string. This would allow the possibility to specify them in the data source definition.


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required data source name
       6      Required table name
       7      Required rowCount
       8      Required sqlStatus
       9      OPTIONAL UserID
      10      OPTIONAL Pwd
      11      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: MAY 22, 2003

 History:

      MAY 22, 2003    Original Release




   Sub CopyDBTableToFile ()

 DESCRIPTION:

      This routine will export the contents of a DBTable to a delimeted file.

      This routine will export the contents of a DBTable  to a delimeted file.
      The output file will conatin alternating lines of text, first the row number
      and then the data fetched from that row.  Not sure if this is a great idea,
      but that is how it works for now.

      To do: if user id and/or password are not specified, don't include
      them in the connection string. This would allow the possibility
      to specify them in the data source definition.  If no output directory is specified,
      the result file get saved under "Datapool\Test\".


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required data source name
       6      Required filename (for output)
       7      Required table name
       8      Optional column
       9      Optional row
      10      Optional sQLQuery
      11      Optional delimiter
      12      Optional dirOut
      13      Required sqlStatus
      14      OPTIONAL UserID
      15      OPTIONAL Pwd
      16      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: JUN 09, 2003

 History:

      JUN 09, 2003    Original Release
      Dec 09, 2003    Bugfix for optional dirOut parameter.




   Sub CopyDBTableColumnToFile ()

 DESCRIPTION:

      This routine will export the contents of one or more DBTable column(s) to a file.

      This routine will export the contents of one or more DBTable column(s) to a
      delimeted file.  The output file will conatin line(s) of text (the data fetched
      from that table) based on the ^SQLQueryString.  If columnDelimiter is omitted,
      a horizontal tab is used to delimit fields.

      To do: if user id and/or password are not specified, don't include them in the
      connection string. This would allow the possibility to specify them in the
      data source definition.  If no output directory is specified, the result file get
      saved under "Datapool\Test\".


 DATA TABLE PARAMETERS:

      FLD     CONTENT
      ---     ------------------------------
       5      Required data source name
       6      Required filename (for output)
       7      Required SQLQuery
       8      OPTIONAL dirOut
       9      Required sqlStatus
      10      OPTIONAL Delimiter
      11      OPTIONAL UserID
      12      OPTIONAL Pwd
      13      OPTIONAL prompt


 ERRORS:

      none

 Orig Author: Natarajan Balashanmugam (Nat)
 Orig   Date: Sep 16, 2003

 History:

      Sep 16, 2003    Original Release
      Dec 09, 2003    Bugfix for optional dirOut parameter.




   Sub Main ()

 DESCRIPTION:

      Entry point to process Database Commands.
      The routine merely reads the Global StepDriverTestInfo.testcommand and
      calls the appropriate subroutine to process it.

      If the testcommand is unrecognized it will be processed by the
      GenericMasterFunctions.GenericUnimplementedCommand routine.

 DATA TABLE PARAMETERS:

      none    -   the called subroutine has the requirements

 ERRORS:

      none

 Orig Author: Alejandro Blengio
 Orig   Date: OCT 17, 2000
 History:

      OCT 17, 2000    Original Release
      OCT 22, 2002    (CANAGL) Warn of unimplemented TF and TW record types.
      DEC 12, 2002    (CANAGL) Added support for XSLComponentActions.MAP
      SEP 12, 2003    (bnat)   Added more database action words.
      SEP 16, 2003    (bnat)   Added new database action word CopyDBTableColumnToFile.
      Dec 09, 2003    (bnat)   Bugfix for optional dirOut parameter.


Copyright (2001,2002,2003) ARTech Consultores S.R.L.

This program is free software; you can redistribute it and/or modify it under 

the terms of the GNU General Public License Version 2 as published by the Free 

Software Foundation. This license version can be viewed in its entirety at:

http://www.opensource.org/licenses/gpl-license.php



THIS CODE IS PROVIDED "AS IS".  THERE ARE NO REPRESENTATIONS OR WARRANTIES, 

EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO WARRANTIES OF MERCHANTABILITY, 

FITNESS FOR ANY PARTICULAR PURPOSE, AND NONINFRINGEMENT.  IN NO EVENT SHALL 

ANYONE BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY ARISING IN CONNECTION 

WITH THE CODE OR ITS USE.  



You should have received a copy of the GNU General Public License along with 

this program; if not, write to:

the Free Software Foundation, Inc., 

59 Temple Place, Suite 330

Boston, MA 02111-1307 USA

================================================================================