FairCom Direct SQL for C and C++

Introduction to Direct SQL

FairCom DB Direct SQL API makes it easy for C / C++ programmers to execute SQL statements against FairCom DB and to process their results.

The process of running and processing SQL statements is simple.

1. Connect to FairCom DB.
2. Get a handle to a SQL command that you will reuse to execute SQL statements.
3. Optionally turn autocommit on to ensure each command you execute is automatically committed or turn autocommit off to manually execute multiple statements under one transaction.
4. Optionally, for autocommit off, set the SQL isolation level.
5. Optionally prepare a SQL statement or a batch of SQL statements for execution.
6. Optionally set SQL parameters from a string value or from specific types: bigint, binary, char, date, time, timestamp, float, integer, nchar, and numeric.
7. Optionally set a longer or shorter timeout for executing the next SQL statement.
8. Execute a SQL statement, such as DDL or DML, or execute a SQL SELECT query.
9. Check for an error, optionally get the error message, and clear the error.
10. Optionally discover which columns are in the results along with each one's name, type, length, precision, scale and nullability.
11. Use a cursor to retrieve records from a SQL SELECT that you executed,
12. Retrieve field values from each record checking if the field is null. See the field data functions.
13. Free the cursor.
14. Optionally commit or rollback a transaction when autocommit is off.
15. Go back to step 3 and repeat as often as desired.
16. When you are done.
    1. Close all open SQL command handles.
    2. Close your connection to FairCom DB.

Using Transactions

• Each connection has one active transaction. If you need multiple concurrent transactions, you need to make multiple connections.
• FairCom’s SQL engine does not support nested transactions
• Direct SQL always has a transaction ready for use.
  • Direct SQL automatically creates a new transaction when you first create a connection and after each commit and abort. Thus, you never need to create a transaction.
  • Each SQL statement executes in the connection's currently open transaction. You cannot manually create a transaction.
• When you want each SQL statement to commit automatically after it is run, turn on autocommit.
• When you want to run multiple SQL statements in a single commit, turn off autocommit, run the statements, and commit the changes. If something goes wrong and you want to roll back the changes, abort the transaction.

Tips

• Once you create a command handle, reuse it each time you run a command. This is faster and more efficient than closing it and reallocating a new command handle. Reusing a command handle has no impact on how transactions work.
• When you want to repeatedly run the same SQL statement, prepare it first and then execute the prepared statement.