How to Use SQL in an Application - dummies

How to Use SQL in an Application

By Allen G. Taylor

To use SQL in an application, you have to combine it with a procedural language such as Visual Basic, C, C++, C#, Java, COBOL, or Python. Because of the way it’s structured, SQL has some strengths and weaknesses. Procedural languages are structured differently from SQL, and consequently have different strengths and weaknesses.

Happily, the strengths of SQL tend to make up for the weaknesses of procedural languages, and the strengths of the procedural languages are in those areas where SQL is weak. By combining the two, you can build powerful applications with a broad range of capabilities.

Recently, object-oriented rapid application development (RAD) tools, such as Microsoft’s Visual Studio and the open-source Eclipse environment, have appeared, which incorporate SQL code into applications developed by manipulating onscreen objects instead of writing procedural code.

Keep an eye out for the asterisk

The asterisk (*) serves as a shorthand substitute for “all columns in the table.” If the table has numerous columns, the asterisk can save a lot of typing. However, using the asterisk this way is problematic when you use SQL in an application program. After your application is written, you or someone else may add new columns to a table or delete old ones.

Doing so changes the meaning of “all columns.” When your application specifies “all columns” with an asterisk, it may retrieve columns other than those it thinks it’s getting.

Such a change to a table doesn’t affect existing programs until they have to be recompiled to fix a bug or make some change, perhaps months after the change was made. Then the effect of the * wildcard expands to include all the now-current columns. This change may cause the application to fail in a way unrelated to the bug fix (or other changes), creating your own personal debugging nightmare.

To be safe, specify all column names explicitly in an application instead of using the asterisk wildcard.

SQL strengths and weaknesses

If important information is buried somewhere in a single-table or multitable database, SQL gives you the tools you need to retrieve it. You don’t need to know the order of the table’s rows or columns because SQL doesn’t deal with rows or columns individually. The SQL transaction-processing facilities ensure that your database operations are unaffected by any other users who may be simultaneously accessing the same tables that you are.

A major weakness of SQL is its rudimentary user interface. It has no provision for formatting screens or reports. It accepts command lines from the keyboard and sends retrieved values to the monitor screen, one row at a time.

Sometimes a strength in one context is a weakness in another. One strength of SQL is that it can operate on an entire table at once. Whether the table has one row, a hundred rows, or a hundred thousand rows, a single SELECT statement can extract the data you want.

SQL can’t easily operate on one row at a time, however — and sometimes you do want to deal with each row individually. In such cases, you can use SQL’s cursor facility or you can use a procedural host language.

Procedural languages’ strengths and weaknesses

Procedural languages are designed for one-row-at-a-time operations, which give the application developer precise control over the way a table is processed. This detailed control is a great strength of procedural languages. But a corresponding weakness is that the application developer must have detailed knowledge about how the data is stored in the database tables. The order of the database’s columns and rows is significant and must be taken into account.

Because of the step-by-step nature of procedural languages, they have the flexibility to produce user-friendly screens for data entry and viewing. You can also produce sophisticated printed reports with any desired layout.

Problems in combining SQL with a procedural language

It makes sense to try to combine SQL and procedural languages in such a way that you can benefit from their mutual strengths and not be penalized by their combined weaknesses. As valuable as such a combination may be, you must overcome some challenges before you can achieve this perfect marriage in a practical way.

Contrasting operating modes

A big problem in combining SQL with a procedural language is that SQL operates on tables a set at a time, whereas procedural languages work on them a row at a time. Sometimes this issue isn’t a big deal. You can separate set operations from row operations, doing each with the appropriate tool.

But if you want to search a table for records meeting certain conditions and perform different operations on the records depending on whether they meet the conditions, you may have a problem. Such a process requires both the retrieval power of SQL and the branching capability of a procedural language.

Embedded SQL gives you this combination of capabilities. You can simply embed SQL statements at strategic locations within a program that you have written in a conventional procedural language.

Data type incompatibilities

Another hurdle to the smooth integration of SQL with any procedural language is that SQL’s data types differ from the data types of all the major procedural languages. This circumstance shouldn’t be surprising, because the data types defined for any procedural language are different from the types for the other procedural languages.

You can look high and low, but you won’t find any standardization of data types across languages. In SQL releases before SQL-92, data-type incompatibility was a major concern. In SQL-92 (and also in subsequent releases of the SQL standard), the CAST statement addresses the problem.