JRI's Notes on Connecting to SQL

JRI's notes for how to load ODBC drivers, testing connections and writing BP code to connect to a SQL database

This web page is designed to help you get connected to a MS SQL database. It includes all the steps and some sample code that can be used (or tweaked as needed) to read (select), write (insert) and clear (truncate) a SQL table.

Installing ODBC Drivers
Accessing SQL from isql
Connecting to SQL from UniData
BP Code Snippets
1. Common SQL BP Code Snippets Required
2. Truncate logic (optional example)
3. Insert logic (optional example)
4. Read (select) logic (optional example)
5. CHECK.STATUS Required
6. Cleanly exit Required

Installing ODBC Drivers to Connect from UniData to a SQL Server

From a PC:

Download the SQL Server ODBC drivers for Linux (from EasySoft.com ==> ODBC Drivers ==> ODBC-SQL Server Driver)
odbc-sqlserver-1.10.13-linux-x86-64-ul64.tar

Save a copy of the tar file to w:\EasySoft (just in case)

Copy the tar file to the Linux box to /backup (use WinSCP or some similar utility)

Log into the Linux box as root

Change directory to the tar file location:
cd /backup

Extract the data from the tar file:
tar -xvf odbc-sqlserver-1.10.13-linux-x86-64-ul64.tar
This builds a sub-directory called xvf odbc-sqlserver-1.10.13-linux-x86-64-ul64

Drill into the new sub-directory:
cd odbc-sqlserver-1.10.13-linux-x86-64-ul64

Install the ODBC drivers:
./install
In general, take the default answers with the following exceptions:

Do you already have the unixODBC DM installed

i

Would you like to request a license now

y
2 (SQLServer)

Do you have a SQL server installed we can access?

y
server: daydscsql02.daysup.com
User id: (not published here)
Password: (not published here)
DB: TechDataSheets_Live
DSN: TechDataSheets_Live
This builds /etc/odbc.ini (which will need additional updates as we make more connections)

Create the following file /etc/profile.d/easysoft.sh with the following contents:
ODBCSYSINI=/etc
ODBCINI=/etc/odbc.ini
export LD_LIBRARY_PATH=/usr/local/easysoft/unixODBC/lib:$LD_LIBRARY_PATH
export LIBPATH=/usr/local/easysoft/lib:/usr/local/easysoft/oob/client/:/usr/local/easysoft/unixODBC/lib:$LIBPATH

Test the ODBC drivers from Linux:

cd /usr/local/easysoft/unixODBC/bin
./isql -v TechDataSheets_Live userID pw
You should now be in isql. Type the following to verify the connection:
select BrandName from TDS_Brands;

Once we have verified that Linux can make the connection to SQL via isql, we need to link UniData to the ODBC drivers. This does not require a stop/start UniData. If you are re-linking to new ODBC drivers, users should log out/in or else they will continue to use the old drivers.

Change directory to the UniData home directory's bin
cd $UDTHOME/bin

Link UniData to the ODBC drivers
./relink.dblibs /usr/local/easysoft/unixODBC/lib

Test the UniData connection from TCL:

cd $UDTHOME/demo (or wherever you want to test)
udt
CONNECT TechDataSheets_Live (or whatever DSN is needed)
userID: (not published here)
passwd: (not published here)
select BrandName from TDS_Brands;

Once we have verified UniData can (CONNECT) to the SQL Server, we need to get some BP programs working.
From the UniData demo account, look at:

BP BCI.DEMO2

BP BCI.DEMO3

From Avante, study the following 3 programs:

SYSS9176.1 Universal program to get the global SQL user Id, password & DSN suffix (Dev, Pilot or Live)

SYSS9177.1 Program to get the WHS, WHS name, Plant manager user id & Plant manager name (from ALIASES)
This calls SYSS9176.1

SYSS9177.1.DRIVER Simulates an Avante program that simply calls SYSS9177.1

Accessing SQL from isql

This assumes the ODBC drivers have been installed (likely from EasySoft.com) and are working. These commands are all run from Linux.

cd to the ODBC drivers
cd /usr/local/easysoft/unixODBC/bin

Connect to database (varies by which database you want)
./isql -v Capex_Dev userID pw
or
./isql -v shop_Dev userID pw

List ALL columns and ALL properties in the Avante_GLAccounts table w/ formatted columns (to make it easier to read)
select * from Information_schema.columns where Table_name like 'Avante_GLAccounts';

List ALL columns and KEY properties in a table
select cast(Column_name as varchar(30)), cast(Data_Type as varchar(20)), cast(Numeric_Precision as varchar(10)), cast(Numeric_Scale as varchar(10)) from Information_schema.columns where Table_name like 'Avante_GLAccounts';

Clear the table
truncate table Avante_GLAccounts;

List the data in the table
select GLAccount, cast(GLDescription as varchar(30)), Debits, Credits, Status, cast(StatusDescription as varchar(15)) from Avante_GLAccounts;

Connecting to SQL from UniData

This assumes the ODBC drivers have been installed (likely from EasySoft.com) and are working.

There are 3 tiers to test for SQL (assuming ODBC drivers have already been loaded):

Putty (most basic connection) Connect to "dayavante"

Log into the test Linux box (likely as root)

Connect to the SQL Server with iqsl:

cd /usr/local/easysoft/unixODBC/bin

./isql -v TestTechDataSheets_Live (not published here) (not published here)

Type any other SQL command as needed

Hit [RTN] w/o a command to exit isql

If isql does not work from putty, don't bother continuing until you get it working

Log into UniData (to test CONNECT - for the initial testing, we need to cd /av1/igi/ud/demo, then type udt)

From TCL, type CONNECT TestTechDataSheets_Live (or CONNECT TestTechDataSheets_Live VERBOSE ON)

Login: (not published here)

Pw: (not published here)

Type a desired SQL command to test syntax

Use the following commands within CONNECT as needed:

Type .w t to truncate based on column lengths

Type .w f to fold (wrap) based on column lengths

Type .W ? to see current settings

Type .w 1,15 to change column 1 to have a length of 15

Type .v on (turns verbose on - good for determining data type)

Type .v off (turns verbose off)

Type .q to quit (to TCL)

If the basic commands do not work, don't bother continuing until you get it working

See unidata_unibasicsqlclientinterfaceuserguide_v810, ch 3 (Using the UniBasic SQL Client Interface (BCI))

See unidata_sqluserguide_v819.pdf (Using UniData SQL)

Log into UniData and test a program

The canned DEMO account in ud should have a program called BP BCI.DEMO

You will need to data source ("TestTechDataSheets_Live"), login ("(not published here)") and password ("(not published here)") - this should be stored in a table so if the connection criteria ever changes, we only need to make a single control data change (not bunches of programs)

Things to try if the basic program does not work

UniData's ODBC stuff does not understand all the data types in SQL

Use the CONNECT command (above) with the VERBOSE ON option and make sure the data types are understood

If a SQL datatype is not understood by UniData, change the SQL view to CAST the native SQL data type to a known type:

SQL.CHAR

SQL.INTEGER (untested)

SQL.B.BINARY (untested)

SQL.B.NUMBER

SQL.B.INTDATE (untested)

SQL.B.INTTIME (untested)

See unidata_unibasicsqlclientinterfaceuserguide_v810, ch 4 (Using the UniBasic SQL Client Interface (BCI))

I have found that large text strings need to be CAST as VARCHAR with a length of 2000 (may change). You will likely need to scrub this data in some way such as:

Change char(13)s to nulls

Change "[li]" to @FM:"* "

Remove leading blank lines (caused by the prior change)

etc.

Common SQL BP Code Snippets

Required
See BP SOPS9132.4 for an example

This assumes the ODBC drivers have been installed (likely from EasySoft.com) and are working.

Any program that is going to connect to a SQL database must have the following include:
```
      $INCLUDE DEMO.INCLUDE ODBC.H
 
```

Every SQL database's name ends in _DEV, _PILOT or _LIVE. These are defined in /etc/odbc.ini. Any program connecting to a SQL database must contain the following lines of code:

*************************************************************************
* Step 1 - Determine datasource, username & password
*
* datasource == Name of the established ODBC datasource
* username   == Name of user as defined on the SQL Database Engine
* passwd     == Password of user
*************************************************************************
      BAR        = STR("=", 78)
      InsertStmt = ""
      SelectStmt = ""

      DSN.PASSED = "Capex"    ;* <========== the sql database (minus the _DEV, _PILOT or _LIVE)
      OPTIONS    = ""
      CALL SYSS9176.1(datasource, DSN.PASSED, username, passwd, OPTIONS, ERRORS)
      IF ERRORS THEN RETURN TO END.OF.PROGRAM
 
      CALL ACCOUNT.INFO(ACCOUNT, SERVER, DOMAIN, REALM.NAME, BOX.NAME, ACCOUNT.INFO.BANK, FUTURE3)
 
      SHOW.CRTS = @TRUE                                            ;* Turn on/off as you see fit
      IF (ACCOUNT.INFO.BANK<2> = "LIVE") THEN SHOW.CRTS = @FALSE   ;* NEVER show CRTs in LIVE realm
      IF SHOW.CRTS THEN
         CRT "Step 1 - Determine datasource, username & password..."
         CRT SPACE(9):"datasource: ":datasource
         CRT SPACE(9):"username..: ":username
         CRT SPACE(9):"passwd....: ":passwd
      END

SYSS9176.1 will take the DSN.PASSED (SQL database name) and

Determine the correct suffix (_DEV, _PILOT or _LIVE)
Verify that the full DSN exists in /etc/odbc.ini
Read global username & password (from DSCTBL ODBC)
Return either:
1. datasource, username & passwd or
2. The fatal error message in ERRORS
OPTIONS is for future enhancements
END.OF.PROGRAM is simply a label for the logic to jump to if things fail and exit the entire subroutine

Now we need to try to connect to the desired database. Add the following lines to make the connection:

*************************************************************************
* Step 2 - Allocate Database Environment
*
* These are the following steps necessary in any BCI application to
* connect to the SQL database.  These connection steps fall into four
* major parts.
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 2 - SQLAllocEnv..."
      END
      STATUS  = SQLALLOCENV(database.env)
      MODULE  = "ESTABLISH.BCI"
      ENVTYPE = "Database"
      Fn      = "SQLAllocEnv"
      GOSUB CHECK.STATUS


*************************************************************************
* Step 3 - Allocate Connection Environment
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 3 - SQLAllocConnect..."
      END
      STATUS  = SQLALLOCCONNECT(database.env, connection.env)
      ENVTYPE = "Connection"
      Fn      = "SQLAllocConnect"
      GOSUB CHECK.STATUS


*************************************************************************
* Step 4 - Connecting to Database
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 4 - SQLConnect..."
         CRT SPACE(9):"Connect to ":datasource:" with user id/pw"
      END
      STATUS  = SQLCONNECT(connection.env, datasource, username, passwd)
      ENVTYPE = "Connection"
      Fn      = "SQLConnect"
      GOSUB CHECK.STATUS


*************************************************************************
* Step 5 - Allocate Statement Environment
*          The Statement Environment is used when executing SQL statement
*          functions
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 5 - SQLAllocStmt..."
      END
      STATUS  = SQLALLOCSTMT(connection.env, statement.env)
      MODULE  = "ALLOC.statement.env"
      ENVTYPE = "Connection"
      Fn      = "SQLAllocStmt"
      GOSUB CHECK.STATUS

Truncate logic (optional example)

If you need to clear the SQL table before loading it, use a tweaked version of the following code:

*************************************************************************
* Step 6 - Clear the table with the "truncate table" command
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 6 - truncate Avante_GLAccounts table in ":datasource:"..."
      END
      TruncateStmt = "Truncate table Avante_GLAccounts"
      STATUS  = SQLEXECDIRECT(statement.env, TruncateStmt)
      MODULE  = "TRUNCATE"
      Expect  = ""
      ENVTYPE = "Statement"
      GOSUB CHECK.STATUS

Insert logic (optional example)

If you want to add (insert) data to the SQL table, you need to:

Step 7 - Build a "template" of the SQL insert command &
Step 8 - Pass SQL statement to data source to prepare it for SQLExecute &
Step 9 - Bind the BP variables to the question marks (?) in the insert statement

BP Variable	Tied to question mark #
GLACT.KEY	1
GL.DESC	2
DEBITS	3
CREDITS	4
GL.STATUS	5
GL.STATUS.DESC	6

Step 10 - Loop thru the Avante data and insert it into the SQL table

*************************************************************************
* Step 7 - Build the insert template command
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 7 - Build insert template"
      END
      * Build the SQL insert command using question marks as place-holder

      InsertStmt  = "insert into Avante_GLAccounts "
      InsertStmt := "(GLAccount, GLDescription, Debits, Credits, Status, StatusDescription) "
      InsertStmt := "values(?, ?, ?, ?, ?, ?);"
 
 
*************************************************************************
* Step 8 - Pass SQL statement to data source to prepare it for SQLExecute
*
* SQLPrepare will now pass the Insert Statement to the datasource.
* The Database usually parses the statement in preparation for the execute
* statement.
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 8 - Pass SQL statement to data source to prepare it for SQLExecute..."
      END
      STATUS  = SQLPREPARE(statement.env, InsertStmt)
      MODULE  = "LOAD.TABLE"
      Fn      = "SQLPrepare"
      ENVTYPE = "Statement"
      GOSUB CHECK.STATUS


*************************************************************************
* Step 9 - Bind the BP variables to the question marks (?) in the insert command
*
* The following SQLBindParameter's bind the previous SQLPrepare statement
* It is necessary to bind all parameters before they are executed.  This
* type of binding is typically done when data will be fetched several
* times.
*
* Using the Statement Environment, UniBasic variables will be
* translated and placed into a field element.
*
* Param 1 - Name of the Statement Environment
* Param 2 - Number of the table element we're referencing
* Param 3 - The type of conversion to be done to variable
*           SQL.B.BASIC translation allows translation to be determined by
*           the data type on the SQL database.  Can be used with any SQL
*           data type.
* Param 4 - SQL data type
* Param 5 - Scale or size of the field (when applicable with data type)
* Param 6 - Precision (when applicable with data type)
* Param 7 - UniBasic variable to load.
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 9 - Bind the BP variables to the question marks (?) in the insert command..."
      END
      Fn      = "SQLBindParameter"
      ENVTYPE = "Statement"
 
      STATUS = SQLBINDPARAMETER(statement.env, 1, SQL.B.BASIC, SQL.NUMERIC, 14, 0, GLACT.KEY)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDPARAMETER(statement.env, 2, SQL.B.BASIC, SQL.VARCHAR, 40, 0, GL.DESC)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDPARAMETER(statement.env, 3, SQL.B.BASIC, SQL.DECIMAL, 18, 2, DEBITS)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDPARAMETER(statement.env, 4, SQL.B.BASIC, SQL.DECIMAL, 18, 2, CREDITS)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDPARAMETER(statement.env, 5, SQL.B.BASIC, SQL.BIT, 1, 0, GL.STATUS)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDPARAMETER(statement.env, 6, SQL.B.BASIC, SQL.VARCHAR, 30, 0, GL.STATUS.DESC)
      GOSUB CHECK.STATUS

Now loop through your data (unique to each program) to populate the 6 bound BP variables (from step 9) and write/insert a row of data to SQL:

*************************************************************************
* Step 10 - Execute the insert to SQL
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 10 - insert data to SQL..."
      END
 
      Loop thru data and populate the 6 bound BP variables:
         GLACT.KEY
         GL.DESC
         DEBITS
         CREDITS
         GL.STATUS
         GL.STATUS.DESC
			
         * Insert to SQL table
         STATUS  = SQLEXECUTE(statement.env)
         MODULE  = "COMMON.EXPORT"
         Fn      = "SQLExecute"
         ENVTYPE = "Statement"
         GOSUB CHECK.STATUS
      Repeat

Read (select) logic (optional example)

If you need to read (select) data from a SQL table, you still need to do the common setup (with the appropriate database).

*************************************************************************
* Step 20 - Build & execute the 1st SQL Statement
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 20 - Build & execute the SQL statement..."
      END
      MODULE     = "GET.FROM.SQL1"
      ENVTYPE    = "Statement"
      Fn         = "SQLExecDirect"
      SelectStmt = "select FacilityNumber, SiteMgrName, Status from vwFacilitiesByADID"   ;* <== the actual SQL statement
      IF SHOW.CRTS THEN
         CRT SPACE(9):SelectStmt
      END
 
 
*************************************************************************
* Step 21 - Execute select statement via Statement Environment
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 21 - Execute select statement..."
      END
      STATUS = SQLEXECDIRECT(statement.env, SelectStmt)
      GOSUB CHECK.STATUS
 
 
*************************************************************************
* Step 22 - Bind the BP variables to the select command
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 22 - Bind SQL column names to BP variables..."
      END
      Fn     = "SQLBindCol"
      STATUS = SQLBINDCOL(statement.env, 1, SQL.B.NUMBER, FACILITY.NUMBER)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDCOL(statement.env, 2, SQL.B.CHAR, SITE.MGR.USER.ID)
      GOSUB CHECK.STATUS
 
      STATUS = SQLBINDCOL(statement.env, 3, SQL.B.NUMBER, SITE.STATUS)
      GOSUB CHECK.STATUS
 
 
*************************************************************************
* Step 23 - Loop through the rows of data and populate the BP variables
*
* As we loop through, we'll get the next row of data from columns 1 - 3,
* populating our new variables
*************************************************************************
      IF SHOW.CRTS THEN
         CRT "Step 23 - Loop through the rows of data and populate the BP variables..."
      END
      STATUS  = 0
      Fn      = "SQLFetch"
      ENVTYPE = "Statement"
      LOOP
      WHILE (STATUS # SQL.NO.DATA.FOUND) DO
         STATUS = SQLFETCH(statement.env)
 
         GOSUB CHECK.STATUS
         IF (STATUS # SQL.NO.DATA.FOUND) THEN
            (Do whatever you need to do with the retrieved variables from SQL)
         END
      REPEAT
 
 
      ENVTYPE = "Statement"
      GOSUB CHECK.STATUS

CHECK.STATUS

Required

After each BP/SQL command, you must test the result of the command. Note that EXIT.PROGRAM is a flag for a fatal error. Every program must have this logic (or possibly a slightly tweaked version).

CHECK.STATUS:
*************************************************************************
* It is a good idea to have some kind of error handling routine.
* Depending on the focus of the application it may be necessary to stop
* processing or it may be ok to continue.
*************************************************************************
      EXIT.PROGRAM = 0
      BEGIN CASE
         CASE (STATUS = SQL.ERROR)
            RETMSG = "STATUS is  ":STATUS:" -> Error Occurred."
            EXIT.PROGRAM = 1
         CASE (STATUS = SQL.INVALID.HANDLE)
            RETMSG = "STATUS is  ":STATUS:" -> Invalid Connection Handle."
            EXIT.PROGRAM = 1
         CASE (STATUS = SQL.NEED.DATA)
            RETMSG = "STATUS is  ":STATUS:" -> Data Required."
         CASE (STATUS = SQL.NO.DATA.FOUND)
            RETMSG = "STATUS is ":STATUS:" -> No Data Found."
         CASE (STATUS = SQL.SUCCESS)
            RETMSG = "STATUS is ":STATUS:" -> Successful."
         CASE (STATUS = SQL.SUCCESS.WITH.INFO)
            RETMSG = "STATUS is ":STATUS:" -> Successful With Info."
         CASE 1
            RETMSG = "STATUS is ":STATUS:" -> Unknown Status."
            EXIT.PROGRAM = 1
      END CASE

      BEGIN CASE
         CASE (ENVTYPE = "Connection")
            CONNECT.VAR = connection.env
         CASE (ENVTYPE = "Statement")
            CONNECT.VAR = statement.env
         CASE (ENVTYPE = "Database")
            CONNECT.VAR = database.env
      END CASE
 
      IF EXIT.PROGRAM THEN
         **************************************************
         * SQLError returns more information about errors *
         **************************************************
         ERROR.STATUS = SQLERROR(-1, CONNECT.VAR, -1, STATE, NATCODE, ERRTXT)
         ERRORS<-1> = "datasource: ":datasource
         ERRORS<-1> = "In Module: ":MODULE
         ERRORS<-1> = "Called ":Fn:" function in ":ENVTYPE:" environment."
         ERRORS<-1> = RETMSG
         ERRORS<-1> = "SQLSTATE , NATCOD are:" : STATE:" , ":NATCODE
         ERRORS<-1> = "Error text is ":ERRTXT
         IF SelectStmt THEN
            ERRORS<-1> = "SelectStmt: ":SelectStmt      ;* This variable name may change
         END
 
         ***************************************************
         * Return the the correct place to cleanly cleanup *
         * the ODBC stuff based on where the logic failed  *
         ***************************************************
         BEGIN CASE
            CASE (ENVTYPE = "Database") & (Fn = "SQLAllocEnv")
               RETURN TO BAIL1
            CASE (ENVTYPE = "Connection") & (Fn = "SQLAllocConnect")
               RETURN TO BAIL2
            CASE (ENVTYPE = "Connection") & (Fn = "SQLConnect")
               RETURN TO BAIL3
            CASE (ENVTYPE = "Connection") & (Fn = "SQLAllocStmt")
               RETURN TO BAIL4
            CASE (ENVTYPE = "Statement") & (Fn = "SQLExecDirect")
               RETURN TO BAIL4
         END CASE
 
         RETURN TO END.OF.PROGRAM
      END
      RETURN

Cleanly Exit