(for Clarion6 and C55, ABC)

Releafe for your Sql connections and interface. 

Demo Download               Installer Download

What it can do:
- Declare Stupid Temp Tables automatically, using any file/field defined in your dct
- Automatically set the Connection String using a wide range of Sql Servers
- Configure the connection window to connect to ODBC, MSSQL, ORACLE and SYBASE using the Clarion Accelerator Drivers.
- Configure the ODBC connection to MSSQL, Oracle, SQLAnywhere, ORACLE EXPRESS, MYODBC 2, MYODBC 3, FIREBIRD, POSTGRES, Sybase or Access
- Create automatically and configure the connection window allowing you to:
-- Initiate your program and configure the Connection String the first time, testing and debugging it.
-- Configure the window to Connect automatically to this connection without asking anything.
-- Configure the window to Connect to this connection asking for User/Password.
-- Configure the window to Connect to this connection changing all the possible connection values.
-- Store multiple server connection values in an ini file, allowing to remember the password, saving it encripted.

What You can do:
- You can have as many Stupid Temp Tables as you need, automatically defined by the template without having to have they defined in the dictionary.
- You can Connect to as many Sql servers or Sql Databases within the same server creating as many Connection Strings as you need.
- You can Fill Queues automatically simply supplying the Sql Query.
- You can send any Query to the Sql backend using any of your table buffers, including, of course, any of the Stupid Temp Tables defined by the template.
- Automatically Build the Select List of any of your tables to be used instead of the SELECT *
- Trace all interfacing it with TraceIt Template

• How to use it

• Global Template

• Stupid Temp Table

• Connect to Sql Backend Template

• Other Functions

• Demo Download

• Download & How to Install

• Future enhacements

How to use it:
- Having an app/dct (Local or Multi DLL) with all Sql tables defined with its own Connection String (Owner)
SqlIt does nothing with the dct or the sql backend table definitions.
- Just add the SqlIt Global Template to each app on your project
- The Global Template options will be available only at the exe app or at the data dll app
- All the SqlIt functions will be available in each app that has the Global Template.

Global Template:

Global Settings:
Window Language: The language used in the Connection Window, you can use any pre-defined language (English, Spanish, French or Portuguese) or you can translate the window using the ini file.
Ini File: The ini filename in which the window will put the connection settings and the translated strings.
Enable TraceIt: Trace all Template functions with TraceIt Template

Stupid Temp Table:
This template uses the Stupid Temp Table theory which simply means you use a temp table to receive the result of a query send to an Sql backend.
The temp table is defined over any other table of the dct that has a field long enough to keep the results of the columns queries to the sql backend.
There´s no need to add files to the dct or the sql database.
The Stupid Temp Tables (STTs from now on) are defined with all its columns data type as CSTRING.

Stupid Temp Table Settings:
Connection String: The global variable in which the template will define the Connection String. It may be or not the variable used in the dct sql tables. The SqlIt Connection Window will use this Global variable to declare the connection string to use with the Stupid Temp Table to connect to the Sql Backend. You can use (and this is advisable) the same Global variable you use in your dct tables.
Driver: The Clarion Accelerator driver which will be used to connect to the Sql backend.
Name: The filename for the Stupid Temp Tables (normally SQL)
Prefix: The filename Prefix (normally SQL)
Over File: Choose a file which has a field long enough to buffer your sql data
Number of fields: number of the Stupid Temp Tables fields (normally 100)
Over field: field of the Over File to buffer your sql data.
Additional Stts: If you need more than one Stupid Temp Table, the template can define additional ones adding a number to the Stupid Temp Table Name, Ex: if you define SQL as the STT name and set the additional Stts to 2, the template will define the following Stts:
SQL
SQL1
SQL2
The fields (columns) names are defined as C1 to Cnn
Then, the second field of the first STT is SQL:C2, the third field of the second STT is SQL1:C3, and so on.

Connect to Sql Backend Code Template:
Now, you can add this code template to the App Frame at the ThisWindow.Ini, before Opening Files

This template will add a Connection Window to your app, this connection window will allow you to configure the connnection parameters to connect to almost any sql backend
You can choose the Connection string, the STT file the template will use to connect and the Mode.

In Normal Mode, you will see the following window:

The Login Tab allow you to fill the Username and password for the sql backend, yuo can check to remember the password, to Automatic LogIn next time you call the window or to Automatically set the connection parameters but still ask for user/password next time.
The connection Tab:

allow you to connect to almost any sql backend, the backend type is the Accelerator driver choosed for the Stts, the ODBC options are:


Once you define all the data the backend need, you can Test the connection pressing the Test button, the window will try to OPEN() the Stt file showing you the Connection string it will use and informing the possible errors.
If the Test is OK then you go back to the Login tab, select the Auto functions and click Connect to finish the Connection procedure.

If you use the Clear Mode, it clears the Auto functions and return to the Normal Mode to redefine the sql backend connection.

If you use the AutoConn Mode then you will see the following window:

and will be prompt to fill the username and password to connect to the sql backend.

If you use the AutoLog Mode then you will se the following window:

this is also the final window of all the other connection modes.

Besides, you can use the following functions:

SqlIt_Connect (file pFile,*string pConnString, byte pSetAuto=0)
This is the procedure the Connect Window Code Template calls to set the Connection String variable and Connect to some Sql Backend. The pSetAuto parameter is the Mode.
Example: SqlIt_Connect( SQL, GLO:SqlConnect, 0)

SqlIt_FillQueue (file pFile,*queue pQueue,string pQuery)
This procedure fill the pQueue with the result set obtained running the pQuery with a pFile{prop:sql} statement. The pFile file must Opened before you call the procedure.
Example:
OPEN(SQL)
SqlIt_FillQueue ( SQL , MyQueue, 'select * from sysobjects')
CLOSE(SQL)

SqlIt_SendQuery (file pFile,string pQuery,byte pCheckError)
This procedure, sends the pQuery to the sql backend using pFile{prop:sql} statement, it can check for errors or not for you to check them in your routine. The pFile file must Opened before you call the procedure.
Example:
OPEN(SQL1)
SqlIt_SendQuery ( SQL1 , 'select * from sysobjects', 0)
LOOP
     NEXT(SQL1)
     IF ERROR() THEN BREAK.
     ! do what you need with the SQL1:C1 to SQL1:Cnn fields
END
CLOSE(SQL1)

SqlIt_BuildSelectList (file pFile,<string pPrefix>)
This function build a list of fields to replace the * from a select query. The problem is * means the list of fields ordered like the sql table and not like the dct table, then if you want to send query using a dct table buffer you can get fields type mismatches because of the different field order. Using this function you assert the resultset will match the file buffer.
Example:
OPEN(clients)
SqlIt_SendQuery ( clients , 'select ' & SqlIt_BuildSelectList(clients) & ' from clients', 0)
LOOP
     NEXT(clientes)
     IF ERROR() THEN BREAK.
     ! do what you need with the clients fields
END
CLOSE(clients)

SqlIt_Cript (string pSource, string pCriptKey, byte pDecript=0)
This function cripts/decripts a string using the XOR technique with the pCriptKey string and transform the result string to a series of numbers allowing to put the result string to an ini file.

Demo:
For easy portability, this Demo consists of an app ABC with a simple dct using an Access database with a table clients with two fields id and name, compiled in Local mode.
In the zip file goes a ini file that sets the ODBC connection to Access and the mdb file to SqlIt_Demo.mdb, it it fails please fill it yourself. The Username and Password must be blank.
You can test the connection window, the app allways prompt you for the mode to test them.
You can fill a Queue with a simple query, send a Query to the backend to calculate the clients record count, see the BuildSeectList result, etc.
  
                                                     

How to Install:

                                                       
The installer detects automatically your Clarion versions and allow you to install to the apropiate clarion directory, in the following subdirectories:  \3rdParty\Templates, Examples and Bin
The installer can register the templates for you or you can register ARMi_SqlIt.tpl (ABC) later manually.

Future enhacements:
Legacy compatibility
Automatic scripts running to upgrade the database
Browse and report generator

History:
August 2008: First beta version, ABC only