/**************************************************************************** ** (c) Copyright IBM Corp. 2007 All rights reserved. ** ** The following sample of source code ("Sample") is owned by International ** Business Machines Corporation or one of its subsidiaries ("IBM") and is ** copyrighted and licensed, not sold. You may use, copy, modify, and ** distribute the Sample in any form without payment to IBM, for the purpose of ** assisting you in the development of your applications. ** ** The Sample code is provided to you on an "AS IS" basis, without warranty of ** any kind. IBM HEREBY EXPRESSLY DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR ** IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF ** MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. Some jurisdictions do ** not allow for the exclusion or limitation of implied warranties, so the above ** limitations or exclusions may not apply to you. IBM shall not be liable for ** any damages you suffer as a result of using, copying, modifying or ** distributing the Sample, even if IBM has been advised of the possibility of ** such damages. ***************************************************************************** ** ** SOURCE FILE NAME: fnuse.sqc ** ** SAMPLE: How to use built-in SQL functions ** ** SQL FUNCTION USED: ** IDENTITY_VAL_LOCAL ** ** SQL STATEMENTS USED: ** CREATE TABLE ** COMMIT ** EXECUTE IMMEDIATE ** VALUES ** PREPARE ** DECLARE CURSOR ** OPEN ** FETCH ** CLOSE ** DROP TABLE ** ** ***************************************************************************** ** ** For more information on the sample programs, see the README file. ** ** For information on developing embedded SQL applications see the Developing Embedded SQL Applications book. ** ** For information on using SQL statements, see the SQL Reference. ** ** For the latest information on programming, building, and running DB2 ** applications, visit the DB2 Information Center: ** http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/index.jsp ****************************************************************************/ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <sqlenv.h> #include <sqlcodes.h> #include <sqlutil.h> #include "utilemb.h" int ScalarFn_IDENTITY_VAL_LOCAL_Use(void); EXEC SQL BEGIN DECLARE SECTION; short empno; char name[15]; char strStmt[257]; short lastIdentityValue; short lastIdentityValueInd; EXEC SQL END DECLARE SECTION; int main(int argc, char *argv[]) { int rc = 0; char dbAlias[SQL_ALIAS_SZ + 1]; char user[USERID_SZ + 1]; char pswd[PSWD_SZ + 1]; /* check the command line arguments */ rc = CmdLineArgsCheck1(argc, argv, dbAlias, user, pswd); if (rc != 0) { return rc; } printf("\nTHIS SAMPLE SHOWS HOW TO USE BUILT-IN SQL FUNCTIONS.\n"); /* connect to the database */ rc = DbConn(dbAlias, user, pswd); if (rc != 0) { return rc; } rc = ScalarFn_IDENTITY_VAL_LOCAL_Use(); /* disconnect from the database */ rc = DbDisconn(dbAlias); if (rc != 0) { return rc; } return 0; } /* main */ int ScalarFn_IDENTITY_VAL_LOCAL_Use(void) { int rc = 0; struct sqlca sqlca; printf("\n-----------------------------------------------------------"); printf("\nUSE THE SQL STATEMENTS:\n"); printf(" CREATE TABLE\n"); printf(" COMMIT\n"); printf(" INSERT\n"); printf(" PREPARE\n"); printf(" DECLARE CURSOR\n"); printf(" OPEN\n"); printf(" FETCH\n"); printf(" CLOSE\n"); printf(" VALUES\n"); printf(" DROP TABLE\n"); printf("TO WORK WITH THE SCALAR FUNCTION 'IDENTITY_VAL_LOCAL'.\n"); printf("\n CREATE TABLE empl(empno INTEGER GENERATED BY DEFAULT" "\n AS IDENTITY(START WITH 30," "\n INCREMENT BY 10)," "\n name VARCHAR(10))\n"); EXEC SQL CREATE TABLE empl(empno INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 30, INCREMENT BY 10), name VARCHAR(10)); EMB_SQL_CHECK("table -- create"); printf(" COMMIT\n"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); printf("\n INSERT INTO empl(name) VALUES('Sanders')\n"); strcpy(strStmt, "INSERT INTO empl(name) VALUES('Sanders') "); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("table -- insert"); printf(" VALUES IDENTITY_VAL_LOCAL() returns: "); EXEC SQL VALUES IDENTITY_VAL_LOCAL() INTO :lastIdentityValue:lastIdentityValueInd; EMB_SQL_CHECK("IDENTITY_VAL_LOCAL -- use"); if (lastIdentityValueInd >= 0) { printf("%d\n", lastIdentityValue); } else { printf("NULL\n"); } printf(" INSERT INTO empl(name) VALUES('Davis')\n"); strcpy(strStmt, "INSERT INTO empl(name) VALUES('Davis') "); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("table -- insert"); printf(" VALUES IDENTITY_VAL_LOCAL() returns: "); EXEC SQL VALUES IDENTITY_VAL_LOCAL() INTO :lastIdentityValue:lastIdentityValueInd; EMB_SQL_CHECK("IDENTITY_VAL_LOCAL -- use"); if (lastIdentityValueInd >= 0) { printf("%d\n", lastIdentityValue); } else { printf("NULL\n"); } printf(" INSERT INTO empl(name) VALUES('Yamaguchi')\n"); strcpy(strStmt, "INSERT INTO empl(name) VALUES('Yamaguchi') "); EXEC SQL EXECUTE IMMEDIATE :strStmt; EMB_SQL_CHECK("table -- insert"); printf(" VALUES IDENTITY_VAL_LOCAL() returns: "); EXEC SQL VALUES IDENTITY_VAL_LOCAL() INTO :lastIdentityValue:lastIdentityValueInd; EMB_SQL_CHECK("IDENTITY_VAL_LOCAL -- use"); if (lastIdentityValueInd >= 0) { printf("%d\n", lastIdentityValue); } else { printf("NULL\n"); } printf(" COMMIT\n"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); printf("\n SELECT empno, name FROM empl\n"); strcpy(strStmt, "SELECT empno, name FROM empl"); EXEC SQL PREPARE stmt1 FROM :strStmt; EMB_SQL_CHECK("statement -- prepare"); printf("\n EMPNO NAME\n"); printf(" ----- ----------\n"); EXEC SQL DECLARE c1 CURSOR FOR stmt1; EXEC SQL OPEN c1; EMB_SQL_CHECK("cursor -- open"); EXEC SQL FETCH c1 INTO :empno, :name; EMB_SQL_CHECK("cursor -- fetch"); while (sqlca.sqlcode != 100) { printf(" %5d %-10s\n", empno, name); EXEC SQL FETCH c1 INTO :empno, :name; EMB_SQL_CHECK("cursor -- fetch"); } EXEC SQL CLOSE c1; EMB_SQL_CHECK("cursor -- close"); printf("\n DROP TABLE empl\n"); EXEC SQL DROP TABLE empl; EMB_SQL_CHECK("table -- drop"); printf(" COMMIT\n"); EXEC SQL COMMIT; EMB_SQL_CHECK("transaction -- commit"); return 0; } /* ScalarFn_IDENTITY_VAL_LOCAL_Use */