Using SQL statements in C

The Standards for C,C++ and  SQL were designed to enable  Application portability.To achieve this portability it is necessary that both platforms must confirm to the international standards.The SQL/2 standard supports embedding sql statements in other languages.Oracle’s embedded SQL environment is called Pro*C.This Tutorial shows how to embed sql statements in C or C++.

A C or C++  program that uses sql statements compiled in 3 steps

1.Pro*C precompiler recognizes the SQL statements in the program.

2.Pro*C precompiler replace the SQL statements with appropriate function call in SQL runtime library.

3.The compiler compiles the code as normal C or C++ code and produce the Executable.

Use of C in SQL :Syntax

EXEC SQL sql_statement;

All sql statements in the program should start with EXEC SQL and should ends with a ;(semi colon).

There is a restriction that You should declare the variables before executable statements.

Declaring Host Variables

The C datatypes that can be used with Oracle include:

    char
    char[n]
    int
    short
    long
    float
    double
    VARCHAR[n]

A host variable reference must be prefixed with a colon “:” in SQL statements, but should not be prefixed with a colon in C statements.

Example

//use of Host variables in C program
int main()
{
int emp_no;
char *emp_name;

EXEC SQL INSERT INTO emp(empno, ename)
VALUES(:emp_no,:emp_name);
}

Declaring Pointers

int main()
{
int *emp_no;
char *emp_name;

//result will be written into *emp_no
EXEC SQL SELCT field_name INTO :emp_no WHERE —-

}

Declaring Arrays

int emp_number[50];
char name[50][11];
/* … */
EXEC SQL INSERT INTO emp(emp_number, name)
VALUES (:emp_number, :emp_name);

Embed C in SQL:SAMPLE PROGRAM

/* Begin program */

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION
emp_name VARCHAR2(20)
emp_number integer
EXEC SQL END DECLARE SECTION

EXEC SQL WHENEVER SQLERROR STOP

EXEC SQL CONNECT frans

/* Formulate query */

EXEC SQL SELECT employee_name,employee_number
INTO emp_name,emp_number
FROM employees
WHERE emp_number = 185678

/* Print emp_name and emp_number */

EXEC SQL DISCONNECT

/* End program */

Program Explanation

EXEC SQL INCLUDE SQLCA Incorporates SQL’s error handling mechanism (SQL Communications Area).
DECLARE SECTION Host variables must be declared to SQL prior to their use in any embedded SQL statements
EXEC SQL WHENEVER SQLERROR STOP An error handling mechanism must precede all executable embedded SQL statements in a program.
EXEC SQL CONNECT personnel Initiates access to the data base. A CONNECT statement must precede any references to a database.
EXEC SQL SELECT SQL select statement
EXEC SQL DISCONNECT close connection between the program and the database.

List of Embedded SQL Statements in C

Declarative Statements
EXEC SQL ARRAYLEN To use host arrays with PL/SQL
EXEC SQL BEGIN DECLARE SECTION

EXEC SQL END DECLARE SECTION

 To declare host variables
EXEC SQL DECLARE To name Oracle objects
EXEC SQL INCLUDE To copy in files
EXEC SQL TYPE To equivalence datatypes
EXEC SQL VAR To equivalence variables
EXEC SQL WHENEVER To handle runtime errors
Executable Statements
EXEC SQL ALLOCATE To define and control Oracle data
EXEC SQL ALTER
EXEC SQL ANALYZE
EXEC SQL AUDIT
EXEC SQL COMMENT
EXEC SQL CONNECT
EXEC SQL CREATE
EXEC SQL DROP
EXEC SQL GRANT
EXEC SQL NOAUDIT
EXEC SQL RENAME
EXEC SQL REVOKE
EXEC SQL TRUNCATE
EXEC SQL CLOSE
EXEC SQL DELETE To query and manipulate Oracle data
EXEC SQL EXPLAIN PLAN
EXEC SQL FETCH
EXEC SQL INSERT
EXEC SQL LOCK TABLE
EXEC SQL OPEN
EXEC SQL SELECT
EXEC SQL UPDATE
EXEC SQL COMMIT To process transactions
EXEC SQL ROLLBACK
EXEC SQL SAVEPOINT
EXEC SQL SET TRANSACTION
EXEC SQL DESCRIBE To use dynamic SQL
EXEC SQL EXECUTE
EXEC SQL PREPARE
EXEC SQL ALTER SESSION To control sessions
EXEC SQL SET ROLE
EXEC SQL EXECUTE

END-EXEC

 To embed PL/SQL blocks

The following rules apply to embedded SQL statements in C:

* Do not split the EXEC SQL keyword pair between lines.

* You must use the SQL statement terminator. If you do not use it,This may cause indeterminate errors.

* C/C++ comments can be placed before the statement initializer or after the statement terminator.
* Multiple SQL statements and C/C++ statements may be placed on the same line.
* The SQL precompiler leaves carriage returns, line feeds, and TABs in a quoted string as is.
* SQL comments are allowed on any line that is part of an embedded SQL statement. These comments are not allowed in dynamically executed statements.

Written by admin on February 15th, 2011 with comments disabled.
Read more articles on Pro *C.

Comments disabled

Comments on this article have been disabled.