SQL Tools: Two Of Everything

When you look at the list of SQL Tools functions, you’ll probably notice that there are two of just about everything. Here’s why…

SQL Tools is capable of handling extremely complex programs. In fact, the SQL Tools Pro DLL could theoretically be used to write a program that uses 256 different databases at the same time, and where each database has 256 SQL statements that are active, all at the same time. (A much more likely scenario would be a program that uses several databases with one active statement at a time, or one database with several active statements, but anything is possible.)

But most of the time, most programs will use a single database and a single statement at a time.

Here is an example of "two of everything"…

One of the most commonly used SQL Tools functions is called SQL_Statement. It is used to execute SQL statements, to tell a database what to do. To use the SQL_Statement function, you need to specify a Database Number (from 1-256), a Statement Number (from 1-256), a parameter like %PREPARE or %EXECUTE, and a string that contains the SQL statement.

Since most of the time you will be dealing with Database #1 and Statement #1, it can be very tedious to type 1,1 at the beginning of every single function's parameter list, so SQL Tools provides a complete set of "abbreviated" functions that use default values for the database number and statement number.

If a function name contains the word "Database", "Statement", 'Table", "Column", or "Result" it is a verbose function that requires you to specify a Database number and/or a Statement Number.

On the other hand, if a function name contains the abbreviation "DB", "Stmt", "Tbl", "Col", or "Res" it is an abbreviated function that does not allow the Database Number and Statement Number to be specified as parameters. (Please note that certain words like "Info" are never spelled out in function names and do not indicate an abbreviated function.)

Here is a specific example of a verbose function…

SQL_Statement 1, 1, %EXECUTE, "SELECT * FROM MYTABLE"

And here is the abbreviated function that would perform precisely the same operation…

SQL_Stmt %EXECUTE, "SELECT * FROM MYTABLE"

The SQL_Statement and SQL_Stmt functions are called "twins".

If you are writing a program that uses one database at a time, with one statement at a time, we recommend that you use the abbreviated functions. It will save you a lot of typing.

If you are writing a more complex program, you have a choice:

1. Use the verbose functions for everything,
2. Use the SQL_UseDB and SQL_UseStmt functions to specify which database and statement you want the abbreviated functions to handle.

For example, a program could use Database 1, Statement 3 followed by Database 2, Statement 9 in this way…<

SQL_Statement 1,3, %EXECUTE, "SELECT * FROM MYTABLE"

SQL_Statement 2,9, %EXECUTE, "SELECT * FROM YOURTABLE"

…or it could do this…

SQL_UseDB 1
SQL_UseStmt 3
SQL_Stmt %EXECUTE, "SELECT * FROM MYTABLE"

SQL_UseDB 2
SQL_UseStmt 9
SQL_Stmt %EXECUTE, "SELECT * FROM YOURTABLE"

If you often switch the default Database number and Statement number at the same time, you can also use this function…

…to change both at once.

The advantage of using the SQL_Use functions is that they are "sticky". In other words, once you use SQL_UseDB 2, all of the abbreviated functions will continue to use Database 2 until you use SQL_UseDB again to change the default. In that way, you can use the SQL_Use functions to specify a database or statement, and then perform a large number of abbreviated functions.

It is also possible to mix the verbose and abbreviated functions.

For example if a program did 90% of its work with one database and 10% with a handful of others, you could use the abbreviated functions to handle Database 1, Statement 1, and use the verbose functions for the other 10%. The use of verbose functions does not affect the setting of the SQL_Use functions.