Home

Precompiler User`s Manual - ALTIBASE Customer Support

1. 7 4 4 2 Arguments savepoint name This is the name of a savepoint to which to return 7 4 4 3 Description This statement is used to restore the database to the state that existed prior to the commencement 99 Embedded SQL Statements 7 4 Using Other Embedded SQL Statements of execution of the current transaction or to the state that existed when a specified savepoint was defined That is this statement undoes all or some of the operations that have been performed by the current transaction If a previously defined savepoint is specified in this statement the current transaction will be only partially rolled back That is the operations performed since the savepoint was defined will be undone 7 4 4 4 Consideration Note that an error is never raised when this statement is executed even when the autocommit mode of the current session is AUTOCOMMIT 7 4 4 5 Example The following example shows how to use the ROLLBACK statement in an embedded SQL statement EXEC SQL ROLLBACK or EXEC SOL ROLLBACK TO SAVEPOINT sp 7 4 5 BATCH This statement is used to change a connection property so as to activate or deactivate batch pro cessing 7 4 5 1 Syntax EXEC SQL BATCH ON OFF 7 4 5 2 Arguments None 7 4 5 3 Description When batch processing mode is active the execution i e transmission to the server of embedded SQL statements is delayed until the transaction is committed or a SELECT statement is
2. SELECT statement select sc gt EXEC SQL BEGIN DECLARE SECTION short s dno char s dname 3041 char s dep location 9 1 EXEC SQL END DECLARE SECTION EXEC SOL SELECT DNAME DEP LOCATION INTO s dname s dep location FROM DEPARTMENTS WHERE DNO s dno INSERT statement insert sc gt EXEC SOL BEGIN DECLARE SECTION char s_gno 10 1 char s gname 2041 char s goods location 9 1 int s stock double s price EXEC SOL END DECLARE SECTION EXEC SOL INSERT INTO GOODS VALUES s gno s gname S goods location S stock s price More detailed information about the syntax of each embedded SQL statement will be provided later in this chapter 7 1 2 Static Versus Dynamic SQL Statements Embedded SQL statements can be broadly classified as either static SOL statements or dynamic SOL Precompiler User s Manual 82 7 1 Overview statements depending on whether the contents of the SQL statement are determined when the application is written or at runtime This chapter describes only static SQL statements For more information about dynamic SQL statements please refer to Chapter7 Embedded SQL Statements Embedded SQL statements can also classified into the following categories based on how they han dle data and the role that they play 7 1 2 1 Host Variable Declaration Section These statements are used to delimit a block of code for declaring host variables for use in other embedded SQL statemen
3. declare indicator variables int s goods location ind int s price ind EXEC SOL END DECLARE SECTION set host variables strcpy s gno X111100002 strcpy s gname XX 101 strcpy s goods location FD0003 S stock 5000 S price 9980 21 set indicator variables S goods location ind SQL NULL DATA S price ind SQL NULL DATA EXEC SOL INSERT INTO GOODS VALUES s gno S gname S goods location s goods location ind S stock S price s price ind Precompiler User s Manual 32 3 4 Classifying Indicator Variables 3 4 Classifying Indicator Variables Indicator variables are classified as either input indicator variables or output indicator variables depending on whether they are used with output host variables or input host variables 3 4 1 Output Indicator Variables If the column corresponding to an output host variable does not have a NOT NULL constraint it is essential that an indicator variable be used for the host variable The reason for this is that when the value of a selected or fetched column is NULL and an indicator variable is not being used the result of execution of the embedded SQL statement sqlca sqlcode will be SQL SUCCESS WITH INFO and a warning message will be returned in the variable sqlca sql errm sqlerrmc If the value of the indicator variable is 1 SOL NULL DATA this means that NULL will be returned from the column Therefore the value of the output host
4. short dno 3 char emp tel 3 15 1 struct tagl int il int i2 int i3 var1 10 EXEC SQL END DECLARE SECTION EXEC SQL UPDATE EMPLOYEES SET DNO dno EMP TEL emp tel WHERE ENO eno acceptable EXEC SQL UPDATE T1 SET I1 vari 0 il I2 varl 0 i2 WHERE I1 varl 0 i3 acceptable 10 2 2 2 Example The following example shows the use of an array type host variable as an input host variable in an UPDATE statement lt Sample Program arraysl sc gt EXEC SQL BEGIN DECLARE SECTION int a eno 3 short a dno 3 char a emp tel 3 1541 EXEC SQL END DECLARE SECTION a eno 0 10 a enodlll e dE a eno 2 12 a dno 0 2001 a dno 1 2001 a dno 2 2001 strcpy a emp tel 0 01454112366 strcpy a emp tel 1 0141237768 strcpy a emp tel 2 0138974563 EXEC SQL UPDATE EMPLOYEES SET DNO a dno EMP TEL a emp tel WHERE ENO a eno 10 2 3 DELETE The array types that can be used with the DELETE statement are as follows Simple arrays Structures comprising arrays as individual elements thereof Precompiler User s Manual 136 10 2 Using Host Array Variables in Embedded SQL Statements 10 2 3 1 Limitation If any of the host variables is an array all host variables must be arrays and furthermore the number of elements in each array must be the same Arrays of structures cannot be used as host variables in the WHERE clause of a DELETE statement Howeve
5. 2 3 Programming using Embedded SQL Statements apre apre option lt filename gt 2 3 6 1 Example In the following example the connect1 sc file is precompiled apre connectl sc Precompiler User s Manual 22 3 Host Variables and Indicator Variables 23 Host Variables and Indicator Variables 3 1 Host Variables 3 1 Host Variables 3 1 1 Overview Host variables are responsible for data exchange between an application written in a host language and a database server In other words host variables store data that have been read from table col umns data that are to be inserted into table columns etc 3 1 2 Declaring Host Variables Host variables are declared as follows Host variables must be declared in the host variable declaration section or the function param eter declaration section If an attempt is made to use a variable in an embedded SQL statement and the variable was not first declared in either the host variable declaration section or the function argument dec laration section an error saying The host variable variable name is unknown will be raised during the precompile operation For more information about the host variable declaration section and the function argument declaration section please refer to Chapter4 Host Variable Declaration Section The syntax for declaring host variables is as follows datatype variable name This is the same as when declaring variables in a C or C
6. ALTIBASE OBJS CC shmutil g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib shmutil o LIBS createdb ALTIBASE OBJS CC createdb g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib createdb o LIBS destroydb ALTIBASE OBJS CC destroydb g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib destroydb o LIBS checkServer ALTIBASE OBJS CC checkServer g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib checkServer o S ALTIBASE HOME lib checkServerPid o LIBS killCheckServer ALTIBASE OBJS CC killCheckServer g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib killCheckServer o ALTIBASE HOME lib checkServerPid o LIBS restoredb ALTIBASE OBJS CC restoredb g0 04 call shared LIBDIRS g0 O4 call shared usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME 1lib restoredb o lmm lqp lsm lid lpd ltli lrt LIBS isql ALTIBASE OBJS CC isql g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib libisqlobj a lodbccli lutil LIBS Precompiler User s Manual 232 Frequently Asked Questions audit ALTIBASE OBJS CC
7. EXEC SOL END DECLARE SECTION s dno 1001 EXEC SOL SELECT DNAME DEP LOCATION INTO s dname s dep location FROM DEPARTMENTS WHERE DNO s dno 3 2 2 Input Host Variables Input host variables are used wherever output host variables are not used Their primary role is to specify data to be used in SQL statements For example an input host variable can be used in the WHERE clause of a SELECT statement to specify a value that is part of a condition or in the VALUES clause of an INSERT statement to specify a value to be inserted into a particular column of a record An input variable can be used anywhere in an embedded SQL statement where the use of a scalar expression would be allowed Note however that in order to use a host variable in the select list or the GROUP BY or ORDER BY clause of a SELECT statement its type must be specified using the CAST operator in the SOL statement An input host variable can be used in a WHERE clause However be aware that when using a host variable in a join predicate in a WHERE clause the query optimizer will be unaware of its data type and thus can only use the NL join method when creating an execution plan To overcome this limita tion and allow the optimizer to choose a more efficient joining method use the CAST operator in the SQL statement to let the optimizer know the type of the host variable Precompiler User s Manual 26 2 1 Examples 3 2 Classifying Host Variables The following
8. The ALTIBASE_APRE macro is intended for use with the ifdef and ifndef preprocessor direc tives Precompiler User s Manual 54 5 6 Considerations 5 6 Considerations This section explains some considerations to keep in mind when using the APRE C C Preproces sor 5 6 1 Defining Macros If a macro is defined using a command line option when executing the C compiler then in most cases the same macro must also be defined using the D command line option when using the APRE C C precompiler For example if a C compiler is executed from the command line as shown below cc DDEBUG Then the DEBUG macro should also be defined when executing the APRE C C precompiler before executing the C compiler This is shown below apre DDEBUG 55 C Preprocessor 5 6 Considerations Precompiler User s Manual 56 6 Host Variable Data Types 57 Host Variable Data Types 6 1 Overview 6 1 Overview Host variables differ from variables in C or C applications in how they are used in their functional ity and in how they are declared It follows that host variable data types are different from C data types The following data types can be used as host variables Most fundamental C and C data types The fundamental C C types that can be used for host variables are described in detail later in this chapter 6 2 Fundamental C C Data Types The extended data types provided by the APRE environment for us
9. 10 1 char a gname 3 20 1 char a goods location 3 9 1 int a stock 3 double a price 3 EXEC SOL END DECLARE SECTION strcpy a gno 0 X111100001 strcpy a gno 1 X111100002 strcpy a gno 2 X111100003 strcpy a gname 0 XX 201 strcpy a gname 1 XX 202 strcpy a gname 2 XX 203 strcpy a goods location 0 AD0010 strcpy a goods location 1 AD0011 strcpy a goods location 2 AD0012 a stock 0 1000 a stock 1 1000 a stock 2 1000 a price 0 5500 21 a price 1 5500 45 a price 2 5500 99 EXEC SOL INSERT INTO GOODS VALUES a gno a gname a goods location a stock a price 10 2 2 UPDATE The array types that can be used with the UPDATE statement are as follows Simple arrays Structures comprising arrays as individual elements thereof 135 Using Arrays in Embedded SQL Statements 10 2 Using Host Array Variables in Embedded SQL Statements 10 2 2 1 Limitation If any of the host variables is an array all host variables must be arrays and furthermore the number of elements in each array must be the same Arrays of structures cannot be used as host variables in the SET or WHERE clause of UPDATE statements However it is possible to use an individual element of one structure that is itself one element in an array of structures in the SET or WHERE clause as shown in the following example Example EXEC SQL BEGIN DECLARE SECTION int eno 3
10. Arrays cannot be used as input host variables in SELECT statements and cursor related state ments Example of Unacceptable Usage EXEC SQL BEGIN DECLARE SECTION int var1 10 int var2 10 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL SELECT I1 I2 INTO varl var2 FROM T1 WHERE I1 var3 unacceptable 147 Using Arrays in Embedded SQL Statements 10 5 Structures and Arrays 10 5 Structures and Arrays Both arrays of structures and structures containing arrays as constituent elements thereof can be declared and used as host variables 10 5 1 Arrays of Structures The following explains how to declare arrays of structures and use them 10 5 1 1 Advantages The use of an array of structures is convenient when using an INSERT statement to insert multi ple records into all of the columns in a table Similarly it is convenient to use an array of structures when using a SELECT or FETCH state ment to retrieve multiple records from all of the columns in a table 10 5 1 2 Disadvantages Because an indicator variable cannot be specified for use with an array of structures it is impossible to use an array of structures when any of the values to be input or any of the values to be retrieved using a SELECT or FETCH statement are NULL 10 5 1 3 Limitations Because the use of multi dimensional arrays as host variables is not supported an array of structures cannot be used if any of the constituent elemen
11. Example 3 The following example shows how to declare a structure that contains array elements and use it as a host variable Sample Program arrays1 sc gt Precompiler User s Manual 132 10 1 Overview EXEC SQL BEGIN DECLARE SECTION struct char gno 3 1041 char gname 3 2041 char goods location 3 9 41 int stock 3 double price 3 a goods2 EXEC SQL END DECLARE SECTION 10 1 2 Advantages Using array type host variables will have a positive impact on system performance A look at one or two examples will give you an idea of the magnitude of the performance improvement that can be expected Compare the use of an array type host variable with the use of a non array type host variables in the execution of an INSERT statement Suppose for example that it is desired to insert 1000 records If an array type host variable having 1000 elements is used it is only necessary to execute an INSERT statement one time to insert all 1000 records In contrast when using a non array type host variable it would be necessary to execute an INSERT statement 1000 times in order to insert all 1000 records This requires 1000 separate communication events with the database server It is thus evident that the use of an array type host variable greatly reduces the consumption of network resources com pared to when using a non array type host variable Now compare the use of an array type host variable with the use of a non array type
12. The reason for this is that the varchar type is a structure and thus the limitation on the use of structures also applies to the varchar type Example EXEC SQL BEGIN DECLARE SECTION varchar varl 10 10 1 int var2 10 EXEC SQL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 I2 VALUES varl var2 unacceptable The following example shows the use of the varchar type In this example both the input host variable and the output host variable are varchar type host variables The variables cus job is the input host variable while the variable s address is the output host variable The programmer is responsible for checking the length of s cus job arr and specifying it in s cus job len Although not shown in the example after the SELECT statement is executed it will be necessary to check whether the value of s address len is 1 which would indicate that a NULL value was returned Sample Program varchar sc gt EXEC SOL BEGIN DECLARE SECTION Precompiler User s Manual 66 6 3 Extended APRE Data Types char s_cname 20 1 varchar s_cus_job 20 1 varchar s address 6041 EXEC SQL END DECLARE SECTION strcpy s cus job arr planner s cus job len strlen s cus job arr EXEC SOL SELECT CNAME ADDRESS INTO s cname s address FROM CUSTOMERS WHERE CNO BIGINT 7 AND CUS JOB s cus job 6 3 2 Date Types APRE date types can be used only with DATE type database columns Three date types are prov
13. This is a list of output host variables and output indicator variables Description The OPEN statement can be executed after the DECLARE CURSOR statement or the CLOSE state ment If an attempt is made to open a cursor that has not been defined an error indicating that the cursor does not exist will be raised Precompiler User s Manual 162 11 2 Using Dynamic SQL Statements The OPEN statement executes the SQL statement that was declared using the DECLARE CURSOR statement When this statement is executed each parameter marker is replaced with a corresponding value These values are specified as a list of host variables in the USING clause The number of host variables in the USING clause must be the same as the number of parameter markers in the SQL statement Furthermore the type of each host variable must be compatible with the type of the database column to which it corresponds If the same cursor identifier is used in multiple DECLARE CURSOR statements within the same appli cation then the most recently declared cursor having that identifier at run time will be used All of the limitations that normally apply to the execution of the SELECT SQL statement in ALTIBASE HDB also apply to the OPEN embedded SQL statement nple The following example shows how to open a cursor called CUR lt Sample Program dynamic3 sc gt EXEC SQL OPEN CUR EXEC SQL FETCH lt cursor name gt INTO lt host_var_list gt Argume
14. This is the identifier of the SOL statement It must start with an alphabetic char acter a z A Z or the underscore character and must not be longer than 50 bytes host var This is a character type variable that includes all of the SQL statement text string literal This is the entire SOL statement hard coded in the form of a string Description This statement is used to prepare an SQL statement for execution If the same SQL statement identifier is used in multiple PREPARE statements within the same appli Precompiler User s Manual 158 11 2 Using Dynamic SQL Statements cation then the SQL statement that is executed when the EXECUTE statement is called at run time will be the most recently prepared SQL statement having that identifier The SQL statement cannot be a SELECT statement Example In the following example the text of an SQL statement is determined according to the conditions at run time and the corresponding SQL statement is then prepared lt Sample Program dynamic2 sc gt char query 100 EXEC SQL BEGIN DECLARE SECTION int s_eno EXEC SQL END DECLARE SECTION if s_eno lt 20 strcpy query delete from employees where eno and e lastname else strcpy query insert into employees eno e lastname values EXEC SQL PREPARE S FROM query 11 Syntax EXEC SQL EXECUTE statement name USING host var list Arguments
15. VARCHAR TYPE s cname DKHAN S address arr YeongdeungpoGu Seoul s address len 20 73 Host Variable Data Types 6 3 Extended APRE Data Types ENGINEER DOCTOR DESIGNER ENGINEER WEBMASTER WEBPD PLANNER PD DESIGNER NULL MANAGER BANKER ENGINEER BANKER MANAGER PLANER NULL ENGINEER NULL WEBMASTER 3 rows inserted 3 times insert success 6 3 4 3 date sc This sample program can be found at ALTIBASE_HOME sample APRE date sc 6 3 4 4 Result of Execution is f schema schema sql make date date lt DATE TYPE gt JOIN DATE of ENO is 3 2000 1 11 JOIN DATE of ENO is 3 2000 1 11 0 0 0 0 Precompiler User s Manual 74 6 3 Extended APRE Data Types Success update with SQL DATE STRUCT 1 rows updated Success update with SQL TIME STRUCT 1 rows updated Success update with SQL TIMESTAMP STRUCT 1 rows updated Success insert 3 rows inserted 3 times insert success 6 3 4 5 binary sc This sample program can be found at ALTIBASE_HOME sample APRE binary sc 6 3 4 6 Result of Execution is f schema schema sql make binary binary BINARY TYPE Success insert with APRE CLOB Sel clob AAAAAAAAAA sel clob ind 10 Success insert with APRE BLOB sel blob 1 11 11 1 sel blob ind 10 Success insert with APRE BINARY sel blob 11 11 1 sel blob ind 10 Success insert with APRE BYTES sel bytes sel bytes ind 5 75 Host Variable Data
16. lt constant gt This value is used to specify a fixed number of array elements to be processed 10 2 7 3 Restriction The array type host variables used in the ONERR clause ret code and err code must not be smaller than any of the arrays used in the SOL statement When using the ONERR clause together with the FOR clause the size of the array type host variable used in the ONERR clause must not be less than cnt that is the number of array ele ments to be processed 10 2 7 4 Example EXEC SQL ONERR err rc err code UPDATE T1 SET c2 c2 1 WHERE cl varl EXEC SQL ONERR err rc err code FOR arr count UPDATE T1 SET c2 c2 1 WHERE cl varl 143 Using Arrays in Embedded SQL Statements 10 3 sqica sqlerrd 10 3 sqica sqlerrd When using an array type host variable in an embedded SQL statement the value of the sqlca sql errd variable can be checked after execution of the embedded SQL statement This section explains the meaning of the contents that can be stored in the sqlca sqlerrd variable When using non array type host variables this value can be checked after executing UPDATE and DELETE statements When using array type host variables this value can be checked after executing INSERT UPDATE DELETE and SELECT statements When the value stored in the sqlca sql code variable is SQL SUCCESS the meaning of the value stored in the sqlca sqlerrd 2 variable varies depending on the kind of embedded SQL sta
17. 11 2 Using Dynamic SQL Statements siseasssssscascsssssassescsssscarsasssseasssiacssseasssascoesssvnssonasasaascasccbassanbasedsesoudsananssadssiasoaabesaazezsd i sndannesanss 157 11 2 1 Method T E E PE PER AEE ES ESE EEO T ETS 157 11 22 Method p 158 1 1 2 3 Method 3 ee EEAS ata al SE MRE E cM Mr a o 160 11 3 S mple Proce is a ea e E N A e EP OS alone 167 11 331 dynamic 158 5 ett ett Oe RT A E E adler conceal M ARA 167 T11 3 2 Result of EXecUtlonh c n e Re ete reete e ec Rete resti ie tepore ed 167 11 3 3 dynamic2 56 os deer aoi o dM bc LU M or 167 11 34 Result of EXecUtion i et re od eee REN en nel e ente e ees 167 T 1 3 5 dynamic3 SC oct ESCENA ERR HR YEN E IS 167 11 3 6 Result of ExecUtiORi i itecto tto obedire tbe qui eoe beide sedie Ene edet 167 12 Using Stored Procedures in C C scccsccssssssssssssesssscsssescsscsscrsssescsssessessecsessessessssssssssessosscsssssssssssssosseseessesees 169 12 1 Using Stored ProcedUr amp s i e trt territi three eie URS eMe ler Rit eU EAEE SEES 170 vi JA SEI iL LES 170 12 12 ALTER top te iU E RM Do 172 PREMIER 172 TQ A EXECUTE n tte eet e NT eee eee te eee ve eee uae a SE 173 12 2 Using Array Type Host Variables with the EXECUTE Statement sessssssecsssessccssecsensscccssesessuseccenseeccsneeesuecssneeessaee 175 12 2 Array Ty DGS nenin aee etse terti eee MERERI Ren NNE RAR EDEN THER
18. 79 Host Variable Data Types 6 4 Column and Host Variable Type Conversion merely invokes the memcpy function to store the contents that were retrieved from the database in the host variable without change It is thus necessary to understand how each column type stores data in memory and to be able to interpret the contents of memory in order to use the APRE_BINARY type as a host variable Therefore although the lack of type conversion means that performance will likely improve when the APRE_BINARY type is used as a host variable the requirement to understand how data are stored in memory complicates development tasks Therefore in most cases it is recommended that the APRE_BINARY type be used only with BLOB type columns Precompiler User s Manual 80 Embedded SQL Statements Embedded SQL Statements 7 1 Overview 7 1 Overview The term embedded SQL statement refers to an SQL statement that resides within an application 7 1 1 Syntax EXEC SQL An embedded SQL statement begins with the words EXEC SQL and ends with a semicolon Between the EXEC SQL keyword and the semicolon various kinds of SOL statements can be used including DML statements such as SELECT and UPDATE statements and DDL statements such as CREATE and DROP statements 7 1 1 1 Limitation The maximum possible length of an SQL statement is 32kB kilobytes 7 1 1 2 Example The following is an example of an embedded SQL statement
19. ables or copy the values of function arguments to local host variables This makes development more convenient and improves performance 4 3 3 Sample Program 4 3 3 1 argument sc The sample program can be found at SALTIBASE_HOME sample APRE argument sc 4 3 3 2 Result of Execution is f schema schema sql make argument argument ARGUMENT Precompiler User s Manual 44 5 C Preprocessor 5 1 Overview 5 1 Overview The purpose of the APRE C C preprocessor is to process C preprocessor directives The APRE C C preprocessor can handle most directives including the include directive for specifying source files to include the def ine directive for defining macros and the if directive for condi tionally including source code 5 1 1 How the C Preprocessor Works The APRE C C preprocessor recognizes most C preprocessor commands and efficiently performs macro substitutions It also includes files as required and includes or excludes source text on the basis of conditions The APRE C C preprocessor uses the macro values obtained in the preprocess ing step to modify the source text and then generates an output file 5 1 1 1 Example The following example illustrates how the APRE C C preprocessor handles preprocessor direc tives include my header h dif A char name 10 endif Assume that the file my_header h is in the current directory and contains the following directive define A 1 I
20. and the CLOSE RELEASE statement each of which provides a different cursor related functionality 9 1 1 Order of Execution of Cursor Related SQL Statements The order in which SQL statements for managing cursors are executed is as follows 1 DECLARE CURSOR 2 OPEN 3 FETCH The FETCH statement is repeatedly executed to retrieve all records that satisfy the given condi tions until the result of execution is SQL_NO_DATA 4 CLOSE or CLOSE RELEASE 9 1 2 Considerations If an OPEN FETCH CLOSE or CLOSE RELEASE statement that references a cursor that has not been declared is executed an error indicating that the cursor does not exist will be raised It is possible to declare more than one cursor with the same name within one application When doing so only the most recently declared cursor will be valid This means that OPEN FETCH CLOSE and CLOSE RELEASE statements will apply to the most recently declared cursor Precompiler User s Manual 120 9 2 9 2 Cursor Related SQL Statements Cursor Related SQL Statements Each cursor related SQL statement is defined and described below in detail 9 2 1 DECLARE CURSOR 9 2 1 9 2 1 9 2 1 9 2 1 This statement is used to declare a cursor 1 Syntax EXEC SQL DECLARE lt cursor name gt CURSOR FOR lt cursor specification gt 2 Arguments cursor name gt This is the name of the cursor It can be a maximum of 50 bytes long It must start with an alphabetic cha
21. audit g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib libauditobj a lodbcecli lutil S LIBS iloader ALTIBASE OBJS CC iloader g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib libiloaderobj a lodbccli lutil S LIBS sesc ALTIBASE OBJS CC sesc g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib libsescobj a lodbccli lutil S LIBS clean rm BINS SERVER BINS CLIENT o core class old mv ALTIBASE ALTIBASE old mv audit audit old mv checkServer checkServer old mv checkipc checkipc old mv createdb createdb old mv dbadmin dbadmin old mv destroydb destroydb old mv iloader iloader old mv isql isql old mv killCheckServer killCheckServer old mv restoredb restoredb old mv server server old mv sesc sesc old mv shmutil shmutil old 233 FAQ Frequently Asked Questions Precompiler User s Manual 234 Index A ALTER FUNCTION 172 ALTER PROCEDURE 172 188 apre environment settings 6 APRE_BINARY 71 APRE_BLOB 70 APRE_BYTES 71 APRE_CLOB 69 APRE_NIBBLE 72 Array of Structures 148 Array Type Host Variables 175 ATOMIC FOR Clause 141 AUTOCOMMIT 98 B BATCH 100 binary types 69 Built in Fuctions Compared 210 C character types 59 char 59 CLOSE 124 165 CLOSE RELEASE 125 165 COMMIT 98 CONNEC
22. if not execute the CLOSE RELEASE statement In practice it is not common to use a cursor only once so the CLOSE RELEASE statement is almost never used Reusing a cursor name by repeatedly executing the CLOSE RELEASE DECLARE CURSOR and OPEN statements has a negative impact on performance In the following example the cursor named CUR is closed and all resources allocated to the cursor are freed EXEC SQL CLOSE RELEASE CUR Precompiler User s Manual 166 11 3 Sample Programs 11 3 Sample Programs 11 3 1 dynamic1 sc SALTIBASE HOME sample APRE dynamicl sc 11 3 2 Result of Execution is f schema schema sql make dynamicl dynamicl lt DYNAMIC SQL METHOD 1 gt Success execution with host variable 11 3 3 dynamic2 sc This sample program can be found at ALTIBASE_HOME sample APRE dynamic2 sc 11 3 4 Result of Execution make dynamic2 dynamic2 lt DYNAMIC SQL METHOD 2 gt Success execute 11 3 5 dynamic3 sc This sample program can be found at ALTIBASE_HOME sample APRE dynamic3 sc 11 3 6 Result of Execution is f schema schema sql make dynamic3 dynamic3 lt DYNAMIC SQL METHOD 3 gt Prepare 167 Dynamic SQL Statements 11 3 Sample Programs Success close cursor Precompiler User s Manual 168 12 Using Stored Procedures in C C 169 Using Stored Procedures in C C 12 1 Using Stored Procedures 12 1 Using Stored Procedures Embedded SQL statements can be used to
23. l When the ATOMIC FOR Clause is used with an array type input host variable in an embedded SQL statement multiple iterations of the statement corresponding to each of the array elements are combined and processed all at the same time using so called Atomic Array Insert Therefore when using ATOMIC FOR if the execution of even one of the iterations fails then the exe cution of the entire statement fails The individual resultant values are never written to disk or shared with other processes That is either none of the values are inserted or all of them are inserted Figure 10 1 Values Resulting from Array Insert and Atomic Array Insert Operations Array Insert a l Atomic Array Insert Insert SUCCESS Insert SUCCESS TTT w Insert FAIL Insert FAIL Insert SUCCESS Rollback Insert FAIL While Non Atomic Array Insert i e the use of an array input host variable with an INSERT embed ded SQL statement already offers the advantage of reduced communication costs Atomic Array Insert increases performance even further because it reduces the number of statements that must be executed Table 10 1 Difference between Array Insert and Atomic Array Insert Array Insert Atomic Array Insert Number of Statement Execu Number of Array Elements One tions Number of Resultant Values Number of Array Elements One Speed Fast Faster 1 0 2 6 1 S V EXEC SQL ATOMIC FOR host var constant
24. lpthread lrt ldld ldl 1std lstream 1Csup lm lcl lc ld Unsatisfied symbol SESStmtCount in file userl altibase altibase home lib libapre a sesSqlcli o l errors Error exit code 1 Stop I am running HP UX 11 0 on an HP L class server D 0 0 9 Answer Change the order in which the lodbccli and lapre link options are specified 231 FAQ Frequently Asked Questions Why do need to relink my project in ALTIBASE MMDB 2 4 1 installed ALTIBASE MMDB 2 4 1p1 but the library versions are not correct and I had to relink my project am running Dec v 4 D 0 0 10 Answer include ALTIBASE HOME install altibase env mk cc usr lib cmplrs cc ld o INCLUDES IS ALTIBASE HOME include at BA LIBDIRS L ALTIBASE HOME lib rpath usr lib cmplrs cxx L usr lib cmplrs cxx LIBS lmm lgp lsm lid lpd 1tli lrt lpthread lm lcxxstd lcxx lexc le LTIBASE OBJS cpp o INS SERVER altibase dbadmin checkServer createdb destroydb killCheckServer estoredb shmutil INS CLIENT isql iloader audit sesc DK WP i all BINS SERVER BINS CLIENT altibase ALTIBASE OBJS CC altibase g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib ALTIBASE o LIBS dbadmin ALTIBASE OBJS CC dbadmin g0 04 call shared LIBDIRS usr lib cmplrs cc crt0 o usr lib cmplrs cxx main o ALTIBASE HOME lib dbadmin o LIBS shmutil
25. must be specified If an attempt is made to establish a connection using the name of a connection that has already been established an error will be raised indicating that an established connection with that name already exists Each connection name must start with an alphabetic character a z A Z or the underscore char acter and must not be longer than 50 bytes 13 1 2 1 Syntax To specify a desired connection when executing an embedded SQL statement use the AT option as shown below EXEC SQL AT conn name conn name 13 1 2 2 Arguments Both hard coded strings and host variables can be used to specify the connection name If using a host variable it does not need to be declared in the host variable declaration section conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable Precompiler User s Manual 180 13 1 Overview 13 1 3 Steps for Authoring a Multiple Connection Application The process of authoring an application that uses multiple database connections is not much differ ent from the process of authoring an application that uses only one database connection The steps to follow when writing an application with multiple database connections are as follows 1 Establish a connection to the database server The name of the connection is defined at this time 2 Execute embedded SQL statements
26. program For detailed information about the data types that host variables can have please refer to Chapter6 Host Variable Data Types Host variables can also be declared as arrays For the CHAR and VARCHAR types it is possible to declare one or two dimensional arrays whereas for the other types it is only possible to declare one dimensional arrays For more information about using arrays with embedded SQL statements please refer to Chapter10 Using Arrays in Embedded SQL Statements APRE can use the CHAR and VARCHAR type host variables to process text data in any of the national character sets supported by ALTIBASE HDB When handling data in the national char acter set use the reserved word shown below character set is nchar cs Note that ifthe nchar var command line option is used when precompiling the source code it is not necessary to use the reserved word shown above The names of host variables must start with an alphabetic character a z A Z or the under score character and must not be longer than 50 bytes 3 1 3 Using Host Variables in Embedded SQL Statements A host variable can be used anywhere in an embedded SQL statement where the use of a scalar Precompiler User s Manual 24 3 1 Host Variables expression would be allowed Host variables must be distinguished from the other elements in embedded SQL statements This is accomplished by prepending the colon character to the names of h
27. statement name This is the identifier of the SOL statement host var list This is a list of input host variables and input indicator variables o escription This statement can only be executed after a corresponding PREPARE statement If an undefined SOL statement identifier is specified when this statement is executed an error indicating that the SOL statement does not exist will be raised The identifier that is specified when this statement is executed cannot be the identifier of a SELECT statement The EXECUTE statement is used to execute a previously prepared SQL statement When this statement is executed each parameter marker is replaced with a corresponding value These values are specified as a list of host variables in the USING clause The number of host variables in the USING clause must be the same as the number of parameter markers in the SQL statement Furthermore the type of each host variable must be compatible with the type of the 159 Dynamic SQL Statements 11 2 Using Dynamic SQL Statements database column to which it corresponds If the same SQL statement identifier is used in multiple PREPARE statements in the same application then when the EXECUTE statement is called at run time the most recently prepared SQL statement having that identifier will be executed Example The following example shows the use of the EXECUTE statement lt Sample Program dynamic2 sc gt EXEC SQL BEGIN DECLARE
28. 13 2 6 Other SOL staterments sscasscesieyssavsucsssssscoscssesxessaacascdeaseansesedsccostscoauensavsccbasnsesauasetoasesdaieseatavavesSsatesaiassteideddadestbenstbern 186 IA aas 187 13 3 Using Stored Procedures in Multiple Connection Applications sescssssssssssecsssessesnsecssssecccsecessseecesnecessneecesneesse 188 13 33 CREATE zoe teet eee er DURANTE DAR ON MU NM e 188 13 322 ALTER tete etie ER re ce tite ep ee e EG AE ro e E pe e re e edd 188 i3 3 2 DROP s toe RM ua UMS TM TR 189 19 3 EXECUTEN Rr tee ee ee e o eet ree a ehe e po e ou eee EO eee e reo eee 189 13 4 Sample Programs i tir e RR IEEE ENTRE RESISTE Pe ice p re RE RES HIER Edd 191 VS AA Maid cM E 191 TSA MEL rdc 191 US AS TMCS SC 1er eR UR EE SANUS ede BR P ND RB A NAM EROR DERE ATRAE 192 14 Multithreaded Applications ccce eese eee ee eene eee eee eo seen aeta otta SENE SENSE NERE soob eese toas ease ease esee eaae eS 195 141 Multithreaded Applications tite tbe obo e eee Deere eR Ett de rcl oe e E Ud e oed eden 196 14 1 1 Configuring an Application as a Multithreaded Application eese 196 JS PPAbISTadeu on e 196 14 2 Sample PrOgratris 2 e t e e Rer Rn s e e eret etre reete eee eb ed 197 42 1 mtlises sot daa Add MEI 197 UITAE 197 15 Error Codes and Messages 199 15 PrecompilerErtOrs coit tei eit ire fe o eR eee etie
29. 177 Using Stored Procedures in C C 12 3 Sample Programs Precompiler User s Manual 178 13 Applications with Multiple Database Connections 179 Applications with Multiple Database Connec 13 1 Overview 13 1 Overview APRE supports the authoring of applications that establish more than one database connection This chapter describes multiple connections and explains how to establish and maintain them 13 1 1 The Need for Multiple Connections Here are some of the circumstances in which it might be desirable to implement multiple database connections within a single application When it is necessary to access multiple database servers from the same application When multiple users need to access a database server using the same application In multi threaded applications For more information about multi threaded applications please refer to Chapter14 Multithreaded Applications 13 1 2 Connection Name In an application with multiple database connections the connections are distinguished from one another using the connection names The name of each connection must be unique within the application Only one connection that does not have a connection name can exist within an applica tion This connection is called the default connection The name of each connection is set when the connection with a database server is established To use this connection in subsequently executed embedded SQL statements the connection name
30. INSERT DELETE CREATE 13 2 3 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable 13 2 3 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that Precompiler User s Manual 184 13 2 SQL Statements for Multi Connections has already been established 13 2 4 Cursor Statements The method of specifying the connection when executing cursor related embedded SQL state ments is explained in this section 13 2 4 1 Syntax EXEC SQL AT conn name conn name DELCARE OPEN FETCH CLOSE cursor name 13 2 4 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable cursor name Cursor name 13 2 4 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established 13 2 5 Dynamic SQL Statements The method of specifying the connection when executing dynamic SQL statements is explained in this section 13 2 5 1 Syntax Method 1 EXEC SOL AT conn name conn name gt EXECUTE IMMEDIATE Method 2 EXEC SQL AT conn name conn name gt PREPARE EXEC SOL AT conn name conn name EXECUTE Method 3 EXEC
31. Ithread Iposix4 Idl Ikvm Ikstat Isocket Insl Igen l iostream ICrun Im Iw Icx and lc 229 FAQ Frequently Asked Questions If want to query data in an MMDB on an authentication server from other servers on which server should compile the library file understand that in order to query data in an MMDB on an authentication server from other servers will need to access the authentication server with an application written using APRE To accomplish this do have to put APRE on all of the other servers and independently make a library for the executable files on each server Or can I just create the library on the authentication server distribute it to the other servers and link them to create executable files If do it the first way have to put APRE on every server that accesses the authentication server which is painful and will be difficult to manage in the long term If do it the second way that is if create a library on the authentication server distribute it and compile and link it on the other serv ers will encounter problems if the servers are different hardware and don t run the same OS would appreciate a suggestion as to how to go about this and am curious about how your other clients have tackled this problem Finally please provide a sample makefile that should use in such a scenario D 0 0 6 Answer Unfortunately when working with heterogeneous operating systems there is no way t
32. New York 16 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13 1003 SOLUTION DEVELOPMENT DEPT Osaka 14 3 rows selected 1001 RESEARCH DEVELOPMENT DEPT 1 New York 16 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13 1003 SOLUTION DEVELOPMENT DEPT Osaka 14 3 rows selected Successfully executed procedure Precompiler User s Manual 154 11 Dynamic SQL Statements Dynamic SQL Statements 11 1 Static versus Dynamic SQL Statements 11 1 Static versus Dynamic SQL Statements 11 1 1 Static SQL Statements 11 1 1 1 Concept Static SQL statements are those that are defined by the programmer in advance and are unchange able Static SQL statements are embedded SQL statements that are hard coded into an application For more information about static SOL statements please refer to Chapter7 Embedded SOL State ments and Chapter9 Using Cursors 11 1 1 2 Disadvantages Static SOL statements cannot be used in applications in which the exact form of the SQL state ment cannot be determined in advance The names of tables and columns to be referred to in static SOL statements must be deter mined by the programmer in advance In other words host variables cannot be used in place of table or column names and they cannot be changed Although the use of input host variables with static SOL statements affords a small amount of flexibility static SOL statements are fundamentally fixed and cannot be changed in any major way 11 1 2 Dynamic SQL St
33. RE ese iO ee ete NI seruus 210 Discorinecting from a Database etit re e te estere etie eee eee es 211 Host Variables israelere T e 212 Host Variable Compatibility riter berti retener ere intente hee tete 212 Host Variable Declaration Sectiori tcc ette x rk ratore pec Pe eit oe PR Dd rob e MER FOR VARE ELSE 213 Using Embedded SQL Statements Basic PI B CIR CORO LU RN Cursor Control SQL Statements 5 e acerbe eU t deber o eee ee uto us bre dee ted ea deles 213 Dynamic 8S E HIC ML ERNNNe 215 Execution Results and Status CodeS iii rsi iaa R SN aaraa 216 SOLCA A M 216 SOLS TATE Rd dee t PA RERO APR EAR S REPAIR RENI REUNIR SEMEN 218 SOLCODE 218 Commit Mode Default Commit Mode 219 Charigirig the Commit Mode e tree eR eee e et EQ I e tente 219 Explicit Comit see ip eee e et ere e et a eee eee esee 220 When Executing a SELECT Statement in Non Autocommit Mode eterne tteennttennntttnnnte 220 Sample Programs eite Re apte e eee tede teer Ue Pte ERR JESSE EA eei e Pide 220 Oracle 220 ALTIBASE HDB c BEDER ETES 222 AppendixC Sample Applications 1000225 Executing the Sample Applications 5 7 tree ttti coe tte ebbe n ia edidit 225 EleMENtS 22 22 san M 225 Ins
34. SECTION int s_eno char s_ename 20 1 EXEC SQL END DECLARE SECTION S eno 10 strcpy s ename YHBAE EXEC SQL EXECUTE S USING s eno s ename 11 2 3 Method 3 Method 3 is the only way to use dynamic SQL to execute a SELECT statement and cannot be used to execute any other kind of statement Method 3 consists of the execution of five statements the PREPARE statement the DECLARE CUR SOR statement the OPEN statement the FETCH statement and the CLOSE statement It is possible to rearrange the order of the first two statements so that the DECLARE CURSOR state ment precedes the PREPARE statement In this case it is necessary to additionally provide the DECLARE STATEMENT statement before the DECLARE CURSOR statement 11 2 3 1 DECLARE STATEMENT Syntax EXEC SQL DECLARE statement name STATEMENT Arguments statement name This is the identifier of the SOL statement It must start with an alphabetic char acter a z A Z or the underscore character and must not be longer than 50 bytes Description This statement is used to declare an identifier for an SOL statement to be used in other embedded SQL statements This statement is only required if the DECLARE CURSOR statement precedes the PREPARE statement itis not required if the PREPARE statement precedes the DECLARE CURSOR statement Precompiler User s Manual 160 11 2 Using Dynamic SQL Statements Example In the following example the
35. SOL AT conn name conn name PREPARE EXEC SQL AT conn name conn name gt DECLARE EXEC SQL AT conn name conn name OPEN EXEC SOL AT conn name conn name FETCH 185 Applications with Multiple Database Connec 13 2 SQL Statements for Multi Connections EXEC SQL AT lt conn_name conn name CLOSE 13 2 5 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable 13 2 5 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established 13 2 6 Other SQL statements The method of specifying the connection when executing other SQL statements such as those for setting the autocommit mode and committing and rolling back transactions is explained in this sec tion 13 2 6 1 Syntax EXEC SQL AT conn name conn name AUTOCOMMIT ON OFF EXEC SQL AT conn name conn name COMMIT EXEC SQL AT lt conn name conn name gt SAVEPOINT savepoint name EXEC SQL AT conn name conn name ROLLBACK TO SAVEPOINT savepoint name EXEC SQL AT conn name conn name FREE EXEC SQL AT conn name conn name BATCH 13 2 6 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This i
36. See ate 175 12 2 2 Limitations eee ete tre eet eo eee testet eese ed reveren enean ebore Pe paetos 175 EREA D I ENIE AE EI A ATE EE EE E EAE E TEE A 176 123 Sample Program S a d RA ted seb E dnte 177 LET dq M 177 12 3 2 PSIMIZ SC retten bete RED e b EE EEEE CR RESI IPS AREE ETES S red Menge a es eee LYSE 177 13 Applications with Multiple Database Connections ceeeee eere eese eene eese ee senses staat s etas es sense tns eas essen stesso 179 EXEC M ERESS as 180 13 1 1 The Need for Multiple CONNections c sssssssssssscssssssssessssssssecssssssssssssessussssusssssecsnecessesssscssssssssessssessucsessessseceses 180 13 2 Cohnection Name nct re Re ERROR EEE ANN 180 13 1 3 Steps for Authoring a Multiple Connection Application cssesssssecsssesscsssecssssessssecessseecssneeessneesesneesse 181 13 2 SQL Statements for MUIti CONMECTIONS sesscsssesscssseccssseecssseccesseccssuecessnsccesssececsuecessuecessuecssueceesuecssneesecnsecesaueesssueeessneeees 182 13 2 1 CONNEG T store resi ee E ero Ee ve ese eee ET e ER AARG 182 13 22 DISCONNECT orna 183 13 2 3 Executing Basic SQL Statements niii iei 184 13 2 4 Cursor Statements iasssiscssssenssascosscorassaxsscsadssncessneassussssasonidsonazczosss onssonasssaassas onssonoissasssnved onas0atssiadaabeosavesoddisendsonoanezs 185 13 2 5 Dynamic SQL acum 185
37. TABLESPACE TEMPORARY THAN THEN THREADS TO TRIGGER TRUE TRUNCATE TYPE TYPESET UNION UNIQUE UNTIL UPDATE USER USING VALUES VARCHAR VARIABLE VIEW VOLATILE WAIT WAKEUP RECPTR WHEN WHENEVER WHERE WHILE WITH WORK WRITE Precompiler User s Manual 12 2 2 Command Line Options 2 2 13 debug macro symbol When this option is used a symbol table containing the names of macros or declared variables in the source code is output This option is provided for use in debugging source code 2 2 13 1 macro If debug macro is specified a macro list containing the names of all defined macros is output 2 2 13 2 symbol If debug symbolis specified a list of information about declared variables is output 2 2 13 3 Example Create the sample1 c file and output a macro list containing the names of all defined macros apre debug macro samplel sc Create the sample1 c file and output a list of information about declared variables apre debug symbol samplel sc Create the sample1 c file and output both a macro list containing the names of all defined macros anda list of information about declared variables apre debug macro symbol samplel sc 2 2 14 nchar utf16 When this option is used national character type data are encoded as UTF 16 during the precompile operation If this option is not specified national character type data are encoded in the format specified using the ALTIBASE NLS USE property Not
38. X ems 11 22 11 spill 4Valles t ere e ote et n tee ER pe RT RE tutti t 12 2 2 12 keyword 12 2 2 13 debug macro symbol 13 2 2 14 nchar utf16 ses 2 13 2 2 15 nchar var variable name list we 13 2 3 Programming using Embedded SQL Statements aa 2 3 1 Declaring Host Variables 15 2 3 2 Connecting to a Database Server a7 2 3 3 Executing Embedded SQL Statements ssssssssssscssssssccssecsssssscsssecessuecccsneecesusesesusecssnsesesnsecssnseeecnseessnueeesaneesesess 18 2 3 A Handling Runtime Error Sasse E EET ANKE ER TURNIER RESI NEM Seth 20 2 3 5 Disconnecting from the Database Server sesssssscssssssccssessssusecsssescessseccsuesccsnsecssssecesssecessuececsueeecnseessnsceesaneeseness 21 2 3 6 The Precompile Operation ssssccsssscssssssccsecsssseecssnsesssusecssnsscessscesssscecsssecesnsccesnsecssssceccssecesnsecssnseeecuseessnseeessneeseness 21 3 Host Variables and Indicator Variables 4 eee e eee eee eese eese tees senten staat tn sense enses ets ss stas etse ease tasto sens taae 23 3 1 Host Variables 4 2 24 3 1 T OVerVIeW 5er tcede tede i tede ESBESSETETSERS SET ESEE SEE ete e o ccide ie bab pe te dee e EAEE 24 3 1 2 Declaring Host Variables rris RE tte irt dere e S ee a iret i 24 3 1 3 Using Host Variables in Embed
39. access is interrupted for whatever reason receipt of the SIGPIPE signal via a software pipeline can forcibly terminate a running application This requires the SIGPIPE signal to be handled within the application However the client library of ALTIBASE HDB is unable to process incoming pipeline signals Addi tionally if the functions in the client library of ALTIBASE HDB are called while a SIGPIPE signal is being processed the application might become nonresponsive It is thus necessary to handle the SIGPIPE signal within the application without calling the functions Precompiler User s Manual 6 2 1 Introduction and Concepts in the client library of ALTIBASE HDB Once the SIGPIPE signal has been handled the client library functions of ALTIBASE HDB can be called as usual 2 1 4 The Precompilation Process APRE is used to precompile a program that was written in C or C and includes embedded SQL statements It outputs a C or C program in which the embedded SQL has been converted into a form that is understandable by the C or C compiler The input file is a text file containing C or C source code and must have the sc extension The file output by APRE can have either the c or Cpp extension The user can choose the desired filename extension using the t command line argument If this is omitted the default extension is c 2 1 4 1 Executing the Precompile Command apre apre options filename 2 1 4 2 APRE Command Line
40. can use the sql ca vari able in their applications to check the result of execution of embedded SQL statements 8 2 1 ulpSqlca Data Structure Definition typedef struct ulpSqlca char sqlcaid 8 not used int sqlcabc not used int sqlcode struct short sgqlerrml char sqlerrmc 2048 sqlerrm char sqlerrp 8 not used int sqlerrd 6 char sqlwarn 8 not used char sqlext 8 not used ulpSqlca 8 2 2 sqica Elements The ulpSqlca structure comprises numerous constituent elements Some of these elements are reserved for future use and are thus not described here The meaning of each element is as follows 8 2 2 1 sqlcode This element is used to store the result of execution of an embedded SQL statement The value stored herein will be one of the following which were described above SQL SUCCESS SQL SUCCESS WITH INFO SOL NO DATA SOL ERROR 8 2 2 2 sqlerrm sqlerrmc This element is used to store error messages The maximum error message length that can be saved herein is 2048 bytes 109 Handling Runtime Errors 8 2 The sqica Structure This element is used to store the length of the returned error message This element is used to store the number of records that were affected by the execution of an INSERT UPDATE or DELETE statement When a SELECT or FETCH statement is executed and the output host variable is an array this ele ment is used to store the numbe
41. cee dece eb ae RS 118 SR AIT 8T z ALS TERT RR C eET NS beo ES re FEE nni ii 118 8 6 5 Res ltof EXecUtlOn c tht e Ene Ru ep eR C reU D COR e ees 118 9 USING CUrSOFS siisisssncsctcscoscencascoasecvasontssecsconsssGubcsasscessoutescotassonscvasecescoutoacoassoosscvacscossessscsntessoctscedencsseessontesseatbocstoneeee 119 9 TOVE EW T P 120 9 1 1 Order of Execution of Cursor Related SQL StateMentS c secssessesssesssesssessssssesssessssssesssesssesseessesssesssesseessees 120 9 12 Considerations tees rela T tara sets tta itv e MC oo ai 120 9 2 Cursor Related SQL Statements a a ttti tortor Fono attend Gina EA Ie RA REPRE ARI TB aei bk eq Ta cR 121 92 1 DECLARE CURSOR ut 121 ODD CPE Duc 122 9 2 3 FETCH 5e eH Eee eoe E ru ENNE A e Re ue USER ree o e oe tee be 122 p cale ERELE 124 P Rekodzidid lee EDER EE 125 9 3 REUSING a Cursor Name coectetuer tope ei et tte dese oem Ee eet Peto pne en 126 9 3 1 Relationships between Cursor Related StateMent csecssssseccssesssssseccsseecssnseccsatecssnseeeesueecssutecssnseseenseesse 126 9 3 2 Cursor Related SQL Statements and Host Variables eerte tentent ttn ntnnnen 126 9 3 3 The CLOSE and CLOSE RELEASE Statements cccccssssssssssssssesssesssesssessesssesssssssssesssesssesesssesssesscessesaseescseseeasees 127 9 4 Sample Progr iris 5 trench t
42. character type column the data will be truncated so that they can be saved in the host variable When this happens the value of sq1ca sq1code will be SQL SUCCESS WITH INFO If no records are affected by an UPDATE or DELETE operation the value of sqlca sqlcode will be SQL NO DATA To determine the number of records that were affected by an UPDATE or DELETE operation check the value of sqica sqlerrd 2 If no records were affected this value will be 0 The SQLCODE error code values are negative decimal integers However the error codes in the Error Message Reference are positive hexadecimal values Therefore when referring to the Error Message Reference convert the absolute values of SQLCODE error codes into hexadeci mal values The scope of applicability of a WHENEVER statement is not the same as the overall program flow In particular a WHENEVER statement applies only to the file in which it is found The WHENEVER statement must precede any embedded SQL statements to which it is intended to apply WHENEVER statements are connection independent In other words a WHENEVER statement Precompiler User s Manual 20 2 3 Programming using Embedded SQL Statements in an application with more than one connection affects all embedded SQL statements within its scope of applicability regardless of the connection to which the embedded SQL statements pertain 2 3 4 2 Example In this example the variables in the sql ca structure a
43. functions should be used in place of corresponding Oracle functions Porting Pro C Applications to APRE Managing Database Connections Built in Functions Compared The name purpose and method of use of each built in function of ALTIBASE HDB is similar to its counterpart in Oracle The following built in functions are supported in ALTIBASE HDB Numeric Functions ABS ACOS ASIN ATAN ATAN2 CEIL COS COSH EXP FLOOR LN LOG MOD POWER RANDOM ROUND SIGN SIN SINH SQRT TAN TANH TRUNC BITTAND BITOR BITXOR BITNOT Aggregate Functions AVG COUNT MAX MIN STDDEV SUM VARIANCE Character Functions ASCII CHAR LENGTH CHR CONCAT DIGITS INITCAP INSTR INSTRB POSITION LOWER LPAD LTRIM NCHR OCTET LENGTH REPLACE2 RPAD RTRIM SIZEOF SUB STR TRANSLATE TRIM UPPER REPLICATE REVERSE STR STUFF Date time Functions ADD MONTHS DATEADD DATEDIFF DATENAME EXTRACT MONTHS BETWEEN ROUND LAST DAY NEXT DAY SYSDATE SYSTIMESTAMP TRUNC Type Conversion Functions ASCIISTR BIN TO NUM CONVERT HEX TO NUM OCT TO NUM TO BIN TO CHAR TO DATE TO HEX TO NCHAR TO NUMBER TO OCT UNISTR Miscellaneous Functions BINARY LENGTH CASE2 CASE WHEN DECODE DIGEST DUMP GREATEST LEAST ROWNUM NVL NVL2 SENDMSG USER ID USER NAME SESSION ID For more information please refer to the SQL Reference Managing Database Connections This chapter describes the differences in the database connection and d
44. having the broader scope i e the statement declared in an outer routine will have no effect and the WHENEVER statement having the narrower scope i e the statement declared in an inner routine will apply WHENEVER statements are connection independent In other words a WHENEVER statement in an 115 Handling Runtime Errors 8 5 WHENEVER Statement application with more than one connection affects all embedded SQL statements within its scope of applicability regardless of the connection to which the embedded SQL statements pertain A WHENEVER statement that has a global scope will affect all embedded SQL statements in the file in which it appears Precompiler User s Manual 116 8 6 Sample Programs 8 6 Sample Programs 8 6 1 runtime_error_check sc This example can be found at SALTIBASE HOME sample APRE runtime error check sc 8 6 2 Result of Execution is f schema schema sql make runtime error check runtime error check RUNTIME ERROR CHECK Sqlca sqlcode 1 Sqlca sqlerrm sqlerrmc String data right truncated SOLSTATE 01004 SOLCODE T sqlca sqlcode 1 sqlca sqlerrm sqlerrme Indicator variable required but not supplied SOLSTATE 22002 SOLCODE 331841 sqlca sqlcode 100 sqlca sqlerrm sqlerrmc Not found data SOLSTATE 02000 SOLCODE 100 sqlca sqlcode 100 sqlca sqlerrm sqlerrmc Not found data SOLSTATE 02000 SQLCODE 100 2 rows fetched Sqlca sqlcode 1 Sqlca
45. header files included using the include directive are also processed Finally all embedded SQL statements are then precompiled Because APRE s internal C parser is activated when the parse option is set to full an error will be raised if any C source code is encountered during the precompile operation Therefore when pre compiling C source code either avoid the use of the parse option or set it to partial or none Precompiler User s Manual 10 2 2 Command Line Options 2 2 6 5 Examples apre parse none t cpp samplel sc apre parse partial t cpp samplel sc apre parse full t cpp samplel sc 2 2 7 D define_name This option is used to specify the name of a macro during the precompile operation This command has the same function as using the define preprocessor directive in your code 2 2 7 1 Example Set the command line option as shown below to define a macro named ALTIBASE when precompil ing sample1 sc apre DALTIBASE t cpp samplel sc 2 2 8 v This displays the version of APRE 2 2 8 1 Example Check the version of the APRE C C precompiler apre v Altibase Precompiler2 APRE Ver 1 6 1 1 1 INTEL LINUX ubuntu 8 10 32bit 6 1 1 1 debug GCC4 3 2 i686 pc linux gnu Dec 17 2009 11 47 30 2 2 9 n This option is used to indicate that any host variables of type CHAR are not null padded To prevent truncation the length of a CHAR type input host variable must be the same as or shorter
46. host variable in the execution of a FETCH statement In order to fetch 1000 records into an array type host variable having 1000 elements it will only be necessary to execute the FETCH statement one time and all 1000 records will be fetched in sequence into the array starting with the Oth element When using a non array type host variable it would be necessary to execute the FETCH statement 1000 times in order to fetch all 1000 records however this would not entail a separate communication event with the database server every time the FETCH statement was executed The reason for this is that inter nally a certain number of records are retrieved from the database in advance and saved and every time the FETCH statement is executed one previously saved record is assigned to the host variable This shows that a notable improvement in performance cannot be expected when using array type host variables to execute a FETCH statement Only a slight performance improvement attributable to the decrease in the number of times the FETCH statement is executed can be expected 10 1 3 CONNTYPE and Host Array Variables 10 1 3 1 CONNTYPE The CONNTYPE option is used to determine the method of communication with the database server and is specified when attempting to establish a connection with the database server The value of the CONNTYPE option has a strong effect on performance The magnitude of this effect var ies depending on whether array type host var
47. iN Tape 87 7 2 3 Samiple Progratis norton an I a RT re Rer Rosa AERIS e RR RUE 88 7 3 Using DDL and DML in Embedded SQL Statements ssssscssssccssessecssecsssssecsssecessueeccsuecessnsecesneessnseseessneeeesuecssneeesenseees 89 pili c 89 VASA Vidi pope IDEM 91 TES EG UPDATE M Er essdotatsuotuesuvesbelovspeaiee A S 93 TBA DELETE m 95 7 3 5 Sample Programs e eee eet rade titre E b e debo e e ere edd 95 7 4 Using Other Embedded SQL Statements 7 4 1 AUTOCOMMIT 7 4 2 COMMIT 98 7 4 3 SAVEPOINT 99 7 4 4 ROLLBACK 99 7 4 5 BATCH 100 TRARA FREE 133 T N 101 p WA SNNIDIz ED 101 TAB Sample Proc rains acte pet ER AR RAE kreerede 102 7 5 OPTION Statements rite Re RR RERO UR EUER TURIN desea eek asco SERERE ER EST QUERN EE REN RN et nce 104 TSVINGCEUDE 3 RE 104 PCR Aid pcc M 104 8 Handling Runtime ZI ET CESRRRRD TETTE T 107 LAETUS TEN 108 8 171 Return Valles zoe i el cet t oe o oe e e o er co ce E ae a ee ee LEES 108 82 The sdlca StitctUre aec c aen n Ree Huerta Re cob eeo rese alene 109 8 2 1 ulpSqlca Data Structure Definition csscssssssesssssssssecsssssssessscsssecsssesssscsssesssscssussesscsessccssccssscssssesssesesseesssesses 109 8 2 2 sla Elements siess vscascecesescnscdssodasacsasscatsaz
48. include samplel sc f mm c i nont This option is used to specify the range within the source file s which are specified within the source code using the include directive that is parsed by the precompiler When this option is not specified it defaults to part ial If this option is set to none the precompiler processes only the macro commands and host variable declarations that are found within the EXEC SOL BEGIN END DECLARE SECTION block and ignores any macro commands and host variable declarations that are not found within that block However all embedded SQL statements found within the source file s are processed If this option is set to partial the precompiler processes all macro commands but processes only the host variable declarations that are found within the EXEC SQL BEGIN END DECLARE SECTION block Additionally the macro commands that are found in the header files that are included using the include directive are processed whereas the host variables found within these files are not However as with the none option all embedded SQL statements found within the source file s are processed If this option is set to full the precompiler executes an internal C parser and processes all host vari ables regardless of whether they were declared inside or outside the EXEC SOL BEGIN END DECLARE SECTION block and all macro commands Furthermore not only all macro commands but also all host variables found within the
49. included in a precompile operation 7 4 7 1 Syntax EXEC SQL INCLUDE lt filename gt 7 4 7 2 Arguments filename This is the name of the header file to be included in the precompile operation 101 Embedded SQL Statements 7 4 Using Other Embedded SQL Statements 7 4 7 3 Description The definitions of host variables and host variable data types are important information that APRE needs to know in order to perform the precompile operation Therefore header files that contain host variable type definitions or host variable declarations to be used in the application must be included using the INCLUDE statement if the parse precompiler option will not be set to full The EXEC SQL INCLUDE command can be used both in the main source file to be precompiled i e the file with the sc extension and in any header files h that are also included using the EXEC SQL INCLUDE command Note however that this command cannot be used within header files that are included using the include command 7 4 7 4 Limitation Recursive header file inclusions are not allowed In other words if myheader2 h is included in myheader1 h then myheader1 h must not also be included in myheader2 h Example myheaderl h EXEC SQL INCLUDE myheader2 h myheader2 h EXEC SQL INCLUDE myheaderl h not allowed 7 4 7 5 Example The following example shows how to use the INSERT statement to specify the header file hostvar h for inclusion in a
50. lprice 1 1 0 lodbccli lapre 1xti lpthread lrt ldld Precompiler User s Manual 228 Frequently Asked Questions D 0 0 4 Answer From the execution results it looks like you used a C compiler rather than a C compiler for the linking operation If you use a C compiler the system library is automatically added during the compiling and link ing operations However when using a C compiler you have to add the system libraries manually as shown below LIBS ldl lstd lstream lCsup 1m lcl lc Then make the following changes cc DA2 0W o MKTDBD dbfunc o main o util o dbif o shm msg o file o dbinit o dbresult o L userl altibase altibase home lib L userl main KTSLEE lib lcom lprice 1 1 0 lodbccli lapre lxti lpthread lrt ldld ldl lstd lstream 1Csup 1m lcl lc cc DA2 0W o MKTDBD dbfunc o main o util o dbif o shm msg o file o dbinit o dbresult o L userl altibase altibase home lib L userl main KTSLEE lib lcom lprice 1 1 0 lodbccli lapre 1lxti lpthread lrt ldld An error occurs when I use gcc to link my project Why gcc o OBJ checkrep OBJ checkrep o L homel shkim src SK DLR v1 0 0 alti base lib L homel shkim src SK DLR v1 0 0 ap lib ldlr lpara lapre lodbc cli lelf lposix4 lc lxnet Undefined first referenced symbol in file kstat close homel1 shkim src SK_DLR_v1 0 0 altibase lib libodbccli a idl o gethostbyname r homel shkim src SK DLR v1 0 0 altibase lib libodbc cli a co
51. memory equal to the value of the MAX CHAR PTR macro and to declare a char type host variable that points to a string that occupies this much memory The MAX CHAR PTR macro does not need to be defined within the host variable declaration sec tion 6 2 3 3 Structure Pointers A pointer to a structure can be used as a host variable data type A pointer to a structure is conve nient to use when using a function argument as a host variable For more information about using a function argument as a host variable please refer to Chapter4 Host Variable Declaration Section After a pointer to a structure has been declared be sure to allocate an appropriate amount of space in memory This is critical because the precompiler has no way of checking whether or not enough space for the structure has been allocated Precompiler User s Manual 60 6 2 Fundamental C C Data Types 6 2 3 4 Pointer to Array When using a pointer to a 1 dimensional array in the VALUES clause of an INSERT statement specify the number of array elements using the FOR clause Note however that when using a pointer to a 1 dimensional array in the VALUES clause of an INSERT statement with the FOR clause the following data types cannot be used Additionally it is impossi ble to use a pointer to a 2 dimensional array of the following data types in an INSERT statement Both cases will lead to unexpected results char varchar APRE BINARY APRE BYTES APRE NIBBLE APRE NUM
52. precompile operation lt Sample Program insert sc gt EXEC SQL INCLUDE hostvar h 7 4 8 Sample Programs 7 4 8 1 free sc This sample program can be found at SALTIBASE HOME sample APRE free sc 7 4 8 2 Result of Execution is f schema schema sql make free free FREE Error 331796 Function sequence error Precompiler User s Manual 102 7 4 Using Other Embedded SQL Statements Reconnect Error 589826 The connection already exists Name default connection 103 Embedded SQL Statements 7 5 OPTION Statements 7 5 OPTION Statements The OPTION statement is used to set various options provided by the ALTIBASE HDB C C precom piler 7 5 1 INCLUDE APRE provides various methods of using embedded SQL statements to specify the location of the header files to be included in a precompile operation One of them is the INCLUDE OPTION state ment 7 5 1 1 Syntax EXEC SQL OPTION INCLUDE lt pathname gt Arguments lt pathname gt This is the location of the header files to be included in the precompile operation Description This command is used to specify one or more locations in which to look for the header files to be included in the precompile operation The current directory does not need to be specified When specifying multiple locations they must be separated by commas 7 The INCLUDE OPTION statement must precede all INCLUDE statements Example In the following exa
53. records into a table 91 Embedded SQL Statements 7 3 Using DDL and DML in Embedded SQL Statements 7 3 2 1 Syntax Please refer to the SQL Reference 7 3 2 2 Arguments None 7 3 2 3 Description Host variables and indicator variables can both be used in the VALUES clause 7 3 2 4 Example Various INSERT statement examples are shown below Example 1 In the following example new records are inserted into the GOODS table lt Sample Program insert sc gt EXEC SQL BEGIN DECLARE SECTION char s_gno 10 1 char s_gname 20 1 char s goods location 9 1 int s stock double s price EXEC SQL END DECLARE SECTION strcpy s gno F111100002 strcpy s gname XX 101 strcpy s goods location FD0003 S stock 5000 S price 9980 21 EXEC SOL INSERT INTO GOODS VALUES s gno s gname s goods location s stock s price Example 2 In the following example a structure type host variable is used to insert new records into the GOODS table Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 10 1 char gname 20 1 char goods location 9 41 int stock double price goods EXEC SQL END DECLARE SECTION lt Sample Program insert sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling Precompiler User s Manual 92 7 3 Using DDL and DML in Embedded SQL Statements EXEC SQL INCLU
54. returned In this case the contents of the host variable will not have any meaning i e will be a garbage value An input host variable cannot be an array Example EXEC SQL BEGIN DECLARE SECTION int varl int var2 10 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL SELECT INTO varl FROM T1 WHERE il var3 unacceptable or EXEC SQL SELECT INTO var2 FROM T1 WHERE il var3 unacceptable If the output host variable in the INTO clause is an array of structures only one output host variable can be used For more information please refer to Chapter6 Host Variable Data Types When the output host variable in an INTO clause is a varchar array it cannot be used with any other output host variables For more information please refer to Chapter6 Host Variable Data Types An input indicator variable cannot be used in the LIMIT clause of a SELECT statement only an input host variable can be used for this purpose Additionally the only input host variable data type that is supported for use with the LIMIT clause is int Various SELECT statement examples are shown below Example 1 In the following example the departments table is searched for records whose DNO col umn value matches the value of the s dno host variable and the values in the DNAME and Precompiler User s Manual 90 7 3 Using DDL and DML in Embedded SQL Statements DEP LOCATION columns are loaded into the s dname and s dep l
55. ro ie den Ee e eee bee eee ope 47 52 2 dH nicltde ioc rt e DER Rr Ee eere ee R A ANRE 47 CLE it IDE 47 5 24 sme M 48 525 n 48 SBAS I p AEE AT 48 E e IUD 49 EXEC 49 5 3 Limitations on the Use of the Preprocessor sssssscssssssccsssssssusecssnssssnsecessusecsssscesssecesueessnsecessuecsssccessueesssusecssuesesnsecseneeests 50 5 3 1 Ignored Directives etn e ee RR PUR tette eee dene nere tede 50 5 3 2 Limitations on the Use of stdefine eessssssscssscccsssecsssseccsnsecssnseessnsccessscessuecesuseessuscessnseessssseessnseeesusecssnsceecnseessness 50 5 3 3 Limitations on the Use of f 25 5 3 4 Limitations on the Use of include 2251 5 Example ccrte teeth REG ern 52 5 5 The ALTIBASE_APRE Macro 54 5 5 1 Example 54 5 6 Considerations 55 5 6 1 Defining Macros 455 6 Host Variable Data Type ccscccscscsscssessscccscsssccssccsccscscsscsccssssescecssescesssecsscssecsecssccsscssessecsscssessscssecssecsossscssecsoes 57 EO ID 58 6 2 Fundamental C C Data TyPes ssseccssconsssssssssussessssusseessesssssesscsvssseecsoussssscesassessssovssessessuseatonuussessssenssesensausensessussensesoussessesoasees 59 6 2 T Numeric Types Jesse tee uester T AN ELAN he eee debere aeg REER 59 622 Charact
56. s dno varchar s emp _job 15 1 EXEC SQL END DECLARE SECTION S eno 2 S dno 1001 Precompiler User s Manual 18 2 3 Programming using Embedded SQL Statements strcpy s emp job arr ENGINEER s emp job len strlen s emp job arr EXEC SOL UPDATE EMPLOYEES SET DNO s dno EMP JOB s emp job WHERE ENO s eno Cursor Control Embedded SQL Example The following is an example of the use of cursor control statements lt Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct department short dno char dname 3041 char dep location 9 41 int mgr no department typedef struct dept ind int dno int dname int dep location int mgr no dept ind EXEC SQL END DECLARE SECTION Sample Program cursor1 sc gt Specify path to header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h declare host variables EXEC SQL BEGIN DECLARE SECTION structure host variables department s department structure indicator variables dept ind s dept ind EXEC SQL END DECLARE SECTION declare cursor EXEC SOL DECLARE DEPT CUR CURSOR FOR SELECT FROM DEPARTMENTS open cursor EXEC SQL OPEN DEPT CUR fetch cursor in loop while 1 use indicator variables to check for NULL values EXEC SQL FETCH DEPT CUR INTO s department s dept ind if sqlca sqlcode SQL SU
57. s goods good ind s good ind EXEC SOL END DECLARE SECTION EXEC SOL SELECT INTO s goods s good ind FROM GOODS WHERE GNO s gno Because the GNO and GNAME columns have the NOT NULL constraint their indicator variables do not have to be checked if sqlca sqlcode SQL SUCCESS if s good ind goods location SQL NULL DATA strcpy s goods goods location NULL if s good ind stock SQL NULL DATA S goods stock 1 is s good ind price SQL NULL DATA S goods price 1 3 4 2 Input Indicator Variables To specify NULL as an input value it is necessary to use an input indicator variable In such cases the value of the indicator variable must be set to 1 When specifying a non NULL input value there is no need to use the corresponding indicator vari able but when using the indicator variable care must be taken to ensure that there is no possibility that a NULL value will be entered The meaning of the value of the indicator variable differs depend ing on the type of the input host variable For more information please refer to 3 5 Meaning of Indi cator Variables 3 4 2 1 Example The following is an example of the use of an input indicator variable In this example the variable s_goods_location_ind is used as an indicator variable for the variable s_goods_location and the variable s_price_ind is used as an indicator variable for the variable s_price The values of s_goods_location_in
58. sample APRE mc3 sc 13 4 3 1 Result of Execution is f schema schema sql make mc3 mc3 MULTI CONNECTION 3 Drop Procedure With CONN2 Precompiler User s Manual 192 13 4 Sample Programs Success drop procedure 193 Applications with Multiple Database Connec 13 4 Sample Programs Precompiler User s Manual 194 14 Multithreaded Applications 195 Multithreaded Applications 14 1 Multithreaded Applications 14 1 Multithreaded Applications APRE can be used to author multi threaded applications This chapter explains how to use embed ded SQL statements in multi threaded programs and highlights some considerations to keep in mind when doing so 14 1 1 Configuring an Application as a Multithreaded Application When precompiling a multithreaded application APRE needs to be informed that the application is a multithreaded application This can be accomplished in either of the following two ways By setting the mt option on the command line Example apre mt samplel sc Using the OPTION statement in the file to be precompiled Example EXEC SQL OPTION THREADS TRUE 14 1 2 Description Each thread must have its own database connection In other words it is impossible for multi ple threads to share a single database connection The name of each connection must be unique within the application If an attempt is made to establish a connection using the name of an established connect
59. sc EXEC SOL BEGIN DECLARE SECTION int s period EXEC SQL END DECLARE SECTION S period 1 1 month 3 quarter year 6 half year EXEC SQL SELECT SUM sale FROM sales GROUP BY FLOOR month CAST S period AS INTEGER Example 7 The following example shows the use of the input host variable s diffin the join predi cate of a WHERE clause lt Sample Program host join sc gt EXEC SQL BEGIN DECLARE SECTION int s diff EXEC SQL END DECLARE SECTION Sodift zd EXEC SOL SELECT FROM t1 t2 WHERE tl il t2 il CAST s diff AS INTEGER Precompiler User s Manual 28 3 3 Indicator Variables 3 3 Indicator Variables 3 3 1 Definition Because NULL table column values cannot be expressed in the host language a method of handling them separately is required To enable APRE to process NULL values the use of so called indicator variables is supported Indicator variables are used alongside host variables in embedded SQL statements to process NULL values 3 3 2 Why use indicator variables 3 3 2 1 For Handling NULL Values Indicator variables can be used to provide information on the basis of which a programmer can judge whether or not a column value is NULL If an input indicator variable is set to 1 SQL_NULL_DATA the corresponding host variable will be processed as NULL If the value of an output indicator variable is 1 SQL_NULL_DATA it means that the value returned for the correspond
60. select from orders break EXEC SQL PREPARE S FROM query 161 Dynamic SQL Statements 11 2 Using Dynamic SQL Statements 11 2 3 3 DECLARE CURSOR Syntax EXEC SQL DECLARE cursor name gt CURSOR FOR statement name Arguments cursor name This is the name of the cursor statement name This is the identifier of the SOL statement Description This statement can be executed after a PREPARE statement a CLOSE statement or a CLOSE RELEASE statement If an undefined SQL statement identifier is specified when the DECLARE CURSOR state ment is executed an error indicating that the SOL statement identifier does not exist will be raised The SQL statement identified by the specified SOL statement identifier must be a SELECT statement This statement declares a cursor that references the provided SQL statement identifier If the same SQL statement identifier is used in multiple PREPARE statements within the same appli cation then the cursor that is declared will reference the most recently prepared SQL statement hav ing that identifier at run time Example In the following example the cursor CUR which references the SQL statement identified by the identifier S is declared Sample Program dynamic3 sc EXEC SOL DECLARE CUR CURSOR FOR S 11 2 3 4 OPEN Syntax EXEC SQL OPEN cursor name USING host var list Arguments cursor name This is the name of the cursor host var list
61. statement EXEC SQL OPTION THREADS TRUE That is it is not necessary to use both the mt command line option and the above SQL statement in the file to be precompiled one or the other may be safely omitted When precompiling multiple files belonging to the same application at one time the use of this option is preferable to the use of the embedded SQL statement shown above because this option applies to all of the files being precompiled at the same time For more information about the use of the OPTION statement please refer to Chapter7 Embedded SQL Statements 2 2 4 1 Example Use the mt option to precompile a multithreaded program written in C This command will result in the creation of a file named sample1 c apre mt samplel sc 2 2 5 l include path This option is used to specify the location s of the header file s to be used in the precompiling 9 The C C Precompiler 2 2 Command Line Options operation Both absolute and relative paths can be specified APRE will always look for the header file in the current directory first followed by the directories specified here In order to specify multiple locations the I option can be specified multiple times Use the I option to specify the location of header files to be used for precompiling When this option is specified as shown APRE will look for the header file in the current directory first and will then look in the include directory apre I I
62. statement cannot be executed using that cursor If it is desired to use the cursor to perform another FETCH operation it will be nec essary to open the cursor before executing the FETCH statement again If the CLOSE statement is executed when there are no more data left to return on the database server i e when all of the records have been fetched then the CLOSE statement has absolutely no effect Therefore if all of the records returned by an OPEN statement have been retrieved from the database server the CLOSE statement can be safely omitted When the CLOSE statement is executed the resources allocated to the cursor are not released Addi Precompiler User s Manual 124 9 2 Cursor Related SQL Statements tionally the results of the SQL statement preparation tasks that were conducted when the DECLARE CURSOR statement was executed are saved To use this prepared SQL statement after the CLOSE statement has been executed omit the DECLARE CURSOR statement and execute the OPEN state ment using the same cursor name 9 2 4 4 Example The following example shows the use of the CLOSE statement to close the DEPT_CUR cursor lt Sample Program cursorl sc gt EXEC SQL CLOSE DEPT CUR 9 2 5 CLOSE RELEASE This statement is used to close a cursor and release all of the resources that were allocated to the cursor 9 2 5 1 Syntax EXEC SQL CLOSE RELEASE cursor name 9 2 5 2 Arguments cursor name This is the name of th
63. than the length of the column in the database 2 2 9 1 Example apre n samplel sc 2 2 10 unsafe null This option is used to prevent an error from being raised even when a NULL value is fetched and an indicator variable is not being used Normally without the use of preventive logic involving indicator variables an error occurs if the value of a column on which a SELECT or FETCH operation is per formed is NULL 11 The C C Precompiler 2 2 Command Line Options 2 2 10 1 Example apre unsafe null samplel sc 2 2 11 spill values This option is specified only when precompiling in an AIX environment This is the same as using the pragma directive as shown below pragma options spill values 2 2 11 1 Example apre spill AIX samplel sc 2 2 12 keyword When this option is used the precompile operation is not performed and apre outputs a list of key words reserved by APRE to help programmers avoid using them as variable names in C source code 2 2 12 1 List of Reserved Keywords apre keyword Keywords for C code ALTIBASE APRE APRE BINARY APRE BIT APRE BLOB APRE BLOB LOCATOR APRE BYTES APRE CLOB APRE CLOB LOCATOR APRE DUPKEY ERR APRE INTEGER APRE NIBBLE APRE NUMERIC MAX CHAR PTR SESC DECLARE SESC INCLUDE SES BINARY SES BIT SES BLOB SES BLOB LOCATOR SES BYTES SES CLOB SES CLOB LOCATOR SES DUPKEY ERR SES INTEGER SES NIBBLE SES NUMERIC SQLFailOverCallback SQLLEN SQL DATE STRUCT SOL TIMESTAMP STRUCT SQL T
64. time the value of sqlca sqlcode is SQL NO DATA 8 5 3 Actions If the result of execution of an embedded SQL statement matches the condition specified in the WHENEVER statement the action specified here will be taken The following actions can be specified in the WHENEVER statement 8 5 3 1 CONTINUE This specifies that the condition is to be ignored and execution is to continue 8 5 3 2 DO BREAK This specifies that the current loop construct is to be exited and that execution is to continue This has the same effect as using the break command in a loop construct This action can only be specified within a loop construct and a WHENEVER statement specifying the action will only have an effect within the loop Precompiler User s Manual 114 8 5 WHENEVER Statement This specifies that control is to be passed to the next iteration of the current loop construct and that execution is to continue This has the same effect as using the continue command ina loop con struct This action can only be specified within a loop construct and a WHENEVER statement specify ing the action will only have an effect within the loop This specifies that control is to be passed to the statement immediately following label name and that execution is to continue This specifies that the connection with the database server is to be closed and that the application is to be shut down The scope of applicability of a WHENEVER statement
65. to compile the sample project found at SALTIBASE_HOME sample APRE sample1 sc in a 64 bit Solaris 2 8 environment COMPILE c opt SUNWspro bin CC mt fast xarch v9 xprefetch yes DPDL NDEBUG c LD opt SUNWspro bin CC LFLAGS mt xarch v9 library iostream no Cstd L opt SUNWspro SC5 0 lib v9 L usr lib sparcv9 xprefetch yes fast L ALTIBASE HOME lib GOPT INCLUDES I ALTIBASE HOME include I LIBDIRS L ALTIBASE HOME lib LIBS xnolib Bdynamic lthread lposix4 ldl lkvm lkstat lsocket lnsl lgen lm lw lc Bstatic liostream 1Crun CC_OUTPUT_FLAG o SRCS samplel c OBJS SRCS cpp o BINS samplel SOBJS SESS cpp o c sc apre all BINS samplel samplel o samplel c LD S LFLAGS GOPT o 0 samplel o lapre lodbccli LIBS 0 COMPILE c INCLUDES CC OUTPUT FLAG lt clean rm BINS SRCS OBJS core An error occurred when attempting to link libraries in HP UX Why A link error occurred in HP UX upon library linking used the following C compiler options cc DA2 0W I include I userl altibase altibase home include I user1 asnl include Ae D REENTRANT DOAM DSRC LINE DDEBUG DDETAIL DEBUG g c dbinit c then linked the resultant object files as follows cc DA2 0W o MKTDBD dbfunc o main o util o dbif o shm msg o file o dbinit o dbresult o L userl altibase altibase home lib L userl main KTSLEE lib lcom
66. together with other host variables in the input host variable list or output host variable list Various examples of the declaration and use of structures containing arrays as host variables are shown below Example 1 The following example shows the use of a structure containing arrays as an input host variable in an UPDATE statement Because the value SOL NULL DATA is set in the elements of the a emp tel ind array the values in the EMP TEL column will be overwritten with NULL lt Sample Program arraysl sc gt EXEC SQL BEGIN DECLARE SECTION struct int eno 3 short dno 3 char emp tel 3 1541 employee int a emp tel ind 3 EXEC SQL END DECLARE SECTION set host variables a employee eno 0 17 a employee eno 1 16 a employee eno 2 15 a employee dno 0 1003 a employee dno 1 1003 a employee dno 2 1003 set indicator variables a emp tel ind 0 SOL NULL DATA a emp tel ind 1 SOL NULL DATA a emp tel ind 2 SOL NULL DATA EXEC SQL UPDATE EMPLOYEES SET DNO a employee dno EMP TEL a employee emp tel a emp tel ind JOIN DATE SYSDATE WHERE ENO a employee eno Example 2 The following example shows the use of a structure containing arrays as an output host variable in a SELECT statement Sample Program arrays2 sc gt EXEC SQL BEGIN DECLARE SECTION short s dno struct short dno 5 char dname 5 30 41 151 Us
67. uiuo acte rectore e Hone e e eR RERUM RE SEEGER 145 10 3 3 Considerations e ete eee dete bo te S RE e Er e TER Erbe dete 146 10 4 Limitations on the Use of Array Type Host Variables ssssssssscssssssccssesssssecssseesssnsecssnsesesnsecessseecssnecessueecesueeessneeessaee 147 16 4 1 In th DECLARE section ottenere bbb biete ipee ee iceberg bibet abit 147 LUC AREIS CIC 147 TO 5 Structures and Arrays e e e e ORO E edle e neut ted Rete e Iter td dite 148 10 5 1 Arrays OF Structures ice haste HORN tt n ECT NERA E ESEAS RT ree aS 148 10 5 2 Structures Containing Arrays scssssscccccscssnipssnaconscasaceasvsacosestvascbapoannsctadennvassavnnssecdsasonipsbancentansvavorueecsndcanbentavanaseeciiae 150 10 6 Sample Programs rtr deer iie he eret s dete tertie dee pe eh 153 10 6 Earra cd rA 153 10 6 2 Result OF EXecution i et ere eret e este ies cited dele e eddie 153 Uoc EP 154 10 6 4 Result of EXecutlon c ct et ere entes etc RR eee eA RR deed 154 11 Dynamic SQL Statements eese 155 11 1 Static versus Dynamic SQL StateMent ssescsesssseecssescsseccseecssescsseessseesssccsseccssccsseccsscesseeesscesssecesseesseecsuessnseceneecsnecenseesse 156 AT T T Static SQL State mie mts sssisssccsiesscossasscasiitiesisrwssissssassctssssstbesesoadesdnassussssossbdesssbosessoasdhocebdascousoss2iasipheabisiadssdotdbsestinssobede 156 113 2 Dynamic SQL Statements veninne E E E Naas 156
68. variable and ins_clob_ind is the corresponding input indi cator variable The value of ins clob ind is set to the length of the value stored in ins clob Meanwhile sel clob is an output host variable and sel clob ind is its output indicator variable After the execution of the SELECT statement a sel clob ind value of 1 means that sel clob is NULL whereas a sel clob ind value greater than 0 indicates the length of the value stored in se clob lt Sample Program binary sc gt EXEC SOL BEGIN DECLARE SECTION APRE CLOB ins _clob 10 1 APRE CLOB sel clob 10 1 SOLLEN ins clob ind SOLLEN sel clob ind EXEC SOL END DECLARE SECTION memset ins clob 0x41 10 ins clob ind 10 set length of ins clob value in indicator variable EXEC SOL INSERT INTO T CLOB VALUES ins clob ins clob ind EXEC SOL SELECT INTO sel clob sel clob ind FROM T CLOB m This type can be used only with BLOB type database columns It is essential that an indicator variable be declared and used with this type When using the APRE BLOB type as an input host variable set the value of the corresponding indi cator variable to 1 to indicate that the value of the host variable is NULL When the value of the host variable is any other value i e a non NULL value set the value of the indicator variable to the length of the data saved in the host variable When using this type as an output host variable a value of 1 in the corr
69. variable is declared for use with the structure type host variable so that a separate indicator variable corresponding to the varchar type element can be provided inside the structure type indicator variable 6 3 1 2 Advantage Because the varchar type has its own internal indicator variable there is no need for the user to specify a separate indicator variable This makes it convenient to use the varchar type when it is necessary to use an indicator variable 6 3 1 3 Considerations Unless a separate indicator variable is specified for use with a varchar type host variable len one of its constituent elements will function as the indicator variable Therefore when using a varchar type variable as an input host variable it is necessary to expressly specify the value of 1en when not using a separate indicator variable If it is desired to input NULL data then set the value of 1en to 1 When entering non NULL data set the value of 1en to the actual length of the arr element excluding the trailing NULL character 65 Host Variable Data Types 6 3 Extended APRE Data Types Example EXEC SQL BEGIN DECLARE SECTION varchar varl EXEC SQL END DECLARE SECTION strcpy varl arr ABC varl len strlen varl arr EXEC SQL INSERT INTO T1 I1 VALUES varl acceptable When using the varchar type as an output host variable that will receive a value from a CHAR type column be sure to declare the length of the host variable so tha
70. variable is not meaningful i e a garbage value If the value of the indicator variable is not 1 SOL NULL DATA this means that the value in the corresponding column is not NULL and will be saved in the output host variable For more detailed information about the value of the indicator variable in such cases please refer to the sec tion 3 5 Meaning of Indicator Variables 3 4 1 1 Examples The following is an example of the use of an output indicator variable In this example the variable s good ind is used as an indicator variable for the variable s goods Becauses goodsisa structure s good ind must also be declared as a structure The two structures will have the same number of components After the SELECT statement is executed each of the members of s good ind will be checked to determine if the value is 1 Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 10 1 char gname 20 1 char goods _location 9 1 int stock double price goods typedef struct good_ind int gno int gname int goods_location int stock int price good ind EXEC SQL END DECLARE SECTION Sample Program indicator sc gt Specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling 33 Host Variables and Indicator Variables 3 4 Classifying Indicator Variables EXEC SQL INCLUDE hostvar h EXEC SOL BEGIN DECLARE SECTION goods
71. 0 2000 12 30 2000 12 30 2000 12 30 f schema schema sql A111100002 E111100001 E111100001 D111100008 D111100004 C111100001 E111100002 D111100002 D111100008 A111100002 D111100002 D111100011 D111100003 D111100010 C111100001 E111100012 C111100001 F111100001 197 Multithreaded Applications 14 2 Sample Programs Precompiler User s Manual 198 15 Error Codes and Messages 199 Error Codes and Messages 15 1 Precompiler Errors 15 1 Precompiler Errors The messages for the errors that can be raised during the precompile operation have the following format 15 1 1 Error Format ERR lt error code gt lt error message gt File file name gt Line line number Offset lt n m gt Error token error statement error code This is the numerical code corresponding to the error that occurred error message This is the corresponding error message file name This is the name of the file containing the code that caused the error line number This is the number of the line in which the error occurred lt n m gt This is the start and end character indicating the portion of the line in which the error occurred error statement This is the kind of statement that caused the error 15 1 1 1 Example The following example shows the error that is raised when the EXEC SOL END DECLARE SECTION statement is missing from the source code ERR 302L EXEC SQL END DECLARE SECTION
72. 00 When no records were returned as the result of execution of a SELECT or FETCH statement that is when the value of sglca sqlcode is SQL NO DATA 1 When an error occurred during the execution of an embedded SQL statement but there is no error code corresponding to the error At this time the value of sqlica sqlcode is SQL ERROR 2 When an attempt was made to execute an embedded SQL statement without first establishing a database connection that is when the value of sqlca sql code is SOL INVALID HANDLE The presence of any value other than the values listed above in SOLCODE is an error message indicating the occurrence of an error in the corresponding SQL statement Commit Mode This section explains the differences between ALTIBASE HDB and Oracle related to the commit mode including the default commit mode how to change the commit mode and how to commit transactions t Commit Oracle ALTIBASE HDB Non Autocommit Mode Autocommit Mode ng the Clann Chang Oracle EXEC SQL ALTER SESSION SET AUTOCOMMIT TRUE or FALSE ALTIBASE HDB EXEC SQL AUTOCOMMIT ON or EXEC SQL ALTER SESSION SET AUTOCOMMIT TRUE or FALSE 219 Porting Pro C Applications to APRE Sample Programs Explicit Commit Oracle EXEC SQL COMMIT Or EXEC SQL COMMIT WORK ALTIBASE HDB EXEC SQL COMMIT When Executing a SELECT Statement in Non Autocommit Mode Oracle There is no ne
73. 4 3 Function Argument Declaration Section 4 3 Function Argument Declaration Section When using a function argument as a host variable it is necessary to have a way to provide informa tion about the function arguments to the C C precompiler The function argument declaration section plays the role of providing information about function arguments to the C C precompiler 4 3 1 Syntax The syntax of the function argument declaration section is as follows EXEC SQL BEGIN ARGUMENT SECTION Declare function arguments to be used as host variables EXEC SQL END ARGUMENT SECTION The function argument declaration section begins with the EXEC SQL BEGIN ARGUMENT SECTION statement and ends with the EXEC SQL END ARGUMENT SECTION statement Function arguments to be used as host variables must be declared between these two statements The function arguments that are declared within the function argument declaration section must be exactly the same as the function arguments declared in the function header 4 3 2 Description The function argument declaration section can be located only inside functions that are found inside the file to be precompiled i e the file with the sc extension Additionally the limitations 4 1 3 Limitations that apply to the host variable declaration section also apply to the function argu ment declaration section Using the function argument declaration section eliminates the need to declare global host vari
74. 6 Input Host Variables EXEC SQL BEGIN DECLARE SECTION int sI1 char sI2FName 32 unsigned int sI2FOpt SOLLEN sI2Ind EXEC SQL END DECLARE SECTION STIs i strcpy sI2FName aInput FileName sI2FOpt APRE FILE READ EXEC SQL INSERT INTO T LOB VALUES sI1 BLOB FILE sI2FName OPTION sI2FOpt INDICATOR sI2Ind 207 Using Files and LOBs Input Host Variables Precompiler User s Manual 208 Datatypes Appendix B Porting Pro C Applications to APRE Refer to this appendix when converting an application that was written using Oracle Pro C C so that it can be compiled using the ALTIBASE HDB C C Precompiler Datatypes This section describes the Oracle data types and the corresponding data types of ALTIBASE HDB ODBC SQL datatype Oracle ALTIBASE HDB Comments SQL_CHAR CHAR CHAR 1 32000 bytes long SQL_TIMESTAMP DATE DATE SQL_LONGVARCHAR LONG BLOB Up to 2147483647bytes SQL_INTEGER INT INTEGER SQL_FLOAT NUMBER NUMBER SQL_DECIMAL NUMBER P NUMBER P 1 38 SQL DECIMAL NUMBER PS NUMBER P S precision 1 38 scale 84 126 SQL_BINARY RAW BYTE 1 32000 SQL_VARCHAR VARCHAR VARCHAR max 32000 bytes SQL_VARCHAR VARCHAR2 VARCHAR max 32000 bytes Embedded Functions Numeric functions date time functions character functions data type conversion functions and built in functions are provided in ALTIBASE HDB just as they are in Oracle This section explains which ALTIBASE HDB
75. 817 EXEC SOL WHENEVER SOLERROR EXEC SQL CONNECT username IDENTIFIED BY password AT db name USING connstr ALTIBASE HDB strcpy user2 ALTIBASE strcpy passwd2 ALTIBASE strcpy connstr2 DSN 192 168 1 12 PORTNO 20310 CONNTYPE 1 strcpy conn name CONN2 EXEC SQL AT conn name CONNECT user2 IDENTIFIED BY passwd2 USING connstr2 Disconnecting from a Database In Oracle the EXEC SQL ROLLBACK WORK RELEASE statement is used to roll back any pending trans actions and disconnect from the database in a single line of code This statement is also supported in ALTIBASE HDB 211 Porting Pro C Applications to APRE Host Variables B 0 0 4 Disconnection Statements Oracle EXEC SQL COMMIT WORK RELEASE or EXEC SQL ROLLBACK WORK RELEASE ALTIBASE HDB EXEC SQL COMMIT WORK RELEASE or EXEC SQL ROLLBACK WORK RELEASE Host Variables This section describes the differences between the host variables used with Oracle Pro C and those used with the ALTIBASE HDB C C Precompiler Host Variable Compatibility Oracle ALTIBASE HDB Database Host Variable Database Host Variable Remarks Column type C type Column type C type CHAR Char CHAR char char 2 single character VARCHAR2 X VARCHAR X VARCHAR X VARCHAR X n byte variable length VARCHAR X character array CHARI X char x CHARI X char x n byte character array NUMBER Int NUMBER int APRE_INT In
76. ASE DECLARE CURSOR and OPEN statements has a negative impact on perfor mance 127 Using Cursors 9 4 Sample Programs 9 4 Sample Programs 9 4 1 cursor1 sc This example can be found at SALTIBASE HOME sample APRE cursor1 sc 9 4 2 Result of Execution is f schema schema sql make cursorl cursor1 CURSOR 1 1001 RESEARCH DEVELOPMENT DEPT 1 New York 16 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13 1003 SOLUTION DEVELOPMENT DEPT Osaka 14 2001 QUALITY ASSURANCE DEPT Seoul 17 3001 CUSTOMER SUPPORT DEPT London 4 3002 PRESALES DEPT Peking 5 4001 MARKETING DEPT Brasilia 8 4002 BUSINESS DEPT Palo Alto 7 Successfully closed cursor 9 4 3 cursor2 sc This example can be found at SALTIBASE HOME sample APRE cursor2 sc 9 4 4 Result of Execution is f schema schema sql make cursor2 cursor2 CURSOR 2 Precompiler User s Manual 128 9 4 Sample Programs Successfully opened cursor 2 1 1500000 00 3 1001 2000000 00 4 3001 1800000 00 5 3002 2500000 00 6 1002 1700000 00 7 4002 500000 00 9 4001 1200000 00 10 1003 4000000 00 11 1003 2750000 00 12 4002 1890000 00 13 1002 980000 00 14 1003 2003000 00 15 1003 1000000 00 16 1001 2300000 00 17 2001 1400000 00 18 4001 1900000 00 19 4002 1800000 00 Successfully closed and released cursor 129 Using Cursors 9 4 Sample Programs Precompiler User s Manual 130 10 Using Arrays in Embedded SQL Statements 10 1 Overview 10 1 Overvie
77. ATE INTO s time s ind FROM EMPLOYEES WHERE ENO 3 s time hour 12 s time minute 12 s time second 12 EXEC SQL UPDATE EMPLOYEES SET JOIN DATE s time WHERE ENO 4 6 3 2 5 SQL TIMESTAMP STRUCT This type comprises year month date hour minute second and nanosecond elements Its struc ture is shown below The fraction element is the element in which the nanoseconds i e bil lionths of a second are stored typedef struct tagTIMESTAMP STRUCT SOLSMALLINT year SQLSMALLINT month SOLSMALLINT day SQLSMALLINT hour SOLSMALLINT minute SQLSMALLINT second SOLINTEGER fraction TIMESTAMP STRUCT 6 3 2 6 Example The following example shows the use of the SQL TIMESTAMP STRUCT type In this example s timestamp is used as both an input and output host variable Sample Program date sc gt Precompiler User s Manual 68 6 3 Extended APRE Data Types EXEC SQL BEGIN DECLARE SECTION SQL TIMESTAMP STRUCT s timestamp int s ind EXEC SOL END DECLARE SECTION EXEC SOL SELECT JOIN DATE INTO s timestamp s ind FROM EMPLOYEES WHERE ENO S timestamp year 2 s timestamp month S timestamp day 9 s timestamp hour s timestamp minute 0 s timestamp second 15 s timestamp fraction 3 EXEC SOL UPDATE EMPLOYEES SET JOIN DATE s timestamp WHERE ENO 5 6 3 3 Binary Types Binary type host variables can be used with CLOB BLOB BYTE or NIBBLE type
78. Altibase Application Development Precompiler User s Manual Release 6 1 1 February 4 2013 Z LTIBASE Altibase Application Development Precompiler User s Manual Release 6 1 1 Copyright 2001 2012 Altibase Corporation All rights reserved This manual contains proprietary information of Altibase Corporation it is provided under a license agreement containing restric tions on use and disclosure and is also protected by copyright patent and other intellectual property law Reverse engineering of the software is prohibited All trademarks registered or otherwise are the property of their respective owners Altibase Corporation 10F Daerung PostTower II 182 13 Guro dong Guro gu Seoul 152 847 Korea Telephone 82 2 2082 1000 Fax 82 2 2082 1099 Homepage http www altibase com Contents Preface ids esce eei adeceunscccevscotsastecodes E E OP A EE E NE E EA EAE i About This Man al 2 er te ae ee eee P P SOR UA ERN N e e nii ii PUGION CO AERE m e TA A EN ii Ter iR I A RII BUM RR EECM ii Organization aona RUPEE eese DD M DM ii Documentation CONVENTIONS 4 eiecit rae eet rua eni ES RE aos ee reete eee egeta seti esit un aee euge iv Related Re dirig ott RH RA RD I RSS Rete a e etii vi OnlinecManuals etr ba debe tette tetra obice bebe ee o Ede de deed NE eee vi Altibase Welcomes Your Comments sssssssssesssessesssesssesssessesssesssessscssesssesssessscesessscsscs
79. CCESS check sqlca sqlcode printf sd s s d n S department dno s department dname 19 The C C Precompiler 2 3 Programming using Embedded SQL Statements S department dep location S department mgr no else if sqlca sqlcode SQL NO DATA break else o printf Error d s n SQLCODE sqlca sqlerrm sqlerrmc break close cursor EXEC SQL CLOSE DEPT CUR 2 3 4 Handling Runtime Errors After every embedded SQL statement has been executed it is necessary to check the result of exe cution The result of execution of embedded SQL statements is stored in the variable sqlca sqlcode and depending on the value of sqlca sqlcode the variables SOLSTATE SOLCODE etc can be checked to obtain more information about the result of execution For detailed information about all of the variables that can be checked to determine the result of execution of an embedded SQL statement please refer to Chapter8 Handling Runtime Errors 2 3 4 1 Considerations when Handling Runtime Errors Thefollowing are some considerations to keep in mind when using SOLCA SOLCODE SQLSTATE and WHENEVER to handle run time errors Every time an embedded SQL statement is executed be sure to check the value of sqlca sql code so that any errors that occurred will be processed appropriately When a SELECT statement is executed if the size of an output host variable is smaller than the size of the corresponding
80. CTION strcpy delete stmt DELETE FROM EMPLOYEES WHERE ENO EXEC SQL PREPARE sql stmt FROM delete stmt emp number 10 EXEC SQL EXECUTE sql stmt USING emp number 215 Porting Pro C Applications to APRE Execution Results and Status Codes B 0 0 10 Method 3 Oracle char sql query 80 int dno 10 char e lastname 10 strcpy sql query SELECT e lastname FROM employees WHERE dno gt v1 EXEC SQL PREPARE S FROM dynstmt EXEC SQL DECLARE C CURSOR FOR S EXEC SQL OPEN C USING dno for EXEC SQL FETCH C INTO e lastname ALTIBASE HDB EXEC SQL BEGIN DECLARE SECTION char sql query 80 int dno 10 char e lastname 10 EXEC SQL END DECLARE SECTION strcpy sql query SELECT e lastname FROM employees WHERE dno EXEC SQL PREPARE S FROM dynstmt EXEC SQL DECLARE C CURSOR FOR S EXEC SQL OPEN C USING deptno for zi EXEC SQL FETCH C INTO e_lastname Execution Results and Status Codes This section will explain the differences between Oracle and ALTIBASE HDB in the use of the SQL STATE SQLCODE and SQLCA variables to handle runtime errors SQLCA SQLCA is a structure in which information about the results of execution of embedded SQL state ments is saved In ALTIBASE HDB the supported elements of the structure are sql code sql errm sqlerrmc sqlerrm sqlerrml1 sqlerrd 2 and sqlerrd 3 Other SQLCA elements such as sql warn are implemented only in the Oracle S
81. Chapter4 Host Variable Declaration Section Definitions of data types typedef to be used as host variable data types must be made in the Precompiler User s Manual 16 2 3 Programming using Embedded SQL Statements host variable declaration section 2 3 1 5 Example The following is an example of a host variable declaration section lt Sample Program insert sc gt declare host variables EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 char s_gno 10 1 char s_gname 20 1 char s goods location 9 1 int s stock double s price EXEC SQL END DECLARE SECTION 2 3 2 Connecting to a Database Server After the host variables have been declared it is necessary to connect to a database server before any SQL statements can be executed After a connection with a database server has been successfully established it will then be possible to execute all embedded SQL statements For detailed instructions on how to connect to database servers please refer to Chapter7 Embed ded SQL Statements 2 3 2 1 About Connections Multiple Connections and Sessions To establish a new connection using the same name as an existing connection it is first neces sary to execute the FREE or DISCONNECT statement to terminate the existing connection If the database server is online execute the DISCONNECT statement whereas if the database server is offline execute the FREE statement If the connection method CO
82. Chapter7 Embedded SQL Statements lt conn_opt2 gt Please refer to Chapter7 Embedded SQL Statements 13 2 1 3 Description When establishing more than one connection in one application it is necessary to specify a name for every connection except the default connection The name of each connection must be unique within the application After the connection has been established the name of the connection to use in subsequently executed embedded SQL statements must be specified using the AT clause 13 2 1 4 Considerations In an application with multiple database connections only one connection that does not have a name is allowed If no connection name is specified in subsequently executed embedded SQL statements they will be processed using this connection the default connection If an attempt is made to establish a connection using the name of an established connection an error will be raised indicating that an established connection with that name already exists To establish a new connection using the same name as an existing connection it is first neces sary to execute the FREE or DISCONNECT statement If the database server is online execute Precompiler User s Manual 182 13 2 SQL Statements for Multi Connections the DISCONNECT statement whereas if the database server is offline execute the FREE state ment 13 2 1 5 Examples The following two examples illustrate how to specify the name of a connection when est
83. DE hostvar h EXEC SQL BEGIN DECLARE SECTION goods s_goods EXEC SQL END DECLARE SECTION strcpy s goods gno F111100003 strcpy s goods gname XX 102 strcpy s goods goods location AD0003 S goods stock 6000 S goods price 10200 96 EXEC SOL INSERT INTO GOODS VALUES s goods For an example of the use of an INSERT statement to insert data from a file into a BLOB or CLOB col umn please refer to Appendix A 7 3 3 UPDATE This statement is used to find records that meet specified conditions and change the values in cer tain columns of those records 7 3 3 1 Syntax Please refer to the SQL Reference 7 3 3 2 Arguments None 7 3 3 3 Description Both host variables and indicator variables can be used in both the SET and WHERE clauses 7 3 3 4 Limitations Arrays must not be used together with non array type variables For example if the host vari able used in the SET clause is an array the host variable used in the WHERE clause must also be an array Example EXEC SQL BEGIN DECLARE SECTION int var1 10 int var2 10 int var3 EXEC SQL END DECLARE SECTION EXEC SQL UPDATE T1 SET Il varl I2 var2 WHERE Il var3 unacceptable 7 3 3 5 Example Various UPDATE statement examples are shown below 93 Embedded SQL Statements 7 3 Using DDL and DML in Embedded SQL Statements Example 1 In this example records for which the value in the ENO column matches the value of the s_eno hos
84. DECLARE CURSOR statement precedes the PREPARE statement and thus the DECLARE STATEMENT is required before the DECLARE CURSOR statement EXEC SQL DECLARE sql stmt STATEMENT EXEC SQL DECLARE emp cursor CURSOR FOR sql stmt EXEC SQL PREPARE sql stmt FROM dyn string 11 2 3 2 PREPARE EXEC SQL PREPARE statement name FROM host var string literal Arguments statement name This is the identifier of the SOL statement It can be a maximum of 50 characters host var This is a character type variable that includes all of the SQL statement text string literal This is the entire SOL statement hard coded in the form of a string Description This statement is used to prepare an SQL statement for execution If the same SQL statement identifier is used in multiple PREPARE statements within the same appli cation then the SQL statement that is used when the DECLARE CURSOR statement is called at run time will be the most recently prepared SQL statement having that identifier The SQL statement must be a SELECT statement Example In the following example the text of an SOL statement is determined according to the conditions at run time and the corresponding SQL statement is then prepared Sample Program dynamic3 sc char query 100 int type switch type case 1 strcpy query select from departments break case 2 strcpy query select from goods break case 3 strcpy query
85. E BLOB 6 3 3 6 Example The following example illustrates the use of the APRE BINARY type In this example ins blob is an input host variable and ins blob ind is the corresponding input indi cator variable The value of ins blob indis set to the length of the value stored in ins blob Meanwhile se blob is an output host variable and se blob ind is its output indicator variable After the execution of the SELECT statement a se blob ind value of 1 means that sel blob is NULL whereas a sel blob ind value greater than 0 indicates the length of the value stored in sel blob lt Sample Program binary sc gt EXEC SQL BEGIN DECLARE SECTION APRE BINARY ins blob 10 1 APRE BINARY sel blob 10 41 int ins blob ind int sel blob ind EXEC SOL END DECLARE SECTION memset ins blob 0x21 10 ins blob ind 10 set length of ins blob value in indicator variable EXEC SOL INSERT INTO T BLOB VALUES ins blob ins blob ind EXEC SOL SELECT INTO sel blob sel blob ind FROM T BLOB 6 3 3 7 APRE BYTES The APRE BYTES type can be used only with BYTE type database columns In all other respects it is identical to the APRE BLOB type For more information please refer to 6 3 3 3 APRE BLOB 71 Host Variable Data Types 6 3 Extended APRE Data Types The following example illustrates the use of the APRE_BYTES type In this example ins_bytes is an input host variable and ins_bytes_ind is the corresponding input ind
86. E HOME sample APRE indicator sc 3 6 2 Result of Execution is f schema schema sql make indicator indicator INDICATOR VARIABLES X111100002 XX 101 NULL 5000 1 00 3 rows updated 3 times update success 3 rows inserted 3 times inserte success Indicator Variable len of VARCHAR With Output Host Variables v address arr Pusan University v address len 16 Indicator Variable len of VARCHAR With Input Host Variables d arrival date2 NULL Precompiler User s Manual 38 4 Host Variable Declaration Section 39 Host Variable Declaration Section 4 1 Host Variable Declaration Section 4 1 Host Variable Declaration Section The name type and length of host variables are critical information during the precompiling opera tion Therefore the host variables to be used must be defined in a form such that the C C precom piler can be aware of them This is accomplished in the host variable declaration section If you set the parse option to none or partial the C C precompiler will be made aware only of the host variables that are declared in a host variable declaration section Setting the parse option to u11 however ensures that all host variables are recognized It is strongly recommended that all host variables to be used in the program be declared in host variable declaration sections 4 1 1 Syntax The syntax shown below is supported for the host variable declaration section EXEC SQL B
87. E sql stmt USING dynamic emp number Disconnect EXEC SQL DISCONNECT exit 0 Precompiler User s Manual 224 Executing the Sample Applications Appendix C Sample Applications This Appendix gives the location of the sample programs used in the manual Executing the Sample Applications The SALTIBASE HOME sample APRE directory includes sample files header files schema creation files and makefiles The list of included files is as follows argument sc arraysl sc arrays2 sc binary sc connectil sc connect2 sc cparsefull sc cursorl sc cursor2 sc date sc declare stmt sc delete sc dynamicl sc dynamic2 sc dynamic3 sc free sc indicator sc insert sc mcl sc mc2 sc mc3 sc mtl sc mt2 sc pointer sc psmi sc psm2 sc runtime error check sc select sc update sc varchar sc wheneverl sc whenever2 sc include hostvar h include hostvar2 h schema schema sql Makefile 225 Sample Applications Table Information of the Example Programs Installation When ALTIBASE HDB is installed the SALTIBASE HOME sample APRE directory will be automatically created For more information about installing ALTIBASE HDB please refer to the ALTIBASE HDB Installation Guide Execution The user can use the makefile saved in the SALTIBASE HOME sample APRE directory to create an executable file C 0 0 1 Compile make file name Example1 This example shows how to compile the delete sc sample program make d
88. E statement This method is useful in situations where an SQL statement is prepared once and then executed several times The SQL statement string to be used in the PREPARE statement can include the question mark parameter marker This parameter marker will be replaced with the value of a host variable in the EXECUTE statement The EXECUTE statement will always use the SQL statement in its most recently prepared form that is in the form that was prepared by the most recently executed PREPARE statement This means that any changes to the contents of the prepared SQL statement or to any bound variables since the most recent execution of the PREPARE statement will be ignored by the EXECUTE statement unless the PREPARE statement is executed again This method is not efficient in situations in which the text of the SOL statement change frequently because the need to repeatedly execute the PREPARE and EXECUTE statements will have a negative effect on performance This method is useful when executing INSERT UPDATE and DELETE statements for which the con tents of the SOL statement are determined at run time This method cannot be used to execute SELECT statements Note Although not required with Method 2 the DECLARE STATEMENT can be provided before the PRE PARE statement Doing so will not cause an error 11 2 2 1 PREPARE Syntax EXEC SQL PREPARE statement name FROM host var string literal Arguments statement name
89. ECLARE SECTION employees s employee EXEC SQL END DECLARE SECTION S eno 20 s employee dno 2001 strcpy s employee emp job arr TESTER s employee emp job len strlen s employee emp job arr EXEC SQL UPDATE EMPLOYEES Precompiler User s Manual 94 7 3 Using DDL and DML in Embedded SQL Statements SET DNO s employee dno EMP JOB s employee emp job JOIN DATE SYSDATE WHERE ENO s eno 7 3 4 DELETE This statement is used to delete the records that satisfy the specified conditions from the corre sponding table 7 3 4 1 Syntax Please refer to the SQL Reference 7 3 4 2 Arguments None 7 3 4 3 Description Both host variables and indicator variables can be used in the WHERE clause 7 3 4 4 Examples The following example shows how to delete records that satisfy the specified conditions from the employees table lt Sample Program delete sc gt EXEC SQL BEGIN DECLARE SECTION int s_eno short s_ dno EXEC SQL END DECLARE SECTION s eno S dno 25 1000 EXEC SQL DELETE FROM EMPLOYEES WHERE ENO gt s eno AND DNO gt s dno AND EMP JOB LIKE P 7 3 5 Sample Programs 7 3 5 1 select sc This sample program can be found at SALTIBASE HOME sample APRE select sc 7 3 5 2 Result of Execution is f schema schema sql make select 95 Embedded SQL Statements 7 3 Using DDL and DML in Embedded SQL Statements select lt SELECT gt Error 331880 Too many rows ret
90. EGIN DECLARE SECTION variable declarations EXEC SQL END DECLARE SECTION The host variable declaration section begins with the EXEC SOL BEGIN DECLARE SECTION statement and ends with the EXEC SOL END DECLARE SECTION statement Host variables to be used in the pro gram must be declared between these two statements A host variable declaration section can be present both in the file sc to be precompiled and in any header files h that are included in the precompile operation Note however that when including a header file using the include preprocessor directive a host variable declaration section is not used to declare host variables and the parse option is set to full during the precompile operation whereas a host variable declaration section is used within a header file that is included using EXEC SQL INCLUDE The reason for this is that a header file h that is included using the include preprocessor directive is referred to not only by the file to be precompiled sc but may also be referred to by C c or C cpp source files that do not contain any embedded SQL so errors may be raised during the com pile operation 4 1 2 Scope of Host Variables Host variables can be global or local in scope depending on the location of the host variable decla ration section The method of determining the scope of declared variables is similar to that of C C If a global host variable and a local host variable have the same nam
91. ERIC APRE BLOB APRE CLOB APRE BIT To use a pointer to an array of integer values as an input host variable in an INSERT statement use the FOR clause as shown in the following example int sInt 10 int sIntptr sIntptr sInt EXEC SQL FOR 10 INSERT INTO T2 VALUES sIntptr For more information about the use of the FOR clause please refer to Chapter10 Using Arrays in Embedded SQL Statements 6 2 3 5 Examples Example 1 This example demonstrates the use of the v ename char type input host variable Sample Program argument sc gt void ins employee int v eno char v ename short v dno EXEC SOL BEGIN ARGUMENT SECTION int v eno char v ename short v dno EXEC SOL END ARGUMENT SECTION EXEC SOL INSERT INTO TODAY EMPLOYEE VALUES v eno v ename v dno Example 2 The following example shows how to define the MAX CHAR PTR macro define MAX CHAR PTR 90000 EXEC SQL BEGIN DECLARE SECTION char varl EXEC SQL END DECLARE SECTION ox EXEC SQL BEGIN DECLARE SECTION define MAX CHAR PTR 90000 char varl EXEC SQL END DECLARE SECTION Example 3 This example shows three different ways to define a structure type declare a structure and declare a pointer to the structure 1 Declare a structure and a pointer to the structure in the same statement 61 Host Variable Data Types 6 2 Fundamental C C Data Types struct tagi int a A A struct tagl malloc sizeof struc
92. EXEC SOL DECLARE cur emp CURSOR FOR SELECT e firstname e lastname emp job salary FROM employees ALTIBASE HDB EXEC SQL DECLARE cur_emp CURSOR FOR SELECT e firstname e lastname emp job salary FROM employees B 0 0 6 Cursor Open and Fetch The methods used to open cursors and fetch records are the same in ALTIBASE HDB and Oracle 213 Porting Pro C Applications to APRE Using Embedded SQL Statements However the error code types and values differ between the two products which means that error handling code written inside the FETCH statement will need to be changed The support for the use of the WHENEVER statement to handle runtime errors e g EXEC SQL WHENEVER NOT FOUND DO BREAK is the same in ALTIBASE HDB as it is in Oracle Oracle EXEC SQL OPEN cur_emp if sqlca sqlcode SQL OK fprintf stderr OPEN CSR ERROR d n sqlca sqlcode close db exit 0 for EXEC SQL FETCH cur_emp INTO emp name job title salary switch sqlca sqlcode case 0 printf emp name s n emp name continue case 1403 Not Found Data break default fprintf stderr FETCH CSR ERROR d sqlca sqlcode close db exit 0 ALTIBASE HDB EXEC SQL OPEN cur emp if sqlca sqlcode SQL SUCCESS fprintf stderr OPEN CSR ERROR d n sqlca sqlcode close db exit 0 for EXEC SQL FETCH cur_emp INTO emp_name job title salary switch sqlca sqlcode cas
93. IME STRUCT VARCHAR Keywords for Embedded SQL statement ABSOLUTE ADD AFTER AGER ALL ALLOCATE ALTER AND ANY ARCHIVE ARCHIVELOG AS ASC ASENSITIVE AT AUTOCOMMIT BACKUP BATCH BEFORE BEGIN BETWEEN BLOB FILE BREAK BY CASCADE CASE CAST CLEAR RECPTRS CLOB FILE CLOSE COALESCE COLUMN COMMIT COM PILE CONNECT CONSTANT CONSTRAINT CONSTRAINTS CONTINUE CREATE CUBE CURSOR CYCLE DATABASE DEALLOCATE DECLARE DEFAULT DELETE DEQUEUE DESC DESCRIPTOR DIRECTORY DISABLE DISABLE RECPTR DISCONNECT DISTINCT DO DROP EACH ELSE ELSEIF ELSIF ENABLE ENABLEALL RECPTRS ENABLE RECPTR END ENQUEUE ESCAPE EXCEPTION EXEC EXECUTE EXISTS EXIT EXTENTSIZE FALSE FETCH FIFO FIRST FIXED FLUSH FOR FOREIGN FOUND FREE FROM FULL FUNCTION GOTO GRANT GROUP GROUPING HAVING HOLD IDENTIFIED IF IMMEDIATE IN INDEX INDICATOR INNER INSENSITIVE INSERT INTERSECT INTO IS ISOLATION JOIN KEY LAST LEFT LESS LEVEL LIFO LIKE LIMIT LOB LOCAL LOCK LOGANCHOR LOOP MAXROWS MERGE MINUS MODE MOVE MOVEMENT NEW NEXT NOARCHIVELOG NOCYCLE NOPARALLEL NOT NULL OF OFF OFFLINE OLD ON ONERR ONLINE ONLY OPEN OPTION OR ORDER OTHERS OUT OUTER PARALLEL PARTITION PARTITIONS PREPARE PRI MARY PRIOR PRIVILEGES PROCEDURE PUBLIC QUEUE RAISE READ REBUILD RECOVER REF ERENCES REFERENCING RELATIVE RELEASE RENAME REPLACE REPLICATION RESTRICT RETURN REVERSE REVOKE RIGHT ROLLBACK ROLLUP ROW ROWCOUNT ROWTYPE SAVEPOINT SCROLL SELECT SENSITIVE SEQUENCE SESSION SET SETS SOME SPLIT SQLCODE SQLERRM SOLERROR SOLLEN START STATEMENT STEP STORE SYNONYM TABLE
94. INSERT host var This is used to set the number of array elements to be processed This variable doesn t have to be declared in the host variable declaration section constant This is used to set a fixed number of array elements to be processed 141 Using Arrays in Embedded SQL Statements 10 2 Using Host Array Variables in Embedded SQL Statements The ATOMIC FOR clause can only be used with INSERT statements It cannot be used with any other DML statements The ATOMIC FOR clause can be used with INSERT statements in which data are inserted into LOB type columns but once the LOB data have been transferred they cannot be rolled back if an error occurs In such cases it will be necessary for the user to roll back the LOB data directly using a savepoint There are several other considerations to keep in mind when using the ATOMIC FOR clause which are set forth in the following table Table 10 2 Restrictions on Atomic Array Insert Array Insert Atomic Array Insert Foreign Key Operates Normally Operates Normally Unique Key Operates Normally Operates Normally Not Null Operates Normally Operates Normally Trigger Each Row Executed N times Executed N times Trigger Each Statement Executed N times Executed Once Partitioned Table Operates Normally Operates Normally Sequence Executed N times Executed N times SYSDATE Executed N times Executed Once LOB Column Op
95. L L ALTIBASE HOME lib o conn main o conn o lapre lodbccli lstdc lsocket 1dl 1nsl lgen lposix4 lkvm lkstat lthread lpthread Precompiler User s Manual 230 Frequently Asked Questions What is wrong with the makefile shown below created the makefile shown below but can t use it to compile my project include ALTIBASE HOME install altibase env mk COMPILE c bin cc DA2 0W DS2 0W DPDL NDEBUG CC OUTPUT FLAG c LD opt aCC bin aCC LFLAGS L DA2 0W DS2 0W W1l vnocompatwarnings L ALTIBASE HOME lib GOPT INCLUDES I ALTIBASE HOME include I LIBDIRS L ALTIBASE HOME lib LIBS 1xti lpthread lrt ldld SRCS OBJS SRCS cpp 0 BINS altitest apre altitest c SOBJS SESS cpp o C COMPILE c INCLUDES CC OUTPUT FLAG lt ScC APRE all BINS 9 C o oe altitest altitest o altitest c LD S LFLAGS GOPT INCLUDES o 0 altitest o lapre lodbccli LIBS clean rm BINS apre o core class D 0 0 8 Answer c sc apre As shown above change APRE in upper case to apre in lower case Why can t I link my project The precompile operation is successful but the following error occurs during the link operation cc DA2 0W o MKTDBD dbfunc o main o util o dbif o shm msg o file o dbinit o dbresult o L userl altibase altibase home lib L userl main KTSLEE lib lcom lprice 1 1 4 lodbccli lapre lxti
96. L BEGIN DECLARE SECTION int sI1 char sI2FName 33 unsigned int sI2FOpt SOLLEN sI2Ind EXEC SQL END DECLARE SECTION strcpy sI2FName aOutFileName sI2FOpt APRE FILE CREATE EXEC SOL SELECT INTO sI1 CLOB FILE sI2FName OPTION sI2FOpt INDICATOR sI2Ind FROM T LOB An example pertaining to BLOB data is in blobSample sc and similar to this example Input Host Variables When it is desired to use an INSERT statement to insert all of the data from a file into a BLOB or CLOB type column the following syntax for input host variables can be used Syntax BLOB FILE host variable OPTION file type INDICATOR lt indicator gt CLOB FILE host variable OPTION file type INDICATOR indicator Arguments host varible This is a character type variable containing the name of the file from which the data are to be read file type This is an integer variable to specify the file access mode when reading data from the file Only the following mode is available APRE FILE READ Open the file for reading If no file having the specified name exists an error will be raised indicator This is an indicator variable that is used to specify that NULL data are to be input Example Example The following example shows how to insert a new record into the T LOB table after read ing binary data from a file in APRE FILE READ mode Sample Program blobSample sc gt Precompiler User s Manual 20
97. L SELECT INTO a department FROM DEPARTMENTS WHERE DNO S dno 10 5 2 Structures Containing Arrays The following explains how to declare and use structures containing arrays as constituent elements 10 5 2 1 Advantages The use of a structure containing arrays is convenient when using an INSERT statement to insert multiple records into all of the columns in a table Similarly it is convenient to use a structure containing arrays when using a SELECT or FETCH statement to retrieve multiple records from all of the columns in a table Because indicator variables can be specified for use with structures containing arrays it is pos sible to handle NULL values Precompiler User s Manual 150 10 5 Structures and Arrays Because it is possible to specify the individual elements of structures as host variables struc tures containing arrays can be used in UPDATE statements and in the WHERE clauses of SELECT UPDATE and DELETE statements The limitation on the use of arrays of structures as host variables stating that the array of struc tures must be the only host variable in the input or output host variable list does not also apply to the use of structures containing arrays as constituent elements When a structure con taining one or more arrays is used as a host variable there is no requirement that the structure be the only input or output host variable That is structures containing arrays can be freely used
98. NNTYPE is set to 2 or 3 in the connection string in a USING clause the DSN and PORT NO options will be ignored even if they are set and an attempt will be made to connect with the local database server When two sets of connection options are specified and a connection is successfully estab lished using the first set of options the value returned in sq1ca sqlcode is SOL SUCCESS If the connection attempt using the first set of options fails but a connection is then success fully established using the second set of options the value returned in sqlca sqlcode is SQL SUCCES WITH INFO If a connection cannot be established using either set of options the value returned in sqlca sqlcode is SQL ERROR A maximum of 1024 embedded SQL statements can be executed per connection In a session in which AUTOCOMMIIT is set to OFF if an application is shut down in the state in which uncommitted transactions exist all transactions that were not committed at the time that the application is shut down will be rolled back However if the DISCONNECT statement is executed before the application is shut down all pending transactions will be committed The AT clause cannot be used in the following kinds of embedded SQL statements 17 The C C Precompiler 2 3 Programming using Embedded SQL Statements INCLUDE statement EXEC SQL INCLUDE OPTION Statement EXEC SQL OPTION WHENEVER statement EXEC SOL WHENEVER 2 3 2 2 Example The fol
99. Options filename This is a text file that contains C or C source code including embedded SQL statements The filename extension must be sc It is possible to specify more than one file in which case all of them will be preprocessed individually When specifying multiple files the asterisk wildcard character is useful Example 1 Precompile a program that was written in C The precompilation operation creates the sample1 c file apre samplel sc Example 2 Precompile multiple programs that were written in C Note the use of the asterisk wildcard character in the second example apre samplel sc sample2 sc apre sc lt apre options gt APRE C C command line options are specified here before the name of the file s to precompile For details please refer to the next section Command Line Options 2 1 4 3 Precompile Messages The screen that is displayed when APRE is executed is shown below apre samplel sc Altibase C C Precompiler Release Version 6 1 1 1 Copyright 2009 ALTIBase Corporation or its subsidiaries All rights reserved 7 The C C Precompiler 2 2 Command Line Options 2 2 Command Line Options The following command line options can be used when precompiling applications This section explains each of the command line options in detail 2 2 1 h When this option is used the precompile operation is not performed and APRE help information is displayed The following scre
100. PARE sql stmt FROM dynamic stmt dynamic emp number 10 EXEC SQL EXECUTE sql stmt USING dynamic emp number Disconnect EXEC SQL COMMIT WORK RELEASE exit 0 ALTIBASE HDB include lt stdio h gt include lt stdlib h gt EXEC SQL BEGIN DECLARE SECTION char emp name 21 char job title 21 int salary int emp number char uid 10 char pwd 10 char dynamic stmt 120 int dynamic emp number EXEC SQL END DECLARE SECTION int main void strcpy uid SYS strcpy pwd MANAGER EXEC SQL CONNECT uid IDENTIFIED BY pwd if sqlca sqlcode SQL SUCCESS fprintf stderr DataBase Connect Error d sqlca sqlcode exit 1 INSERT value setting emp number 10 strcpy emp name ALTIBASE1 strcpy job title dbal salary 10000 INSERT DML EXEC SQL INSERT INTO employees eno e lastname emp job salary VALUES emp number emp name job title salary if sqlca sqlcode SQL SUCCESS fprintf stderr DataBase Connect Error d sqlca sqlcode exit 1 Precompiler User s Manual 222 Sample Programs emp number 20 strcpy emp name ALTIBASE2 strcpy job title dba2 salary 20000 EXEC SQL INSERT INTO employees eno e lastname emp job salary VALUES emp number emp name job title salary SELECT DML emp number 10 EXEC SQL SELECT e lastname emp job salary INTO emp name job tit
101. QL AT conn name conn name DROP PROCEDURE procedure name function name 13 3 3 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable procedure name This is the name of the stored procedure to drop function name This is the name of the stored function to drop 13 3 3 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established 13 3 4 EXECUTE This statement is used to execute a stored procedure or stored function in an application with multi ple active database connections 13 3 4 1 Syntax EXEC SQL AT conn name conn name EXECUTE BEGIN stored procedure block here 189 A Applications with Multiple Database Connec 13 3 Using Stored Procedures in Multiple Connection Applications END END EXEC 13 3 4 2 Arguments conn name This is the name of the connection in the form of a string literal conn name This is the name of the connection stored in a host variable procedure name This is the name of the stored procedure to execute function name This is the name of the stored function to execute Stored procedure block here Please refer to the Stored Procedures Manual 13 3 4 3 Consideration The connection name if specified must be the name of a valid co
102. QLCA structure and are not sup ported for use in ALTIBASE HDB B 0 0 11 SQLCA Declaration Oracle Precompiler User s Manual 216 Execution Results and Status Codes EXEC SQL INCLUDE SQLCA or include lt sqlca h gt ALTIBASE HDB In APRE this structure is defined by default and does not have to be explicitly declared Oracle Status Code Description 0 Success gt 0 No rows returned lt 0 database system network or application error ALTIBASE HDB Status Code Description SQL_SUCCESS Success SOL SUCCESS WITH INFO SQL NO DATA No rows returned SOL ERROR SOL INVALID HANDLE sqlerrmc and sqlerrml are implemented identically in Oracle and ALTIBASE HDB Oracle This element indicates the number of records that were affected by an INSERT UPDATE DELETE or SELECT INTO operation This number is cumulative ALTIBASE HDB Unlike Oracle in ALTIBASE HDB the number stored in this element is not cumulative When an INSERT UPDATE or DELETE operation is performed this element indicates the number of records that were affected When a SELECT or FETCH statement is executed using an array type output host variable this element indicates the number of records that were returned 217 Porting Pro C Applications to APRE Execution Results and Status Codes SQLSTATE SQLSTATE is used to store a status code which is used to determine the kind of error or ex
103. S a_goods Example 2 The following example shows the use of a FOR clause in an UPDATE statement The number of array elements to be processed is constant 2 and thus only two elements starting with the Oth element will be processed That is the dno and emp tel columns in the records in the employees table for which the value in the eno column matches the first two values of a employee eno will be respectively updated with the Oth and 1st elements of a employee dno and a_employee emp_tel lt Sample Program arraysl sc gt EXEC SQL BEGIN DECLARE SECTION struct int eno 3 short dno 3 char emp_tel 3 15 1 a employee EXEC SOL END DECLARE SECTION EXEC SOL FOR 2 UPDATE EMPLOYEES SET DNO a employee dno EMP TEL a employee emp tel JOIN DATE SYSDATE WHERE ENO a employee eno Example 3 The following example shows the use of a FOR clause in a DELETE statement The num ber of array elements to be processed is determined by the host variable cnt in the FOR clause and thus the records in the employees table for which the value in the dno column matches the first two values of a dno will be deleted lt Sample Program arraysl sc gt EXEC SQL BEGIN DECLARE SECTION short a dno 3 EXEC SQL END DECLARE SECTION int cnt ent 2 25 Precompiler User s Manual 140 10 2 Using Host Array Variables in Embedded SQL Statements EXEC SQL FOR cnt DELETE FROM EMPLOYEES WHERE DNO a_dno CF s
104. SE statement it is nec essary to execute the DECLARE CURSOR statement again The reason for this is that when the CLOSE RELEASE statement is executed all of the information and resources related to the cur sor are deleted so it is necessary to execute the DECLARE CURSOR statement to allocate nec essary resources and prepare for the execution of the SQL statement Therefore when it is desired to reuse a cursor in most cases it is more appropriate to execute the CLOSE statement rather than the CLOSE RELEASE statement After all results have been fetched i e when the result returned by the FETCH statement is SQL NO DATA it is possible to execute either the CLOSE statement or the CLOSE RELEASE statement When planning to reuse the cursor the CLOSE statement is more appropriate whereas the CLOSE RELEASE statement is more appropriate when it is not expected that the cursor will be reused It is possible to execute the CLOSE RELEASE statement after the CLOSE statement has been executed but this is somewhat wasteful because the cursor closing oper ation is performed twice In summary the CLOSE statement is more appropriate when expecting to reuse the cursor and the CLOSE RELEASE statement is more appropriate when not expecting to reuse it In prac tice however there are few cases in which a cursor is not used more than once so the CLOSE RELEASE statement is almost never used Reusing a cursor name by repeatedly executing the CLOSE RELE
105. SQL COMMIT 7 4 2 2 Arguments None 7 4 2 3 Description This statement is used to indicate that the current transaction was successful and terminate it The changes effected by the transaction will be stored in the database permanently 7 4 2 4 Consideration Note that an error is never raised when this statement is executed even when the autocommit mode of the current session is AUTOCOMMIT Precompiler User s Manual 98 7 4 Using Other Embedded SQL Statements 7 4 2 5 Example The following example shows how to use the COMMIT statement in an embedded SQL statement EXEC SQL COMMIT 7 4 3 SAVEPOINT 7 4 3 1 Syntax EXEC SQL SAVEPOINT lt savepoint name gt 7 4 3 2 Arguments lt savepoint_name gt This is the name to be given to the savepoint 7 4 3 3 Description A savepoint is a defined point in time at which the changes made by a transaction that has not fin ished executing are temporarily stored The SAVEPOINT embedded SQL statement is used to define an explicit savepoint to which the transaction can be rolled back if necessary 7 4 3 4 Consideration Note that an error is never raised when this statement is executed even when the autocommit mode of the current session is AUTOCOMMIT 7 4 3 5 Example The following example shows how to use the SAVEPOINT statement in an embedded SQL state ment EXEC SOL SAVEPOINT sp 7 4 4 ROLLBACK 7 4 4 1 Syntax EXEC SQL ROLLBACK TO SAVEPOINT lt savepoint name
106. T 84 182 CONTIUE 114 CREATE FUNCTION 170 CREATE PROCEDURE 170 188 Cursor Control SQL Statements 213 Cursor Statements 185 D Database Connections 210 default connection 211 named connection 211 Datatype Comparison Table 209 Datatypes 209 date types 67 SQL DATE SURUCT 67 SQL TIMESTAMP STRUCT 68 SQL TIME STRUCT 68 DECLARE CURSOR 121 162 DECLARE STATEMENT 160 161 Default Commit Mode 219 DELETE 95 DISCONNECT 87 183 DO BREAK 114 DO CONTINUE 115 DROP FUNCTION 172 DROP PROCEDURE 172 189 Dynamic SOL Method 1 157 Dynamic SOL Method 2 158 Dynamic SQL Method 3 160 Dynamic SQL Statement 82 156 Dynamic SQL Statements 185 215 E Embedded Functions 209 EXEC SQL 82 EXECUTE FUNCTION 173 EXECUTE IMMEDIATE 157 EXECUTE PROCEDURE 173 189 Explicit Commit 220 extended APRE data types 65 F FAQ 227 FETCH 122 163 FOR Clause 138 FREE 101 Function Argument Declaration Section 44 G GOTO 115 H Host Array Variables 135 DELETE 136 INSERT 135 SELECT 137 UPDATE 135 Host Variable Compatibility 212 Host Variable Declaration Section 40 Host Variables 24 212 I INCLUDE 101 INCLUDE OPTION 104 Indicator Variables 29 Input Host Variables 77 INSERT 91 L LOBs BLOB_FILE 205 206 CLOB_FILE 205 using files in INSERT statements 206 Index using files in SELECT statements 205 M Meaning of Indicator Variables 36 Multiple Connections 180 connection name 180 Multithreaded Application 196 N NO
107. T FOUND 114 numeric types 59 integer 59 real 59 O ONERR Clause 142 OPEN 122 162 OPTION 104 Output Host Variables 78 P pointer types 60 char 60 Precompiling 7 PREPARE 161 preprocessor ALTIBASE_APRE macro 54 directives 47 example 52 limitations 50 R Reusing a Cursor Name 126 ROLLBACK 99 S SAVEPOINT 99 Scope of Host Variables 40 SELECT 89 SOLCA 216 sqica 109 sglcode 109 sqlerrd 144 sqlerrd 2 component in 110 144 sqlerrd 3 component in 110 145 sqlerrm sqlerrmc 109 sqlerrm sqlerrml 110 SQLCODE 111 218 SQL_ERROR 85 108 109 SQLERROR 114 SQL_NO_DATA 90 108 109 SQLSTATE 113 218 Index 236 07006 113 07009 113 08001 113 22002 113 8S01 113 HY000 113 HY001 113 HY009 113 HY010 113 HY090 113 SQL_SUCCESS 85 108 109 SQL SUCCESS WITH INFO 85 108 109 Static SOL Statement 82 156 STOP 115 structure pointers 60 structure types struct 62 Structures and Arrays 148 structures containing arrays 150 T THREADS OPTION 104 typedef 42 U UPDATE 93 V VARCHAR 65 W WHENEVER Statement 114
108. Types 6 3 Extended APRE Data Types Success insert with APRE NIBBLE sel nibble 1 sel nibble ind 6 sel nibble 0 10 Precompiler User s Manual 76 6 4 Column and Host Variable Type Conversion 6 4 Column and Host Variable Type Conversion Various host variable types can be used with each database column type The following tables set forth the type conversions that are possible between host variable types and column types The tables also indicate which type conversions are the least expensive from the aspect of consump tion of system resources Naturally the host variable types that entail the minimum resource con sumption when converted are the recommended types when working with the corresponding column type 6 4 1 Input Host Variables This table sets forth the input host variable types that can be converted into each database column type Host variable types that can be Host variable types that Column Type converted into the column incur the minimum type conversion expense CHAR char varchar char varchar short int long long long double float SQL_DATE_STRUCT SQL_TIME_STRUCT SQL_TIMESTAMP_STRUCT Character APRE_BINARY type VARCHAR char varchar char varchar short int long long long double float SQL_DATE_STRUCT SQL_TIME_STRUCT SQL_TIMESTAMP_STRUCT APRE_BINARY SMALLINT char varchar short short int long long long double float INTEGER char varchar int Inte
109. a sqlcode if sqlca sqlcode SQL SUCCESS sqlca sqlerrd 2 holds the rows processed inserted count printf d rows inserted n n sqlca sqlerrd 2 else printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc disconnect EXEC SQL DISCONNECT check sqlca sqlcode if sqlca sqlcode SQL SUCCESS printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc 53 C Preprocessor 5 5 The ALTIBASE_APRE Macro 5 5 The ALTIBASE APRE Macro The APRE C C preprocessor contains a predefined macro called ALTIBASE APRE which is used when determining whether to precompile certain portions of source code The ALTIBASE APRE macro is particularly useful when preprocessing source code that contains large and unnecessary header files that are not necessary for the precompile operation The following example illustrates the use of the ALTIBASE APRE macro to avoid precompiling the header h file 5 5 1 Example ifndef ALTIBAS E APRE include lt header h gt endif In the above example the header h file will not be read while the APRE C C preprocessor precom piles the source code because the ALTIBASE_APRE macro is defined within APRE and thus APRE evaluates the i ndef condition as false However the ALTIBASE_APRE macro is not defined within a separate C or C compiler and thus the compiler will evaluate the i fndef condition as true and include the header h file in the compile operation
110. ables or global variables Please check whether you have declared all of your host variables properly Why will my APRE application compile in Linux but not in Solaris can compile my ALTIBASE HDB application in Linux but can t compile it in Solaris What is the problem When I execute make clean the make operation encounters a fatal error at this point reader user rttech sjyu altibase home install src makefiles wrapper m acros GNU line 113 Unexpected end of line seen ifeq exceptions 0 override exceptions endif exceptions OS Solaris 2 7 Interface APRE C C Precompiler Product altibase SPARC SOLARIS 2 7 32bit compat5 2 6 3 release tar gz D 0 0 2 Answer The reason an error occurred when make clean was executed is because you did not use GNU Make GNU Make is bundled with most Linux distributions but in Solaris it needs to be downloaded and installed manually After you have installed GNU Make you should be able to compile your applica tion in Solaris GNU MAKE for Solaris can be downloaded from www sunfreeware com 227 FAQ Frequently Asked Questions Can I get a sample showing the use of a general makefile I am assuming that the makefile in the sample directory that is created when ALTIBASE HDB is installed isa GNU makefile I m not sure how to go about using other makefiles Can get a sample showing the use of a general makefile D 0 0 3 Answer The following sample makefile would be used
111. ables with the EXECUTE Statement Example EXEC SQL BEGIN DECLARE SECTION int varl int var2 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL EXECUTE BEGIN PROC1 varl in var2 in var3 in not acceptable END END EXEC Due to the last two limitations above array type host variables cannot be used in stored function execution statements 12 2 3 Example The following example shows the use of array type host variables as IN parameters in a stored proce dure execution statement lt Sample Program arrays2 sc gt EXEC SQL BEGIN DECLARE SECTION char a_gno 3 10 1 char a_gname 3 2041 EXEC SQL END DECLARE SECTION strcpy a gno 0 G111100001 strcpy a gno 1 G111100002 strcpy a gno 2 G111100003 strcpy a gname 0 AG 100 strcpy a gname 1 AG 200 strcpy a gname 2 AG 300 EXEC SQL EXECUTE BEGIN GOODS PROC a gno in a gname in END END EXEC Precompiler User s Manual 176 12 3 Sample Programs 12 3 Sample Programs 12 3 1 psm1 sc This sample program can be found at ALTIBASE_HOME sample APRE psm1 sc 12 3 1 1 Result of Execution is f schema schema sql make psmi psmi SQL PSM 1 Execute Procedure Success drop procedure 12 3 2 psm2 sc This sample program can be found at SALTIBASE HOME sample APRE psm2 sc 12 3 2 1 Result of Execution is f schema schema sql make psm2 psm2 SQL PSM 2 Success drop function
112. ablishing a connection Example 1 In the following example a connection is established with a database server and the connection is identified using the provided string literal Thus CONN1 is the name of the estab lished connection lt Sample Program mcl sc gt EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 EXEC SQL END DECLARE SECTION set username strcpy usr SYS set password strcpy pwd MANAGER connect to ALTIBASE server with CONN1 EXEC SQL AT CONN1 CONNECT usr IDENTIFIED BY pwd Example 2 In this example a connection is established with a database server and the connection is identified using the value of the host variable Thus CONN2 is the name of the established con nection Sample Program mc2 sc gt char usr 10 char pwd 10 char conn name2 10 set username strcpy usr ALTITEST set password strcpy pwd ALTITEST set connname strcpy conn name2 CONN2 connect to ALTIBASE server with conn_name2 EXEC SQL AT conn_name2 CONNECT usr IDENTIFIED BY pwd 13 2 2 DISCONNECT This statement is used to terminate the database server connection identified by the specified con nection name 13 2 2 1 Syntax EXEC SQL AT lt conn_name conn name gt DISCONNECT 183 Applications with Multiple Database Connec 13 2 SQL Statements for Multi Connections 13 2 2 2 Arguments lt conn_name gt This i
113. age Reference are positive decimal and hexadecimal numbers Therefore when refer ring to the Error Message Reference use the absolute value of the error code returned by SQLCODE or convert this absolute value into a hexadecimal number Precompiler User s Manual 112 8 4 SQLSTATE 8 4 SQLSTATE SQLSTATE is used to store a status code This status code can be used to determine the error that occurred or the warning that was raised Checking the value of SQLSTATE is useful when the result of execution of an embedded SQL statement is SQL_ERROR or SQL_SUCCESS_WITH_INFO 8 4 1 Definition of Data Structure char SQLSTATE 6 8 4 2 Status Codes 00000 This code is returned upon successful execution of an embedded SQL statement 01004 This code is returned when the size of a character type output host variable is the same as or smaller than the corresponding column size At this time the returned data are truncated so that they can be stored in the host variable 07006 This code is returned when the host variable type is not compatible with the corre sponding column type 07009 This code is returned when the number of the columns is greater than the number of corresponding host variables 08001 This code is returned when the database server is not running 08501 This code is returned when the connection with the database server is interrupted 21S01 This code is returned when the number of columns is not same as t
114. ample The following example shows how to declare various kinds of host variables EXEC SQL BEGIN DECLARE SECTION 1 int y z 2 char c1 50 c2 100 3 varchar v1 50 4 struct tagli int x char y 50 varchar z 50 sti 5 struct tagl st2 6 EXEC SQL END DECLARE SECTION 7 1 Indicates the beginning of the host variable declaration section 2 Declares the variables x y and zas int type host variables 3 Declares the variables c1 and c2 as char type variables that are 50 and 100 bytes long respec tively 4 Declares the variable v7 as a varchar type variable 50 bytes long 5 Defines a structure type called tag1 and declares the variable st7 which is of type tag1 6 Declares the variable st2 which is also of type tag1 7 Indicates the end of the host variable declaration section 41 Host Variable Declaration Section 4 2 Data Type Definition 4 2 Data Type Definition In addition to the data types that are supported for use in embedded SQL statements it is also possi ble to use host variables based on user defined types in embedded SQL statements Such user defined types are defined using the typedef statement 4 2 1 Description Definitions of data types intended for use as host variable data types must be indicated in a way such that the preprocessor can recognize them The data type definition i e the typedef state ment can only be located in the host variable dec
115. anges effected by each of the successful operations corresponding to individual array elements are permanently stored in the database even if the operations corresponding to some of the other array elements fail For example if an INSERT statement is executed using a host variable of which the array size is 3 and the insertion operations corresponding to the first two array elements are successful but the insertion operation corresponding to the last array element fails then two records will be permanently inserted into the table Precompiler User s Manual 146 10 4 Limitations on the Use of Array Type Host Variables 10 4 Limitations on the Use of Array Type Host Vari ables There are several factors that limit the use of arrays as host variables Please keep the following in mind when writing applications 10 4 1 In the DECLARE section Arrays of pointers cannot be declared Example EXEC SQL BEGIN DECLARE SECTION char vari 10 not allowed EXEC SQL END DECLARE SECTION Only single dimensional arrays can be used as host variables The exception is that two dimensional arrays are allowed for the char and varchar types Example of Acceptable Usage EXEC SQL BEGIN DECLARE SECTION char var1 10 10 int var2 10 EXEC SQL END DECLARE SECTION Example of Unacceptable Usage EXEC SQL BEGIN DECLARE SECTION char var3 10 10 10 int var4 10 10 EXEC SQL END DECLARE SECTION 10 4 2 In SQL Statements
116. ar dname 3041 char dep location 9 41 int mgr no department typedef struct dept ind int dno int dname int dep location int mgr no dept ind EXEC SOL END DECLARE SECTION Sample Program cursor1 sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompile EXEC SOL INCLUDE hostvar h 123 Using Cursors 9 2 Cursor Related SQL Statements EXEC SQL BEGIN DECLARE SECTION declare host variables department s department structure indicator variables dept ind s dept ind EXEC SQL END DECLARE SECTION while 1 EXEC SQL FETCH DEPT CUR INTO s department s dept ind if sqlca sqlcode SQL SUCCESS printf sd s s d n S department dno s department dname S department dep location S department mgr no else if sqlca sqlcode SQL NO DATA break else printf Error d s n SQLCODE sqlca sqlerrm sqlerrmc break 9 2 4 CLOSE This statement is used to close a cursor 9 2 4 1 Syntax EXEC SQL CLOSE lt cursor name gt 9 2 4 2 Arguments lt cursor name gt This is the name of the cursor to close 9 2 4 3 Description If the CLOSE statement is executed when there are still data left to return on the database server i e when not all of the records have been fetched then the unfetched results will be discarded In other words once the CLOSE statement has been executed the FETCH
117. aracter set to use during the session KO16KSC5601 Korean US7ASCIl English MS949 BIG5 GB231280 UTF8 SHIFTJIS Precompiler User s Manual 84 7 2 Database Connection Statements EUCJP BATCH This is used to specify the batch processing mode for the session ON Batch Processing Mode OFF Non Batch Processing Mode conn opt2 This is used to specify the same database server connection options that are specified using conn_opt1 If an attempt to connect with a database server using the options specified in conn opt1 fails another connection attempt will automatically be made using the options speci fied in conn opt2 7 2 1 3 Description One application is permitted to establish one or more connections to a database using embedded SQL statements When multiple connections to a database are established within a single applica tion there can be only one connection that does not have a connection name This chapter only describes the connection that does not have a connection name For more information about establishing multiple connections within a single application please refer to Chapter13 Applications with Multiple Database Connections For more information about multi threaded applications please refer to Chapter14 Multithreaded Applications Note If PORT NO and NLS USE are not specified in the connection string then the environment variables shown below mus
118. atements 11 1 2 1 Concept The text of dynamic SQL statements is composed in an area of memory i e a character type vari able set aside for that purpose at run time Therefore the SOL statement does not need to be hard coded in the application source code 11 1 2 2 Advantages The SQL statement to be executed does not need to be predetermined within the program in advance That is it becomes possible to use dynamic SQL statements The tables and columns to be accessed can be determined dynamically at run time 11 1 2 3 Disadvantage Because the SQL statement to be executed as well as the names of the tables and columns to be accessed are determined only at run time dynamic SOL statements may be less effective than static SOL statements from the aspect of performance Precompiler User s Manual 156 11 2 Using Dynamic SQL Statements 11 2 Using Dynamic SQL Statements Broadly speaking in ALTIBASE HDB there are three approaches to the implementation of dynamic SQL statements in applications Each of these approaches can be thought of as a method of execut ing a series of SQL statements in a prescribed order The three methods are explained below 11 2 1 Method 1 This method consists of only one statement the EXECUTE IMMEDIATE statement This method is not suggested for use with frequently executed SQL statements as it is inefficient and compromises performance This method is useful when executing DDL statements fo
119. ays in Embedded SQL Statements 6 2 3 1 char A Pointer to a character string can be used as a host variable The char type is convenient for use when using a function argument as a host variable For more information about using a function argument as a host variable please refer to Chapter4 Host Vari able Declaration Section 6 2 3 2 MAX CHAR PTR When using a pointer to a character string as a host variable the precompiler assumes that the max imum size of the string to which the host variable points is 65000 bytes which is predefined in the internally provided MAX CHAR PTR macro This is because the precompiler cannot know the actual allocated size Therefore when a smaller amount of memory than the value of the MAX CHAR PTR macro is allocated to a char type output host variable care must be taken because a character string that is longer than the allocated memory size and smaller than the value of the MAX CHAR PTR macro can be stored in the host variable In this case memory corruption will occur It may become necessary to declare a pointer to a string that is more than 65000 bytes long In such cases before declaring the char type host variable use the MAX CHAR PTR macro to redefine the maximum size of a string to which a char type host variable can point Redefine the MAX CHAR PTR macro as follows define MAX CHAR PTR 90000 After the MAX CHAR PTR macro has been redefined it becomes possible to allocate an amount of
120. bles If you need to include a header file that con tains embedded SQL statements or VARCHAR declarations you must use the EXEC SQL INCLUDE statement to include the header file When EXEC SQL INCLUDE is used to include a header file the APRE C C Preprocessor includes the entire contents of the header file in the output source file i e the resultant output file with the c or cpp filename extension Therefore it is acceptable for header files included in this way to contain embedded SQL statements and VARCHAR declarations In contrast when the include directive is used to include a header file APRE only processes the header file s macro commands and C variable declarations 51 C Preprocessor 5 4 Example 5 4 Example The following simple example illustrates how the def ine and ifdef directives can be used to conditionally input data into a database Because a macro called ALTIBASE is defined the host vari able s_goods_altiis declared If it were not defined the host variable s_goods_ora would be declared lt Sample Program macro sc gt kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk SAMPLE MACRO 1 Using define if ifdef kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h define ALTIBASE define ALTIBASE int main declare h
121. ception that has occurred B 0 0 15 Declaring and Using SQLSTATE Oracle In Oracle SOLSTATE must be declared and the MODE ANSI precompiler option must be speci fied when precompiling char SQLSTATE 6 ALTIBASE HDB In ALTIBASE HDB it is not necessary to declare SOLSTATE B 0 0 16 SQLSTATE Status Codes The values of the SQLSTATE status codes and their meanings in Oracle differ from the ODBC stan dard Therefore it will be necessary to appropriately convert SOLSTATE status codes to the ODBC equivalents with reference to 8 4 2 Status Codes and the ODBC code table SQLCODE SQLCODE is used to store error codes after the execution of an embedded SQL statement B 0 0 17 Declaring and Using SQLCODE Oracle In Oracle SQLCODE must be declared and the MODE ANSI precompiler option must be speci fied when precompiling long SQLCODE ALTIBASE HDB In ALTIBASE HDB it is not necessary to declare SOLCODE SOLCODE data type in ALTIBASE HDB is int B 0 0 18 SQLCODE Status Code Values Oracle The SQLCODE status codes are the same as for sqlca sqlcode ALTIBASE HDB Precompiler User s Manual 218 Commit Mode Status Code Description 0 Upon successful execution of the embedded SQL statement that is when the value of sqlca sqlcode is SQL SUCCESS 1 When the embedded SQL statement is executed successfully but a warning is detected at which time the value of sqlca sqlcode is SQL SUCCESS WITH INFO 1
122. ch is described below UAR E eem SQL SUCCESS This result indicates that the value retrieved by the FETCH operation was successfully stored in the corresponding host variable and that there are still data waiting to be returned on the database server Applications are typically written such that they will continue to fetch additional records when the result of a FETCH operation is SOL SUCCESS SQL NO DATA This result indicates that nothing was retrieved by the FETCH operation and that no data are stored in the corresponding host variable The contents of the host variable are therefore meaningless i e a garbage value This result means one of two things either that all records that satisfy the given conditions have been returned from the database server or that there were originally no records that satisfied the given conditions Xe d N Evamnla gt Example This example shows the use of the previously declared and opened DEPT_CUR cursor to fetch records Each of the returned column values is stored in a corresponding element of the s_department structure The s_dept_ind structure type indicator variable can be used to check whether any of the returned column values is NULL The while loop continues to perform the FETCH operation and retrieve records that satisfy the conditions until SQL_NO_DATA is returned lt Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct department short dno ch
123. connection in the form of a string literal conn name This is the name of the connection stored in a host variable procedure name This is the name of the stored procedure to create function name This is the name of the stored function to create 13 3 1 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established 13 3 2 ALTER This statement is used to recompile a stored procedure or stored function in an application with multiple active database connections 13 3 2 1 Syntax EXEC SQL AT conn name conn name ALTER PROCEDURE procedure name function name COMPILE Precompiler User s Manual 188 13 3 Using Stored Procedures in Multiple Connection Applications 13 3 2 2 Arguments lt conn_name gt This is the name of the connection in the form of a string literal lt conn_name gt This is the name of the connection stored in a host variable procedure name This is the name of the stored procedure to recompile function name This is the name of the stored function to recompile 13 3 2 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established 13 3 3 DROP This statement is used to drop a stored procedure or stored function in an application with multiple active database connections 13 3 3 1 Syntax EXEC S
124. create and execute stored procedures and stored func tions within applications 12 1 1 CREATE This statement is used to create a stored procedure or stored function 12 1 1 1 Syntax Stored procedure EXEC SQL CREATE OR REPLACE PROCEDURE procedure name gt parameter declaration list AS IS declaration section BEGIN lt statement gt EXCEPTION exception handler END procedure name END EXEC Stored function EXEC SQL CREATE OR REPLACE FUNCTION function name parameter declaration list RETURN data type AS IS declaration section BEGIN lt statement gt EXCEPTION exception handler END function name END EXEC 12 1 1 2 Arguments procedure name This is the name of the stored procedure function name This is the name of the stored function parameter declaration list Please refer to the Stored Procedures Manual declaration section Please refer to the Stored Procedures Manual statement Please refer to the Stored Procedures Manual exception handler Please refer to the Stored Procedures Manual data type Please refer to the Stored Procedures Manual 12 1 1 3 Examples Various examples in which stored procedures and stored functions are created are shown below Precompiler User s Manual 170 12 1 Using Stored Procedures Example 1 The following example illustrates the creat
125. d and s_price_ind are set to SQL_NULL_DATA 1 to insert NULL into the GOODS LOCATION and PRICE columns respectively Sample Program indicator sc gt EXEC SQL BEGIN DECLARE SECTION declare host variables char s_gno 10 1 char s_gname 20 1 char s goods location 9 1 int s stock double s price declare indicator variables int s goods location ind Precompiler User s Manual 34 int s price ind EXEC SOL END DECLARE SECTION set host variables strcpy s gno X111100002 strcpy s gname XX 101 strcpy s goods location FD0003 S stock 5000 S price 9980 21 set indicator variables S goods location ind SQL NULL DATA S price ind SQL NULL DATA EXEC SOL INSERT INTO GOODS VALUES s gno S gname S goods location s goods location ind S stock S price s price ind 35 3 4 Classifying Indicator Variables Host Variables and Indicator Variables 3 5 Meaning of Indicator Variables 3 5 Meaning of Indicator Variables The following table describes the meanings of indicator variable values depending on the type of the host variable and on whether the indicator variable is an input indicator variable or an output indicator variable An indicator variable value of 1 always signifies a NULL host variable value The meaning of indica tor variable values other than 1 however differs depending on the type of the host variable and on whether the indicator va
126. d when entering a NULL value in a NIBBLE type column or reading a NULL value from a NIBBLE type column For more information about the use of the APRE_NIBBLE data type please refer to Chapter6 Host Variable Data Types 3 3 6 Considerations When a host variable is a structure the corresponding indicator variable must also be a struc ture The two structures must have the same number of elements Example EXEC SOL BEGIN DECLARE SECTION struct tagl int il int i2 varl struct tag2 int il ind int i2 ind varl indl struct tag3 int i1 ind int i2 ind int i3 ind varl_ind2 EXEC SOL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 I2 VALUES varl varl indl acceptable EXEC SQL INSERT INTO T1 I1 I2 VALUES varl varl ind2 unacceptable An indicator variable cannot be used with a host variable that is an array of structures Example EXEC SOL BEGIN DECLARE SECTION struct tagl int il int i2 char i3 11 var1 10 struct tag2 int i1 ind int i2 ind int i3 ind varl_ind1 10 EXEC SQL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 I2 I3 VALUES varl varl indl unacceptable When dealing with a VARCHAR type host variable if an indicator variable is specified for use with the host variable it will be used as the indicator variable whereas if no indicator variable is specified the 1en variable which is an element of the VARCHAR type will automatically be used as the indicato
127. database columns The definitions of the binary types that are supported in APRE are as follows typedef char typedef char typedef char typedef char typedef char APRE CLOB APRE BLOB APRE BINARY APRE BYTES APRE NIBBLE Each of these types is described in detail below 6 3 3 1 APRE CLOB This type can be used only with CLOB type database columns It is essential that an indicator variable be declared and used with this type When using the APRE CLOB type as an input host variable set the value of the corresponding indi cator variable to 1 to indicate that the value of the host variable is NULL When the value of the host variable is any other value i e a non NULL value set the value of the indicator variable to the length of the data saved in the host variable When using this type as an output host variable a value of 1 in the corresponding indicator variable indicates that a NULL value was returned to the host variable whereas an indicator variable value greater than 0 indicates that a non NULL value was returned to the host variable and furthermore indicates the length of the data saved in the host variable Finally if the data returned to the host variable was truncated SOL NO TOTAL 4 is returned in the indicator variable 6 3 3 2 Example The following example demonstrates the use of the APRE CLOB type 69 Host Variable Data Types 6 3 Extended APRE Data Types In this example ins clob is an input host
128. de o ete ee t be dre e DR NE dan 200 T5 Error Format irte rette hs etie pee te ehe Peeters reris 200 15 1 2 Error Code ro niri sms 200 15 1 3 Error Codes Messages List siscsssscvisesessscvaisssstsvnoasscesaassessounisscesiaieossonsoaiesscoieasacssaicendesenseastcandsasontbassinccobabieedionssitsn 201 AppendixA Using Files and LOBs 205 Output Host Variables casita ena E TCR D eria rea RONA TENES 205 Scy ce TEE 205 VAROUD ET AB EEEIEE E EAE E toaceadcesaneassatens spaisnbssadcdediaseatssnscsdscbaanunsooastisdeddbdestiapeatece 205 Example RE EE EA ORA AE TARR oes 205 Input Host Variables e er ere e re GALS Lc eS 206 SV iEn n DESERTUM NRA NBI VEM NEREA PARVIS NV RUE eee De un n onehut 206 AGUM NTS E 206 Example ctc E RE TE EANNA OAE NERA 206 AppendixB Porting Pro C Applications to APRE sessssessssossesossessososoesossossesossosossossosossossosossosoesossossesessesossesossosoe 209 BE LE VV POS arr EKE is AREE as SeS M ATIRE 209 Datatype Comparison Table 28 saae araber ab dt een ar re sk ES 209 Embedd d Mas Teile H A 209 Bit FUNCTIONS Compared rrt rt XO HEN Teo ERNA EN EREA bee dU 210 vii Managing Database Connections sssssccsssssssssescssssscssnseccsssecssnsscesssecsesssccssnecsssusecssnseecsusecsssseeccsnsesesusecssaseeesnsecesssceccssceceaneeessnees 210 Connecting to Database cR
129. ded SQL Statements ssssccsssssscssseccsnesssensecesssescssneceesseeccsueeessneeesnseesnseessnees 24 ER MZ Ce RR E E m 25 3 2 Classifying Host Variables ea eb tlbi deleted ORE ii 26 3 2 Output Host Variables aset eia a o toa Hed ado TRA 26 3 22 Input Host Variables ct RTENE N E ebd 26 3 3Indicator Variables irr et reni etre ee Pe Lehre et Hb eet nettes 29 3 3 Definitionen aaea a E dio D M EDU M A IRTE 29 3 3 2 Why use indicator variables usnici n a EA N EAR i 29 3 3 3 Declaring Indicator Variables tet ee b ERI NHX Ree REPRE dete 30 BBA EDGE 30 3 3 5 When is it necessary to use indicator variables ee eeesteeenttteenntttentttnnncttnntttttnncts 30 3 3 6 Considerations ee s eer ee ee ee e niet ee A tee ede 31 EAS Cel EET vou acsesebstelusacascesadassuushvesencsscesebeesscassteassscvviaseasectstevesee 32 3 4 Classifying Indicator Variables sesssssssssssssecssssssccssecssssssccsseecssusecssnscsssnsecessscesssuccessuccssnseecssusesesuscessnseeessucesssuecssneceesueessneessts 33 ZA T Output Indicator Varlables rtr ette deles EE 33 3 4 2 Input Indicator Variables ccssssssssssecssscssessssccsssssssecssscsssscsssessnsccssssessecssscessccsnscesuccssscssssecsnecessssssscssssessuessasessneesssess 34 3 5 Meaning of Indicator Variables s ssssssscsssatcsaseasssncsscosiscosasssesassedsoncsssesedsccnbiscosissnsavescbsiseossaiescdnbiaconiaensavsscbanseo
130. does not exist File tmp sc Line 4 Offset 1 31 Error token EXEC SOL BEGIN DECLARE SECTION 15 1 2 Error Code Format ERR xxxY xxx This is the error number Y This is a single alphabetic character that indicates the error category see below 15 1 2 1 Error Numbers The error numbers fall within certain ranges that indicate the kind of error that occurred These ranges are as follows 101 199 These are system errors Precompiler User s Manual 200 15 1 Precompiler Errors 201 299 These are errors related to host variables 301 399 These are cursor related errors 401 499 These are general errors 701 799 These are errors related to the lack of support for some functionality in the current ver sion These error categories indicate the precompiling task that was underway when the error occurred E This indicates that the error occurred while processing the host variable declaration section L This indicates that the error occurred while processing an embedded SQL statement M This indicates that the error occurred while performing macro substitutions H This indicates that the error occurred when performing a task other than those listed above Error Code Error Message 101H File lt file name gt open error 102H FileSize file name is zero 103H The include file file name gt does not exist in the folder 104H File lt file name gt d
131. e the local variable i e the vari able having the narrower scope will take precedence over the global variable i e the variable hav ing the broader scope The precompiler is capable of handling 50 levels of overlapping variables 4 1 2 1 Example The following example shows how the preprocessor handles multiple variables that have the same name but are declared such that they have different scopes The locally declared variable name indi cated by 2 takes priority over the global variable name indicated by 1 within the myfunc func tion Therefore when reference is made to a variable called name within the function at 3 this is Precompiler User s Manual 40 4 1 Host Variable Declaration Section handled as a reference to the local variable EXEC SQL BEGIN DECLARE SECTION char name 20 1 EXEC SQL END DECLARE SECTION int myfunc void EXEC SQL BEGIN DECLARE SECTION char name 20 2 EXEC SQL END DECLARE SECTION EXEC SQL INSERT INTO T1 VALUES name 3 4 1 3 Limitations The host variable declaration section is limited in two ways that developers must keep in mind when authoring software The names of host variables must begin with an alphabet letter a z A Z or the underscore character and must not exceed 50 bytes in length Indicator variables must also be declared in the host variable declaration section and are also sub ject to the above limitations 4 1 4 Ex
132. e Program connectl sc gt EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 char conn opt3 100 EXEC SQL END DECLARE SECTION strcpy usr SYS strcpy pwd MANAGER strcpy conn opt3 DSN 192 168 11 12 CONNTYPE 1 PORT NO 53000 EXEC SQL CONNECT usr IDENTIFIED BY pwd USING conn opt3 Example 3 This example shows how to connect to the database server by specifying two different sets of connection options in the USING clause In this case an attempt will be made to connect to the database server using the user name and the user password stored in the usr and pwd host vari ables and the connection information stored in the conn opt host variable If this attempt fails another attempt will be made to connect to the database server using the same user name and user password but this time with the connection information stored in the conn opt2 host variable Sample Program connect2 sc gt EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 char conn opt1 100 char conn opt2 100 Precompiler User s Manual 86 7 2 Database Connection Statements EXEC SQL END DECLARE SECTION strcpy usr SYS strcpy pwd MANAGER strcpy conn_opt1 DSN 192 168 11 12 CONNTYPE 1 PORT NO 53000 strcpy conn opt2 DSN 192 168 11 22 CONNTYPE 1 PORT NO 53000 EXEC SQL CONNECT usr IDENTIFIED BY pwd USING conn optl conn opt2 if sqlca sqlcode SQL SUCCESS check sqlca sqlcode prin
133. e SQL SUCCESS printf emp name s n emp name continue case SQL NO DATA Not Found Data break default fprintf stderr FETCH CSR ERROR d sqlca sqlcode close db exit 0 B 0 0 7 Closing Cursors The use of the CLOSE statement to close a cursor is the same in both Oracle Pro C and APRE Oracle Precompiler User s Manual 214 Using Embedded SQL Statements EXEC SQL CLOSE cur_emp ALTIBASE HDB EXEC SQL CLOSE cur_emp Dynamic SQL Statements ALTIBASE HDB supports Oracle Dynamic SQL Methods 1 2 and 3 but not Method 4 In Oracle both the syntax v 1 n and the question mark can be used as parameter markers within embed ded SQL statements ALTIBASE HDB only supports the use of the question mark B 0 0 8 Method 1 Oracle char dynstmt1 80 strcpy dynstmt1 DROP TABLE EMPLOYEES EXEC SQL EXECUTE IMMEDIATE dynstmt1 ALTIBASE HDB EXEC SQL BEGIN DECLARE SECTION char dynstmt1 80 EXEC SQL END DECLARE SECTION strcpy dynstmt1 DROP TABLE EMPLOYEES EXEC SQL EXECUTE IMMEDIATE dynstmt1 B 0 0 9 Method 2 Oracle int emp number char delete stmt 120 strcpy delete stmt DELETE FROM EMPLOYEES WHERE ENO v1 EXEC SQL PREPARE sql stmt FROM delete stmt emp number 10 EXEC SQL EXECUTE sql stmt USING emp number ALTIBASE HDB EXEC SQL BEGIN DECLARE SECTION int emp number char delete stmt 120 EXEC SQL END DECLARE SE
134. e created as a result of the APRE precompiling operation When this option is set to c the filename extension will be c whereas when this option is set to cpp the filename extension will be cpp If neither extension is specified the filename extension will be c Precompiler User s Manual 8 2 2 Command Line Options 2 2 2 1 Example Use the t option to precompile a program written in C After APRE has executed the command a file named sample1 cpp will be created apre t cpp samplel sc 2 2 3 o output path This is used to specify the location of the file s created by APRE If this option is omitted the resul tant file s will be created in the current directory Only one path can be specified That is when pre compiling and creating multiple files the resultant files must all be created in the same directory 2 2 3 1 Example Use the o option to specify the location of the file generated by APRE The resultant file sample1 c will be created in the src directory apre o src samplel sc 2 2 4 mt If the file to be precompiled is a multi threaded program this option must be specified If the pro gram to be compiled consists of more than one file this option must be used when precompiling all of the files for the application so care must be taken when not precompiling all of the files for an application at the same time This option has the same function as the following embedded SQL
135. e cursor 9 2 5 3 Description The CLOSE RELEASE statement releases the resources allocated to the cursor and deletes the results of the SQL statement preparation tasks that were conducted when the DECLARE CURSOR statement was executed If there are still data left to return on the database server when the CLOSE RELEASE statement is executed then the unfetched results will be discarded If it is desired to use the same cursor name after the CLOSE RELEASE statement has been executed it will be necessary to execute the DECLARE CURSOR statement followed by the OPEN statement In other words once the CLOSE RELEASE statement has been executed the OPEN statement cannot be executed using that cursor name 9 2 5 4 Example In the following example the CLOSE RELEASE statement is executed on the EMP CUR cursor At this time the SOL statement preparations that were made when the DECLARE EMP CUR CURSOR state ment was executed will be discarded and the resources allocated to the EMP CUR cursor will be released Sample Program cursor2 sc gt EXEC SQL CLOSE RELEASE EMP CUR 125 Using Cursors 9 3 Reusing a Cursor Name 9 3 Reusing a Cursor Name This section explains how to use the same cursor name repeatedly It sets forth the order in which to perform tasks and highlights some important considerations to keep in mind when reusing a cursor name 9 3 1 Relationships between Cursor Related Statements The following describes the order in
136. e in embedded SQL state ments These are described in detail later in this chapter 6 3 Extended APRE Data Types User defined data types declared in the host variable declaration section This chapter ends with a discussion of the issues related to performing type conversion between host variable types and the data types that are used in an Altibase database Precompiler User s Manual 58 6 2 Fundamental C C Data Types 6 2 Fundamental C C Data Types Most of the fundamental data types supported for use as C and C data types can also be used for host variables The fundamental C C types that can be used for host variables are set forth below 6 2 1 Numeric Types The following numeric types can be used as host variable data types 6 2 1 1 Integer Types int short int long int short long long long unsigned int unsigned short int unsigned long int unsigned short unsigned long unsigned long long 6 2 1 2 Real Number Types float double 6 2 1 3 Unavailable Numeric Type The long double type is not supported for use as a host variable data type 6 2 2 Character Types The following character types can be used as host variable data types 6 2 2 1 Character Type char unsigned char 6 2 2 2 Precautions An output host variable corresponding to a CHAR type database column must be declared so that its length is one 1 byte longer than the length of the database column The reason for this is that the leng
137. e of host variable 1 2 1 6 3 3 10 Example The following example illustrates the use of the APRE_NIBBLE type In this example ins_nibble is an input host variable Because the value to be input is not NULL the length of the actual data stored in ins_nibble is set in the first byte of ins_nibble Meanwhile se nibble is an output host variable and se nibble ind is its output indicator variable After the execution of the SELECT statement a se nibble ind value of 1 means that se nibble is NULL whereas a sel nibble ind value greater than 0 indicates the total length in bytes of the value stored in se nibble Additionally the first byte in se nibble i e sel nibble 0 contains the total length in nibbles of the actual data which are stored starting from the second byte of sel nibble i e sel nibble 1 lt Sample Program binary sc gt EXEC SOL BEGIN DECLARE SECTION APRE NIBBLE ins nibble 5 2 APRE NIBBLE sel nibble 5 2 int sel nibble ind EXEC SOL END DECLARE SECTION memset ins nibble 1 0x21 5 ins nibble 0 10 set length of ins nibble value in ins nibble 0 EXEC SOL INSERT INTO T NIBBLE VALUES ins nibble EXEC SOL SELECT INTO sel nibble sel nibble ind FROM T NIBBLE 6 3 4 Sample Program 6 3 4 1 varchar sc This sample program can be found at SALTIBASE HOME sample APRE varchar sc 6 3 4 2 Result of Execution is f schema schema sql make varchar varchar
138. e tere recette esi earned 128 QA T CUPS ONT cicer E FEE SE FISSE ERE ERUB BEVIS 128 942 Result OP EXECUTION ai enaar ea EN EA TEOSA aret eA ASRINE ASERNE ANITE AtS 128 QA I C UE SOT2 S C ncs NARRA A EA ANER baths 128 9 4 4 Resultof EXeCUtiOTy aar eri A NAN EAA T E eel eon eie tee aS 128 10 Using Arrays in Embedded SQL Statements eee ee e eee eene esee eee en etes tn stessa sess tas tns etas ense enses sens ees sno 131 TOT OVENIEW P 132 10 1 1 Definition and Declarations n Renere ebbe nes 132 TOTZ Advantages eee retener e AER e eee UR eet ere eee t ere 133 10 1 3 CONNTYPE and Host Array Variables ssscsssssscssscsssssscsssesccsseccssseessnsecesssccssssseccssecsesusecssneeeesueeessueessneesesaee 133 10 2 Using Host Array Variables in Embedded SQL Statements cssssssssssecsssssecsssssccssecsessseccssesessusecssneeesnseccsueecssnseessaee 135 10 23 INSER pe M 135 Ova uorum 135 10 2 3 DELETE M 136 1024 Edlldniep e aE 137 OPI oDSeLN cc 138 10 256 ATOMIC FOR C la se tite t ERRARE RUE E E E REEN 141 10 2 7 ONERR Gla use aret e S eb deese ict e b ence ite Le ced e un ebd edel 142 10 3 SdIca Salertals o coeno ema esie Ae MU sr qus 144 10 3 1d sqlca sdlerrd 2 n eer er e e e eee pn ret eese hee eta 144 10 3 2 sqlca salerrd 3
139. e that if the default encoding method is used the character set specified in the ALTIBASE NLS USE property might not be able to express all of the Unicode values stored in the database and thus data loss may occur when querying data 2 2 14 1 Example apre nchar utfl6 t cpp sample sc 2 2 15 nchar var variable name list When this option is used APRE processes the specified variables using the national character set of ALTIBASE HDB Blanks between variable names are not allowed Additionally variables within struc tures cannot be specified 13 The C C Precompiler 2 2 Command Line Options 2 2 15 1 Examples Specify that the variables var and var2 in sample1 sc are to be handled as national character type data apre nchar var varl var2 samplel sc Precompiler User s Manual 14 2 3 Programming using Embedded SQL Statements 2 3 Programming using Embedded SQL Statements In this section a brief explanation of the general flow of applications containing embedded SQL statements is provided as well as a description of how to approach writing such applications Generally the order in which an application is authored should mirror the general flow of execution of the application which is as follows Declaring Host Variables Connecting to a Database Server Executing Embedded SQL Statements Handling Runtime Errors Disconnecting from the Database Server 2 3 1 Declaring Host Variables When writing a prog
140. ed SQL statements 137 Using Arrays in Embedded SQL Statements 10 2 Using Host Array Variables in Embedded SQL Statements Example EXEC SQL BEGIN DECLARE SECTION int varl int var2 10 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL SELECT INTO varl FROM T1 WHERE il var3 unacceptable ox EXEC SQL SELECT INTO var2 FROM T1 WHERE il var3 unacceptable If the number of records that are returned is greater than the size of the array an error indicating that too many rows were returned will be raised The following example shows the use of an array type host variable as an output host variable in a SELECT statement Note that the input host variable is not an array Sample Program arrays2 sc EXEC SQL BEGIN DECLARE SECTION short s dno short a dno 5 char a dname 5 3041 char a dep location 5 9 41 EXEC SQL END DECLARE SECTION s dno 3000 EXEC SOL SELECT DNO DNAME DEP LOCATION INTO a dno a dname a dep location FROM DEPARTMENTS WHERE DNO s dno Sometimes it is desired to process only some of the array elements in an embedded SQL statement that uses an array type input host variable For example when using an array type input host vari able to fetch data and then insert the fetched data back into the database using the same host vari able the number of fetched data items may be smaller than the size of the array In such cases the number of
141. ed to commit SELECT statements in Pro C applications ALTIBASE HDB SELECT statements need to be committed Use EXEC SOL COMMIT to commit them Sample Programs The following sample source code contains examples of the points described above Oracle include lt stdio h gt include lt stdlib h gt EXEC SQL include sqlca h EXEC SQL BEGIN DECLARE SECTION char emp_name 21 char job title 21 int salary int emp number EXEC SQL END DECLARE SECTION char uid 10 char pwd 10 SCOTT TIGER int main void int dynamic emp number char dynamic stmt 120 EXEC SQL CONNECT uid IDENTIFIED BY pwd if sqlca sqlcode 0 fprintf stderr DataBase Connect Error d sqlca sqlcode exit 1 INSERT value setting Precompiler User s Manual 220 Sample Programs emp number 10 strcpy emp name oraclel strcpy job title oracle dbal salary 10000 INSERT DML EXEC SQL INSERT INTO employees eno e lastname emp job salary VALUES emp number emp name job title salary if sqlca sqlcode 0 fprintf stderr DataBase Connect Error d sqlca sqlcode exit 1 emp number 20 strcpy emp name oracle2 strcpy job title oracle dba2 salary 10000 EXEC SQL INSERT INTO employees eno e lastname emp job salary VALUES emp number emp name job title salary if sqlca sqlcode 0 fprintf stderr In
142. edded SQL statement it was necessary to either copy the values of function parameters into local host variables or to declare global host variables to store the values to be used in embedded SQL statements This inconve nience has now been eliminated making development more convenient and improving perfor mance For detailed information please refer to Chapter4 Host Variable Declaration Section and Chapter6 Host Variable Data Types 1 1 2 3 Changes to Data Type Names The names of the SES CLOB SES BLOB SES BINARY SES BYTES and SES NIBBLE data types have been changed to APRE CLOB APRE BLOB APRE BINARY APRE BYTES and APRE NIBBLE respec tively Of course backward compatibility has been assured meaning that it s also safe to use the old names 3 New Features in APRE C C 1 1 New Precompiler Features in ALTIBASE HDB 5 3 3 1 1 2 4 New Names for the Executable and Library Files The name of the precompiler executable file has been changed from sesc to apre and the name of the libsesc a file has been changed to libapre a However there is no need to modify existing make file code because copies of the apre executable and the libapre a library named sesc and libsesc a respectively have been provided in the package Precompiler User s Manual 4 2 The C C Precompiler 5 The C C Precompiler 2 1 Introduction and Concepts 2 1 Introduction and Concepts 2 1 1 Introduction APRE the ALTIBASE HDB C C Precompile
143. elements to be inserted can be set using a FOR clause The FOR clause plays the role of determining the number of array elements to process when using an array type input host variable When using a FOR clause it takes priority over the size of the array type host variable in determining the number of array elements that are processed For example when the size of a host variable array is 10 and the number of array elements to be processed as specified by a FOR clause is 5 only five of the elements in the array type host variable namely the Oth to 4th elements will be processed The use of the FOR clause is particularly convenient in situations where the number of array ele ments to process changes every time the embedded SQL statement is executed FOR clauses can be used with the following kinds of embedded SQL statements Precompiler User s Manual 138 EXEC SQL FOR host var constant INSERT UPDATE DELETE 10 2 Using Host Array Variables in Embedded SQL Statements INSERT UPDATE DELETE 10 2 5 1 Syntax 10 2 5 2 Arguments host var This is used to set the number of array elements to be processed host var does not need to be declared in the host variable declaration section constant This is used to set a fixed number of array elements to be processed 10 2 5 3 Considerations The value specified in the FOR clause must be at least one Example EXEC SQL BEGIN DECLARE SECTION int cn
144. elete apre t cpp delete sc APRE C C Precompiler Ver 6 1 1 1 Copyright 2000 ALTIBASE Corporation or its subsidiaries All rights reserved g D GNU SOURCE W Wall pipe D POSIX PTHREAD SEMANTICS D POSIX THREADS D POSIX THREAD SAFE FUNCTIONS D REENTRANT DPDL HAS AIO CALLS g DDEBUG fno implicit templates fno exceptions fcheck new DPDL NO INLINE DPDL LACKS PDL TOKEN DPDL LACKS PDL OTHER c I home trunk work altidev4 altibase home include I o delete o delete cpp g L g L home trunk work altidev4 altibase home lib o delete delete o lapre lodbccli lalticore 1d1l lpthread lcrypt lrt C 0 0 2 Execution file name Example2 This example shows how to execute delete the executable file created using the delete sc sample application and how to check the result delete DELETE 7 rows deleted Table Information of the Example Programs Please refer to Appendix A in the SQL Reference Precompiler User s Manual 226 Frequently Asked Questions Appendix D FAQ Frequently Asked Questions Why were incorrect data inserted into my database table The application created using APRE generates data and inserts the data into a database table How ever have noticed that my application inserts unexpected values Why is this D 0 0 1 Answer One of the things to keep in mind when using APRE to develop an application is whether it is appro priate to declare host variables as local vari
145. elete error 105H Memory allocation error 106H Latch initialize error lt file name gt lt line gt 107H Latch read error lt file name gt lt line gt 108H Latch release error lt file name gt lt line gt 109H Latch write error lt file name gt lt line gt 110H Latch destroy error lt file name gt lt line gt 111H File close error 112H File lt file name gt write error 201 Error Codes and Messages 15 1 Precompiler Errors Error Code Error Message 201E C type comment is not closed 202E The structure name lt name gt is unknown 203E The structure name lt name gt is a duplicate 204E The symbol name lt name gt cannot be added to the symbol table 205E The symbol name exceeds the maximum length lt name gt 206E Redefinition of lt name gt 207E Unknown type lt name gt 208E Invalid scope depth lt depth gt 209E Inconsistent brace count 210E Inconsistent parenthesis count error 211E The nested structure exceeds the maximum possible depth 212E VARCHAR declarations are not permitted in include files Error Code Error Message 301L The C include file can t contain embedded SQL statements 302L EXEC SQL END DECLARE SECTION does not exist 303L EXEC SQL BEGIN DECLARE SECTION does not exist 304L EXEC SQL END ARGUMENT SECTION does not exist 305L EXEC SQL BEGIN ARGUMENT SECTION doe
146. en will be shown APRE Altibase Precompiler C C Precompiler HELP Screen Usage apre options lt filename gt h Display this help information t lt c cpp gt Specify the file extension for the output file c File extension is c default cpp File extension is cpp o output path Specify the directory path for the output file default current directory mt When precompiling a multithreaded application this option must be specified I include path Specify the directory paths for files included using APRE C C default current directory parse lt none partial full gt Control which non SQL code is parsed D lt define name gt Use to define a preprocessor symbol v Output the version of APRE n Specify when CHAR variables are not null padded unsafe_null Specify to suppress errors when NULL values are fetched and indicator variables are not used align Specify when using alignment in AIX spill lt values gt Specify the register allocation spill area size keyword Display all reserved keywords debug lt macro symbol gt Use for debugging macro Display macro table symbol Display symbol table nchar_var lt variable_name_list gt Process the specified variables using the Altibase national character set nchar utf16 Set client nchar encoding to UTF 16 2 2 2 t c cpp This is used to choose the filename extension of the fil
147. er s Manual 78 6 4 Column and Host Variable Type Conversion Host variable types into which Host variable types that Column Type the column type can be incur the minimum converted conversion expense SMALLINT char varchar short short int long long long double float APRE_BINARY INTEGER char varchar int short int long long long Integer Type double eg es APRE_BINARY BIGINT char varchar long long long short int long long long double float APRE_BINARY NUMERIC char varchar char NUMBER short int long long long long long long DECIMAL double float float double APRE_BINARY FLOAT char varchar float short int long long long double float Real Number APRE_BINARY Type REAL char varchar double short int long long long double float APRE_BINARY DOUBLE char varchar double short int long long long double float APRE_BINARY DATE char char SQL_DATE_STRUCT SQL_DATE_STRUCT Data Type SQL_TIME_STRUCT SQL_TIME_STRUCT SQL_TIMESTAMP_STRUCT SQL_TIMESTAMP_STRUCT APRE_BINARY CLOB APRE_CLOB APRE_CLOB BLOB APRE_BLOB APRE_BLOB Binary Type BINARY APRE_BINARY APRE_BINARY BYTE APRE_BYTES APRE_BINARY APRE_BYTES NIBBLE APRE_NIBBLE APRE_BINARY APRE_NIBBLE The APRE_BINARY type can be used as an output host variable for all column types The APRE_BINARY type does not involve any type conversion because assigning a value to this type
148. er Types e ee ere RR e etd ERE eet 59 VASE POINTER bdo 60 6 24 Structure Types sait eec ope e reta a e E dee RUP re ERR be re ere ipeo 62 6 3 Extended APRE Data Types ice RR UR HR a e EE eee P RESI 65 6 3 1 VARGCHAR 4 este Ree e tS ra PED est o ene eee er ve ARP bete tete nere des 65 6 352 Date TYPES reii M 67 633 BIMALY B SER A A EEEN EERE EAEE DS 69 6 344 Sar ple Progtam termes ETEA AEN E ARRENE ESEE E AREAL 73 6 4 Column and Host Variable Type COnVersion sssscssssscsssessccssecssnssscsssessesssecssnsseccssecessseeccnsecessueecssuscessucesssuseessueesesusecssaeeesens 77 6 4 1 Inp t Host Variablesissccsscsesccsscsesscoasssndassessscussavnscossessaseotbssasascesseceusecnese ass adsdeassiadssdesdbcuticsassean ebeotonsslesdebsnievascennioiaste 77 642 Output Host Variables s ee RR RES ERE EUREN HEN reote 78 7 Embedded SQL Statement 81 ye JI E 82 PANE dT 82 7 1 2 Static Versus Dynamic SQL Statements sesssesccssscssescssecsseecssescsscecscsesssecssccssecesscessecesusscsseccusessseeessesenseesueetee 82 7 2 Database Connection Staterments ssssesscscssessssscsssesssecsssessensensssesecsnsessseesnssesecssnesecsesssesearsaneseseesnsssesseaneseeseesnseesscenseseseeensess 84 7 2 1 CONNECT m 84 42 32 DISCONNEG T tette tette dte A EEA bte stp aE exte ie enter abe
149. er and how to use it and gives a detailed description of the procedure for writing applications that contain embedded SQL statements Chapter3 Host Variables and Indicator Variables Precompiler User s Manual ii About This Manual This chapter describes both host variables and indicator variables and explains how to inter pret the meaning of indicator variables Chapter4 Host Variable Declaration Section This chapter explains both the host variable declaration section and the function argument declaration section Chapter5 C Preprocessor Chapter6 Host Variable Data Types This chapter describes the data types that are used for host variables Chapter7 Embedded SQL Statements This chapter explains how to use embedded SQL statements including those for managing database connections and executing DDL and DML statements Chapter8 Handling Runtime Errors This chapter explains how to use the standard variables for handling runtime errors Chapter9 Using Cursors This chapter explains the statements used to manage cursors Chapter10 Using Arrays in Embedded SQL Statements This chapter covers how to use array type host variables and discusses arrays of structures and the limitations on their use Chapter11 Dynamic SQL Statements This chapter explains dynamic SQL statements Chapter12 Using Stored Procedures in C C This chapter describes how to use stored functions and stored procedures Chapter13 Applications wi
150. erates Normally Atomicity cannot be guaran teed when an error occurs Procedure Operates Normally Operates Normally When a INSERT statement contains a subquery A new view for the subquery is created N times The view corresponding to the first execution is reused EXEC SQL ATOMIC FOR 20 INSERT INTO T1 VALUES data Using this clause with an embedded SQL statement that uses an array type input host variable enables detection of whether execution corresponding to each ofthe array elements was successful This makes it possible to manage the list of elements for which execution failed by defining some management tasks that use DML statements EXEC SQL ONERR ret code Precompiler User s Manual 142 err code INSERT UPDATE DELETE 10 2 Using Host Array Variables in Embedded SQL Statements or EXEC SQL ONERR lt ret_code err_code gt FOR lt cnt constant gt INSERT UPDATE DELETE 10 2 7 2 Arguments lt ret_code err_code gt The result of execution of the SQL statement is saved in the first host variable ret_code This variable must be declared as a pointer to an array of short The error code is saved in the second host variable err_code This variable must be declared as a pointer to an array of int lt cnt gt This value is used to specify the number of array elements to be processed This variable doesn t have to be declared in the host variable declaration section
151. es and Indicator Variables 3 3 Indicator Variables 3 3 3 Declaring Indicator Variables Indicator variables are declared as follows Indicator variables are declared in the host variable declaration section or the function argu ment declaration section If an attempt is made to use an indicator variable in an embedded SQL statement and the indicator variable was not previously declared in the host variable declaration section or the function argument declaration section an error saying The host variable variable name is unknown will be raised during the precompile operation For more information about the host variable declaration section and the function argument declaration section please refer to Chapter4 Host Variable Declaration Section The syntax for declaring indicator variables is as follows datatype indicator variable name The data type of an indicator variable must be int or SQLLEN a predefined type in ODBC It can also be a data structure as long as it comprises only the int and SOLLEN types The names of indicator variables must start with an alphabetic character a z A Z or the underscore character and must not be longer than 50 bytes 3 3 4 Syntax The syntax for using indicator variables within embedded SQL statements is as follows host variable INDICATOR indicator variable The keyword INDICATOR can be omitted If the host variable is not a structure the indicator va
152. es s eno and s dnoina DELETE statement The values of the host variables are used in the WHERE clause to determine which records are to be deleted Sample Program delete sc gt EXEC SOL BEGIN DECLARE SECTION int s eno short s dno EXEC SQL END DECLARE SECTION S eno 5 s dno 1000 EXEC SOL DELETE FROM EMPLOYEES WHERE ENO gt s eno AND DNO gt s dno AND EMP JOB LIKE P 27 Host Variables and Indicator Variables 3 2 Classifying Host Variables Example 4 The following example shows the use of the input host variable s dno in a SELECT state ment The value of s dno is used in the WHERE clause to determine which records to retrieve Sample Program select sc gt EXEC SQL BEGIN DECLARE SECTION short s dno char s dname 3041 char s dep location 9 1 EXEC SQL END DECLARE SECTION s dno 1001 EXEC SOL SELECT DNAME DEP LOCATION INTO s dname s dep location FROM DEPARTMENTS WHERE DNO s dno Example 5 IThe following example shows the use of the input host variable s call in the select list of a SELECT statement Sample Program host target sc EXEC SQL BEGIN DECLARE SECTION double s call EXEC SQL END DECLARE SECTION S call 0 045 EXEC SQL SELECT principal sum 1 CAST s call AS DOUBLE FROM count Example 6 The following example shows the use of the input host variable s period in the GROUP BY clause of a SELECT statement Sample Program host group
153. esponding indicator variable indicates that a NULL value was returned to the host variable whereas an indicator variable value greater than 0 indicates that a non NULL value was returned to the host variable and furthermore indicates the length of the data saved in the host variable The following example demonstrates the use of the APRE BLOB type In this example ins blob is an input host variable and ins blob ind is the corresponding input indi cator variable The value of ins blob indis set to the length of the value stored in ins blob Meanwhile se blob is an output host variable and se blob ind is its output indicator variable After the execution of the SELECT statement a sel blob ind value of 1 means that sel blob is NULL whereas a sel blob ind value greater than 0 indicates the length of the value stored in sel blob Sample Program binary sc gt EXEC SQL BEGIN DECLARE SECTION APRE BLOB ins blob 10 1 Precompiler User s Manual 70 6 3 Extended APRE Data Types APRE BLOB sel_blob 10 1 SOLLEN ins blob ind SOLLEN sel blob ind EXEC SQL END DECLARE SECTION memset ins blob 0x21 10 ins blob ind 10 set length of ins blob value in indicator variable EXEC SOL INSERT INTO T BLOB VALUES ins blob ins blob ind EXEC SOL SELECT INTO sel blob sel blob ind FROM T BLOB 6 3 3 5 APRE BINARY This type is identical to the APRE BLOB type For more information please refer to 6 3 3 3 APR
154. etcaseszcasscasssssghonaiesasabecesdaccauasnsansecbdanicntiadstoiosadevacasasaesbastcabiaestoadebladevidessdieds 109 8 2 3 PrecatlOns 5 edat e ERR RERO SM AREE SE aden ANE C SERERE PRAE AD ANN CIERRE MEER AENEAN eee e deg 110 83 SOL CODE PET 111 8 31 Data Structure Definition ssizsissssiediscacceccusasasssaasosccrscouasoviannieadensssssnseasoseciaasvonibasondessuauetisaaeasacscisuniasnoaesduddsasouasseatseesdane 111 8 32 90ECODE Return Val es coco c ei ee sacs sist te p en roseo oe lan aen e tuere tee aes 111 83 3 Error Codes cdd ei E Ree Red ARRA DURER CON RR N UE 111 8 34 Preca tlon ret nee direi e ie cese ev Mese eed ee re pe Pe na 112 BZESOESTATE tre RIS EON PRI ORE AEE Ode LI EARS NEAR AUREAS EN 113 84 3 Definition Of Data Structure er e ect eir c Pen aee toe i e tu estes 113 8 4 2 Status GOR SS esc reen never en ner ERE CREDERE dene due 113 8 5 WHENEVER St terment ettet triti ittis tese RNAAR GERNE eher e Poder basiert insere Pee ER 114 Bi551 SYMCAK C M HE 114 8 5 2 Conditlons c d ede b eap ete ett era be be pte datae doe eode d dread 114 8 5 3 ACION S eT M 114 SERM DOSCII PEIN ROT RN 115 8 6 Sample Progratnis eitis FEDERER SEHE e ELLE EVARA Sa ERE NRE tda aasia EEK GE 117 8 631 r ntime errorchecksc deett Oe tratte oe ee E e E 117 8 6 2 R sult of Ex CUtiOn Re Re e eto EE GSS 117 8 0 3 Whenever 1 8 C so eoo tec en ee neca ec adr e eno
155. examples illustrate the use of input host variables in various ways Example 1 The following example shows the use of the input host variables s gno s gname s goods location s stock and s price in an INSERT statement The values stored in the input host variables are inserted into respective table columns Sample Program insert sc gt EXEC SQL BEGIN DECLARE SECTION char s_gno 10 1 char s_gname 20 1 char s goods location 9 1 int s stock double s price EXEC SQL END DECLARE SECTION strcpy s gno F111100002 strcpy s gname XX 101 strcpy s goods location FD0003 S stock 5000 S price 9980 21 EXEC SQL INSERT INTO GOODS VALUES s gno s gname S_ goods location S Stock s price Example 2 The following example shows the use of the input host variables s dno s emp job and s enoin an UPDATE statement The values in the DNO and EMP JOB columns of the records that sat isfy the condition in the WHERE clause are updated with the values of s dno and s emp job respec tively Sample Program update sc EXEC SQL BEGIN DECLARE SECTION int s eno short s dno varchar s emp job 1541 EXEC SQL END DECLARE SECTION S eno 2 s dno 1001 strcpy s emp job arr ENGINEER s emp job len strlen s emp job arr EXEC SQL UPDATE EMPLOYEES SET DNO s dno EMP JOB s emp job WHERE ENO s eno Example 3 The following example shows the use of the input host variabl
156. f the records have been fetched then the unfetched results will be discarded How ever the resources allocated to the cursor are not freed at this time Therefore after the CLOSE state ment is executed the OPEN statement can be executed immediately without first executing the DECLARE CURSOR statement Example The following example shows the use of the CLOSE statement Sample Program dynamic3 sc EXEC SOL CLOSE CUR Syntax EXEC SQL CLOSE RELEASE cursor name Arguments cursor name This is the name of the cursor Description The CLOSE RELEASE statement can be executed after the DECLARE CURSOR statement the OPEN statement the FETCH statement or the CLOSE statement If an attempt is made to reference a cursor that has not been defined in the CLOSE RELEASE statement an error indicating that the cursor does not exist will be raised If there are still data left to return on the database server when the CLOSE RELEASE statement is exe cuted then the unfetched results will be discarded Additionally all resources allocated to the cursor will be freed Therefore after the CLOSE RELEASE statement has been executed it will be necessary to execute the DECLARE CURSOR and OPEN statements in sequence In other words the OPEN state ment cannot be executed after the CLOSE RELEASE statement 165 Dynamic SQL Statements 11 2 Using Dynamic SQL Statements If it is desired to reuse a cursor execute the CLOSE statement
157. f updated or deleted records will be stored in sqlca sqlerrd 2 Because there can be more than one record that meets a condition specified using each element of an array type host variable this value may be higher than the array size For example assume that you have performed an UPDATE operation using an array type host vari able having three elements and that the operation was successful for each of the three array ele ments If there were three records that satisfied the condition when using the Oth element two records that satisfied the condition when using the 1st element and no records that satisfied the condition when using the 2nd element a total of five records would be updated so the value stored Precompiler User s Manual 144 10 3 sqica sqlerrd in sqlca sqlerrd 2 would be 5 If the output host variable is not an array type host variable this value will be meaningless i e a gar bage value If the output host variable is an array type host variable the number of records that have been selected or fetched at the present moment in time will be stored in sqica sqlerrd 2 Note that this value is not the cumulative number of records fetched using multiple FETCH statements It is only the number of records that have been fetched by the current statement Therefore a value larger than the size of the array will never be observed If the number of records that were returned is the same as or smaller than the array size t
158. file to be precompiled is a multi threaded program lt Sample Program mtl sc gt EXEC SQL OPTION THREADS TRUE 105 Embedded SQL Statements 7 5 OPTION Statements Precompiler User s Manual 106 8 Handling Runtime Errors 107 Handling Runtime Errors 8 1 Overview 8 1 Overview Applications must be able to handle internal runtime errors APRE provides the programmer with numerous methods for handling runtime errors including variables such as SQLCODE and SQLSTATE and the WHENEVER statement 8 1 1 Return Values After an embedded SQL statement is executed the result of execution is stored in sqlca sql code This variable can have the following values SOL SUCCESS This value indicates that the embedded SQL statement was executed successfully SOL SUCCESS WITH INFO This value indicates that the embedded SQL statement was executed but that a warning was detected SOL NO DATA This value indicates that no records were returned by an executed SELECT or FETCH statement SOL ERROR This value indicates that an error occurred during execution of the embedded SQL statement Precompiler User s Manual 108 8 2 The sqica Structure 8 2 The sqlca Structure sqlca is an instance of the u1pSq1ca structure that is declared during the precompile operation ulpSqlca isan internal structure that is used to store the results of execution of an embedded SQL statement and is defined in the ulpLibInterface h file Developers
159. from or inserting data into multiple columns in a table Instead it is possible to use a single host variable which makes the development process much more convenient For example a structure type host variable can be used in the VALUES clause of an INSERT statement or in the INTO clause of a SELECT statement Even arrays of structures and structures containing arrays are valid data types for use as host vari ables For more information about the use of arrays please refer to Chapter10 Using Arrays in Embedded SQL Statements 6 2 4 2 Limitations When a host variable is a structure the corresponding indicator variable must also be a struc ture and must have the same number of elements as the host variable Example EXEC SQL BEGIN DECLARE SECTION struct tagl int il int i2 varl struct tag2 int il ind int i2 ind varl indl struct tag3 int i1 ind int i2 ind int i3 ind varl_ind2 EXEC SOL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 I2 VALUES varl varl indl acceptable EXEC SQL INSERT INTO T1 I1 I2 VALUES varl varl ind2 unacceptable Nested structures cannot be used as host variables In other words a structure cannot have another structure as one of its constituent elements Example EXEC SQL BEGIN DECLARE SECTION struct tagi int i1 struct tag2 int i2 int i3 sub_var vari EXEC SQL END DECLARE SECTION unacceptable It is impossible to use an indicator va
160. ger type short int long long long double float BIGINT char varchar long long long short int long long long double float 77 Host Variable Data Types 6 4 Column and Host Variable Type Conversion Host variable types that can be Host variable types that Column Type converted into the column incur the minimum type conversion expense NUMERIC char varchar char NUMBER short int long long long long long long DECIMAL double float float double FLOAT char varchar float short int long long long Real number double float type REAL char varchar double short int long long long double float DOUBLE char varchar double short int long long long double float DATE char char BETYDE SQL DATE STRUCT SQL DATE STRUCT yp SQL TIME STRUCT SQL TIME STRUCT SQL TIMESTAMP STRUCT SQL TIMESTAMP STRUCT CLOB APRE CLOB APRE CLOB BLOB APRE BLOB APRE BLOB Binary Type BINARY APRE BINARY APRE BINARY BYTE APRE BYTES APRE BYTES NIBBLE APRE NIBFBLE APRE NIBBLE This table sets forth the output host variable types into which each database column type can be converted Host variable types into which Host variable types that Column Type the column type can be incur the minimum converted conversion expense Character CHAR char varchar APRE_BINARY char varchar Type VARCHAR char varchar APRE_BINARY char varchar Precompiler Us
161. guments ENABLE DISABLE COMPILE V Preface About This Manual Convention Meaning Example Indicates that the previous argu ment is repeated or that sample code has been omitted iSQL gt select e lastname from employees E LASTNAME Moon Davenport Kobain 20 rows selected Other symbols Symbols other than those shown above are part of the actual code EXEC pl 1 acc NUMBER 11 2 Italics Statement elements in italics indi cate variables and special values specified by the user SELECT FROM table name CONNECT userID password Lower Case Let ters Indicate program elements set by the user such as table names col umn names file names etc SELECT e lastname FROM employees Upper Case Let ters Keywords and all elements pro vided by the system appear in upper case DESC SYSTEM SYS INDICES Related Reading For additional technical information please refer to the following manuals ALTIBASE HDB Getting Started ALTIBASE HDB Administrator s Manual ALTIBASE HDB ODBC Reference ALTIBASE HDB SQL Reference ALTIBASE HDB Application Program Interface User s Manual ALTIBASE HDB iSQL User s Manual ALTIBASE HDB Error Message Reference Online Manuals Online versions of our manuals PDF or HTML are available from the Altibase Customer Support http support altibase com Altibase Welcomes Your C
162. he number of cor responding input host variables for an INSERT statement 22002 This code is returned when NULL data are returned and no indicator variable is being used to detect NULL data HY000 This code indicates a general error HY001 This code is returned when a memory allocation error occurs HY009 This code is returned when the host variable and the indicator variable are both NULL pointers HY010 This code is returned when an attempt is made to fetch records using a cursor that has not been opened HY090 This code is returned when the value of the indicator variable is invalid 113 Handling Runtime Errors 8 5 WHENEVER Statement 8 5 WHENEVER Statement APRE provides the WHENEVER statement for use in handling runtime errors 8 5 1 Syntax EXEC SQL WHENEVER condition action 8 5 1 1 Arguments condition This is the result of execution of an embedded SQL statement action This is the action to take in response to the result of execution of the embedded SQL state ment 8 5 2 Conditions The following conditions can be set in a WHENEVER statement 8 5 2 1 SQLERROR This condition is used to detect the occurrence of an error meaning a sqlica sqlcode value of SQL ERROR during the execution of an embedded SQL statement 8 5 2 2 NOT FOUND This condition is used to detect the state in which no records are returned in response to the execu tion of a SELECT or FETCH statement at which
163. hen the value stored in sqlca sqicode will be SOL SUCCESS and the number of records that were returned will be stored in sqlca sqlerrd 2 If no records were returned the value stored in sqlca sqlcode will be SOL NO DATA and zero 0 will be stored in sqlca sqlerrd 2 For example assume that you have performed a SELECT operation using an array type output host variable having ten 10 elements If there were five records that met the conditions those five records would be stored in the output host variable in sequence starting with the Oth element At this time the value of sqlca sqlcode would be SQL SUCCESS and 5 would be stored in sqlca sql errd 2 After the execution an embedded SQL statement using an array type input host variable this vari able stores the number of array elements for which execution was successful regardless of whether the statement is an INSERT UPDATE or DELETE statement Therefore a value larger than the size of the array will never be observed If the value of sqlca sqlcode is SOL SUCCESS the value of the sglca sglerrd 3 variable will be equal to the size of the array All of the following conditions must be met in order for this variable to contain a meaningful value This value must be checked only after the execution of an embedded SQL statement using an array type input host variable The statement that was just executed before checking this value must be an INSERT UPDATE or DELETE SQL state
164. i cator variable The value of ins_bytes_ind is set to the length of the value stored in ins_bytes Meanwhile se bytes is an output host variable and sel bytes ind is its output indicator variable After the execution of the SELECT statement a se bytes ind value of 1 means that sel bytes is NULL whereas a sel bytes ind value greater than 0 indicates the length of the value stored in sel bytes lt Sample Program binary sc gt EXEC SOL BEGIN DECLARE SECTION APRE BYTES ins bytes 5 1 APRE BYTES sel bytes 5 1 int ins bytes ind int sel bytes ind EXEC SOL END DECLARE SECTION memset ins bytes 0x21 5 ins bytes ind 5 set length of ins bytes value in indicator variable EXEC SOL INSERT INTO T BYTES VALUES ins bytes ins bytes ind EXEC SOL SELECT INTO sel bytes sel bytes ind FROM T BYTES The APRE NIBBLE type can be used only with NIBBLE type database columns When using the APRE NIBBLE type as an input host variable use an indicator variable to indicate that the value of the host variable is NULL but use the first byte of the host variable to indicate the length of the host variable when the value of the host value is any other value i e a non NULL value Note that the indicator variable will take precedence over the first byte of the host variable That is the value of the indicator variable is first checked and if it is found to be 1 the host variable is handled as having a NULL va
165. iables are being used For information on how to set the CONNTYPE option please refer to 7 2 1 CONNECT 133 Using Arrays in Embedded SQL Statements 10 1 Overview 10 1 3 2 Supported Connection Types As was explained fully in 7 2 1 CONNECT the supported connection types are TCP UNIX and IPC 10 1 3 3 Relationship between CONNTYPE and Host Variable Array Size Normally the IPC connection type realizes the best performance followed in descending order by the UNIX and TCP connection types However when using array type input host variables this is not necessarily the case Therefore when using array type input host variables it is suggested that you test each of the connection methods with various array sizes to determine the combination that yields the best performance in your environment Precompiler User s Manual 134 10 2 Using Host Array Variables in Embedded SQL Statements 10 2 Using Host Array Variables in Embedded SQL Statements Array type host variables can be used in embedded SQL statements in various ways 10 2 1 INSERT The array types that can be used with INSERT statements are as follows Simple arrays Arrays of structures Structures comprising arrays as individual elements thereof 10 2 1 1 Example The following example shows the use of an array type host variable as an input host variable in an INSERT statement lt Sample Program arraysl sc gt EXEC SQL BEGIN DECLARE SECTION char a gno 3
166. ided for use within APRE The developer can choose the date type that is most appropriate for the task at hand 6 3 2 1 SQL DATE STRUCT This type comprises year month and date elements Its structure is shown below typedef struct tagDATE STRUCT SQLSMALLINT year SQLSMALLINT month SQLSMALLINT day DATE STRUCT 6 3 2 2 Example The following example shows the use of the SQL_DATE_STRUCT type In this example s_date is used as both an input and output host variable lt Sample Program date sc gt EXEC SQL BEGIN DECLARE SECTION SQL DATE STRUCT s date int s ind EXEC SQL END DECLARE SECTION EXEC SQL SELECT JOIN DATE INTO s date s ind FROM EMPLOYEES WHERE ENO 3 S date year 2003 S date month 5 S date day 9 EXEC SQL UPDATE EMPLOYEES SET JOIN DATE s date WHERE ENO 3 67 Host Variable Data Types 6 3 Extended APRE Data Types 6 3 2 3 SQL_TIME_STRUCT This type comprises hour minute and second elements Its structure is shown below typedef struct tagTIME STRUCT SQLSMALLINT hour SQLSMALLINT minute SQLSMALLINT second TIME STRUCT 6 3 2 4 Example The following example shows the use of the SQL_TIME_STRUCT type In this example s_time is used as both an input and output host variable lt Sample Program date sc gt EXEC SQL BEGIN DECLARE SECTION SQL TIME STRUCT s time int s ind EXEC SOL END DECLARE SECTION EXEC SOL SELECT JOIN D
167. igint s order date in date S eno in integer s cno in char 13 S gno in char 10 s gty in integer RETURN INTEGER AS p cnt integer BEGIN INSERT INTO ORDERS ONO ORDER DATE ENO CNO GNO QTY VALUES s ono s order date s eno s cno s gno s qty SELECT COUNT INTO p cnt FROM ORDERS RETURN p ent END 171 Using Stored Procedures in C C 12 1 Using Stored Procedures END EXEC 12 1 2 ALTER This statement is used to precompile a stored procedure or stored function 12 1 2 1 Syntax Stored procedure EXEC SQL ALTER PROCEDURE lt procedure_name gt COMPILE Stored function EXEC SQL ALTER FUNCTION lt function_name gt COMPILE 12 1 2 2 Argument lt procedure_name gt This is the name of the stored procedure lt function_name gt This is the name of the stored function 12 1 2 3 Description This statement is used to recompile a currently invalid stored procedure or stored function to restore it to a valid state 12 1 2 4 Examples Two examples in which stored procedures and stored functions are recompiled are shown below Example 1 In the following example the stored procedure ORDER_PROD is recompiled EXEC SQL ALTER PROCEDURE ORDER_PROC COMPILE Example 2 In the following example the stored function ORDER_FUNC is recompiled EXEC SQL ALTER FUNCTION ORDER_FUNC COMPILE 12 1 3 DROP This statement is used to delete the stored procedure or stored function 12 1 3 1 Syntax St
168. ing Arrays in Embedded SQL Statements 10 5 Structures and Arrays char dep location 5 9 1 int mgr no 5 a department2 EXEC SQL END DECLARE SECTION s dno 2000 EXEC SQL SELECT INTO a department2 FROM DEPARTMENTS WHERE DNO s dno Precompiler User s Manual 152 10 6 Sample Programs 10 6 Sample Programs 10 6 1 arrays1 sc This sample can be found at ALTIBASE_HOME sample APRE arrays1 sc 10 6 2 Result of Execution is f schema schema sql make arrays1 arrays1 ARRAYS 1 3 rows inserted 3 times insert success 3 rows inserted 3 times insert success 3 rows inserted 3 times insert success SOLCODE 69720 Sqlca sqlerrm sqlerrmc The row already exists in a unique index 1 rows inserted 1 times insert success 3 rows updated 3 times update success 12 rows updated 3 times update success 6 rows deleted 3 times delete success 2 rows inserted 2 times insert success 2 rows updated 2 times update success 153 Using Arrays in Embedded SQL Statements 10 6 Sample Programs 3 rows deleted 2 times delete success 10 6 3 arrays2 sc This sample can be found at ALTIBASE_HOME sample APRE arrays2 sc 10 6 4 Result of Execution is f schema schema sql make arrays2 arrays2 ARRAYS 2 3001 CUSTOMER SUPPORT DEPT London 3002 PRESALES DEPT Peking 4001 MARKETING DEPT Seoul 4002 BUSINESS DEPT Palo Alto 4 rows selected 1001 RESEARCH DEVELOPMENT DEPT 1
169. ing column is NULL For example an indicator variable can be used to indicate whether the value of a host variable to be used in an INSERT statement is NULL or whether a column value returned by a SELECT statement is NULL 3 3 2 2 For Managing the Length of Data Indicator variables can also be used to specify the length of an input value or store the length of a column value returned by a SELECT statement Indicator variables can be used to manage data length only for character or binary type host vari ables To specify the length of an input value an input indicator variable would be used whereas an out put indicator variable would be used to store the length of a returned column value If a host variable is a character type variable and the value to be input or the returned column value is terminated with a null terminator 0 and is known not to be NULL there is no need to use an indicator variable When dealing with a binary type host variable it is essential to use an indicator variable even when the input value or the returned column value is known not to be NULL This is because the binary type is not terminated with a NULL character and the database needs a way of knowing the length of the input value while the application needs a way of knowing the length of the returned column For more information about the use of binary type host variables please refer to Chapter6 Host Vari able Data Types 29 Host Variabl
170. ion an error will be raised indicating that an established connection with that name already exists Only one connection that does not have a name i e the default connection is allowed The name of the connection to use must be specified within each embedded SQL statement For more information about the use of connection names please refer to Chapter13 Applica tions with Multiple Database Connections Note In previous versions of APRE i e SESC versions 3 5 5 and earlier it was necessary to use the EXEC SQL THREADS statement in all functions that contained embedded SQL statements and were intended for use in a multithreaded environment In versions subsequent to version 3 5 5 this is not necessary but there is no need to remove the EXEC SQL THREADS command from existing programs as its presence does not cause any compatibility problems Precompiler User s Manual 196 14 2 Sample Programs 14 2 Sample Programs 14 2 1 mt1 sc This sample can be found at SALTIBASE HOME sample APRE mt1 sc 14 2 1 1 Result of Execution is f schema schema sql make mt1 mt1 MULTI THREADS 1 14 2 2 mt2 sc This sample can be found at SALTIBASE HOME sample APRE mt2 sc 14 2 2 1 Result of Execution is make mt2 mt2 MULTI THREADS 2 gt 2000 11 29 2000 11 29 2000 11 29 2000 12 10 2000 12 01 2000 12 29 2000 12 29 2000 12 30 2000 12 30 2000 12 30 2000 12 30 2000 12 30 2000 12 30 2000 12 30 2000 12 3
171. ion of a stored procedure In this example the stored procedure ORDER_PROC is created This procedure searches for one record in which the value in the ONO column is the same as the value of the s_ono parameter of the stored procedure If no records are found then a new record having default values is inserted into the ORDERS table If one record is found then the values in the PROCESSING column in the record that is found are written to P lt Sample Program psml sc gt EXEC SQL CREATE OR REPLACE PROCEDURE ORDER PROC s ono in bigint AS p order date date p eno integer p cno bigint p gno char 10 p qty integer BEGIN SELECT ORDER DATE ENO CNO GNO QTY INTO p order date p eno p cno p gno p qty FROM ORDERS WHERE ONO s ono EXCEPTION WHEN NO DATA FOUND THEN p order date SYSDATE p eno 13 p ceno BIGINT 7610011000001 p gno E111100013 p qty 4580 INSERT INTO ORDERS ONO ORDER DATE ENO CNO GNO QTY VALUES s ono p order date p eno p cno p gno p qty WHEN TOO MANY ROWS THEN NULL WHEN OTHERS THEN UPDATE ORDERS SET PROCESSING P WHERE ONO s ono END END EXEC Example 2 The following example illustrates the creation of a stored function This stored function inserts a new record into the ORDERS table and then counts and returns the number of records in the ORDERS table Sample Program psm2 sc EXEC SOL CREATE OR REPLACE FUNCTION ORDER FUNC S ono in b
172. is not determined by the flow of execution of the application A WHENEVER statement is valid only within the current file The WHENEVER statement must precede any embedded SQL statements to which it is intended to apply That is all embedded SQL statements that follow a WHENEVER statement in the source file will be tested A WHENEVER statement tests the results of execution of all embedded SQL statement in the routine in which it is declared and all routines within that routine Therefore whenever the result of execu tion of any embedded SQL statement in this scope matches the condition in the WHENEVER state ment the corresponding action will be taken A WHENEVER statement that checks for the SOLERROR condition and a WHENEVER statement that checks for the NOT FOUND condition can coexist without having any effect on each other When execution control moves out of the routine in which a WHENEVER statement was declared the WHENEVER statement has no further effect From that point onward embedded SQL statements will be affected by WHENEVER statements located in the current routine or higher routines If two WHENEVER statements that listen for the same condition are present within the same routine the WHENEVER statement that appeared first will have no effect and the more recent WHENEVER statement will apply If two WHENEVER statements that check for the same condition exist but have different scopes of applicability the WHENEVER statement
173. isconnection methods between Oracle and ALTIBASE HDB Connecting to a Database The command that is used to establish a default connection is the same in Oracle and ALTIBASE HDB Oracle and ALTIBASE HDB are also similar in that multiple connections can be established if names are assigned to individual connections and in that connection options can be set using the USING clause B 0 0 1 The CONNECT Statement Oracle EXEC SOL CONNECT user IDENTIFIED BY oldpswd usr psw AT dbname host variable USING connect string ALTIBASE HDB EXEC SQL AT conn name conn name CONNECT user IDENTIFIED BY lt passwd gt Precompiler User s Manual 210 Managing Database Connections USING lt conn_opt gt lt conn_opt2 gt j B 0 0 2 Establishing the Default Connection Oracle char username SCOTT char password TIGER char connstr ORA817 EXEC SOL WHENEVER SOLERROR EXEC SQL CONNECT username IDENTIFIED BY password USING connstr ALTIBASE HDB strcpy username SYS strcpy password MANAGER strcpy connstr DSN 192 168 1 2 PORTNO 20310 CONNTYPE 1 EXEC SQL CONNECT username IDENTIFIED BY password USING connstr If the USING clause is not specified the application will attempt to connect to an Altibase data base on the same system B 0 0 3 Establishing a Named Connection Oracle char username SCOTT char password TIGER char connstr ORA
174. ith this symbol it is not a complete command Indicates that the command continues from the previous line If a syntactic element starts with this symbol it is not a com plete command Indicates the end of a statement Indicates a mandatory element SELECT Precompiler User s Manual iv About This Manual Element Description Indicates an optional element NOT A Indicates a mandatory element comprised of options One and only one option must be specified ADD DROP Indicates an optional element comprised of options ASC DESC Indicates an optional element in which multiple elements may be specified A comma must precede all but the first element ASC DESC d W7 The code examples explain SQL statements stored procedures iSQL statements and other com mand line syntax The following table describes the printing conventions used in the code examples Convention Meaning Example Indicates an optional item VARCHAR size FIXED VARIABLE Indicates a mandatory field for ENABLE DISABLE COMPILE which one or more items must be selected A delimiter between optional or ENABLE DISABLE COMPILE mandatory ar
175. laration section Newly defined data types can be used as host variables just like other data types 4 2 2 Examples Various examples of data type definitions are shown below Example 1 The following example shows the use of the typedef keyword EXEC SQL BEGIN DECLARE SECTION typedef unsigned int UINT typedef unsigned char UCHAR EXEC SQL END DECLARE SECTION Example 2 The following examples illustrate various ways to define data types and structures 1 This example shows a data type definition that follows the definition of the structure on which itis based EXEC SQL BEGIN DECLARE SECTION struct department short dno char dname 30 41 char dep location 941 int mgr no typedef struct department department EXEC SQL END DECLARE SECTION 2 This example shows how to define a structure and the corresponding data type at the same time EXEC SQL BEGIN DECLARE SECTION typedef struct department short dno char dname 30 1 char dep location 941 int mgr no department EXEC SQL END DECLARE SECTION 3 This example shows a data type definition that precedes the definition of the structure on which it is based Precompiler User s Manual 42 4 2 Data Type Definition EXEC SQL BEGIN DECLARE SECTION typedef struct department department struct department short dno char dname 30 1 char dep location 941 int mgr no EXEC SOL END DECLARE SECTION 43 Host Variable Declaration Section
176. le Salary FROM employees WHERE eno emp number if sqlca sqlcode SQL SUCCESS fprintf stderr Select Error d sqlca sqlcode exit 1 printf SELECT result ename s job s sal d n emp name job title salary UPDATE DML emp number 10 Salary 2000 EXEC SOL UPDATE employees SET salary salary WHERE eno emp number if sqlca sqlcode SQL SUCCESS fprintf stderr Update Error d sqlca sqlcode exit 1 Cursor Create EXEC SQL DECLARE cur_emp CURSOR FOR SELECT e lastname emp job salary FROM employees Cursor Open EXEC SQL OPEN cur_emp if sqlca sqlcode SQL SUCCESS fprintf stderr OPEN CSR ERROR d n sqlca sqlcode exit 1 Fetch Cursor for EXEC SQL FETCH cur_emp INTO emp_name job title salary switch sqlca sqlcode case SQL SUCCESS printf Fetch Result emp_name s job s sal d n emp name job title salary continue case SQL NO DATA Not Found Data break default fprintf stderr FETCH CSR ERROR d s n sqlca sqlcode sqlca sqlerrm sqlerrmc exit 1 break Cursor Close EXEC SQL CLOSE cur_emp 223 Porting Pro C Applications to APRE Sample Programs Dynamic SQL strcpy dynamic stmt DELETE FROM EMPLOYEES WHERE ENO EXEC SQL PREPARE sql stmt FROM dynamic stmt dynamic emp number 10 EXEC SQL EXECUT
177. lowing example shows how to connect to a database server lt Sample Program connectl sc gt declare host variables EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 EXEC SQL END DECLARE SECTION set username strcpy usr SYS set password strcpy pwd MANAGER EXEC SQL CONNECT usr IDENTIFIED BY pwd if sqlca sqlcode SQL SUCCESS check sqlca sqlcode else printf Successfully connected to ALTIBASE server n n printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc exit 1 2 3 3 Executing Embedded SQL Statements After a connection with a database server has been successfully established it is possible to execute embedded SQL statements The term embedded SQL statements encompasses DML statements such as the SELECT and INSERT statements DDL statements such as object creation statements sys tem control statements cursor related SQL statements dynamic SQL statements and all other SQL statements of ALTIBASE HDB For more information about using each of the various kinds of embed ded SQL statements please refer to Chapters 7 9 10 11 and 12 2 3 3 1 Examples What follows are examples of the use of various kinds of embedded SQL statements UPDATE Embedded SQL Example The following is an example of an UPDATE statement lt Sample Program update sc gt declare host variables EXEC SQL BEGIN DECLARE SECTION int s_eno short
178. lt cursor name gt 9 2 2 2 Arguments lt cursor name gt This is the name of the cursor to open 9 2 2 3 Description The OPEN statement executes the SQL statement that was specified using the DECLARE CURSOR statement The SELECT statement that is executed using the OPEN statement was previously prepared for exe cution on the database server when the DECLARE CURSOR statement was executed When the OPEN statement is executed the server searches the corresponding table s for records that satisfy the conditions in the WHERE clause 9 2 2 4 Example The following example shows the statement that is used to open a cursor called DEPT CUR lt Sample Program cursorl sc gt EXEC SOL OPEN DEPT CUR 9 2 3 FETCH This statement is used to read column values from an open cursor and store them in corresponding host variables 9 2 3 1 Syntax EXEC SQL FETCH cursor name INTO host var list 9 2 3 2 Arguments cursor name This is the name of the cursor host var list This is a list of output host variables and output indicator variables 9 2 3 3 Description The FETCH statement first instructs the cursor to move to the next record and then stores that record s column values in corresponding host variables Precompiler User s Manual 122 9 2 Cursor Related SQL Statements taf Evariitin of Execution The two possible results of execution of a FETCH statement are SOL SUCCESS and SQL NO DATA each of whi
179. lue If the value of the indicator variable is anything other than 1 the first byte of the host variable is taken as the length of the input data Therefore to input NULL data the indicator variable must be set to 1 whereas to input other non NULL values the length of the input data must be specified in the first byte of the host variable Because the length of the input data is stored in the first byte the actual data will be stored starting in the second byte of the host variable Therefore the length of the input data that is the nibble count will be counted from the second byte of the host variable One nibble is 4 bits When using the APRE NIBBLE type as an output host variable a value of 1 in the corresponding indicator variable indicates that a NULL value was returned to the host variable An indicator variable value greater than 0 indicates the total length in bytes of the data saved in the host variable Meanwhile as mentioned above the length of the actual output data in nibbles one nibble 4 bits is stored in the first byte of the host variable and the actual data are stored starting in the sec ond byte Therefore when non NULL data are returned the data length is indicated by both the indicator vari Precompiler User s Manual 72 6 3 Extended APRE Data Types able and the first byte of the host variable The relationship between the two data length values is as follows indicator variable first byt
180. ment or a stored procedure When using Atomic Array Insert if the Atomic Array Insert operation is completely successful this value will be 1 but if even one insertion operation fails this value will be O 2 1 Example For example if an UPDATE statement is executed using a host variable of which the array size is 3 and the update operations corresponding to the Oth and 1st array elements are successful but no records are updated by the UPDATE operation corresponding to the 2nd array element then the value returned in sqlca sqlcode will not be SOL SUCCESS and the value returned in sqlca sqlerrd 3 will be 2 If there were three records that satisfied the condition when using the Oth element and two records that satisfied the condition when using the 1st element a total of five records would be 145 Using Arrays in Embedded SQL Statements 10 3 sqica sqlerrd updated so the value stored in sglca sglerrd 2 would be 5 Unless the value of sqica sqlcode is SOL SUCCESS the value stored in the sqlca sqlerrd 2 vari able will have no meaning i e will be a garbage value Therefore check the value of the sglca sglerrd 2 variable only when the value of sqlca sqlcode is SQL SUCCESS When using an array type host variable in AUTOCOMMIT mode a transaction is not the total ity of operations performed using the entire array Rather each of the operations correspond ing to a single array element is one transaction Therefore the ch
181. ments ment must be executed whereas if the database server is not running the FREE statement must be executed If the connection method CONNTYPE is set to 2 or 3 in the connection string the DSN and PORT_NO options will be ignored even if they are set and an attempt will be made to connect with the local database server Various database server connection examples are shown below Example 1 This example shows how to connect to the database server by specifying only the user name and the user password In this case the other information that is necessary in order to estab lish a connection with the database server will be read from the environment variables lt Sample Program connectl sc gt EXEC SQL BEGIN DECLARE SECTION char usr 10 char pwd 10 EXEC SQL END DECLARE SECTION strcpy usr SYS strcpy pwd MANAGER EXEC SQL CONNECT usr IDENTIFIED BY pwd Example 2 This example shows how to connect to the database server by specifying the connec tion method in the USING clause In this case the connection to the database server will be estab lished using the user name and the user password stored in the usr and pwd host variables and the connection information stored in the conn_opt3 host variable Any information that is necessary in order to establish a connection with the database server but could not be found in the conn_opt3 host variable will be read from the environment variables lt Sampl
182. mple the INCLUDE OPTION statement is used to specify the location in which to look for hostvar h namely the include directory after which the hostvar h file is included using the INCLUDE statement lt Sample Program insert sc gt EXEC SQL OPTION INCLUDE include EXEC SQL INCLUDE hostvar h 7 5 2 THREADS APRE supports the use of embedded SQL statements in multi threaded applications The THREADS OPTION statement provides a basis on which the precompiler can determine whether or not the file to be precompiled is a multi threaded program 7 5 2 1 Syntax EXEC SQL OPTION THREADS TRUE FALSE Precompiler User s Manual 104 7 5 OPTION Statements 7 5 2 2 Arguments None 7 5 2 3 Description The THREADS OPTION statement can have either of the following two values TRUE When the file to be precompiled is a multi threaded program FALSE When the file to be precompiled is not a multi threaded program If the THREADS OPTION statement is not specified APRE assumes that the file to be precompiled is not a multi threaded program Therefore if the file to be precompiled is a multi threaded program then the THREADS OPTION must specified and must be set to TRUE If the mt option is used on the command line when precompiling a multi threaded program the THREADS OPTION statement can be omitted 7 5 2 4 Example The following example shows how to set the THREADS OPTION to TRUE using the OPTION state ment when the
183. n Unless the host variable is an array only one record must be returned If more than one record is returned the value returned in the sqlca sqlcode variable will be SOL ERROR and the value returned in the sqlca sqlerrm sqlerrmc variable will be an error message indicating that too many rows were returned When it is expected that more than one record is to be returned it is necessary to use an array or a cursor If the host variable is an array the number of returned records must be the same as or less than the size of the array If the number of returned records is greater than the array size the value returned in the sglca sqlcode variable will be SOL ERROR and the value returned in the sqlca sqlerrm sqlerrmc variable will be an error message indicating that too many rows were returned In such cases it is necessary either to increase the size of the array or use a cursor 89 Embedded SQL Statements 7 3 Using DDL and DML in Embedded SQL Statements When the host variable is not an array When the host variable is an array Number oF Result of Execution Number of returned Result of Execution returned records records 0 SQL_NO_DATA 0 SQL NO DATA 1 SQL SUCCESS Fewer than the size ofthe SOL SUCCESS array The same number as the SQL SUCCESS size of the array More than 1 SQL ERROR More than the size of the SOL ERROR array An sqica sqlcode value of SOL NO DATA means that zero 0 records were
184. n as well as any output host variables for storing the result of the stored function 173 Using Stored Procedures in C C 12 1 Using Stored Procedures 12 1 4 3 Examples Examples of the execution of stored procedures and stored functions are shown below Example 1 In the following example the stored procedure ORDER_PROC is executed lt Sample Program psm1 sc gt EXEC SQL BEGIN DECLARE SECTION long long s_ono EXEC SQL END DECLARE SECTION s ono 111111 EXEC SQL EXECUTE BEGIN ORDER PROC s ono in END END EXEC Example 2 In the following example the stored function ORDER FUNC is executed Sample Program psm2 sc EXEC SOL BEGIN DECLARE SECTION long long s ono char s order date 1941 int s eno char s_cno 13 1 char s_gno 10 1 int sS qty int s cnt EXEC SQL END DECLARE SECTION S ono 200000001 S eno 20 s qty 2300 strcpy s order date 19 May 03 strcpy s cno 7111111431202 strcpy s gno C111100001 EXEC SOL EXECUTE BEGIN S cnt ORDER FUNC s ono in s order date in S eno in sS cno in 8 gno in s qty in END END EXEC Precompiler User s Manual 174 12 2 Using Array Type Host Variables with the EXECUTE Statement 12 2 Using Array Type Host Variables with the EXECUTE Statement Array type host variables can be used with the EXECUTE statement When array type host variables are used with the EXECUTE statement executing the EXECUTE statement a single time has
185. n is repeated until SQL_NO_DATA is returned lt Sample Program dynamic3 sc gt int type declare host variables EXEC SQL BEGIN DECLARE SECTION declare output host variables department s department goods s goods orders s orders declare indicator variables dept ind s dept ind good ind s good ind order ind s order ind EXEC SOL END DECLARE SECTION while 1 use indicator variables to check for null values switch type case 1 EXEC SQL FETCH CUR INTO s department s dept ind break case 2 EXEC SQL FETCH CUR INTO s goods s good ind break case 3 EXEC SQL FETCH CUR INTO s orders s order ind break if sqlca sqlcode SQL SUCCESS cnt else if sqlca sqlcode SQL NO DATA printf d rows selected n n cnt break else printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc break Precompiler User s Manual 164 11 2 Using Dynamic SQL Statements EXEC SQL CLOSE cursor name gt Arguments cursor name This is the name of the cursor to close The CLOSE statement can be executed after the DECLARE CURSOR statement the OPEN statement or the FETCH statement If an attempt is made to close a cursor that has not been defined an error indicating that the cursor does not exist will be raised If the CLOSE statement is executed when there are still data left to return on the database server i e when not all o
186. n the above example the APRE C C preprocessor first reads my header h and remembers the definition of the value A Therefore when it subsequently processes the if condition it substi tutes 1 for A Because the if condition is thus true the declaration of the name array is included in the source file i e the output text If the i condition had evaluated to false the name array dec laration would have been excluded from the source file Precompiler User s Manual 46 5 2 C Preprocessor Directives 5 2 C Preprocessor Directives The C preprocessor directives that the APRE C C preprocessor recognizes are define undef include if ifdef ifndef else elif and endif 5 2 1 define undef def ine defines the name of a macro to be used by the APRE C C preprocessor while undef deletes a previously defined macro 5 2 1 1 Example define A define func undef A undef func In the above example the APRE C C preprocessor handles the def ine command and stores the names A and func in a symbol table and then performs the required macro substitutions when ever the names A and func subsequently appear Finally when the APRE C C preprocessor encounters the undef command it deletes the stored names from the symbol table 5 2 2 include This directive instructs the APRE C C preprocessor to read the specified external source file and incorporate any def ine macros and variables found in the exte
187. n tl linkamy project ue re tiere et edu st ire pesi a 231 Why do I need to relink my project in ALTIBASE MMDB 2 4 1 sssssssssscsssesscessecssssecccsseceesusecssnsecesnsecesseeeesseeeesaee 232 viii Preface About This Manual About This Manual This manual explains how to use the embedded SQL statement of ALTIBASE HDB and C C pre compiler The user can create an application using the embedded SQL statement of ALTIBASE HDB and precompile the created program Audience This manual has been prepared for the following ALTIBASE HDB users Database administrators Performance managers Database users Application developers Programmers Technical support workers It is recommended that those reading this manual possess the following background knowledge Basic knowledge in the use of computers operating systems and operating system utilities Experience in using relational databases and an understanding of database concepts Computer programming experience Experience in database server operating system or network administration Software Environment This manual has been prepared assuming that ALTIBASE HDB 6 will be used as the database server Organization This manual is organized as follows Chapter1 New Features in APRE C C This section describes the new features that were introduced with APRE in ALTIBASE HDB 5 3 3 Chapter2 The C C Precompiler This chapter presents an introduction to the C C precompil
188. nal arrays The exception is that two dimensional char and varchar type arrays are allowed An indicator variable cannot be used with a host variable that is an array of structures 15 The C C Precompiler 2 3 Programming using Embedded SQL Statements When an array of structures is used as an output host variable in the INTO clause of a SELECT or FETCH statement only one output host variable can be used In other words the array of struc tures cannot be used with other output host variables Therefore if the output host variable to be used in the INTO clause is an array of structures the number of elements in the structure must be the same as the number of columns in the select list When an array of structures is used as an insert host variable in the VALUES clause of an INSERT statement only one input host variable can be used In other words the array of structures cannot be used with other input host variables Therefore if the input host variable to be used in the VALUES clause is an array of structures the number of elements in the structure must be the same as the number of columns in the INSERT statement Internally the varchar type is handled as a kind of structure so it is subject to the above lim itations Array type host variables must not be used together with non array type host variables in INSERT UPDATE or DELETE statements If an array type output host variable is used when a SELECT or FETCH statement is execu
189. nnection that is a connection that has already been established Precompiler User s Manual 190 13 4 Sample Programs 13 4 Sample Programs 13 4 1 mc1 sc This sample application can be found at ALTIBASE_HOME sample APRE mc1 sc 13 4 1 1 Result of Execution is f schema schema sql make mci mc1 MULTI CONNECTION 1 Success close cursor with CONN1 13 4 2 mc2 sc This sample application can be found at SALTIBASE HOME sample APRE mc2 sc 13 4 2 1 Result of Execution is f schema schema sql make mc2 mc2 MULTI CONNECTION 2 Success dynamic sql method 1 with conn name2 Dynamic SQL Method 2 PREPARE With conn name2 Success dynamic sql method 2 prepare with conn name2 Dynamic SQL Method 3 PREPARE With conn namel Success dynamic sql method 3 prepare with conn namel Dynamic SQL Method 3 DECLARE CURSOR With conn namel Success dynamic sql method 3 declare cursor with conn namel 191 Applications with Multiple Database Connec 13 4 Sample Programs Dynamic SQL Method 3 OPEN CURSOR With conn namel Success dynamic sql method 3 open cursor with conn namel Dynamic SQL Method 3 FETCH CURSOR With conn namel gt Dynamic SQL Method 2 EXECUTE INSERT With conn name2 Dynamic SQL Method 3 CLOSE CURSOR With conn namel Success dynamic sql method 3 close cursor with conn namel 13 4 3 mc3 sc This sample application can be found at SALTIBASE HOME
190. nnection o Symbol included in usr lib libnsl so 1 of hint depend ing kstat lookup homel shkim src SK DLR v1 0 0 altibase lib libodbccli a idl o kstat read homel shkim src SK DLR v1 0 0 altibase lib libodbccli a idl o kstat open homel shkim src SK DLR v1 0 0 altibase lib libodbccli a idl o kstat data lookup homel shkim src SK DLR v1 0 0 altibase lib libodbc cli a idl o D 0 0 5 Answer The Sun compiler includes different system libraries during the linking operation depending on whether you are running Solaris 2 7 or 2 6 The system libraries may also vary depending on whether you use CC cc or gcc for the linking operation Although we cannot recommend any solution with 10096 certainty we suggest that you try linking your project using either cc or CC and test it thoroughly If the trouble persists please contact us again The only libraries that we provide are the libodbccli a and libapre a libraries The other libraries are all system libraries and can be used by linking to the system archive at user lib the exact location of this directory may vary This will help avoid collisions The symbols to which lodbccli and lapre refer are all found in the system library and thus the system library must also be linked If you want to compile using gcc we also provide versions of the libodbccli a and libapre a libraries for use when compiling using gcc Additionally when linking the following system libraries must be linked
191. nts lt cursor_name gt This is the name of the cursor lt host_var_list gt This is a list of output host variables and output indicator variables The FETCH statement can be executed after the OPEN statement If an attempt is made to performa FETCH operation using a cursor that has not been defined an error indicating that operations are being performed out of sequence will be raised This statement is used to return records that were retrieved when the cursor was opened The host variables into which the records are returned are specified in the host variable list in the INTO clause Unless a structure type output host variable is being used the number of host variables in the INTO clause must be the same as the number of columns in the SELECT clause Furthermore the type of each host variable must be compatible with the type of the database column to which it corre sponds If the same cursor identifier is used in multiple DECLARE CURSOR statements within the same appli cation then the most recently declared cursor having that identifier at run time will be used 163 Dynamic SQL Statements 11 2 Using Dynamic SQL Statements Example The following example shows the use of the cursor CUR to fetch results into different output host variables depending on the situation Because an indicator variable has been defined for each host variable it is possible to handle NULL values The use of the while loop ensures that the FETCH operatio
192. o avoid installing the ALTIBASE HDB client module which is required for compiling and linking on all of the other servers In the typical scenario in which all machines are running the same OS the program is compiled on a dedicated compiler machine or standard machine and then only the library for exe cutable files needs to be copied to the other servers When working with a variety of operating sys tems however the linker must be available on all of the machines because it refers to the system library for that OS The makefile is in the SALTIBASE HOME sample APRE directory Be sure to use the makefile with the libapre a and libodbccli a libraries provided by us when linking Additionally the altibase env mk contains all of the essential information required to compile and link APRE projects A compile error occurred while I was testing a simple piece of source code Why It seems to me that there is a C standard I O conflict between ALTIBASE HDB and g D 0 0 7 Answer The following example shows how to use gcc to compile an application apre t cpp conn sc g W Wall Wpointer arith pipe D POSIX PTHREAD SEMANTICS D REENTRANT fno implicit templates fno exceptions fcheck new IS ALTIBASE HOME include I c o conn o conn cpp g W Wall Wpointer arith pipe D POSIX PTHREAD SEMANTICS D REENTRANT fno implicit templates fno exceptions fcheck new I ALTIBASE HOME include I c o main o main cpp g
193. ocation host variables respec tively lt Sample Program select sc gt EXEC SQL BEGIN DECLARE SECTION short s dno char s dname 3041 char s dep location 9 1 EXEC SQL END DECLARE SECTION s dno 1001 EXEC SOL SELECT DNAME DEP LOCATION INTO s dname s dep location FROM DEPARTMENTS WHERE DNO s dno Example 2 The following example shows the use of a structure type host variable In this example the departments table is searched for records whose DNO column value matches the value of the s dno input host variable and the column values are stored in the corresponding elements of the s department structure Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct department short dno char dname 3041 char dep location 941 int mgr no department EXEC SQL END DECLARE SECTION lt Sample Program select sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h EXEC SOL BEGIN DECLARE SECTION short s dno department s department EXEC SQL END DECLARE SECTION s dno 1002 EXEC SOL SELECT INTO s department FROM DEPARTMENT WHERE DNO s dno For an example in which a SELECT statement is used to retrieve the contents of a BLOB or CLOB data base column which are then stored to a file please refer to Appendix A 7 3 2 INSERT This statement is used to insert new
194. omments Precompiler User s Manual vi About This Manual Please feel free to send us your comments and suggestions regarding this manual Your comments and suggestions are important to us and may be used to improve future versions of the manual When you send your feedback please make sure to include the following information The name and version of the manual that you are using Any comments that you have about the manual Your full name address and phone number For immediate assistance with technical issues please contact the Altibase Customer Support http support altibase com We always appreciate your comments and suggestions vii Preface About This Manual Precompiler User s Manual viii 1 New Features in APRE C C This section describes the new features that were introduced with version 5 3 3 of the APRE C C Precompiler 1 New Features in APRE C C 1 1 New Precompiler Features in ALTIBASE HDB 5 3 3 1 1 New Precompiler Features in ALTIBASE HDB 5 3 3 This chapter explains the new features that were added to the APRE C C Precompiler in ALTIBASE HDB 5 3 3 APRE In ALTIBASE HDB 5 3 1 and earlier the precompiler was known as the SESC C C Precompiler The name change to APRE reflects the large number of improvements and increases to functionality that were introduced with ALTIBASE HDB 5 3 3 This chapter will be of particular interest to those who are porting development projects f
195. on whether the input host variables used to declare the cursor are global or local in scope If the input host variables in the DECLARE CURSOR statement are global in scope then the OPEN statement can be executed after the CLOSE statement when reusing a cursor name If a host variable used in a DECLARE CURSOR statement is local in scope that is if the host vari able was declared inside a function then in order to use the same cursor name the DECLARE CURSOR statement must be executed after the CLOSE statement Precompiler User s Manual 126 9 3 Reusing a Cursor Name The reason for this limitation is that the values of the host variable pointers used in the DECLARE CURSOR statement are stored internally when the DECLARE CURSOR statement is executed and these stored values are used when the OPEN statement is executed Therefore if these host variables are local then if the function is exited and then called again the pointer values may be changed and thus their values may no longer be valid when the OPEN state ment is executed This means that it is necessary to execute the DECLARE CURSOR statement to save the pointer values every time the function is called Additionally the local host vari ables must be declared in the function containing the DECLARE CURSOR and OPEN state ments The difference between the CLOSE and CLOSE RELEASE statements is described below To reuse the name of a cursor that was released using the CLOSE RELEA
196. one of the above categories These statements include task control statements and DCL statements for control ling the system and individual transactions 7 1 2 8 OPTION Statements These statements are used to set various options provided by the APRE C C precompiler 83 Embedded SQL Statements 7 2 Database Connection Statements 7 2 Database Connection Statements Connection related SQL statements are those that are used to manage a connection with a database server including the CONNECT and DISCONNECT statements 7 2 1 CONNECT This statement is used to connect to the database server 7 2 1 1 Syntax EXEC SQL CONNECT lt user gt IDENTIFIED BY lt passwd gt USING conn opti lt conn_opt2 gt 1 7 2 1 2 Arguments lt user gt This is the name of the user with which to connect to the database server lt passwd gt This is the password corresponding to the user with which a connection is established to the data base server lt conn_opt1 gt This is used to specify various options related to the database server connection DSN This is the IP address of the database server with which to establish a connection CONNTYPE This is used to specify the protocol with which to communicate with the database server 1 TCP IP 2 UNIX DOMAIN 3 IPC PORT NO This is used to specify the port number via which to communicate with the data base server NLS USE This is used to set the ch
197. ored procedure EXEC SQL DROP PROCEDURE lt procedure_name gt Precompiler User s Manual 172 12 1 Using Stored Procedures Stored function EXEC SQL DROP FUNCTION function name 12 1 3 2 Argument procedure name This is the name of the stored procedure function name This is the name of the stored function 12 1 3 3 Examples Two examples in which stored procedures and stored functions are dropped are shown below Example 1 In the following example the stored procedure ORDER PROC is dropped lt Sample Program psml sc gt EXEC SOL DROP PROCEDURE ORDER PROC Example 2 In the following example the stored function ORDER FUNC is dropped Sample Program psm2 sc EXEC SQL DROP FUNCTION ORDER FUNC 12 1 4 EXECUTE This statement is used to execute a stored procedure or stored function 12 1 4 1 Syntax Stored procedure EXEC SQL EXECUTE BEGIN procedure name host var IN OUT IN OUT m 1 1 END END EXEC Stored function EXEC SOL EXECUTE BEGIN host var function name gt host var IN OUT IN OUT m 1 1 END END EXEC 12 1 4 2 Argument procedure name This is the name of the stored procedure function name This is the name of the stored function host var This is used to specify any IN OUT or IN OUT parameters i e input or output host vari ables that are necessary for the execution of the stored procedure or functio
198. ost variables char usr 10 char pwd 10 char conn opt 1024 structure type ifdef ALTIBASE goods s goods alti else goods s goods ora endif int i printf lt INSERT gt n set username strcpy usr SYS set password strepy pwd MANAGER set various options strcpy conn opt DSN 127 0 0 1 CONNTYPE 1 PORT_NO 20300 connect to altibase server EXEC SOL CONNECT usr IDENTIFIED BY pwd USING conn opt check sqlca sqlcode if sqlca sqlcode SQL SUCCESS printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc exit 1 use structure host variables ifdef ALTIBASE strcpy s goods alti gno F111100010 strcpy s goods alti gname ALTIBASE strcpy s goods alti goods location AD0010 S goods alti stock 9999 S goods alti price 99999 99 Helse strcpy s goods ora gno F111100011 Precompiler User s Manual 52 5 4 Example strcpy s goods ora gname ORACLE Sstrcpy s goods ora goods location AD0011 S goods ora stock 0001 S goods ora price 00000 01 endif conditional insertion using ifdef EXEC SQL INSERT INTO GOODS VALUES ifdef ALTIBASE S goods alti Helse S goods ora endif r3 printf In printf Structure Host Variables Mn printf In check sqlc
199. ost variables whenever they appear in embedded SQL statements 3 1 4 Example In the following example the host variables s dno s dname and s dep location are declared Sample Program select sc gt EXEC SQL BEGIN DECLARE SECTION short s dno char s dname 3041 char s dep location 9 1 EXEC SQL END DECLARE SECTION EXEC SOL SELECT DNAME DEP LOCATION INTO s dname s dep location FROM DEPARTMENTS WHERE DNO s dno 25 Host Variables and Indicator Variables 3 2 Classifying Host Variables 3 2 Classifying Host Variables Host variables are classified as either input host variables or output host variables depending on whether they are used to input data into a database server or extract data from a database server 3 2 1 Output Host Variables 3 2 1 An output host variable is used in an INTO clause of a SELECT or FETCH statement to store query results An output host variable thus plays the same role as a variable used in the ODBC SQLBindCol function 1 Example The following is an example of the use of output host variables In this example s dname and s dep location are host variables The values in the DNAME and DEP LOCATION columns for the records that satisfy the condition in the WHERE clause are stored in the host variables s dname and s dep location respectively Sample Program select sc gt EXEC SQL BEGIN DECLARE SECTION short s dno char s dname 3041 char s dep location 9 1
200. r is a programming tool that accepts source code con taining embedded SQL as input translates the embedded SQL statements into standard runtime library calls and generates a modified source program that can be compiled in the host language and executed APRE makes it easy for users to write and precompile applications that contain embedded SQL state ments By embedding SQL statements into applications users can create applications that have all of the functionality that is available when creating a program using the ODBC API and can do so much more easily 2 1 2 Precompiler Environment Settings The following environment settings are required in order to compile and link a file that is output by APRE 2 1 2 1 Required Header File The ulpLibInterface h header file is necessary It is located in the SALTIBASE HOME include direc tory That is in order to compile an application that was precompiled with APRE it will be necessary to use the following option in your C C compiler I SALTIBASE HOME include 2 1 2 2 Required Library Files The library files libapre a and libodbccli a or apre lib and odbccli lib in Windows are also necessary They are located in the SALTIBASE HOME lib directory In order to link a compiled application program with these libraries it is necessary to use all of the following options L SALTIBASE HOME lib lapre lodbccli lalticore lpthread 2 1 3 Handling SIGPIPE In the event that network
201. r it is possible to use an individual element of one structure that is itself one element in an array of structures in the WHERE clause as shown in the fol lowing example Example EXEC SQL BEGIN DECLARE SECTION int eno 3 short dno 3 char emp tel 3 15 1 struct tagl int il int i2 int i3 var1 10 EXEC SQL END DECLARE SECTION EXEC SQL DELETE FROM EMPLOYEES WHERE ENO eno and DNO dno acceptable EXEC SQL DELETE FROM T1 WHERE I1 varl1 0 ii AND I2 var1 0 i2 AND I3 varl 0 i3 acceptable 10 2 3 2 Example The following example shows the use of an array type host variable as an input host variable in a DELETE statement lt Sample Program arraysl sc gt EXEC SOL BEGIN DECLARE SECTION short a dno 3 EXEC SQL END DECLARE SECTION a dno 0 4001 a dno 1 4002 a dno 2 2001 EXEC SQL DELETE FROM EMPLOYEES WHERE DNO a dno 10 2 4 SELECT The array types that can be used with the SELECT statement are as follows The array types listed below can also be used in FETCH statements and the same limitations apply Simple arrays Arrays of structures Structures comprising arrays as individual elements thereof 10 2 4 1 Limitation If any of the host variables in the INTO clause of a SELECT statement is an array all of the host vari ables in the INTO clause must be arrays Array type input host variables cannot be used in the WHERE clause of SELECT embedd
202. r Code Error Message 501H option name option is repeated 502H Option string option string is too long 503H The mt and sea options cannot be used together 504H Input file must be a form of sc 505H Unknown embedded SQL statement type 203 Error Codes and Messages 15 1 Precompiler Errors Error Code Error Message 701L No CURSOR SENSITIVITY options are supported yet 702L No CURSOR INSENSITIVITY options are supported yet 703L No CURSOR ASENSITIVITY options are supported yet 704L WITH HOLD option is not supported yet 705L WITH RETURN option is not supported yet 706L READ ONLY option does is supported yet 707L ALTER COMPACT option is not supported yet Precompiler User s Manual 204 Output Host Variables Appendix A Using Files and LOBs This appendix explains how to use the file system with the BLOB and CLOB data types so that data in files can be inserted into tables or so that data from tables can be written to files Output Host Variables When it is desired to select data from a BLOB or CLOB type column and store the data in a file the fol lowing syntax is used to specify output host variables in the INTO clause of a SELECT statement BLOB FILE host variable OPTION file type INDICATOR lt indicator gt CLOB FILE host variable OPTION file type INDICATOR indicator host varible This is a character type variable containing
203. r h EXEC SQL BEGIN DECLARE SECTION goods a goods 3 EXEC SQL END DECLARE SECTION strcpy a goods 0 gno Z111100001 strcpy a goods 1 gno Z111100002 strcpy a goods 0 gname ZZ 201 strcpy a goods 2 gno Z111100003 strcpy a goods 1 gname ZZ 202 149 Using Arrays in Embedded SQL Statements 10 5 Structures and Arrays strcpy a_goods 2 strcpy a_goods 0 strcpy a_goods 1 strcpy a_goods 2 gname ZZ 203 goods location goods location goods location a goods 0 stock 3000 a goods 1 stock a goods 2 stock a goods 0 price a goods 1 price a goods 2 price EXEC SOL INSERT INTO GOODS VALUES 4000 5000 7890 21 5670 45 500 99 AD0020 AD0021 AD0022 a goods Example 2 The following example shows the use of an array of structures as an output host variable in a SELECT statement lt Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct department short dno char dname 30 1 char dep location 9 1 int mgr no department EXEC SOL END DECLARE SECTION Sample Program arrays2 sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h EXEC SQL BEGIN DECLARE SECTION short s dno department a department 5 EXEC SQL END DECLARE SECTION Ss dno 2000 EXEC SQ
204. r of records that were returned This is only the number of records that were returned by the most recently executed SELECT or FETCH statement not a cumulative number of records returned by multiple statements Therefore this value will not be larger than the array size The value of this variable can be checked after an array type input host variable is used to execute an embedded SQL statement This value indicates the number of elements of the array type host variable for which the operation was successfully performed Therefore this value cannot be larger than the array size For example if an array type input host variable whose size is 3 is used to execute an UPDATE state ment and the respective update operations are successful for the Oth array element unsuccessful for the 1st array element and successful for the 2nd array element a value of 2 will be stored in this variable Meanwhile the number of records that were actually updated will be stored in sqlca sql errd 2 which means that a value higher than 2 might be stored in sqlca sqlerrd 2 In order to ensure that errors are reliably detected and handled it is necessary to check sqica sqlcode every time an embedded SQL statement is executed When a SELECT statement is used to retrieve a value from a character type column and the size of the corresponding output variable is smaller than the size of the column 1 the char acter column data will be truncated to the leng
205. r variable In this case it is acceptable to use the value of 1en as the indica tor variable Example EXEC SQL BEGIN DECLARE SECTION varchar varl int varl ind EXEC SQL END DECLARE SECTION Inserting TEST in column I1 of table T1 when varl len is used as an indicator variable 31 Host Variables and Indicator Variables 3 3 Indicator Variables strcpy varl arr TEST varl len strlen varl arr EXEC SQL INSERT INTO T1 I1 VALUES varl Inserting NULL in column I1 of table T1 when varl len is used as an indicator variable varl len 1 EXEC SQL INSERT INTO T1 I1 VALUES varl Inserting TEST in column I1 of table T1 when varl ind is used as an indicator variable strcpy varl arr TEST varl ind strlen varl arr EXEC SQL INSERT INTO T1 I1 VALUES varl varl ind 3 3 7 Examples In the following example s goods location ind is used as the indicator variable for the s goods location host variable and s price ind is used as the indicator variable for the s price host variable Because the value of both indicator variables is SQL NULL DATA NULL will be inserted in the corresponding columns even though the values of the s goods location and s price host vari ables are not NULL Sample Program indicator sc gt declare host variables EXEC SQL BEGIN DECLARE SECTION char s_gno 10 1 char s gname 2041 char s goods location 9 1 int s stock double s price
206. r which the entire SQL string is not known until run time This method cannot be used to execute SELECT statements 11 2 1 1 EXECUTE IMMEDIATE Syntax EXEC SQL EXECUTE IMMEDIATE host var string literal Arguments lt host var This is a character type variable that includes all of the SQL statement text string literal This is the entire SOL statement hard coded in the form of a string Description When using a host variable only one host variable can be used This host variable must include all of the SQL statement text Furthermore this SQL statement text must not contain any host variables or the question mark parameter marker Example Example 1 The following example shows the use of Method 1 to execute a dynamic SQL statement using a provided SQL statement string lt Sample Program dynamic1 sc gt EXEC SQL EXECUTE IMMEDIATE DROP TABLE T1 EXEC SQL EXECUTE IMMEDIATE CREATE TABLE T1 I1 INTEGER I2 INTEGER Example 2 The following example shows the use of Method 1 to execute a dynamic SQL statement that is stored in a host variable lt Sample Program dynamic1 sc gt 157 Dynamic SQL Statements 11 2 Using Dynamic SQL Statements char query 100 strcpy query drop table t2 EXEC SQL EXECUTE IMMEDIATE query strcpy query create table t2 il integer EXEC SQL EXECUTE IMMEDIATE query 11 2 2 Method 2 Method 2 consists of two steps the PREPARE statement and the EXECUT
207. r1 10 tagl int i3 int i4 var2 10 int var3 EXEC SQL EXEC SQL END DECLARE SECTION SELECT I1 I2 INTO varl FROM T1 WHERE I1 var3 acceptable EXEC SOL SELECT I1 I2 I3 I4 INTO varl var2 FROM T1 WHERE I1 var3 not allowed An indicator variable cannot be specified for use with a host variable that is an array of struc tures Therefore when an array of structures is used as an output host variable it must be guar anteed that no NULL values will be returned Example of Unacceptable Usage EXEC SOL struct struct EXEC SQL EXEC SOL 10 5 1 4 Examples BEGIN DECLARE SECTION tagl int il int i2 char i3 10 var1 10 tag2 int i1 ind int i2 ind int i3 ind varl_ind 10 END DECLARE SECTION SELECT INTO varl varl ind not allowed Various examples of the declaration and use of arrays of structures as host variables are shown below Example 1 The following example shows the use of an array of structures as an input host variable in an INSERT statement Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 10 1 char gname 20 1 char goods_location 9 1 int stock double price goods EXEC SQL END DECLARE SECTION lt Sample Program arrays1 sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostva
208. racter a z A Z or the underscore character cursor specification This is an SQL SELECT statement of ALTIBASE HDB For complete information on authoring SELECT statements please refer to the ALTIBASE HDB SQL Reference 3 Description The DECLARE CURSOR statement must be executed before any other cursor related SQL statements If another statement that references a cursor that has not been declared is executed an error indi cating that the cursor does not exist will be raised When the DECLARE CURSOR statement is executed all SQL statement preparation tasks such as syn tax checking semantics checking optimization and execution plan creation are conducted on the server This is similar to the functionality of the SOLPrepare ODBC statement The SQL statement need only be prepared in advance one time in order to be executed multiple times using OPEN CUR SOR which is like the SOLExecute ODBC statement 4 Limitations All of the limitations that apply to the SELECT ALTIBASE HDB SQL statement also apply to the DECLARE CURSOR statement 9 2 1 5 Example The following example shows how to declare a cursor for retrieving all of the records in the depart ments table lt Sample Program cursorl sc gt EXEC SOL DECLARE DEPT CUR CURSOR FOR SELECT FROM DEPARTMENTS 121 Using Cursors 9 2 Cursor Related SQL Statements 9 2 2 OPEN This statement is used to open a cursor 9 2 2 1 Syntax EXEC SQL OPEN
209. ram it is first necessary to declare the host variables and indicator variables that will be used Host variables must be declared in the host variable declaration section if the partial precompiler option is not set to full For more information about host variables and indicator variables please refer to Chapter3 Host Variables and Indicator Variables 2 3 1 1 Considerations when Declaring Host Variables Nested structures cannot be used as host variables In other words a structure cannot be an element of another structure When declaring array type host variables macros can be used only to specify the number of array elements Macro definitions cannot be used for example to specify the location at which the value of a host variable is to be substituted in an embedded SQL statement When declaring a character type i e char or varchar output host variable the length of the host variable must be defined so that it is at least one byte longer than the size of the corre sponding column Otherwise when a SELECT or FETCH statement is executed the value in the column will be truncated In this case the value returned in sqlca sqlcode will be SQL SUCCESS WITH INFO 2 3 1 2 Special Considerations when Declaring Array Type Host Variables For complete information about using arrays with embedded SQL statements please refer to Chapter10 Using Arrays in Embedded SQL Statements Array type host variables can only be one dimensio
210. re checked to determine the result of execu tion of an embedded SQL statement lt Sample Program delete sc gt declare host variables EXEC SQL BEGIN DECLARE SECTION int s_eno short s dno EXEC SQL END DECLARE SECTION s eno S dno 5 1000 EXEC SQL DELETE FROM EMPLOYEES WHERE ENO gt s eno AND DNO gt s dno AND EMP JOB LIKE P check sqlca sqlcode if sqlca sqlcode SQL SUCCESS sqlca sqlerrd 2 holds the rows processed deleted count printf d rows deleted n n sqlca sqlerrd 2 else printf Error d s n n SQLCODE sqlca sqlerrm sqlerrmc 2 3 5 Disconnecting from the Database Server After all embedded SQL statements have been executed it will be necessary to disconnect from the database server before shutting down the application Disconnecting from the database server frees all resources that were allocated for the connection After disconnecting from the database server it is of course impossible to execute any more embedded SQL statements For detailed information about how to connect to and disconnect from database servers please refer to Chapter7 Embedded SQL Statements 2 3 5 1 Example The following example shows how to disconnect from a database server lt Sample Program connectl sc gt EXEC SQL DISCONNECT 2 3 6 The Precompile Operation This is how to execute the precompiling operation using the APRE precompiler 21 The C C Precompiler
211. recompiler User s Manual 64 6 3 Extended APRE Data Types 6 3 Extended APRE Data Types Beyond the fundamental C C data types APRE provides additional data types for use as host vari ables and in embedded SQL statements These extended data types are described below along with how to use them 6 3 1 VARCHAR 6 3 1 1 varchar The varchar type may be declared in either lower case or upper case that is as either varchar or VARCHAR Internally it is a kind of structure For example if a varchar type variable is declared as follows varchar a 10 The underlying structure would appear thus struct int len char arr 10 a The constituent elements of the varchar type variable can thus be referred to using the period operator in this way a arr This is the same as when referring to the constituent elements of any other structure The varchar type has its own built in indicator variable This role is played by the first constituent element namely 1en Therefore when it is necessary to use an indicator variable with a varchar type host variable there is no need to declare a separate indicator variable thus making the use of the varchar type very convenient Although the varchar type comes with its own indicator variable it is still possible to declare and use a separate indicator variable This is useful when the varchar type is just one element inside another structure type host variable and a structure type indicator
212. riable is an input indicator variable or an output indicator variable There fore it is important to understand the information in the following table and to refer back to it when using indicator variables It is particularly important that the value of input indicator variables be set correctly because these values are used internally by the precompiler and the database server Value of Input Indicator Variables Value of Output Indicator Variables Host Variable Type 1 Values other than 1 1 Values other than 1 Numeric types Means Not internally used Means the Contains the size of theinput Not meaningful returned the host variable value is value is NULL sizeof NULL a The actual Character types Used to indicate the value of the Contains the length length of the input host variable Of the returned value value strlen Must doesnot strlen be set mean any Date type Not internally used thing Gar Contains the size of Not meaningful bage value the host variable sizeof APRE_BINARY Used to indicate the Contains the length length in bytes of in bytes of the the input value returned value Must be set APRE_BLOB Used to indicate the Contains the length length in bytes of in bytes of the the input value returned value Must be set APRE_CLOB Used to indicate the SQL_NO_TOTAL 4 length in bytes of indicates that the the inpu
213. riable must not be a structure either However if the host variable is a structure the indicator variable must be a structure too 3 3 5 When is it necessary to use indicator variables Indicator variables must be used in the following cases 3 3 5 1 When an input value is NULL When inputting a NULL value an indicator variable must be used and its value must be set to 1 SOL NULL DATA 3 3 5 2 When querying a column that does not have a NOT NULL constraint If the value of a selected or fetched column is NULL and an indicator variable is not being used the result of execution of the embedded SQL statement sqlca sqlcode will be SQL SUCCESS WITH INFO and a warning message will be returned in the variable sqlca sql errm sqlerrmc Precompiler User s Manual 30 3 3 Indicator Variables 3 3 5 3 When the type of an input or output host variable is APRE_BINARY APRE_BLOB or APRE_BYTES Because binary types are not NULL terminated the database needs a way of knowing the length of an input value Therefore the length of the input data must be specified using the indicator variable In the same way when dealing with output host variables the length of returned column values must be stored in indicator variables For more information about the APRE_BINARY APRE_BLOB and APRE_BYTES data types please refer to Chapter6 Host Variable Data Types 3 3 5 4 When using an APRE_NIBBLE type output host variable An indicator variable must be use
214. riable with a host variable that is an array of structures This means that it is necessary to guarantee that no NULL column values are returned when using an array of structures as an output host variable If a NULL column value is returned the value stored in the sglca sglcode variable will be SOL SUCCESS WITH INFO Example EXEC SQL BEGIN DECLARE SECTION struct tagl int il int i2 char i3 11 var1 101 struct tag2 int il ind int i2 ind int i3 ind var1 ind 10 EXEC SQL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 i2 i3 VALUES varl varl ind unacceptable When using an array of structures as a host variable in the INTO clause of a SELECT or FETCH statement only one output host variable can be used In other words such an output host variable cannot be used with other host variables Therefore when using an array of structures as an output host variable in the INTO clause the underlying structure will need the same 63 Host Variable Data Types 6 2 Fundamental C C Data Types number of constituent elements as the number of columns in the select list Similarly when using an array of structures as a host variable in the VALUES clause of an INSERT statement only one input host variable can be used In other words such an input host variable cannot be used with other host variables Therefore when using an array of structures as an input host variable in the VALUES clause the underlying structure will need
215. rnal source file For more detailed information please refer to the 5 1 1 1 Example The portion of the files referenced using include directives that is actually incorporated in the source text varies depending on how the parse option is set 5 2 3 if When the APRE C C preprocessor encounters the iti directive it evaluates the given condition and uses the result of the evaluation as the basis on which to determine whether to include the source text between the if and endif directives in the precompile operation 5 2 3 1 Example define A 1 1 define BA 2 Hif B int var endif Hif defined A int var2 endif 47 C Preprocessor 5 2 C Preprocessor Directives In the above example the APRE C C preprocessor substitutes A 2 for B and then 1 1 for A when evaluating the first i condition This results in the expression 1 1 2 which evaluates to 0 or false Therefore the source text between the first i and endi commands is not included in the out put The second condition if defined is handled in the same way as an ifdef condition 5 2 4 ifdef When the APRE C C preprocessor encounters the ifdef condition it determines whether to include the source text between the ifdef and endif directives based on whether the name that follows the ifdef keyword is a defined name 5 2 4 1 Example define A ifdef A int var endif In the above example the text int var is included in
216. rom SESC to APRE 1 1 1 New Features 1 1 1 1 More Flexible Host Variable Declaration It is now possible to use all variables as host variables even if they were declared outside of the host variable declaration section 1 1 1 2 Additional Preprocessor Directive Support APRE now supports preprocessor directives such as if ifdef etc For detailed information on the support for preprocessor directives please refer to Chapter5 C Preprocessor 1 1 1 3 DECLARE STATEMENT The DECLARE STATEMENT statement can now be used to declare identifiers for SQL statements or stored procedure blocks For detailed information please refer to Chapter1 1 Dynamic SOL State ments 1 1 1 4 Using DO function name in WHENEVER statement With version 5 3 3 it became possible to use the DO clause in the WHENEVER statement to call a desired function in response to the occurrence of an error For detailed information please refer to Chapter8 Handling Runtime Errors 1 1 1 5 New Command Line Options e D This option is used to specify a macro name so that it will be recognized when it is present in code keyword This option is used to output a list of all reserved words e parse This option is used to define a range to be parsed by the precompiler Precompiler User s Manual 2 1 1 New Precompiler Features in ALTIBASE HDB 5 3 3 e I This option is used to specify an include path debug This option is used for debugging and o
217. ror code 8 3 3 Error Codes Depending on the origin of the error the error code that is returned when an error occurs during the execution of an embedded SQL statement will be either an APRE error code or a database server error code 8 3 3 1 Embedded SQL Statement Error Codes Errors that are raised by embedded SQL statements at runtime will return APRE error codes These include the following For information on errors other than the following please refer to Error Mes sage Reference 589826 This code is returned when a connection with the same name already exists 589841 This code is returned when the name of the connection exceeds 50 bytes 589857 This code is returned when an attempt is made to execute a SOL statement for han dling a cursor and the name of the cursor has not been declared 589858 This code is returned when an attempt is made to execute a dynamic SQL statement using an identifier for a SOL statement that has not been prepared 111 Handling Runtime Errors 8 3 SQLCODE 8 3 3 2 Database Server Error Codes When an error that occurs during the execution of an SQL statement originates in the database server a database server error code will be returned For complete information about all ALTIBASE HDB error codes please refer to the ALTIBASE HDB Error Message Reference 8 3 4 Precaution The error codes returned by SQLCODE are negative decimal numbers However the error codes in the Error Mess
218. rror message 5 3 1 4 pragma The pragma directive is used to pass implementation dependent information to the C compiler 5 3 1 5 line This directive is used to provide line number information to the C compiler 5 3 2 Limitations on the Use of define The use of the def ine directive with the APRE C C precompiler is limited in one important way names defined using the define directive cannot be used within embedded SQL statements 5 3 2 1 Example define RESEARCH DEPT 40 EXEC SQL SELECT empno sal INTO emp number salary host arrays FROM emp WHERE deptno RESEARCH DEPT INVALID In the above example the embedded SQL statement would result in an error because 40 will not be Precompiler User s Manual 50 5 3 Limitations on the Use of the Preprocessor substituted for RESEARCH DEPT in the WHERE clause 5 3 3 Limitations on the Use of if When a user defined macro function is used in an Hi condition the condition may not evaluate as expected so it is recommended that i conditions not contain user defined macro functions 5 3 3 1 Example define fun X Y X Y if fun 1 1 int var else int var2 endif 5 3 4 Limitations on the Use of include The APRE C C Preprocessor raises an error if a header file that is included using the include directive contains embedded SQL statements Additionally header files included in this way must not include declarations of VARCHAR type varia
219. s is included in the output of the pre compile operation if the Heli condition evaluates to true Bp 1 Evamnla 5 2 7 1 Example define A 0 define B 1 Hif A int varl delif B int var2 else int var3 endif In the above example the source text between the elif and el1se directives is included in the output of the precompile operation because the if condition is not satisfied and the e1if con dition is satisfied This indicates the end of a block of text that is included or excluded based on the result of evaluation of an if ifdef or ifndef condition 49 C Preprocessor 5 3 Limitations on the Use of the Preprocessor 5 3 Limitations on the Use of the Preprocessor This section covers a few limitations of the APRE C C Preprocessor including some directives that it ignores 5 3 1 Ignored Directives The APRE C C preprocessor ignores some C preprocessor directives because they are not neces sary for the precompile operation One example is the pragma directive which does not need to be processed by the precompiler because it is used only by the C compiler The commands that are ignored by the APRE C C preprocessor during the precompile operation are as follows 5 3 1 1 This directive converts a preprocessor macro parameter to a string constant 5 3 1 2 This directive merges two preprocessor tokens in a macro definition 5 3 1 3 error This directive is used to output a compile time e
220. s not exist 306L Unterminated string error 307L The connection name lt name gt is too long max length is 50 308L The cursor name lt name gt is too long max length is 50 309L Statement name lt name gt is too long max length is 50 310L The number of FOR loop iterations must be greater than zero 311L The host variable lt name gt is unknown 312L The host variable in a FREE LOB statement must be a LOB locator 313L Unterminated embedded SQL statement Precompiler User s Manual 202 15 1 Precompiler Errors Error Code Error Message 314L The indicator variable lt name gt should be of type SQLLEN or a compatible type 315L Two or more arrays of structures are bound to host variables in the same state ment Error Code Error Message 401M An unknown macro is too long gt 2k 402M Macro if statement syntax error 403M Macro elif statement syntax error 404M Macro elif statement sequence error 405M Macro else statement sequence error 406M Macro endif statement sequence error 407M An empty char constant cannot be used with an if macro expression 408M Include files are nested too deeply maximum lt 0 s gt 409M No endif error 410M A closing parenthesis is missing from the macro parameter list 411M Unknown macro name or missing parenthesis after macro name 0965s 412M Unterminated string error Erro
221. s the name of the connection stored in a host variable savepoint name This is the name of a savepoint 13 2 6 3 Consideration The connection name if specified must be the name of a valid connection that is a connection that has already been established Precompiler User s Manual 186 13 2 SQL Statements for Multi Connections 13 2 7 Exceptions The AT clause is never used with the following embedded SQL statements even in applications in which multiple database connections have been established 13 2 7 1 Syntax EXEC SQL INCLUDE EXEC SOL OPTION EXEX SOL WHENEVER 187 Applications with Multiple Database Connec 13 3 Using Stored Procedures in Multiple Connection Applications 13 3 Using Stored Procedures in Multiple Connection Applications SQL statements for managing stored procedures can also be used in applications with multiple data base connections The syntax for these statements is the same as was described in Chapter12 Using Stored Procedures in C C with the exception that the AT clause is additionally provided 13 3 1 CREATE This statement is used to create a stored procedure or stored function in an application with multi ple active database connections 13 3 1 1 Syntax EXEC SQL AT lt conn_name conn_name gt CREATE OR REPLACE lt PROCEDURE FUNCTION gt END procedure name function name END EXEC 13 3 1 2 Arguments conn name This is the name of the
222. s the name of the connection to terminate in the form of a string literal conn name This is the name of the connection to terminate stored in a host variable 13 2 2 3 Considerations The connection name if specified must be the name of a valid connection that is a connection that has already been established In an application in which multiple database connections have been established it will be necessary to specify the name of each connection except for the default connection when terminating the connection 13 2 2 4 Examples The following two examples illustrate how to terminate a connection Example 1 In the following example a connection with a database server identified using a string literal is terminated The name of the connection to be terminated is CONN1 Sample Program mcl sc gt EXEC SOL AT CONN1 DISCONNECT Example 2 In the following example a connection with a database server identified using a host variable is terminated The name of the connection to be terminated is CONN2 Sample Program mc2 sc gt char conn name2 10 strcpy conn name2 CONN2 EXEC SOL AT conn name2 DISCONNECT 13 2 3 Executing Basic SQL Statements The method of specifying the connection when executing DML statements such as SELECT and UPDATE and DDL statements such as CREATE and DROP is explained in this section 13 2 3 1 Syntax EXEC SQL AT conn name conn name SELECT UPDATE
223. sert Error d sqlca sqlcode exit 1 SELECT DML emp number 10 EXEC SQL SELECT e lastname emp job salary INTO emp name job title Salary FROM employees WHERE eno emp number if sqlca sqlcode 0 fprintf stderr Select Error d sqlca sqlcode exit 1 printf SELECT result ename s job s sal d n emp name job title salary UPDATE DML emp number 10 salary 2000 EXEC SQL UPDATE employees SET salary salary WHERE eno emp number if sqlca sqlcode 0 fprintf stderr Update Error d sqlca sqlcode exit 1 Cursor Create EXEC SQL DECLARE cur_emp CURSOR FOR SELECT e lastname emp job salary FROM employees Cursor Open EXEC SQL OPEN cur emp if sqlca sqlcode 0 fprintf stderr OPEN CSR ERROR d n sqlca sqlcode exit 1 Fetch Cursor for EXEC SQL FETCH cur_emp INTO emp_name job title salary switch sqlca sqlcode case 0 printf Fetch Result emp_name s job s sal d n emp name job title salary 221 Porting Pro C Applications to APRE Sample Programs continue case 1403 Not Found Data break default fprintf stderr FETCH CSR ERROR d sqlca sqlcode exit 1 break Cursor Close EXEC SQL CLOSE cur_emp Dynamic SQL strcpy dynamic stmt DELETE FROM EMPLOYEES WHERE ENO v1 EXEC SQL PRE
224. sesssessscssecssesssesssesssesssessecsscesseesseesees vi 1 New Features in APRE C C eee eese eee nette 1 1 1 New Precompiler Features in ALTIBASE HDB 5 3 3 2 1 1 1 New Features 52 1 1 2 Changes to Functionality 3 2 The C C Precompiler 5 2 1 Introduction and Concepts 6 2 1 1 Introduction aset D RODE ESSEN laa nie 6 2 1 2 Precompiler Environment Settings sssssscsssseccsseeccssesccsseececsseccsnseecsssccessseeecssecessuceessuecessuseesensceecnsccessueeessneeeesneeees 6 2 13 Handling SIGRI P E teinte ettet eR EI EET ote eot eet edet 6 2 1 4 The Precompilation Process csssssssssssssssssssssessssssssssssssssesssscssssssssesssscssuscssscsssscsssessssessscsssuccessesasscssussessesssscesseesseesess 7 22 Command Lirie OptiOnS cities RR ere eee RUE CHR TORRE re tor EROS rec Reed 8 PIA m c I 8 pP Ale Fe 2 pm M 8 2 2 3 0 Output path ss au aaa nte tdt de e EU eR RR ee HAN ead 9 PAZ E sc M M 9 22 5 Includes path eter tto maet E ERR HE AREAS BERRES RARE RAE repe Pee NESS 9 22 6 parse none partial full tette t PR oreet deterius 10 2 2 7 Dtdefine name e ER D HERR Dto pe teal 11 pn M 11 PARE 11 22 Fe PEU E AES nullara nna T A
225. sqlerrm sqlerrmc The row already exists in a unique index SQLSTATE 23000 SQLCODE 69720 Sqlca sqlcode 1 sqlca sqlerrm sqlerrmc The cursor must be opened to fetch rows SOLSTATE HY010 SQLCODE 1 117 Handling Runtime Errors 8 6 Sample Programs sqlca sqlcode 0 sqlca sqlerrd 2 12 sqlca sqlcode 0 sqlca sqlerrd 2 12 Sqlca sqlerrd 3 3 8 6 3 whenever1 sc This example can be found at SALTIBASE HOME sample APRE wheneverl1 sc 8 6 4 whenever2 sc This example can be found at SALTIBASE HOME sample APRE whenever2 sc 8 6 5 Result of Execution is f schema schema sql make whenever whenever WHENEVER Successful connection 1001 PAPER TEAM New York 16 1002 RESEARCH DEVELOPMENT DEPT 2 Sydney 13 1003 SOLUTION DEVELOPMENT DEPT Osaka 14 2001 QUALITY ASSURANCE DEPT Seoul 17 3001 CUSTOMER SUPPORT DEPT London 4 3002 PRESALES DEPT Peking 5 4001 MARKETING DEPT Brasilia 8 4002 BUSINESS DEPT Palo Alto 7 Precompiler User s Manual 118 9 Using Cursors 119 Using Cursors 9 1 Overview 9 1 Overview When it is expected that a query will return multiple records a cursor can be declared and used to manipulate the records APRE supports the use of various embedded SQL statements for declaring and managing cursors Briefly the Cursor related SQL statements that are available in APRE are the DECLARE CURSOR state ment the OPEN statement the FETCH statement the CLOSE statement
226. subsequently executed Batch processing is possible because the result of uncommitted INSERT UPDATE and DELETE statements can only be read from within the same transaction Batch processing can improve performance in environments in which INSERT UPDATE and DELETE statements are frequently executed 7 4 5 4 Example The following examples show how to use the BATCH statement in an embedded SQL statement to change the batch mode To activate batch processing mode Precompiler User s Manual 100 7 4 Using Other Embedded SQL Statements EXEC SQL BATCH ON To deactivate batch processing mode EXEC SQL BATCH OFF 7 4 6 FREE This statement is used to release all resources that were allocated when the connection with the database server was established and embedded SQL statements were executed 7 4 6 1 Syntax EXEC SQL FREE 7 4 6 2 Arguments None 7 4 6 3 Description If the connection with the server is lost while embedded SQL statements are being executed it is necessary to execute the FREE statement before attempting to re establish the connection The database server must not be running when the FREE statement is executed If the database server is running use the DISCONNECT statement instead of the FREE statement 7 4 6 4 Example The following example shows the use of the FREE statement lt Sample Program free sc gt EXEC SQL FREE 7 4 7 INCLUDE This statement is used to specify a header file that is to be
227. t int var1 10 int var2 10 EXEC SQL END DECLARE SECTION ent 5 OK EXEC SQL FOR cnt INSERT INTO T1 VALUES varl var2 cnt 0 unacceptable EXEC SQL FOR cnt INSERT INTO T1 VALUES varl var2 cnt 1 unacceptable EXEC SQL FOR cnt INSERT INTO T1 VALUES varl var2 In a FOR clause Input host variables do not need to be array type variables Example EXEC SQL BEGIN DECLARE SECTION int cnt int varl EXEC SQL END DECLARE SECTION cub 1 EXEC SQL FOR cnt INSERT INTO T1 VALUES varl acceptable 10 2 5 4 Examples Examples of the use of FOR clauses in various kinds of SQL statements follow 139 Using Arrays in Embedded SQL Statements 10 2 Using Host Array Variables in Embedded SQL Statements Example 1 The following example shows the use of a FOR clause in an INSERT statement The num ber of array elements to be processed is determined by the host variable cnt in the FOR clause and thus only the Oth and 1st elements in the a_goods array are inserted into the GOODS table lt Sample Program hostvar h gt XEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 10 1 char gname 20 1 char goods _location 9 1 int stock double price goods EXEC SQL END DECLARE SECTION lt Sample Program arrays1 sc gt EXEC SQL BEGIN DECLARE SECTION goods a_goods 3 EXEC SQL END DECLARE SECTION int cnt cnt 223 EXEC SQL FOR cnt INSERT INTO GOODS VALUE
228. t exist and must have the same values that are set in the corresponding properties in the altibase properties file export ALTIBASE PORT NO 20300 export ALTIBASE NLS USE US7ASCII 7 2 1 4 Specifying Two Sets of Connection Options When two sets of connection options are specified and an attempt is made to connect to a database the following three outcomes are possible SOL SUCCESS This is returned when a connection is successfully established using the first set of options SOL SUCCESS WITH INFO This is returned when the connection attempt using the first set of options fails but a connection is then successfully established using the second set of options The error message related to the failure of the first connection attempt is stored in sqlca sql errm sqlerrmc SQL ERROR This is returned when both connection attempts fail The error messages related to the failure of both connection attempts are consecutively stored in sqlca sqlerrm sqlerrmc 7 2 1 5 Considerations If an attempt to establish a connection is made when a connection already exists an error message indicating that a connection has already been established will be displayed Therefore if it is desired to establish a connection while a connection exists it is first necessary to execute FREE or DISCON NECT to terminate the existing connection If the database server is running the DISCONNECT state 85 Embedded SQL Statements 7 2 Database Connection State
229. t it is one 1 byte longer than the size of the corresponding CHAR type column The reason for this is that the length of the data stored in a CHAR type column is fixed so the length of the data that are returned will always be the same as the length of the column and the host variable requires one additional byte to store the NULL terminating character at the end If a host variable is not declared so that it is at least one character longer than the database col umn to which it corresponds then when a SELECT or FETCH statement is executed the value returned in the sglca sqlcode variable will be SQL SUCCESS WITH INFO When using a two dimensional varchar array as a host variable in the INTO clause of a SELECT or FETCH statement only one output host variable can be used In other words such an output host variable cannot be used with other output host variables Therefore when using a two dimensional varchar array as an output host variable in an INTO clause the cor responding select list can contain only one column Similarly when using a two dimensional varchar array as a host variable in the VALUES clause of an INSERT statement only one input host variable can be used In other words such an input host variable cannot be used with other input host variables Therefore when using a two dimensional varchar array as an input host variable in the VALUES clause of an INSERT statement the VALUES clause can contain only one column value
230. t tagl INSERT INTO T1 VALUES A or INSERT INTO T1 VALUES A 5a First declare the structure and then declare a pointer to the structure in a separate statement struct tagi int a struct tagl A A struct tagl malloc sizeof struct tagl SELECT I1 INTO A FROM T1 or SELECT I1 INTO A gt a FROM T1 First declare a structure and define a type based on the structure in the same statement and then declare a pointer to the type in a separate statement typedef struct tagl int a taglType taglType A A taglType malloc sizeof taglType SELECT I1 INTO A FROM T1 or SELECT I1 INTO A gt a FROM T1 In the following example vDataT2 is a pointer to a structure and is used as an input host variable lt Sample Program pointer sc gt EXEC SQL BEGIN DECLARE SECTION typedef struct tag char n1 11 int n2 tagType tagType dataT2 EXEC SQL END DECLARE SECTION void ins t2 tagType vDataT2 EXEC SQL BEGIN ARGUMENT SECTION tagType vDataT2 EXEC SQL END ARGUMENT SECTION EXEC SQL INSERT INTO T2 VALUES vDataT2 n1 vDataT2 gt n2 6 2 4 Structure Types 6 2 4 1 struct Structures struct can be used as host variable data types Precompiler User s Manual 62 6 2 Fundamental C C Data Types Using the structure type obviates the need to list multiple host variables one by one in an embed ded SQL statement when retrieving data
231. t value returned value was Must be set truncated Indicator variable values other than 4 are the length in bytes of the returned host variable value Precompiler User s Manual 36 3 5 Meaning of Indicator Variables Value of Input Indicator Variables Value of Output Indicator Variables Host Variable Type 1 Values other than 1 1 Values other than 1 APRE BYTES APRE NIBBLE Used to indicate the length in bytes of the input value Must be set Not internally used Not meaningful Contains the length in bytes of the returned value The length in bytes of the returned value is stored Indicator variables are usually used for handling NULL values However as shown in the above table input indicator variable values other than 1 can be meaningful and are checked and used within the system Therefore when using an input indicator variable it is important to set its value accu rately even when the value of the corresponding host variable is not NULL When the value of an input indicator variable that corresponds to a CHAR or BINARY type host vari able is not 1 the database server will take the value of the indicator variable as the length of the input value and process the value accordingly 37 Host Variables and Indicator Variables 3 6 Sample Programs 3 6 Sample Programs 3 6 1 indicator sc This example can be found at SALTIBAS
232. t variable are found and the values in the DNO and EMP_JOB columns for those records are updated with the values of the s_dno and s_emp_job arr host variables respectively lt Sample Program update sc gt EXEC SQL BEGIN DECLARE SECTION int s_eno short s dno varchar s emp job 1541 EXEC SOL END DECLARE SECTION S eno 2 s dno 1001 strcpy s emp job arr ENGINEER s emp job len strlen s emp job arr EXEC SOL UPDATE EMPLOYEES SET DNO s dno EMP JOB s emp job WHERE ENO s eno Example 2 This example illustrates the use of a structure type host variable in an UPDATE state ment Records for which the value in the ENO column matches the value of the s_eno host variable are found and the values in the DNO EMP_JOB and JOIN_DATE columns for those records are updated with the values of s_employee s_dno s_employee s_emp_job arr and SYSDATE respectively lt Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct employees int eno har e lastname 20 1 har e firstname 20 1 archar emp_job 15 1 har emp _tel 15 1 hort dno ouble salary har sex har birth 6 1 har join date 19 1 har status 1 1 employees EXEC SQL END DECLARE SECTION qaaqaqaqanadcaa lt Sample Program update sc gt specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h EXEC SOL BEGIN D
233. t2 sc 7 2 3 4 Result of Execution is f schema schema sql make connect2 connect2 CONNECT 2 Failed to connect to ALTIBASE server with both first and second options Error 327730 Failed first connection Client unable to establish connection Failed second connection Client unable to establish connection Precompiler User s Manual 88 7 3 7 3 Using DDL and DML in Embedded SQL Statements Using DDL and DML in Embedded SQL Statements The notional category basic embedded SQL statements essentially refers to those embedded SQL statements in which DML statements such as SELECT UPDATE INSERT and DELETE and DDL state ments such as CREATE DROP and ALTER are executed 7 3 1 SELECT 7 3 1 7 3 1 7 3 1 This statement is used to search one or more database tables for records that meet the specified conditions and store the returned data in host variables The basic syntax is the same as the general SELECT statement supported in ALTIBASE HDB SQL however in order to be able to use host vari ables an additional INTO clause is needed 1 Syntax EXEC SQL SELECT ALL DISTINCT lt select list INTO lt host_var_list gt FROM table expression WHERE 2 Arguments select list Please refer to the SQL Reference host var list This is a list of output host variables and output indicator variables table expression Please refer to the SQL Reference 3 Descriptio
234. tallati n RER RES SEEST a REE TNE TNE N EENEN NN a 226 EXECUT Oiarso EE i RE EAA A STE NEEE AA EN EAEN 226 Table Information of the Example Programs sssscsssssssssssscsssecssnsscecssecssnssecssseccesutecssseccssueecesusccecusecssnscescasecessueeessueeeesnseessnees 226 AppendixD FAQ eeeeeeeeee eese teens enses en stnae 227 Frequently Asked Questions t RN EE REEERE e RYAN EN e ERRARE RSEN CARERE Hte 227 Why were incorrect data inserted into my database table e teseteeenttennnttnnnis 227 Why will my APRE application compile in Linux but not in Solaris serene 227 Can get a sample showing the use of a general Makefile e nag 228 An error occurred when attempting to link libraries in HP UX WAY ssssssssccssesccssseccssscceessccesseeecesseessnneesee 228 An error occurs when I use gcc to link my project Why u sescsssssccsseccsssssesseccsssececnsecessseeccssecessueeessneeessneeesenseesse 229 If want to query data in an MMDB on an authentication server from other servers on which server should compile the library file eerte tetti ettet titre quie a tee e ARE Tp ee eee EAEE saarea aii 230 A compile error occurred while was testing a simple piece of source code Why sss 230 What is wrong with the makefile shown DelOW ssssssssccsseccsssesessnsececssccssseecssnseesssseecssseceesusecssasecesnsecesnecssneeeesaee 231 Why ca
235. te ment that was executed The following describes the meaning for each kind of embedded SQL state ment 10 3 1 1 IN When not using an array type input host variable the value of sqlca sqlerrd 2 will be 1 after success ful execution of an INSERT statement This means simply that one record has been inserted When using an array type input host variable the value of sqlca sqlerrd 2 will be the number of records that were successfully inserted Note that for an INSERT statement this value will never be larger than the size of the array For example if an INSERT statement is executed using a host variable of which the array size is 3 and the insertion operations corresponding to all three array elements are successful then three records will be inserted and the value stored in sqlca sqlerrd 2 will be 3 If the insertion operations corre sponding to the first two array elements are successful but the insertion operation corresponding to the third array element fails then two records will be inserted and this value will be 2 However the value stored in sglca sglerrd 2 is somewhat different when using Atomic Array Insert If the Atomic Array Insert operation is completely successful this value will be equal to the number of rows that were inserted i e the number of array elements but if even one insertion operation fails this value will be 0 10 3 1 2 UPDATE DELETE After a successful UPDATE or DELETE operation the number o
236. ted and the number of returned records is smaller than the array size the value of sqlca sqlcode will be SQL_SUCCESS Array type input host variables cannot be used with SELECT statements or cursor related statements The FOR clause can be used with array type input host variables to execute an embedded INSERT UPDATE or DELETE statement When working with array type host variables in AUTOCOMMIT mode a transaction is not the totality of operations performed using the entire array Rather the operations corresponding to each element are individual transactions and thus they are committed separately from one another Arrays of pointers cannot be declared or used as host variables 2 3 1 3 Considerations When Declaring Indicator Variables The data type of indicator variables must be int When using the varchar type as an input host variable without a separately defined indica tor variable it is necessary to specify the value of 1en which is one of the elements of the varchar structure If the value of the varchar array is not NULL set the value of 1en to the length of the arr element If the varchar array is NULL set Len to 1 For numeric type host variables indicator variable values other than 1 are meaningless Indicator variables must be used when working with binary type host variables 2 3 1 4 Host Variable Declaration Section For complete information about the host variable declaration section please refer to
237. teger INTEGER NUMBER PS short NUMBER PS short small integer int int APRE INTEGER integer long long large integer float float float point number double double double precision float ing point number DATE char n DATE char n n 20 varchar n varchar n Precompiler User s Manual 212 Using Embedded SQL Statements Host Variable Declaration Section The statements used to delimit the host variable declaration section are the same in ALTIBASE HDB and Oracle Oracle EXEC SQL BEGIN DECLARE SECTION Host variable declaration EXEC SQL END DECLARE SECTION ALTIBASE HDB EXEC SQL BEGIN DECLARE SECTION Host variable declaration EXEC SQL END DECLARE SECTION Using Embedded SQL Statements This section compares the use of basic SOL statements SELECT UPDATE INSERT DELETE cursor control SQL statements and dynamic SQL statements in Oracle Pro C and the ALTIBASE HDB C C Precompiler Basic DML Statements Basic DML statements for example the SELECT INSERT UPDATE and DELETE statements are exe cuted the same way i e using EXEC SQL in both Oracle and ALTIBASE HDB Cursor Control SQL Statements The fundamental method of declaring cursors is the same in Oracle and ALTIBASE HDB They only differ in that Oracle supports the declaration of cursor variables in the host variable declaration sec tion just like host variables whereas ALTIBASE HDB does not B 0 0 5 Cursor Declaration Oracle
238. tesseZe sesdaseiaeanavdtesss 36 3 6 Sample Programs sede de ERES ite AEE EE range 38 3 6 1 indicators e 38 3 6 2 Res lt of Executiori eat eer rp t eR HEU Na perte iR eee de tope dlree ttes 38 4 Host Variable Declaration Section 1 eese eee eese eerte eee eese eaten staat tn stas tn sets setas De eN Ene es Dee es essensen Dee 39 4 1 Host Variable Declaration Sectionin a E a E aieiaa 40 AN Syntaxis Se eed te ee he e o esie oe ENRON p e eR A reete ae An 40 41 2 Scope OF Host Variables eoe DRESD ER Ed ee oa eee Re ebur 40 AST SS bigirtatiOTis ise reete tette a certet idee eee EOE BESES ESSENS SEES pte SOANE 41 4 1 4 Example 41 4 2 Data Type Definition 4 2 1 Description 2 42 4 2 2 Examples sse 42 4 3 Function Argument Declaration Section 44 4 3 1 SYNCAX ssssesscscccsscsassceonsccsasonnvasconscsaseanes 44 4 3 2 Description 44 4 33 Sample ProGtar oot E eR AREE HARE EA EEEN UER Ri 44 LM M 45 D RO UI M 46 5 1 1 How the C Preprocessor Works ssssccssssccssssscsssessssssecssnsscesssecsssseecsssecessuseccsuecessusesesuscessnsecesueccsnsssesuseessnsseesnseessaess 46 5 2 CPreprocessor Directives oe e EE e E t A E a ERA a en Ra 47 5 2 138define t ridef s eere rei ee nt
239. tf Successful connection to ALTIBASE server with first optionsWMnWMn else if sqlca sqlcode SQL SUCCESS WITH INFO failed connection with first options and then successful connection with second options printf Successful connection to ALTIBASE server with second options n printf First connection error d s n n SQLCODE sqlca sqlerrm sql errmc else printf Failed to connect to ALTIBASE server with both first and second options n printf Error d n SQLCODE printf s n n sqlca sqlerrm sqlerrmc exit 1 7 2 2 DISCONNECT This statement is used to disconnect from the database server 7 2 2 1 Syntax EXEC SQL DISCONNECT 7 2 2 2 Arguments None 7 2 2 3 Description This statement disconnects from the database server and releases all resources that were allocated to the connection 7 2 2 4 Example The following example shows the use of the DISCONNECT statement lt Sample Program connectl sc gt EXEC SQL DISCONNECT 87 Embedded SQL Statements 7 2 Database Connection Statements 7 2 3 Sample Programs 7 2 3 1 connect1 sc This sample program can be found at ALTIBASE_HOME sample APRE connect1 sc 7 2 3 2 Result of Execution is f schema schema sql make connecti connectl CONNECT 1 Successful disconnection from altibase server 7 2 3 3 connect2 sc This sample program can be found at SALTIBASE HOME sample APRE connec
240. th Multiple Database Connections This chapter covers how to write applications that use multiple database connections Chapter14 Multithreaded Applications This chapter discusses how to write multithreaded applications Chapter15 Error Codes and Messages This chapter explains the APRE error codes and messages Appendix A Using Files and LOBs This chapter explains how to store LOB type data in the file system Appendix B Porting Pro C Applications to APRE iii Preface About This Manual This chapter discusses how to convert applications written with Oracle Pro C C to APRE Appendix C Sample Applications This chapter explains the location of the sample applications e Appendix D FAQ This chapter lists frequently asked questions about how to use APRE and embedded SQL state ments This section describes the conventions used in this manual Understanding these conventions will make it easier to find information in this manual and in the other manuals in the series There are two sets of conventions Syntax Diagram Conventions Sample Code Conventions In this manual the syntax of commands is described using diagrams composed of the following ele ments Element Description Indicates the start of a command If a syntactic element starts with an arrow it is not a complete command Reserved word Indicates that the command continues to the next line If a syntactic element ends w
241. th of the data stored in a CHAR type column is fixed so the length of the data that are returned will always be the same as the length of the column and the host variable requires one additional byte to store the NULL terminating character at the end If a host variable is not declared so that it is at least one byte longer than the database column to which it corresponds then when a SELECT or FETCH statement is executed the value returned in the sqlca sqlcode variable will be SQL SUCCESS WITH INFO rather than SOL SUCCESS When declaring a host variable for use with a database column it is common to declare and use a single input output host variable that is a host variable that is used as both an input and output variable for the column rather than declaring separate input and output host variables Therefore for the above reason pertaining to output host variables when declaring an input output variable of this type for use with a CHAR type database column it must be declared such that its length is one byte greater than the length of the column 59 Host Variable Data Types 6 2 Fundamental C C Data Types 6 N 3 Pointer Types All host variable types that are available in APRE can be used as base types for pointers When using a pointer to an array as an input variable in an INSERT statement set the number of array elements using the FOR clause For more information about the FOR clause please refer to Chapter10 Using Arr
242. th of the host variable 1 so that it can be stored therein At this time the contents of sqica sqlcode will be SOL SUCCESS WITH INFO If no records are affected by an UPDATE or DELETE operation the contents of sglca sglcode will be SOL NO DATA To check the number of records that were affected by an UPDATE or DELETE operation check the value of sqlca sglerrd 2 Precompiler User s Manual 110 8 3 SQLCODE 8 3 SQLCODE If the result of execution of an embedded SQL statement is SQL_ERROR the error code will be stored in SQLCODE 8 3 1 Data Structure Definition int SQLCODE 8 3 2 SQLCODE Return Values 0 This is returned upon successful execution of an embedded SQL statement At this time the value of sqlca sqlcode will be SOL SUCCESS 1 This is returned when the embedded SQL statement was executed but a warning was detected At this time the value of sq1ca sqicode will be SOL SUCCESS WITH INFO 100 This is returned when no records were returned by an executed SELECT or FETCH state ment At this time the value of sq1ca sq1code will be SOL NO DATA 1 This is returned when an error occurred during execution of the embedded SQL statement but there is no error code corresponding to the error that occurred At this time the value of sqlca sqlcode will be SOL ERROR Other negative values indicate that an error occurred during execution of the embedded SOL statement In this case the return value is the actual er
243. the name of the file into which to store the data file type This is an integer type variable that is used to specify the mode with which to access the file in order to write data to the file The following modes are available APRE FILE CREATE In this mode a new file is created and the data are written to the new file If a file having the specified name already exists an error will be raised APRE FILE OVERWRITE In this mode an existing file is opened and its contents are overwrit ten starting from the beginning of the file If no file having the specified name exists a new file is created and the data are written thereto APRE FILE APPEND In this mode an existing file is opened for appending that is the data are written at the end of the file after any existing data If no file having the specified name exists an error will be raised indicator This is an indicator variable that is used to check for NULL returned values or to get the length of the data stored in the file Example The following example shows the use of the CLOB FILE keyword and a file open mode option In this example the T LOB table is queried an int type column value is stored in the s 7 205 Using Files and LOBs Input Host Variables output host variable and a CLOB type column value is stored in a file whose name is specified in the string sl2FName which is opened in APRE FILE CREATE mode Sample Program clobSample sc gt EXEC SQ
244. the output of the precompile operation because A has been defined 5 2 5 ifndef This condition is the opposite of the ti def condition If the Hi nde condition evaluates to false the source text between the tti ndef and endif directives is included in the output of the pre compile operation whereas if it evaluates to true the source text is excluded from the output of the precompile operation 5 2 5 1 Example define A ifndef A int var endif In this example the text int var is excluded from the output of the precompile operation because A has been defined 5 2 6 else If this directive is provided between an if ifdef or ifndef condition and the corresponding endif directive and the condition evaluates to false then the source text between the Helse and endif directives will be included in the output of the precompile operation 5 2 6 1 Example Precompiler User s Manual 48 5 2 C Preprocessor Directives define A 0 Hif A int varl else int var2 endif In the above example the source text between the else and endif directives will be included in the output of the precompile operation because the if condition evaluates to false If this directive is provided between an if ifdef or ifndef condition and the corresponding endif directive and the first condition evaluates to false then the e1i condition is evaluated and the source text between the e1if and fendi f directive
245. the same effect as executing the EXECUTE statement a number of time equal to the size of the array thereby realizing a performance improvement 12 2 1 Array Types When used with the EXECUTE statement array type host variables can only be used as IN parame ters That is they cannot be used as IN OUT or OUT parameters The following shows the array types that can be used in the EXECUTE statement Simple arrays Structures containing arrays as elements thereof 12 2 2 Limitations The use of array type host variables with the EXECUTE statement is limited in the following ways Please keep them in mind when writing applications Array type host variables cannot be used as OUT or IN OUT parameters Example EXEC SQL BEGIN DECLARE SECTION int var1 10 int var2 10 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL EXECUTE BEGIN PROC1 varl in var2 out var3 in out not acceptable END END EXEC The value returned by a stored function cannot be assigned to an array type host variable Example EXEC SQL BEGIN DECLARE SECTION int var1 10 int var2 10 int var3 10 EXEC SQL END DECLARE SECTION EXEC SQL EXECUTE BEGIN varl FUNC1 var2 in var3 in not acceptable END END EXEC Array type host variables cannot be used together with non array type host variables as parameters for stored procedures or functions 175 Using Stored Procedures in C C 12 2 Using Array Type Host Vari
246. the same number of constituent elements as the number of columns in the INSERT statement Example EXEC SQL BEGIN DECLARE SECTION struct tagl int il int i2 var1 10 int var2 10 EXEC SQL END DECLARE SECTION EXEC SQL INSERT INTO T1 I1 I2 i3 VALUES varl var2 unacceptable The last two limitations are due to the internal rule that requires all host variables to be included in the structure when the host variable is a structure array 6 2 4 3 Examples The following example demonstrates the use of the structure type In this example a structure type called goods is defined and the host variable s goods which is of the goods type is declared The s goods variable is then used as an input host variable in an INSERT statement Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 10 1 char gname 20 41 char goods location 9 1 int stock double price goods EXEC SOL END DECLARE SECTION Sample Program insert sc gt Specify path of header file EXEC SQL OPTION INCLUDE include include header file for precompiling EXEC SQL INCLUDE hostvar h EXEC SQL BEGIN DECLARE SECTION goods s goods EXEC SQL END DECLARE SECTION strcpy s goods gno F111100003 strcpy s goods gname XX 102 strcpy s goods goods location AD0003 S goods stock 6000 S goods price 10200 96 EXEC SOL INSERT INTO GOODS VALUES s goods P
247. ts For more detailed information please refer to Chapter4 Host Variable Declaration Section 7 1 2 2 Function Argument Declaration Section These statements are used to delimit a block of code for declaring function arguments for use in other embedded SQL statements For more detailed information please refer to Chapter4 Host Vari able Declaration Section 7 1 2 3 Connection Related SQL Statements These statements are used to connect to and disconnect from a database 7 1 2 4 Basic Embedded SQL Statements These statements include DML statements such as SELECT UPDATE INSERT and DELETE state ments as well as DDL statements such as CREATE DROP and ALTER statements 7 1 2 5 Cursor Control SQL Statements These statements are used together with cursors to process data and include statements for defin ing cursors opening cursors using cursors to retrieve data and closing cursors For more detailed information please refer to Chapter9 Using Cursors 7 1 2 6 SQL Statements for Controlling Stored Procedures and Functions These statements are used for handling stored procedures and stored functions and include state ments for creating recompiling executing and deleting stored procedures and functions For more detailed information please refer to Chapter12 Using Stored Procedures in C C 7 1 2 7 Other Embedded SQL Statements This category includes all SQL statements supported in ALTIBASE HDB that do not fall into
248. ts of the structure are arrays Example of Unacceptable Usage EXEC SQL BEGIN DECLARE SECTION struct tagl int i1 10 int i2 10 var1 10 not allowed EXEC SQL END DECLARE SECTION When using an array of structures as an output host variable in the INTO clause of a SELECT or FETCH statement only one output host variable can be used In other words the array of struc tures cannot be used with any other output host variables Therefore if the host variable to be used in the INTO clause of a SELECT or FETCH statement is an array of structures the number of elements in the structure must be the same as the number of columns in the select list Similarly when using an array of structures as an input host variable in the VALUES clause of an INSERT statement only one input host variable can be used In other words the array of struc tures cannot be used with any other input host variables Therefore if the host variable to be used in the VALUES clause of an INSERT statement is an array of structures the number of ele ments in the structure must be the same as the number of columns specified in the INSERT statement The foregoing two limitations are due to an internal rule that requires the structure to include all host variables when the host variable is an array of structures Example Precompiler User s Manual 148 EXEC SQL struct struct 10 5 Structures and Arrays BEGIN DECLARE SECTION tagl int il int i2 va
249. urned 7 3 5 3 insert sc This sample program can be found at SALTIBASE HOME sample APRE insert sc 7 3 5 4 Result of Execution is f schema schema sql make insert insert lt INSERT gt 1 rows inserted 7 3 5 5 update sc This sample program can be found at ALTIBASE_HOME sample APRE update sc 7 3 5 6 Result of Execution is f schema schema sql make update update lt UPDATE gt 1 rows updated Precompiler User s Manual 96 7 3 Using DDL and DML in Embedded SQL Statements 7 3 5 7 delete sc This sample program can be found at ALTIBASE_HOME sample APRE delete sc 7 3 5 8 Result of Execution is f schema schema sql make delete delete lt DELETE gt 7 rows deleted 97 Embedded SQL Statements 7 4 Using Other Embedded SQL Statements 7 4 Using Other Embedded SQL Statements This category includes task control statements DCL statements for controlling the system and indi vidual transactions and the INCLUDE OPTION and THREAD OPTION statements 7 4 1 AUTOCOMMIT This statement is used to change the AUTOCOMMIT mode for the current session 7 4 1 1 Syntax EXEC SQL AUTOCOMMIT ON OFF 7 4 1 2 Arguments None 7 4 1 3 Example The following examples illustrate how to change the AUTOCOMMIT mode To change to AUTOCOM MIT mode EXEC SQL AUTOCOMMIT ON To change to Non AUTOCOMMIT mode EXEC SQL AUTOCOMMIT OFF 7 4 2 COMMIT 7 4 2 1 Syntax EXEC
250. using the name of the established connection 3 Terminate the established connection The above steps must be followed for each respective connection that is established 181 A Applications with Multiple Database Connec 13 2 SQL Statements for Multi Connections 13 2 SQL Statements for Multi Connections The method of using embedded SQL statements in applications with multiple database connections is not very different from when using embedded SQL statements in other applications The basic syntax of each command is the same all that is additionally required is to specify the name of the connection using the AT clause The following describes how to use embedded SQL statements in applications with multiple database connections 13 2 1 CONNECT This statement is used to establish a connection to the database server and define a name for the connection 13 2 1 1 Syntax EXEC SQL AT conn name conn name CONNECT user IDENTIFIED BY lt passwd gt USING conn opti conn opt2 1 13 2 1 2 Arguments conn name This is the name to use for the connection in the form of a string literal conn name This is the name to use for the connection stored in a host variable lt user gt This is the name of the user with which to connect to the database server lt passwd gt This is the password for the user with which the connection to the database server is established lt conn_opt1 gt Please refer to
251. utputs information about host variables and the names of macros For detailed information about all available command line options please refer to Chapter2 The C C Precompiler 1 1 2 Changes to Functionality 1 1 2 1 Elimination of the SES_DECLARE Macro The requirement to use ifdef SES_DECLARE to determine whether the SES_DECLARE macro has been defined in a header file in order to declare host variables has been lifted To declare host vari ables anywhere in source files set the value of the parse command line option to full or move the host variable declarations to the host variable declaration section For detailed information please refer to Chapter2 The C C Precompiler 1 1 2 2 Lifted Limitations on Host Variables Values can now be assigned to host variables in the host variable declaration section When using structures as host variables it is now possible to use typedef to define a structure type and then declare structures based on that type Additionally it is possible to access individual array elements of array type variables in embedded SQL statements Pointers can now be used as host variables not only for the char and structure types but also for other data types as well Host variables can be used without the colon in the INTO clause of a SELECT statement Additionally union type variables can also be used as host variables In SESC in order to use the value of a function parameter in an emb
252. w This chapter explains how to declare and use array type host variables in embedded SQL state ments 10 1 1 Definition and Declaration The term array host variable refers to a one dimensional or two dimensional array of a data type that can be used as a host variable that is itself declared for use as a host variable One or two dimensional arrays can be declared for use with character types and the varchar type whereas only one dimensional arrays can be declared for use with other data types One exception is that an array of pointers cannot be declared 10 1 1 1 Example Various examples that illustrate how array host variables are declared are shown below Example 1 The following example shows how to declare character type and numeric type arrays as host variables lt Sample Program arrays1 sc gt EXEC SQL BEGIN DECLARE SECTION char a_gno 3 10 1 char a_gname 3 20 1 char a goods location 3 9 1 int a stock 3 double a price 3 EXEC SQL END DECLARE SECTION Example 2 The following example shows how to declare an array of structures as a host variable Sample Program hostvar h gt EXEC SQL BEGIN DECLARE SECTION typedef struct goods char gno 1041 char gname 20 41 char goods location 9 41 int stock double price goods EXEC SQL END DECLARE SECTION Sample Program arrays1 sc gt EXEC SQL BEGIN DECLARE SECTION goods a goods 3 EXEC SQL END DECLARE SECTION
253. which cursor related statements must be executed when reus ing a cursor name In detail it sets forth the cursor related statements that can precede each cursor related statement DECLARE CURSOR When reusing a cursor name the DECLARE CURSOR statement must be executed after the CLOSE statement or the CLOSE RELEASE statement OPEN When reusing a cursor name the OPEN statement must be executed after the CLOSE state ment If all records have been fetched it can also be executed after the FETCH statement FETCH When reusing a cursor name the FETCH statement must be executed after the OPEN state ment It can also be executed after another FETCH statement if the result of execution of the previous FETCH statement was SQL_SUCCESS CLOSE When reusing a cursor name the CLOSE statement can be executed after the DECLARE CUR SOR statement the OPEN statement or the FETCH statement When it is executed after a FETCH statement it doesn t matter whether the result of execution of the FETCH statement was SQL_SUCCESS or SQL_NO_DATA CLOSE RELEASE When reusing a cursor name the CLOSE RELEASE statement can be executed after any other statement When it is executed after a FETCH statement it doesn t matter whether the result of execution of the previous FETCH statement was SOL SUCCESS or SOL NO DATA 9 3 2 Cursor Related SQL Statements and Host Variables The following describes how cursor related SQL statements must be used depending

Precompiler User`s Manual - ALTIBASE Customer Support

Contents

Download Pdf Manuals

Related Search

Related Contents