Connecting to an ODBC database

Before you can connect to your database from Citect, you will need to do some configuration work from the Windows Control Panel to create a Data Source. A Data Source is what Citect communicates with to query and update your database. To create a Data Source follow the procedure below:

Windows for Workgroups Example

• Go to the Windows Control Panel.
• Startup the ODBC setup utility (if you do not already have an ODBC icon in your control panel, you may have to install one. See the documentation provided with your ODBC driver).
• Data Sources are configured by selecting the ODBC driver you wish to From within the setup utility, you will need to set up a Data Source. To do this hit the Add... button.
• From the 'Add Data Source' dialogue box, select the ODBC driver you wish you Data Source to use. This will take you to a Setup Dialogue.
  Note: if your ODBC driver does not appear in the list of 'Installed ODBC Drivers:' then go back to the ODBC 'Data Sources' dialogue and install your driver by hitting the 'Drivers...' button.
• That is all you have to do. You have now created a Data Source which is ready to be used from within Citect.

Windows 95 Example

• Go to the Windows Control Panel.
• for 32 bit odbc see the windows NT description, for 16 bit odbc, the interface should be the same (information will be added later.)
• Windows NT 4.0 Example
• Go to the Windows Control Panel.
• Startup the ODBC setup utility
• Add either a User DSN or a System DSN
• Select the appropriate driver for the database you are using
• Enter a unique DSN name, and select the database you want to access
• (I did not get the File DSN working)

If you are running Windows NT, applications that are 16-bit can use either 16-bit or 32-bit ODBC drivers. 32-bit applications may use only 32-bit ODBC drivers.

If you are running Windows 95, 16-bit applications can only use 16-bit ODBC drivers and 32-bit applications can only use 32-bit ODBC drivers.

How to connect to your database from Citect

Citect provides two methods of communicating with your database: through Citect Devices and through the SQL class of cicode functions. Devices are most useful when you wish to use the automatic reporting and logging features of Citect. The SQL functions, on the other hand, provide absolute control and flexibility over your connections and queries. Querying a database via the Device system will be slower than constructing your own query via an SQL function due to the generic nature of a device.

Using the Cicode SQL class of functions

The SQLxxx() cicode functions provide the most flexible means of communicating with your database from within Citect. You have complete control over your connections. By carefully constructing your queries, and using indices, you also have the fastest way to access large volumes of data.

A typical connection and query
The query below shows how to query and retrieve data from a database.

INT
FUNCTION
GetNames()
   INT hSQL;
   STRING sName;
   INT Status;

   hSQL = SQLConnect( "DSN=Personnel" );
   IF hSQL <> -1 THEN
      Status = SQLExec( hSQL, "SELECT CNAME FROM FETISH" );
      IF Status = 0 THEN
         WHILE SQLNext( hSQL ) = 0 DO
            sName = SQLGetField( hSQL, "CNAME" );
            :
            // whatever you wish to do with this data...
            :
         END;
         SQLEnd( hSQL );
      ELSE
         Message( "Error", SQLErrMsg(), 48 );
      END;
      SQLDisconnect( hSQL );
   ELSE
      Message( "Error", SQLErrMsg(), 48 );
   END;
END

Things to notice are that for every connection via SQLConnect() there should also be a disconnection via SQLDisconnect(). Generally, connecting to an ODBC database takes a relatively large amount of time. This is especially the case if you are going through a Client and the database Server resides on another machine in the network. For this reason you should avoid connecting and disconnecting frequently - certainly not in a tight loop.

Another point to highlight is that for every query made via SQLExec(), you should eventually also call SQLEnd() to free up memory used by that query.

Finally, you should check that all SQLxxx() functions return successfully, and do a clean-up otherwise. For information on why an SQL function failed, call the cicode function SQLErrMsg().

A brief overview of SQL

This section provides a very brief introduction to Structured Query Language (SQL). Space, and the wish for brevity do not allow us to dwell overmuch on the full constructs and syntax of the language. For this sort of information you should refer to an SQL text. Most database packages that support SQL queries will have such a text. A good proportion of this section is devoted to instructing-by-example. All these examples will be based on the following two tables.

The SELECT statement

The SELECT statement will likely be the most common SQL statement that you will use. The syntax of this statement has many variations lending it a great deal of power. In most cases, however, all your querying needs will be met by a SELECT statement of the following format:

SELECT <Fields>
FROM <Tables>
[WHERE <conditions>]

When specifying more than one field or table, you should delimit them with commas.

Selecting an entire table
There are two ways you can select an entire table. The first highlights a deviation from the SQL statement syntax described earlier. If you wished to select the entire FETISH table then you could execute the statement "SELECT FETISH" via the SQLExec() cicode function:

SQLExec(hSQL, "SELECT FETISH");

The other method involves selecting all the fields from the table. This could be carried out as follows:

SQLExec(hSQL, "SELECT CNAME, SURNAME, PERSONID, STARTDATE, PET, FETISH FROM FETISH" );

Hint: When you wish to specify all fields, you can you a Wildcard (*). The above statement would then become:

SQLExec(hSQL, "SELECT * FROM FETISH" );

A basic conditional query
When querying databases it is important that take note of the letter case.

SQLExec(hSQL, "SELECT CNAME FROM FETISH WHERE SURNAME = 'Jumpy' " );

which will result in:

Note: Case sensitivity is important in string comparisons. The following highlights a common mistake giving the impression that the query has failed:

SQLExec(hSQL, "SELECT CNAME " +
"FROM FETISH " +
"WHERE SURNAME = 'JUMPY' " )

which will result in:

Notice that nothing has been selected. You can, however, use the SQL UPPER() function to get around this. Note: not all database systems or their ODBC drivers will necessarily support the UPPER() function; check with your documentation.

SQLExec(hSQL, "SELECT CNAME " +
"FROM FETISH " +
"WHERE UPPER(SURNAME) = 'JUMPY' " )

which will result in:

Queries based on a set of data - the IN and NOT IN operators
You can query a database asking for all records in which a field equals values found in a set of data. This is carried out via the IN operator. For example:

SQLExec(hSQL, "SELECT CNAME, PERSONID " +
"FROM FETISH " +
"WHERE SURNAME IN ( 'Jumpy', 'Engine' ) " );

which will result in:

Or contrarily, you could ask for all the records in which a field does not equal any value found in a set of data. This is carried out via the NOT IN operator. For example:

SQLExec(hSQL, "SELECT CNAME, PERSONID " +
"FROM FETISH " +
"WHERE SURNAME NOT IN ( 'Jumpy', 'Engine' ) " );

which will result in:

Queries with boolean conditions - the AND and OR operators
You can query a database asking for all records that satisfy multiple conditions. These conditions are separated by the AND and OR operators with respect to the condition logic that you wish. For example:

SQLExec(hSQL, "SELECT CNAME, PERSONID " +
"FROM FETISH " +
"WHERE SURNAME = 'Jumpy' " +
"OR SURNAME = 'Engine' ) " );

which will result in:

SQLExec(hSQL, "SELECT * " +
"FROM FETISH " +
"WHERE SURNAME IN ( 'Jumpy', 'Engine', 'Point' ) " +
"AND FETISH IN ( 'Earlobes', 'Armpits', 'Pins' ) " );

which will result in:

Querying for a range of values - the BETWEEN operator
It is possible to specify a range in a query using the BETWEEN operator.

SQLExec(hSQL, "SELECT * " +
"FROM FETISH " +
"WHERE STARTDATE BETWEEN '1994-06-20' AND '1994-07-30' " );

which will result in:

Querying search style - the LIKE operator
It is possible to carry out partial string matching in queries using the LIKE operator. You can specify whether the comparison is to match the beginning of a string, the end of a string, or any portion of a string. For example, 'p%' would match strings that begin with the letter 'p'; '%p' would match strings that end in the letter 'p'; and '%p%' would match strings that contained the letter 'p' anywhere in them.

SQLExec(hSQL, "SELECT * " +
"FROM FETISH " +
"WHERE CNAME LIKE '%n%' " );

which will result in:

Querying multiple tables - joining two tables
With SQL it is possible to query across many tables. This involves 'joining' the tables in a query. Tables can only be joined if they have a common column with values that can be compared. Joining tables in an SQL statement is implied by listing all the table in the FROM portion of the statement, and then specifying the join condition in the WHERE part of the statement. The following two examples show how the FETISH and COUNTRY tables have been joined for queries.

SQLExec(hSQL, "SELECT * " +
"FROM FETISH, COUNTRY " +
"WHERE FETISH.PERSONID = COUNTRY.PERSONID")

which will result in:

SQLExec(hSQL, "SELECT CNAME, FETISH " +
"FROM FETISH, COUNTRY " +
"WHERE COUNTRY = 'Jamaica' " +
"AND FETISH.PERSONID = COUNTRY.PERSONID" );

which will result in:

When querying multiple tables it may be necessary to use qualifiers to refer to field names. This is necessary when both table have fields of the same name. This can be noticed in the above examples when we refer to the PERSONID field; it has been prefixed with 'FETISH.' and 'COUNTRY.' as necessary.

The INSERT statement

To add records to a database table, use the INSERT statement. The syntax of this statement is shown below:

INSERT INTO <table> [(<fields>)]
VALUES (<expr>, ...)

The field names are optional, and if used should be delimited by commas. If you omit the column names then the values must be in the order of the columns that they are to enter. Hence for safety's sake, don't omit the column names. This function is used to add new records to a database. A fairly common example would be:

SQLExec(hSQL, "INSERT INTO FETISH " +
"( CNAME, SURNAME, PERSONID,STARTDATE, PET, FETISH ) " +
"VALUES ( 'Wallace', 'Wretch', 783, {10/27/1994}, 'Wallaby', 'Whips' ) " );

which will result in the database table looking like:

The UPDATE statement

To modify records in a database table, use the UPDATE statement. The syntax of this statement is shown below:

UPDATE <table>
SET <field> = <expression>,...
[WHERE <conditions>]

The expression may be a constant value or a subquery. Be wary when using the UPDATE statement, as it will modify every record that meets the WHERE criteria.

SQLExec(hSQL, "UPDATE FETISH " +
"SET FETISH = 'Cold-cream' " +
"WHERE PERSONID = 367" );

which will result in the database table looking like:

SQLExec(hSQL, "UPDATE FETISH " +
"SET STARTDATE = {06/21/1994}, FETISH = 'Jelly' " +
"WHERE CNAME = 'Jill' " );

which will result in the database table looking like:

The DELETE statement

To delete records in a database table, use the DELETE statement. The syntax of this statement is shown below:

DELETE FROM <table>
[WHERE <conditions>]

If you don't specify a WHERE condition then all records in the table are deleted.

SQLExec(hSQL, "DELETE FROM FETISH " +
"WHERE CNAME = 'James' " );

which will result in the database table looking like:

Expressing Dates and Times in SQL
The way in which you express a date in SQL varies across database systems. With dBase, you would normally specify a date in curly braces: {02/18/95}, and in Oracle you would use the format: to_date('02/18/95', 'MM/DD/YY').

To make it even a bit more complicated, the ODBC driver you use may ask for it in another format - a common ODBC format being: 'YYYY-MM-DD'.

Database independent date-time syntax
If you which your queries to be database independent, you can use the following syntax for dates and times:

[d'YYYY-MM-DD HH:MM:SS.FFFFFF']

The first letter after the opening square bracket must be either a:

d   Date,
t   Time, or
dt   Date and Time.

Regardless of whether you're specifying a Date, Time, or Date-Time, you still must provide the full 26 character string. An example of how to specify just a date follows:

[d'1995-02-18 00:00:00.000000']