Home

IBM Informix Embedded SQLJ User's Guide

1. 2 2 410 Handling Errors 2 4 a OS a GROS ce de ox a Ro aW Aes EY ose X 39 dec Re doce ou oe a AO In This Chapter This chapter provides detailed information about using the Embedded SQL language For syntax and reference information about specific statements refer to the IBM Informix Guide to SQL Syntax Embedded SQLJ Versus Traditional Embedded SQL Embedded SQLJ has some differences from the earlier embedded SQL languages defined by ANSI ISO ESOL C ESQL ADA ESQL FORTRAN ESOL COBOL and ESQL PL 1 The major differences are as follows The SQL connection statement of traditional embedded SQL is replaced by a Java connection context object This approach enables Embedded SQL programs to open multiple database connections simultaneously e In Embedded SQLJ there is no host variable definition section preceded by a BEGIN DECLARE SECTION statement and terminated by an END DECLARE SECTION statement All legal Java variables can be used as host variables Embedded SQLJ does not include the WHENEVER GOTO CONTINUE statement because Java has well developed rules for declaring and handling exceptions Embedded SQLJ uses iterator objects rather than cursors to manage result sets A result set iterator is a Java object from which you can retrieve the data returned by a SELECT statement Unlike cursors iterator objects can be passed as parameters to methods Embedded SQLJ supports access to data in colum
2. INSERT INTO customer VALUES 101 Ludwig Pauli All Sports Supplies 213 Erstwild Court Sunnyvale CA 94086 408 789 8075 h You can use the SELECT INTO statement to retrieve data into Java variables host variables Host variables within SOL statements are designated by a preceding colon For example the following query places values in the variables customer num fname Iname company address1 address2 city state zipcode and phone sql SELECT INTO customer num fname lname company addressl address2 city state zipcode phone FROM customer WHERE customer num 101 h SQL statements are case insensitive and can be written in uppercase lowercase or mixed case letters Java statements are case sensitive and so are host variables You use SELECT INTO statements for queries that return a single record for queries that return multiple rows a result set you use an iterator object described in the next section Handling Result Sets Embedded SQLJ uses result set iterator objects rather than cursors to manage result sets cursors are used by languages such as IBM Informix ESQL C A result set iterator is a Java object from which you can retrieve the data returned by a SELECT statement Unlike cursors iterator objects can be passed as parameters to a method Important Names of iterator classes must be unique within an application When you declare an i
3. System out printIn n n row_cnt System out printIn Total No Of rows Selected row cnt cust_rec close The next method of the iterator object advances processing to successive rows of the result set It returns FALSE after it fails to find a row to retrieve The Java compiler detects type mismatches for the accessor methods The validity of the types and names of the iterator columns and their related columns in the SELECT statement are checked at translation time if you perform Online online checking For information about setting up online checking see Checking on page 5 8 Using Column Aliases When an expression returned by a SELECT statement has an SQL name that is not a valid Java identifier use SQL column aliases to rename them For example the name Not valid for Java is acceptable as a column name in SQL but not as a Java identifier You can use a column alias that has a name acceptable as a Java identifier by using the AS clause SELECT Not valid for Java AS Col1 FROM tablename When you create a named iterator class for this query you specify the column alias name for the Java variable as in sql iterator Iterator name String Coll Iterator Methods Both named and positional iterator objects have the following methods e rowCount Chapter 4 The Embedded SQLJ Language 4 5 Returns the number of rows retrieved by the iterator object close Closes the iterator raises SO
4. nvoke the SOLJ translator using the following command java ifxsqlj Dfile encoding SJIS file sqlj Importing Java Packages Your Embedded SQLJ programs need to import the JDBC API java sql and SQL runtime sqlj runtime packages to which they refer The classes you are likely to commonly use are In package java sql for the JDBC API The SOLException class includes all runtime exceptions raised by Embedded SQLJ and classes you explicitly use such as java sql Date java sql ResultSet In package sqlj runtime for SOLJ runtime Chapter 4 The Embedded SQLJ Language 4 9 SQL stream types explicitly referenced for example BinaryStream the ConnectionContext class and the reference implementation of Embedded SQLJ classes in sglj runtime ref SQLJ Reserved Names This section lists names reserved by the SOLJ translator Do not use these names in your Embedded SQL programming Parameter Field and Variable Names The string _sJT is a reserved prefix for generated variable names Do not use this prefix for the names of Variables declared within blocks that include SOL statements Parameters to methods that contain SOL statements Fields in classes that contain SOL statements or whose subclasses contain SOL statements Class Names and Filenames Do not declare classes that conflict with the names of internal classes Do not create files that conflict with generated internal resource files The
5. If you have instructed the Java compiler to output its results to a file use the compiler output file option to specify the filename Specifies a list of JDBC drivers that can be used to interpret JDBC connection URLs for online checking see Online Checking on You specify a class name or a comma separated list of class names For example specify IBM Informix JDBC Driver as follows driver com informix jdbc IfxDriver Specifies a Java class to implement off line checking The default off line checker class is sqlj semantics OfflineChecker Off line checking only runs when online checking does not either because online checking was not enabled or because it stopped because of error Off line checking verifies SOL syntax and the usage of Java types With off line checking there is no connection to the database Specifies a Java class or list of classes to implement online checking The default online checker class is sqlj semantics JdbcChecker You can specify an online checker class for a particular connection context as in online ctxclass2 sqlj semantics JdbcChecker You must specify a user name with the user option for online checking to occur The password url and driver options must be appropriately set as well Specifies a password for the user name set with the user option If you specify the user option but not the password option the translator prompts you for the password If you are using multiple con
6. customer_num A An SQL statement that is not enclosed within curly braces will generate a syntax error SOL statements are case insensitive unless delimited by double quotes and can be written in uppercase lowercase or mixed case letters Java statements are case sensitive IBM Informix Embedded SOLJ User s Guide Host Variables Host variables are variables of the host language in this case Java that appear within SQL statements A host variable represents a parameter variable or field and is prefixed by a colon as in the following example sql ctx SELECT INTO customer WHERE customer num cust no You use the SELECT statement with the INTO as shown in this example the FETCH statement with the INTO clause described in page 4 3 or an accessor method described in Named Iterators on page 4 4 retrieve data into host variables to SELECT Statements That Return a Single Row You use the SELECT INTO statement for queries that return a single record of data For queries that return multiple rows called a result set you use an iterator object as described in the next section Handling Result Sets The SELECT INTO statement includes a list of host variables in the INTO clause to which the selected data is assigned For example sql SELECT INTO customer num fname lname company addressl address2 city state zipcode phone FROM customer WHERE customer_nu
7. 5 8 Option Description cache Turns on the caching of results from online checking Caching saves you from unnecessary connections to the database in subsequent runs of the translator for the same file Results are written to the file SOLChecker cache in your current directory The cache holds serialized representations of all SOL statements that translated without errors or warnings The cache is cumulative and grows through successive invocations of the translator You empty the cache by deleting the SOLChecker cache file Caching is off by default you turn caching on by setting the cache option to true 1 or on for example cache true You turn caching off by setting the option to false 0 or off IBM Informix Embedded SOLJ User s Guide compile Set this flag to false to disable processing of java files by the compiler This applies to generated java files and to java files specified on the command line compiler executable Specifies a particular Java compiler for the java ifxsqlj command to use If unset the translator uses javac If you do not specify a directory path the java ifxsqlj command searches for the executable according to the setting of your PATH environment variable compiler encoding flag Set this flag to false to prevent the value of the SOLJ encoding option from being automatically passed to the compiler compiler output file driver offline online password ser2class
8. Code D MGE s getErrorCode System err println Error Message 4 s getMessage IBM Informix Embedded SOLJ User s Guide Initialize database connection thru Connection Manager Demo03 ConnectionManager initContext void runDemo throws SQLException drop_db sql CREATE DATABASE demo sqlj WITH LOG MODE ANSI sgl create table customer customer_num serial 101 fname char 15 Iname char 15 company char 20 address1 char 20 address2 char 20 city char 15 state char 2 zipcode char 5 phone char 18 primary key customer_num E Insert 4 Records in a try block try sql INSERT INTO customer VALUES 101 Ludwig Pauli All Sports Supplies 213 Erstwild Court Sunnyvale CA 94086 408 789 8075 h sgl INSERT INTO customer VALUES 102 Carole Sadler Sports Spot 785 Geary St San Francisco CA 94117 415 822 1289 h sql INSERT INTO customer VALUES 103 Philip Currie Phil s Sports 654 Poplar P 0 Box 3498 Palo Alto CA 94303 415 328 4543 E sql INSERT INTO customer VALUES 104 Anthony Higgins Play Ball East Shopping Cntr 422 Bay Road Redwood City CA 94026 415 368 1100 h Chapter 3 Building an Embedded SQLJ Program 3 5 catch SQLException e System out println INSERT Exception e n System out println Error
9. Code s ue e getErrorCode System err println Error Message DO e getMessage System out printIn System out println Running demo program Demo03 System out printIn Declare Iterator of type CustRec CustRec cust_rec sql cust rec SELECT FROM customer int row_cnt 0 while cust rec next System out println System out println CUSTOMER NUMBER cust rec customer num System out println FIRST NAME cust rec fname System out println LAST NAME cust rec lname System out println COMPANY cust rec company System out println ADDRESS cust rec addressl n m cust_rec address2 System out printIn CITY cust rec city System out println STATE cust rec state System out println ZIPCODE cust rec zipcode System out println PHONE cust rec phone System out println System out printIn n n row_cnt System out println Total No Of rows Selected row cnt cust rec close System out printin Ininininn drop db d drop db throws SQLException try sql drop database demo sqlj catch SQLException s 3 6 IBM Informix Embedded SQL User s Guide Chapter 4 The Embedded SQLJ Language In This Chapter Lo We we ce A MATES A ow i NOU See esr Se Bek Embedded SQL Versus Traditional Embedded S
10. ES Bod We de do US Ws coo Ro e oo oce ox 92 Command Options e s s x x x 6 o3 Roo E e POOR AO RO we o O3 oe ek ce a a ox OD Basic Options s e woo ue o we de ea GS cR Uus A cx cw OA ose oS UR Tw d owe ox D Advanced Options s s s e es Gose sof m w e 4 OA Setting Options 0127021720100 70m Setting Options on the Command Line Hue in e a E Supplying Options in Property Files lt s lt s wos soe soa soso soso sos mos so o a 07 Precedence of Options 4 se 9 2 2 oo se BoP be a a e 27 Format of Property FileS s s cie a x ox oko Rok eoe RR sa 9S OR aog ce ae OO Online Checking vod 9 X oq whe we Be ea a a OW 9 79 o we XO Setting the user and password Options 3 wees 8 eee S som EU WU uS ue Ub ou Setting the url and driver Options 2 2 4 oso ae a sooo Rogo o oo o 0 9 Thedfxprofp Tool i4 os X cod Odo Woo me a oe e a a axe OA In This Chapter This chapter describes how to create executable Java programs from your Embedded SQLJ source code It explains e How to use the SQL translator Basic translation and compilation options Advanced translation and compilation options How to use property files How to perform online checking Translating Compiling and Running Embedded SQLJ Programs You use the command java ifxsqlj to create executable Java class files from your Embedded SQL source code When you run the java ifxsqlj command with an sqlj source file the source file is pr
11. ISO 9075 1992 In addition many features of IBM Informix database servers comply with the SQL 92 Intermediate and Full Level and X Open SOL Common Applications Environment CAE standards How to Provide Documentation Feedback viii You are encouraged to send your comments about IBM Informix user documentation by using one of the following methods Send e mail to docinfGus ibm com e Go to the Information Center at http publib boulder ibm com infocenter idshelp v115 index jsp and open the topic that you want to comment on Click Feedback at the bottom of the page fill out the form and submit your feedback Feedback from both methods is monitored by those who maintain the user documentation of Dynamic Server The feedback methods are reserved for reporting errors and omissions in our documentation For immediate help with a technical problem contact IBM Technical Support For instructions see the IBM Informix Technical Support Web site at http www ibm com planetwide We appreciate your suggestions IBM Informix Embedded SQLJ User s Guide Chapter 1 Introducing IBM Informix Embedded SQLJ In This Chapter c cs le dos 4 x u amp ao ue E ae e A amp el Whats Embedded SOLJ ss odo aca ee AA A A 2 ope o dS xe es ded How Does Embedded SQLJ Work 2 2 2 2 s s s S 1 Embedded SOLJ Versus JDBC s s e e a s s a po e s ag s s 12 In This Chapter This chapter explains what IBM Infor
12. SQL translator creates internal classes and resource files for use by generated code The names of these files and classes have a prefix composed of the name of the original input file followed by the string SJ For example if you translate a file called Filel sglj that uses the package COM foo the names of some of the internal classes produced are e COM foo File1 SJInternalClass e COM foo File1 SJProfileKeys e COM foo File1 SJInternalClass Inner e COM foo File1 SJProfile0 e COM foo File1_SJProfilel Generated files for these internal classes which are created in the same directory as the input file File1 sqlj are called e File1 SJInternalClass java includes the class COM foo File1_SJInternalClass Inner File1 SJProfileKeys java e File1_SJProfile0 ser File1 SJProfilel ser Files with the ser extension are internal resource files that contain information about SQL operations in an sqlj file Handling Errors 4 10 Some iterator and connection context methods might raise exceptions specified by the JDBC API SQLException class For information about using SQLException methods to obtain information about these errors refer to your JDBC API documentation IBM Informix Embedded SOLJ User s Guide Chapter 5 Processing Embedded SQLJ Source Code In This Chapter bc At er Sk Oe A i Se Sn ee del Translating Compiling and Running Embedded SQLJ Programs a A a ss al The ifxsql Command 3 R G x
13. System System System out print items rec item num t t out print items rec stock num t t out print items rec manu code t t System out print items rec quantity t t System out print items rec total price t t System out print An System out printIn n void drop_db try throws SQLException sql ctx drop database orders db sql ctx drop database items db catch SQLException s BRR KERRIER KER ERA RR RR RR KR KI RR RR K KAKI AKA KERIKERI KIKI KIRKE IBM Title E E E E E E E E E SH Se E E Se CEC CR UF CONFIDENCE DISCLOSED IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN AGREEMENT SIGNED BY AN OFFICER OF IBM CORPORATION Description IBM CORPORATION PROPRIETARY DATA THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF CORPORATION THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN INFORMATION CONTAINED HEREIN MAY NOT BE USED COPIED OR THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE UNITED STATES CODE UNAUTHORIZED USE COPYING OR OTHER REPRODUCTION IS PROHIBITED BY LAM demoUtil java Utilities used in the demo programs kkkkxkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk import java import java import java import java io util lang sql public class demoUtil private private priv
14. e null default nonull Specifies whether the translator checks nullable columns and nullable Java variable types for conversion loss when data is transferred between database columns and Java host variables The translator must connect to the database for this option to be in effect precision default noprecision Specifies whether the translator checks for loss of precision when data is transferred between database columns and Java variables The translator must connect to the database for this option to be in effect portable default noportable Turns on or off warning messages about the portability of Embedded SQL statements strict default nostrict Specifies whether the translator checks named iterators against the columns returned by a SELECT statement and issues a warning for any mismatches The translator must connect to the database for this option to be in effect verbose default noverbose Turns on or off additional information messages about the translation process The translator must connect to the database for this option to be in effect For example the following setting of the warn option turns off all warnings and then turns on the precision and nullability checks warn nonenull precision Advanced Options The following table lists the advanced options available for use with the java ifxsqlj command Many of these options are for online checking which is discussed in Online Checking on page
15. file location when translating or compiling your application You run your Embedded SQLJ program like any other Java program by using the Java interpreter as follows java Filel The ifxsqlj Command You use the java ifxsqlj command to translate and compile your Embedded SQL source code as described above You run the java ifxsqlj command at the DOS or UNIX prompt The syntax of the java ifxsqlj command is as follows java ifxsqlj optionlist filelist optionlist A set of options separated by spaces Some options have prefixes to indicate they are to be passed to utilities other than the SOLJ translator such as the Java compiler filelist A list of filenames separated by spaces for example filel sqlj file2 sqlj You must include the absolute or relative path to the files in filelist The files can have the extension sqlj or java You can specify sqlj files together with java files on the same command line If you have sqlj and java files that require access to code in each other s file enter all of these files on the command line for the same execution of the java ifxsqlj command You can use an asterisk as a wildcard to specify filenames for example c sqlj processes all files beginning with c that have the extension sglj When you run the java ifxsqlj command your CLASSPATH environment variable must be set to include any directories that contain class files and ser files the translator needs to acc
16. or set them in a property file user password url and driver These options are described in Advanced Options on page 5 4 Setting the user and password Options You enable online checking by setting the user option The user option also supplies the user name for the database connection to be used for checking You do not have to specify the same database or user name for online checking as the application uses at runtime In the simplest case you supply a user name with the user option and online checking is performed using the default connection context as in user joyce You can supply the password for the user name by using the password option or by combining the password with the user name for example user joyce jcs123 or user joyce password jcs123 To disable online checking on the command line set the user option to an empty value as in user or omit the option entirely To disable online checking in a property file comment out the line specifying sqlj user To enable online checking against a nondefault connection context you specify the connection context with the user name in the user option In the following example the SQLJ translator connects to the database specified in the connection context object conctx using the user name fred user conctx fred Setting the url and driver Options The url option specifies a JOBC URL for establishing a database connection see Databa
17. runtime SQLJ runtime classes import sqlj runtime ref Declare ConnectionContext classes OrdersCtx and ItemsCtx OrdersCtx is related to the orders table which is in orders db database ItemsCtx is related to the items table which is in items db database Instances of these classes are used to specify where SQL operations on orders table or items table shld should execute We create the 2 databases using a default context using ConnectionManager For an order from the orders table in the orders db database we try to query the items table in the items db database for the line items which make up that order A A ok OR A OR A ok 0k X sql context OrdersCtx sql context ItemsCtx Declare 2 named iterators for Items and Orders fsql iterator OrdersRec Appendix A Connecting to Databases A 3 Integer order_num Date order_date String po_num Date paid_date sql iterator ItemsRec Short item num int order num Short stock num String manu code Integer quantity BigDecimal total price public class MultiConnect extends demoUtil private OrdersCtx o ctx null private ItemsCtx i ctx null private DefaultContext ctx null The constructor sets up a default database context MultiConnect Initialize database connection thru Connection Manager and create a default context ctx ConnectionManager initContext public static void main String args throw
18. sqlj The sample program MultiConnect sqlj creates two databases with one table each Orders and Items and inserts two records in the Orders table and corresponding records in the Items table The program prints the order line items for all the orders from both tables which exist in different databases by creating separate connection contexts for each database MultiConnect sqlj calls the methods executeSQLScript and getConnect These methods are contained in demoUtil java which follows this program PARAR RR REIKI RIKER KAKA KER KAKA KARR KKK IK K K KE A RARA K K K K K RARA RARA IBM CORPORATION PROPRIETARY DATA THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF IBM CORPORATION THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN CONFIDENCE INFORMATION CONTAINED HEREIN MAY NOT BE USED COPIED OR DISCLOSED IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN AGREEMENT SIGNED BY AN OFFICER OF IBM CORPORATION THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE UNITED STATES CODE UNAUTHORIZED USE COPYING OR OTHER REPRODUCTION IS PROHIBITED BY LAW Title MultiConnect sqlj Description This demonstrates usage of 2 connection contexts using different URLs A A A A AECA OX OX OX AECA OR HF OX 0X HF HF ok HF X kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk import java sql import java math import java lang import sqlj
19. IBM Informix Version 3 50 ana OSDAL 1 NI A IBM Informix Embedded SQLJ User s Guide SC23 9414 00 IBM Informix Version 3 50 ana OSDAL 1 NI A IBM Informix Embedded SQLJ User s Guide SC23 9414 00 Note Before using this information and the product it supports read the information in Notices on page D 1 This document contains proprietary information of IBM It is provided under a license agreement and is protected by copyright law The information contained in this publication does not include any product warranties and any statements provided in this manual should not be interpreted as such When you send information to IBM you grant IBM a nonexclusive right to use or distribute the information in any way it believes appropriate without incurring any obligation to you O Copyright International Business Machines Corporation 1996 2008 All rights reserved US Government Users Restricted Rights Use duplication or disclosure restricted by GSA ADP Schedule Contract with IBM Corp Contents Introduction In This Introduction About This Publication Types of Users Software Dependencies Global Language Support Documentation Conventions Typographical Conventions Feature Product and Platform Markup Example Code Conventions Additional Documentation Compliance with Industry Standards How to Provide Documentation Feedback Chapter 1 id IBM Informix E
20. L myURL myURL user user password passwd public Connection getConnect connSetup try conn DriverManager getConnection myURL catch SQLException e System out println Connect Error e getErrorCode System out println Failed to connect e toString e printStackTrace return conn IBM Informix Embedded SOLJ User s Guide public Connection getConnect Connection i conn connSetup try i conn DriverManager getConnection myURL catch SQLException e System out println Connect Error e getErrorCode System out println Failed to connect e toString e printStackTrace return i conn Appendix A Connecting to Databases A 9 A 10 IBM Informix Embedded SOL User s Guide Appendix B Sample Programs The following table lists and describes the online sample programs that are included with IBM Informix Embedded SQL Demo Program Name Description Demo01 sqlj Demonstrates a simple connection to the database Demo02 sq j Demonstrates a simple SELECT statement and the use of host variables Demo03 sq j Demonstrates the use of a named iterator Demo04 sqlj Demonstrates the use of a positional iterator Demo05 sqlj Demonstrates interoperability between a JDBC ResultSet object and an SQL iterator Demo06 sqlj Demonstrates interoperability between a JDBC Connection object and an SOLJ connection context object The samp
21. LException if the iterator is already closed isClosed Returns TRUE after the iterator s close method has been called otherwise it returns FALSE Positional iterators also have the endFetch method The endFetch method returns TRUE when no more rows are available Named iterators also have the next method The next method advances processing to successive rows of the result set It returns FALSE after it fails to find a row to retrieve For an example of how to use the next method see Named Positioned Updates and Deletes To perform positioned updates and deletes in a result set you use the WHERE CURRENT OF clause with a host variable that contains an iterator object For example sql delete statement update statement WHERE CURRENT OF iter At runtime the variable iter must contain an open iterator object that contains a result set selected from the same table accessed by the query in either delete_statement or update_statement The current row of that iterator object is deleted or updated Monitoring the Execution of an SQL Query You can monitor and modify the execution of an SQL query by using the execution context associated with it An execution context is an instance of the class sqlj runtime ExecutionContext an execution context is associated with each executable SQL operation in an Embedded SQLJ program You can supply an execution context explicitly for an SQL statement sql execCtx SQL statem
22. QL Doma aa AA ss a arel Embedded SOLJ Source Files b A dp doom Um su a Ehe HA Ge GR dec diuum d OB wp Um cx cw 42 Identifying Embedded SOLJ Statements E oiod Rk OR ee Ok Ge ux UR GOES we e u uo AD SQL Statements oe Go 2 2 o 2 4 Host Variables be ohooh iy Uh Bee ete bot amp eic amp AR ue S de we we UR Oe GEO SELECT Statements That Return a Single Row bod ak ke oe bok Gh oS ok de geo Rd We OR ate ve ok MED Handling Result Sets eh A oS Ru NW um Me NUN sepu Ah OR Lus E ESO Positional Iterators 4 4 4 4 e so o a e on o o os s 43 Named Iterators 2 2 0 2 4 4 4 4 4 A Using Column Aliases 4 4 e 2 4 2 9 e x x0 Roo a 0 9 09 9 2 o 8 a a 4D Iterator Methods 44s Wb Wo We Ro d o X Ox 7A ome desde CAD Ep oom cO e 7 4x 4 0x 5 wo x ED Positioned Updates and Deletes E a BR a BR ee d RR me EUR e aG Monitoring the Execution of an SQL Query sokor eo s bos os A Calling SPL Routines and Functions lt lt a o 2 so s o s o LA SOL and Java Type Mappings a 47 Language Character Sets lt o 4 tos 4 s 2 s o3 oo RG tos ox ow d ee x 3 a e x AD Importing Java Packages 2 o s 4 e 4 9 c 0x o9 Roe Rok ox Ro ES 3S4 o ow o s 49 SOLJ Reserved Names SI d Wo ce de us we P AO e deo WU Wy o ox e e x ARO Parameter Field and Variable Names E um he A A HN DR OR 0E A a O Class Names and Filenames
23. These examples have not been thoroughly tested under all conditions IBM therefore cannot guarantee or imply reliability serviceability or function of these programs You may copy modify and distribute these sample programs in any form without payment to IBM for the purposes of developing using marketing or distributing application programs conforming to IBM s application programming interfaces IBM Informix Embedded SQLJ User s Guide Trademarks Each copy or any portion of these sample programs or any derivative work must include a copyright notice as follows O your company name year Portions of this code are derived from IBM Corp Sample Programs O Copyright IBM Corp enter the year or years All rights reserved If you are viewing this information softcopy the photographs and color illustrations may not appear IBM the IBM logo and ibm com are trademarks or registered trademarks of International Business Machines Corporation in the United States other countries or both These and other IBM trademarked terms are marked on their first occurrence in this information with the appropriate symbol or indicating US registered or common law trademarks owned by IBM at the time this information was published Such trademarks may also be registered or common law trademarks in other countries A current list of IBM trademarks is available on the Web at http www ibm com legal copytrade shtml Adobe Acrobat Port
24. a stored function by using the EXECUTE FUNCTION statement For example sql EXECUTE FUNCTION func name func arg into num SQL and Java Type Mappings When you retrieve data from a database into an iterator object see Handling Result Sets on page 4 3 or into a host variable you must use Java types that are compatible with the SOL types The following table shows valid conversions from SQL types to Java types Chapter 4 The Embedded SOLJ Language 4 7 4 8 SOL Type Java Type BIGINT BIGSERIAL bigint BLOB byte BOOLEAN boolean BYTE byte CHAR CHARACTER String CHARACTER VARYING String CLOB bytel DATE java sql Date DATETIME java sql Timestamp DECIMAL NUMERIC DEC java math BigDecimal FLOAT DOUBLE PRECISION double INT8 long INTEGER INT int INTERVAL IfxIntervalDF IfxIntervalYM LVARCHAR String MONEY java math BigDecimal NCHAR NVARCHAR String SERIAL int SERIAL8 long SMALLFLOAT float SMALLINT short TEXT String VARCHAR String TfxIntervalYM and IfxIntervalDF are Informix extensions to JDBC 2 0 This mapping is JDBC compliant You can use IBM Informix JDBC Driver to map SMALLFLOAT data type via the JDBC FLOAT data type to the Java double data type for backward compatibility by setting the IFX_GET_SMFLOAT_AS_FLOAT environment variable to 1 You must also use compatible Java types for host variables that are arguments t
25. able Document Format PDF and PostScript are either registered trademarks or trademarks of Adobe Systems Incorporated in the United States other countries or both Intel Intel logo Intel Inside Intel Inside logo Intel Centrino Intel Centrino logo Celeron Intel Xeon Intel SpeedStep Itanium and Pentium are trademarks or registered trademarks of Intel Corporation or its subsidiaries in the United States and other countries Linux is a registered trademark of Linus Torvalds in the United States other countries or both Microsoft Windows Windows NT and the Windows logo are trademarks of Microsoft Corporation in the United States other countries or both Java and all Java based trademarks and logos are trademarks of Sun Microsystems Inc in the United States other countries or both UNIX is a registered trademark of The Open Group in the United States and other countries Other company product or service names may be trademarks or service marks of others Notices D 3 D 4 IBM Informix Embedded SOL User s Guide Index Special characters SJT prefix 4 10 C prefix 5 7 cache option 5 4 compile option 5 5 compiler encoding flag option 5 5 compiler executable option 5 5 compiler output file option 5 5 d option 5 3 dir option 5 3 driver option 5 5 encoding option 5 3 help option 5 3 J prefix 5 7 linemap option 5 3 offline option 5 5 online option 5 5 password option 5 5 pro
26. ame name value A name value pair that specifies a value for the Informix environment variable contained in the name variable recognized by either IBM Informix JDBC Driver or Informix database servers The value of name is case insensitive For information about environment variables supported by IBM Informix JDBC Driver and how to set them refer to the IBM Informix JDBC Driver Programmer s Guide Using Nondefault Connection Contexts A 2 This section explains how to use nondefault connection contexts Embedded SOLJ uses a connection context object to manage the connection to the database in which you want an SQL statement to execute You can specify different connection context objects for different SOL statements in the same Embedded SQL program as shown in the sample program MultiConnect sqlj included in this section To use a nondefault connection context 1 Define the connection context class by using an Embedded SQLJ connection statement The syntax of the connection statement is as follows sql modifiers context java class name modifiers A list of Java class modifiers for example public IBM Informix Embedded SOLJ User s Guide java_class_name The name of the Java class of the new connection context 2 Create a connection context object for connecting to the database 3 Specify the connection context object in your Embedded SQL statement in parentheses following the sql string MultiConnect
27. an with the JDBC API e Compile time syntax and semantics checking The Embedded SQLJ translator checks the syntax of SQL statements Compile time type checking The Embedded SQL translator and the Java compiler check that the Java data types of arguments are compatible with the SQL data types of the SOL operation Compile time schema checking You can connect to a sample database schema during translation to check that your program uses valid SOL statements for the tables views columns stored procedures and so on in your sample IBM Informix Embedded SOLJ User s Guide Chapter 2 Preparing to Use Embedded SQLJ In This Chapter s Ree e e i EM e E What Components Do You Need gt so a 0 mo coso oao 21 Setting Up Your Software s e x ode 4 oh hos Se de Vecu Xe ox P XS OL de ae amp Del Examples pode ih us 2 1 In This Chapter This chapter describes the software you must have to develop Embedded SQLJ programs and how to set up this software What Components Do You Need You need the following software to create and run SQL programs e BM Informix Embedded SQL The JavaSoft software Java Development Kit JDK Version 1 2 or later or any Java software compatible with JDK 1 2 also known as Java 2 BM Informix JDBC Driver Version 2 0 or later to enable your programs to connect to the database server One of the following Informix database servers Informix Dynamic Serv
28. anslation and Compilation of an Embedded SQLJ Program When you run your program it uses the IBM Informix JDBC Driver to connect to an Informix database as shown in Copyright IBM Corp 1996 2008 1 1 Java executable Calls to JDBC Results from the database JDBC Driver Result sets for SELECT statements Queries and other SQL statements gt Figure 1 2 Runtime Architecture for Embedded SQLJ Programs See the IBM Informix JDBC Driver Programmer s Guide for information about using the IBM Informix JDBC Driver Embedded SQLJ Versus JDBC 1 2 Embedded SQLJ does not support dynamic SQL you must use the JDBC API if you want to use dynamic SQL Your Embedded SQLJ program can call the JDBC API to perform a dynamic operation the SOLJ connection context object that you use to connect an Embedded SQLJ program to the database contains a JDBC Connection object that you can use to create JDBC statement objects If you are using static SOL Embedded SQL provides the following advantages Default connection context You only need to set the default connection context once within a program then every subsequent Embedded SQLJ statement uses this connection context unless you specify otherwise Reduced statement complexity For example you do not need to explicitly bind each variable Embedded SQL performs binding for you Generally this feature allows you to create smaller programs th
29. ate private private private private private private private private private private private String driver String URL String myURL String user String passwd int count 0 int lineno 0 int errors 0 boolean end of file false FileInputStream fs null DataInputStream in null BufferedReader br null String line null StringBuffer read_line null public Connection conn A 6 IBM Informix Embedded SQLJ User s Guide public void executeSQLScript String SQLscript try fs new FileInputStream SQLscript catch Exception e System out println Script File Not Found e printStackTrace in new DataInputStream fs br new BufferedReader new InputStreamReader in line getNextLine read line line null new StringBuffer new StringBuffer line while lend of file if line null 88 line indexOf line length 1 tryExecute read line read line new StringBuffer line getNextLine if line nul1 read line append line append if read line null amp amp read_line length gt 0 tryExecute read_line System out printIn n private boolean isComment String s if s null s trim return s null s equals s length gt 2 amp amp s substring 0 2 equals s length gt 4 amp amp s substring 0 4 toUpperCase equals REM private Strin
30. ced Options Setting Options Setting Options on the Command line Supplying Options in Property Files Online Checking Setting the user and password Options Setting the url and driver Options The ifxprofp Tool Appendix A Connecting to Databases Appendix B Sample Programs Appendix C Accessibility Accessibility features for IBM Informix Dynamic ovd Accessibility Features Keyboard Navigation Related Accessibility Inform tica IBM and Accessibility Notices Trademarks Index iv IBM Informix Embedded SOL User s Guide 4 10 4 10 4 10 4 10 52 5 7 C4 Introduction In This Introduction About This Publication Types of Users Software Dependencies Global Language Support Documentation Conventions Typographical Conventions Feature Product and Platform Markup Example Code Conventions Additional Documentation Compliance with Industry Standards How to Provide Documentation Feedback vii viii viii In This Introduction This introduction provides an overview of the information in this manual and describes the conventions it uses About This Publication Types This publication contains information about using IBM Informix Embedded SQLJ This section discusses the intended audience and the associated software products that you must have to use IBM Informix Embedded SQLJ of Users This guide is for programmers who wa
31. cense inquiries regarding double byte DBCS information contact the IBM Intellectual Property Department in your country or send inquiries in writing to IBM World Trade Asia Corporation Licensing 2 31 Roppongi 3 chome Minato ku Tokyo 106 0032 Japan The following paragraph does not apply to the United Kingdom or any other country where such provisions are inconsistent with local law INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS PUBLICATION AS IS WITHOUT WARRANTY OF ANY KIND EITHER EXPRESS OR IMPLIED INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF NON INFRINGEMENT MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE Some states do not allow disclaimer of express or implied warranties in certain transactions therefore this statement may not apply to you This information could include technical inaccuracies or typographical errors Changes are periodically made to the information herein these changes will be incorporated in new editions of the publication IBM may make improvements and or changes in the product s and or the program s described in this publication at any time without notice Any references in this information to non IBM Web sites are provided for convenience only and do not in any manner serve as an endorsement of those Web sites The materials at those Web sites are not part of the materials for this IBM product and use of those Web sites is at your own risk IBM may use or distribute any
32. code String phone This iterator can hold the result set of any query that returns the columns defined in the iterator class You use accessor methods of the same name as each iterator column to obtain the returned data as shown in the example in Embedded SQLJ Program on page 3 3 The SOLJ translator uses the iterator IBM Informix Embedded SOLJ User s Guide column names to create accessor methods Iterator column names are case sensitive therefore you must use the correct case when you specify an accessor method You cannot use the FETCH INTO statement with named iterators The following example illustrates the use of named iterators Declare Iterator of type CustRec CustRec cust rec sql cust rec SELECT FROM customer int row cnt 0 while cust rec next System out pri ntln U s s s s System out println CUSTOMER NUMBER cust rec customer num System out println FIRST NAME cust rec fname System out println LAST NAME cust rec lname System out println COMPANY cust rec company System out printIn ADDRESS cust rec addressl n j cust_rec address2 System out println CITY cust rec city System out println STATE cust rec state System out println ZIPCODE cust rec zipcode System out println PHONE cust rec phone System out println
33. context 4 6 F FETCH statement 3 3 4 1 4 3 4 4 file encoding property 4 9 5 3 Files ser 5 1 5 5 5 9 ConnectionManagerjava 3 1 ibjdbcjar 5 2 ifxsqlj jar 5 1 ifxtoolsjar 5 2 iterator name class 5 1 java properties 4 9 profilekeys class 5 1 Property files 5 7 SOLChecker cache 5 4 sqlj properties 5 7 Functions 4 7 G getExecutionContext method 4 6 getMaxFieldSize method 4 7 getMaxRows method 4 7 getQueryTimeout method 4 7 getSQLWarnings method 4 7 getUpdateCount method 4 7 GLS feature 5 3 H Host variables 3 2 4 1 4 3 IBM Informix JDBC Driver 1 1 2 1 A 1 ifxidbc jar file 5 2 ifxprofp tool 5 9 ifxsqlj command 5 1 ifxsqljjar file 5 1 ifxtools jar file 5 2 Informix database servers 2 1 INFORMIXSERVER environment variable A 2 initContext method 3 1 3 4 A 1 Internal resource files 5 1 IP addresses setting in database URLs A 2 isClosed method 4 6 Iterator objects 3 2 3 4 4 1 4 3 iterator_name class file 5 1 J Java compiler 1 1 Java Development Kit JDK 2 1 Java interpreter 5 2 Java types 4 7 java properties file 4 9 JDBC 1 1 1 2 4 9 5 5 5 6 L Language character sets 4 9 Latin 1 character set 4 9 Line numbers 5 3 M main method 3 4 MultiConnect sqlj program A 2 Multiple database connections 4 1 N Name value pairs of database URLs A 2 Named iterators 3 3 4 4 native2ascii tool 4 9 newConnection method A 1 next method 4 5 4 6 Nondefault connec
34. default name of a property file is sqlj properties you can specify a different name by using the props option on the command line see Basic Options on page 5 3 You cannot use a property file to specify The props help and version basic options The vm advanced option Options with the prefix J for passing options to the Java interpreter Precedence of Options The java ifxsqlj command checks for the existence of files called sqlj properties in the following directories in the following order Chapter 5 Processing Embedded SQL Source Code 5 7 1 The Java home directory 2 Your home directory 3 The current directory The translator processes each property file it finds and overrides any previously set option if it finds a new setting for that option Later entries in the same property file override earlier entries Options on the command line override options set by property files If you set options on the command line or in a property file specified using the props option these options override any options set in sqlj properties files Format of Property Files In a property file you Specify one option per line Begin a line with the symbol to denote a comment Tip The translator ignores empty lines The syntax for specifying options is the same as shown in Command Options on page 52 5 2 except you replace the initial hyphen with a string followed by a period that indicates to which uti
35. desde e go Fundamentals of Embedded SQL s s 2 2 2 2 2 BD SQL Statement Identifier 2 2 2 2 2 2 s s s s s s s s s s 94d Connecting toa Database s s s s o s 2 x Soa Bopo ogo osoa Go k Goa dm S noe og oe a la a BL Embedding SOL Statements lt 4 2 2 4 9 ow 9 aokoa Re kee ee ee ww ox s BD Handling Result Sets So Be ee ee ks eee SUBIRE HET ee ee US 3 2 Positional Iterators o BB Named Iterators oo See in ee ee a a Oe GO mE UM NP BI A Simple Embedded SQL Programa 33 In This Chapter This chapter explains the fundamentals of building an Embedded SQL program and includes a demonstration program Fundamentals of Embedded SQLJ This chapter introduces simple Embedded SQL statements see Chapter 4 The Embedded SQL Language on page 4 1 for detailed information about the language SQLJ Statement Identifier Each SQL statement in an Embedded SQL program is identified by sql at the beginning of the statement The SQL translator recognizes sql and translates the rest of the statement into Java code using JDBC calls Connecting to a Database You can use a class called ConnectionManager located in a file in the demo sq lj directory to initiate a JDBC connection The ConnectionManager class uses a JDBC driver and a database URL to connect to a database Database URLs are described in Database URLs on page A 1 To enable your Embedded SQLJ pr
36. ences 4 9 Updates positioned 4 6 User names setting in database URLs A 2 W WHENEVER GOTO CONTINUE statement 4 1 WHERE CURRENT OF clause 4 6 Printed in USA SC23 9414 00
37. ent If you do not explicitly supply an execution context the SOL statement uses the default execution context for the connection context you are using If you want to supply an explicit connection context and an explicit execution context the SOL statement looks like this sql connCtx execCtx SQL statement You use the getExecutionContext method of the connection context to obtain that connection s default execution context The execution context object has attributes and methods that provide information about an SQL operation and the ability to modify its execution For each of the following attributes there is a method called getattribute that reads the value of the attribute and a method called setattribute that sets its value The attributes are 4 6 IBM Informix Embedded SQLJ User s Guide Attribute Description MaxRows The maximum number of rows a query can return MaxFieldSize The maximum number of bytes that can be returned as data for any column or output variable QueryTimeout The number of seconds to wait for an SOL operation to complete SOLWarnings Any warnings that occurred during the last SOL operation UpdateCount The number of rows updated inserted or deleted during the last SOL operation Calling SPL Routines and Functions You can call a Stored Procedure Language SPL procedure by using the EXECUTE PROCEDURE statement For example sql EXECUTE PROCEDURE proc name arg name You can call
38. er Version 9 x and later BM Informix Extended Parallel Server Version 8 x IBM Informix Dynamic Server IDS Version 7 x IBM Informix Dynamic Server IDS Workgroup and Developer editions Version 7 x IBM Informix OnLine Dynamic Server Version 5 x BM Informix SE Versions 5 x to 7 2x Setting Up Your Software Before you install Embedded SQLJ you must already have installed the JavaSoft software Java Development Kit JDK Version 1 2 or later For more information about the Java language see the JavaSoft Web site at http java sun com For further information about installing and using IBM Informix JDBC Driver see the IBM Informix JDBC Driver Programmer s Guide If you do not already have your Informix server installed refer to the IBM Informix Installation Guide that accompanies that software Examples IBM Informix Embedded SQLJ includes sample online programs in the demo sq j directory The README file in this directory briefly explains what each of the programs demonstrates and how to set up compile and run the programs The programs also enable you to verify that IBM Informix Embedded SQLJ and IBM Informix JDBC Driver are correctly installed The examples in this manual are taken from these sample programs O Copyright IBM Corp 1996 2008 2 1 2 2 IBM Informix Embedded SQL User s Guide Chapter 3 Building an Embedded SQLJ Program In This Chapter oca wax deo a ae E A E dS SO Rs
39. ess for type resolution of variables in your Embedded SOLJ source code Command Options 5 2 Many options are available to customize how you run the java ifxsqlj command Basic options are described in the next section e Advanced options are described in Advanced Options on page 5 4 You can set options either on the command line or in property files Options set on the command line can be passed to the SQLJ translator the Java compiler or the Java interpreter Options set in property files can be passed to the SQLJ translator IBM Informix Embedded SOLJ User s Guide or the Java compiler but not to the Java interpreter For more information see Basic Options e 5 7 and The following table lists the basic options available for use with the java ifxsqlj command Option d dir encoding help linemap props status version Description Specifies the root output directory for generated ser and class files If you do not specify this option files are generated under the directory of the input sqlj file Specifies the root output directory for generated java files If you do not specify this option files are generated under the directory of the input sqlj file Specifies the GLS encoding for sqlj and java input files and for java generated files If unspecified the setting of the file encoding property for the Java interpreter is used The encoding option is also passed
40. from the suppliers of those products their published announcements or other publicly available sources IBM has not tested those products and cannot confirm the accuracy of performance compatibility or any other claims related to non IBM products Questions on the capabilities of non IBM products should be addressed to the suppliers of those products All statements regarding IBM s future direction or intent are subject to change or withdrawal without notice and represent goals and objectives only All IBM prices shown are IBM s suggested retail prices are current and are subject to change without notice Dealer prices may vary This information contains examples of data and reports used in daily business operations To illustrate them as completely as possible the examples include the names of individuals companies brands and products All of these names are fictitious and any similarity to the names and addresses used by an actual business enterprise is entirely coincidental COPYRIGHT LICENSE This information contains sample application programs in source language which illustrate programming techniques on various operating platforms You may copy modify and distribute these sample programs in any form without payment to IBM for the purposes of developing using marketing or distributing application programs conforming to the application programming interface for the operating platform for which the sample programs are written
41. g getNextLine String line null lineno try line br readLine if line nul1 end_of_file true catch IOException e line null end_of_file true return isComment line null line private String bufferToCommand StringBuffer sb String s sb toString trim chop off trailing semicolon if s substring s length 1 s length equals Appendix A Connecting to Databases A 8 s s substring 0 s length 1 return s private void tryExecute StringBuffer sb String cmd bufferToCommand sb System out print System out flush try count Statement stmt conn createStatement stmt executeUpdate cmd stmt close catch SQLException e errors System out println SQL Error line lineno e getMessage System out println SQLState e getSQLState System out println ErrorCode e getErrorCode System out println Offending statement cmd e printStackTrace public void set_driver String driver this driver driver public void set_url String url this URL url public void set_user String userName this user userName public void set_passwd String passwd this passwd passwd public void connSetup try Class forName driver catch Exception e System out println Failed to load IBM Informix JDBC driver e printStackTrace myURL UR
42. h the translator connects to the database see Online Checking on For example to enable online checking on the default connection context and connect with the user name fred use the following option user fred If you are using multiple connection contexts the setting for user for the default connection context also applies to any connection context that does not have a specific setting If you want to enable online checking for the default context but turn off online checking for another connection for example ctxcon2 you need to specify the user option twice user fred user ctxcon2 To enable online checking for a particular connection context specify that context with the user name as in user ctxcon3 joyce The classes of the connection contexts you specify must all be declared in your source code or previously compiled into a class file Specifies a particular Java interpreter for the java ifxsqlj command to use You must also include the path to the interpreter If you do not specify a particular Java interpreter using this option the translator uses java as a default The vm option must be specified on the command line you cannot set it in a property file IBM Informix Embedded SOLJ User s Guide Setting Options You specify options for the java ifxsqlj command either on the command line or in a property file Command line options are discussed in Property files are discussed in g Propert
43. le programs are located in the IFXJLOCATION demo sqlj directory IFXJLOCATION refers to the directory where you chose to install Embedded SQLJ The README file in the directory explains how to compile and run the programs O Copyright IBM Corp 1996 2008 B 1 B 2 IBM Informix Embedded SQLJ User s Guide Appendix C Accessibility IBM strives to provide products with usable access for everyone regardless of age or ability Accessibility features for IBM Informix Dynamic Server Accessibility features help a user who has a physical disability such as restricted mobility or limited vision to use information technology products successfully Accessibility Features The following list includes the major accessibility features in IBM Informix Dynamic Server These features support Keyboard only operation Interfaces that are commonly used by screen readers The attachment of alternative input and output devices Tip The IBM Informix Dynamic Server Information Center and its related publications are accessibility enabled for the IBM Home Page Reader You can operate all features using the keyboard instead of the mouse Keyboard Navigation This product uses standard Microsoft Windows navigation keys Related Accessibility Information IBM is committed to making our documentation accessible to persons with disabilities Our publications are available in HTML format so that they can be accessed with assistive technol
44. lity the option is passed You can pass options to the SOLJ translator or the Java compiler however you cannot pass options to the Java interpreter from a property file The strings for specifying utilities are as follows Precede an option with To pass it to this utility sqlj SQLJ translator compile Java compiler An example property file looks like this Turn on online checking and specify the user to connect with sqlj user joyce sqlj password x x x x x JDBC Driver to connect with sqlj driver com informix jdbc IfxDriver Database URL sqlj url jdbc ipaddr portno demo isqlj informixserver INFORMIXSERVER Instruct the compiler to output status messages during compile compile verbose Online Checking 5 8 Online checking analyzes the validity of the embedded SQL statements against the database schema user name password and database you specify Online checking performs the following operations Passes SQL data manipulation statements DML to the database to verify their syntax and semantics and their validity for the database schema Checks stored procedures and functions for overloading Runs the checks covered by off line checking IBM Informix Embedded SOLJ User s Guide Off line checking verifies SOL syntax and usage of Java types there is no connection to a database for off line checking To set up online checking you use the following options with the java ifxsqlj command
45. ls gt Options means choose the Options item from the Tools menu Feature Product and Platform Markup Feature product and platform markup identifies paragraphs that contain feature specific product specific or platform specific information Some examples vi IBM Informix Embedded SQLJ User s Guide of this markup follow Dynamic Server Identifies information that is specific to IBM Informix Dynamic Server End of Dynamic Server Windows Only Identifies information that is specific to the Windows operating system End of Windows Only This markup can apply to one or more paragraphs within a section When an entire section applies to a particular product or platform this is noted as part of the heading text for example Table Sorting Windows Example Code Conventions Examples of SQL code occur throughout this publication Except as noted the code is not specific to any single IBM Informix application development tool If only SOL statements are listed in the example they are not delimited by semicolons For instance you might see the code in the following example CONNECT TO stores_demo DELETE FROM customer WHERE customer_num 121 COMMIT WORK DISCONNECT CURRENT To use this SQL code for a specific product you must apply the syntax rules for that product For example if you are using an SQL API you must use EXEC SQL at the start of each statement and a semicolon
46. m 101 h The number of selected expressions must match the number of host variables The SQL types must be compatible with the host variable types If you use online checking the SOLJ translator checks that the order number and types of the SOL expressions and host variables match For information on how to perform online checking see Online Checking on page 5 8 Handling Result Sets Embedded SQLJ uses iterator objects to manage result sets returned by SELECT statements A result set iterator is a Java object from which you can retrieve the data returned from the database Iterator objects can be passed as parameters to methods and manipulated like other Java objects Important Names of iterator classes must be unique within an application When you declare an iterator object you specify a set of Java variables to match the SOL columns that your SELECT statement returns There are two types of iterators positional and named Positional Iterators The order of declaration of the Java variables in a positional iterator must match the order in which the SOL columns are returned For example the following statement generates a positional iterator class called CustIter with six columns fsql iterator CustIter int String String String String String Chapter 4 The Embedded SQLJ Language 4 3 4 4 This iterator can hold the result set from the following SELECT statement SELECT customer num fname Iname addre
47. mbedded SQLJ In This Chapter What Is Embedded SQLJ How Does Embedded SOLJ Work Embedded SQLJ Versus JDBC Chapter 2 Preparing to Use Embedded SQLJ In This Chapter What Components Do You Need Setting Up Your Software Examples Chapter 3 Building an Embedded SQLJ EEOAE In This Chapter Fundamentals of Embedded SOL SQL Statement Identifier Connecting to a Database Embedding SQL Statements Handling Result Sets ENS A Simple Embedded SOLJ Program Chapter 4 The Embedded SQLJ Language In This Chapter Embedded SQL Versus Traditional Embedded SOL Embedded SQLJ Source Files CIN Identifying Embedded SOLJ Statements SQL Statements RC amp g Host Variables SELECT Statements That Ret m a Single Bow Handling Result Sets Au ur ee A Positional Iterators Named Iterators Using Column Aliases Iterator Methods Positioned Updates and Deletes Monitoring the Execution of an SQL Query Calling SPL Routines and Functions SQL and Java Type Mappings Language Character Sets Importing Java Packages Copyright IBM Corp 1996 2008 3 1 41 42 43 43 iii SOLJ Reserved Names 2 Parameter Field and Variable Names Class Names and Filenames Handling Errors Chapter 5 Processing Embedded SQLJ Source Code In This Chapter Translating Compiling and Running Embedded SOL Programs The ifxsqlj Command 2 5 zocAO xc Command Options Basic Options Advan
48. mix Embedded SQLJ allows you to do and provides an overview of how it works What Is Embedded SQLJ IBM Informix Embedded SQLJ enables you to embed SQL statements in your Java programs IBM Informix Embedded SQLJ consists of e The SOIJ translator which translates SQLJ code into Java code e A set of Java classes that provide runtime support for SQLJ programs IBM Informix Embedded SQLJ includes the standard SQLJ implementation as defined by the SQLJ consortium plus specific Informix extensions The rest of this manual refers to IBM Informix Embedded SQLJ as Embedded SQLJ The standard SQLJ implementation is referred to as traditional Embedded SQLJ How Does Embedded SQLJ Work When you use Embedded SQLJ you embed SQL statements in your Java source code You use the SQLJ translator to convert the embedded SQL statements to Java source code with calls to JDBC JDBC is the JavaSoft specification of a standard application programming interface API that allows Java programs to access database management systems Finally you use the Java compiler to compile your translated Java program into an Figure 1 1 executable Java class file as shown in SQLJ Source Java source code Java Source Java source code Java class File Java byte codes with embedded with callsto with calls to SQL SQLJ JDBC Java JDBC translator compiler Figure 1 1 Tr
49. nection contexts the setting for password for the default connection context also applies to any connection context that does not have a specific setting Set this flag to true to convert the generated ser files to class files This is necessary if you are creating an applet to be run from a browser such as Netscape 4 0 that does not support loading a serialized object from a resource file Chapter 5 Processing Embedded SQLJ Source Code 5 5 5 6 url user The original ser file is not saved Specifies a JDBC URL for establishing a database connection for online checking Database URLs on page A 1 and Online Checking on page 5 8 The URL can include a host name a port number and an Informix database name The format is jdbc informix sqli ip address lt domain name gt lt port number gt lt dbname gt INFORMIXSERVER lt server name gt user lt username gt password lt password gt lt name gt lt value gt lt name gt lt value gt If you are using multiple connection contexts the setting for url for the default context also applies to any connection context that does not have a specific setting You can specify a URL for a particular connection context as in urlOctxclass2 Any connection context with a URL must also have a user name set for it using the user option for online checking to occur Enables online checking and specifies the user name with whic
50. ng statement generates a named iterator class called CustRec sql iterator CustRec int customer num String fname String lname String company String addressl String address2 String city String state String zipcode String phone This iterator class can hold the result set of any query that returns the columns defined in the iterator class The result set from the query can have more columns than the iterator class but the iterator class cannot have more columns than the result set For example this iterator class can hold the result set of the following query because the iterator columns include all of the columns in the customer table SELECT FROM customer A Simple Embedded SQLJ Program This sample program Demo03 sqlj demonstrates the use of a named iterator to retrieve data from a database This simple program outlines a standard sequence for many Embedded SQL programs 1 Import necessary Java classes 2 Declare an iterator class Chapter 3 Building an Embedded SOLJ Program 3 3 3 4 3 Define the main method All Java applications have a method called main which is the entry point for the application where the interpreter starts executing the program 4 Connect to the database The constructor of the application makes the connection to the database by calling the initContext method of the ConnectionManager class DN O y Run queries Create an iterator object and populate i
51. ns of iterator objects by name through generated accessor methods You can also access this data by position using the FETCH INTO statement as used by traditional embedded SQL O Copyright IBM Corp 1996 2008 4 1 Unlike other host languages Java allows null data Therefore you do not need to use null indicator variables with Embedded SQLJ Embedded SQLJ does not include dynamic SOL you must use JDBC instead The rest of this chapter describes how to use the Embedded SQLJ language Embedded SQLJ Source Files The files containing your Embedded SQL source code must have the extension sqlj for example custapp sqlj Identifying Embedded SQLJ Statements To identify Embedded SQLJ statements to the SOLJ translator each SOL statement must begin with sql The SQL translator recognizes sql and translates the statement into Java code SQL Statements 4 2 Embedded SQLJ supports SOL statements at the SOL92 Entry level with the following additions The EXECUTE PROCEDURE statement for calling SPL routines and user defined routines The EXECUTE FUNCTION statement for calling stored functions The BEGIN END block SQL statements must appear within curly braces as follows sql create table customer customer_num serial 101 fname char 15 Tname char 15 company char 20 addressl char 20 address2 char 20 city char 15 state char 2 zipcode char 5 phone char 18 primary key
52. nt to write Java programs that can Connect to Informix databases Issue SQL statements to manipulate data in the database This manual is written with the assumption that you have the following background A working knowledge of your computer your operating system and the utilities that your operating system provides Experience with the Java programming language Experience working with relational databases or exposure to database concepts Experience with the SOL query language If you have limited experience with relational databases SOL or your operating system refer to the IBM Informix Dynamic Server Getting Started Guide for your database server for a list of supplementary titles Software Dependencies Copyright IBM Corp 1996 2008 To run IBM Informix Embedded SQL programs you must use one of the following database servers Informix Dynamic Server Version 9 x and later e IBM Informix Extended Parallel Server Version 8 x BM Informix Dynamic Server IDS Version 7 x BM Informix Dynamic Server IDS Workgroup and Developer editions Version 7 x e IBM Informix OnLine Dynamic Server Version 5 x BM Informix SE Versions 5 x to 7 2x To enable your programs to connect to the server you must use IBM Informix JDBC Driver Version 2 0 or later You must use the JavaSoft software Java Development Kit JDK Version 1 2 or later or any Java software compatible with JDK 1 2 to create y
53. o SQL operations This table shows valid conversions from Java types to SQL types IBM Informix Embedded SOLJ User s Guide Java Type SOL Type java math BigDecimal DECIMAL boolean BOOLEAN byte BYTE java sql Date DATE double FLOAT float SMALLFLOAT int INT long INT8 short SMALLINT String CHAR java sql Time DATETIME java sql Timestamp DATETIME com informix jdbc IfxIntervalDF INTERVAL com informix jdbc IfxIntervalYM INTERVAL This mapping is JDBC compliant You can use IBM Informix JDBC Driver to map the Java double data type via the JDBC FLOAT data type to the Informix SMALLFLOAT data type for backward compatibility by setting the IFX GET SMFLOAT AS FLOAT environment variable to 1 Important Unlike other host languages for example C Java allows null data Therefore you do not need to use null indicator variables with Embedded SQLJ The Java null value is equivalent to the SOL NULL value Language Character Sets Embedded SQLJ supports Java s Unicode escape sequences Also if you set your Java property file encoding to 8859 1 or do not set it at all you can use the Latin 1 character set To process files with a different encoding for example SJIS you have the following choices Use the Sun JDK tool native2ascii to convert the native encoded source to a source with ASCII encoding Set file encoding SJIS in java properties in the Java home directory
54. ocessed in two stages In the first stage called translation the SQLJ translator creates a Java source file with the extension java For example when you process a file called File1 sqlj the SOLJ translator creates a file called File1 java The SOLJ translator also creates internal resource files with the extension ser In the second stage of processing the SOLJ translator passes java files to a Java compiler Compilation creates files with the extension class in this example your compiled Java program is called File1 class An internal resource file named profilekeys class is also created If your program includes an iterator a file called iterator name class is produced Tip To perform translation only execute the java ifxsqlj command with the compile option set to FALSE For information about the compile option see Advanced Options on page 5 4 To create a complete application you must include the directories that contain the SQL runtime classes in sqlj runtime in your CLASSPATH environment variable O Copyright IBM Corp 1996 2008 5 1 definition The SQL runtime files are available in ifxsqlj jar the file that you installed when you first installed the Embedded SQLJ product as described in In addition you must include the locations of ifxtools jar and the relevant version of the JDK in your CLASSPATH definition At runtime you must also include the location of ifxjdbc jar however you do not need to include this
55. of the information you supply in any way it believes appropriate without incurring any obligation to you Licensees of this program who wish to have information about it for the purpose of enabling i the exchange of information between independently created O Copyright IBM Corp 1996 2008 D 1 D 2 programs and other programs including this one and ii the mutual use of the information which has been exchanged should contact IBM Corporation J46A G4 555 Bailey Avenue San Jose CA 95141 1003 USA Such information may be available subject to appropriate terms and conditions including in some cases payment of a fee The licensed program described in this information and all licensed material available for it are provided by IBM under terms of the IBM Customer Agreement IBM International Program License Agreement or any equivalent agreement between us Any performance data contained herein was determined in a controlled environment Therefore the results obtained in other operating environments may vary significantly Some measurements may have been made on development level systems and there is no guarantee that these measurements will be the same on generally available systems Furthermore some measurements may have been estimated through extrapolation Actual results may vary Users of this document should verify the applicable data for their specific environment Information concerning non IBM products was obtained
56. ogram to connect to a database you assign values to the following data members of the ConnectionManager class in the file demo sqlj ConnectionManager java Member Description UID The user name PWD The password for the user name DRIVER The JDBC driver DBURL The URL for the database You must include the directory that contains your ConnectionManager class file produced when you compile ConnectionManager java in your CLASSPATH environment variable definition Your Embedded SQLJ program connects to the database by calling the initContext method of the ConnectionManager class as follows ConnectionManager initContext O Copyright IBM Corp 1996 2008 3 1 3 2 The ConnectionManager Class on page A 1 provides details about the functionality of the initContext method As an alternative to using the ConnectionManager class you can write your own input methods to read the values of user name password driver and database URL from a file or from the command line The connection context that you set up is the default connection context all sql statements execute within this context unless you specify a different context For information about using nondefault connection contexts see Using Nondefault Connection Contexts on page A 2 Embedding SQL Statements Embedded SQL statements can appear anywhere that Java statements can legally appear SQL statements must appear within curly braces as follows sql
57. ogy such as screen reader software The syntax diagrams in our publications are available in dotted decimal format You can view the publications for IBM Informix Dynamic Server in Adobe Portable Document Format PDF using the Adobe Acrobat Reader IBM and Accessibility See the IBM Accessibility Center at http www ibm com able for more information about the commitment that IBM has to accessibility Copyright IBM Corp 1996 2008 C 1 C 2 IBM Informix Embedded SQLJ User s Guide Notices IBM may not offer the products services or features discussed in this document in all countries Consult your local IBM representative for information on the products and services currently available in your area Any reference to an IBM product program or service is not intended to state or imply that only that IBM product program or service may be used Any functionally equivalent product program or service that does not infringe any IBM intellectual property right may be used instead However it is the user s responsibility to evaluate and verify the operation of any non IBM product program or service IBM may have patents or pending patent applications covering subject matter described in this document The furnishing of this document does not give you any license to these patents You can send license inquiries in writing to IBM Director of Licensing IBM Corporation North Castle Drive Armonk NY 10504 1785 U S A For li
58. ontexts The ConnectionManager Class You use the ConnectionManager class to make a connection to a database as described in Connecting to a Database on page 3 1 The ConnectionManager class has two methods newConnection initContext The newConnection method creates and returns a new JDBC Connection object using the current values of the DRIVER DBURL UID and PWD attributes If any of the needed attributes is null or a connection cannot be established an error message is printed to System out and the program exits The initContext method returns the currently installed default context If the current default context is null a new default context instance is created and installed using a connection obtained from a call to getConnection Database URLs The DBURL data member of the ConnectionManager class and the value for the url option that you specify for online checking are database URLs For information about online checking see Online Checking on page 5 8 Database URLs specify the subprotocol the database connectivity mechanism the database or server identifier and a list of properties Your Embedded SQL program uses IBM Informix JDBC Driver to connect to an Informix database IBM Informix JDBC Driver supports database URLs of the following format jdbc informix sqli ip address host name port number dbname INFORMIXSERVER server name user user password passwordl name val
59. or other appropriate delimiter at the end of the statement If you are using DB Access you must delimit multiple statements with semicolons Tip Ellipsis points in a code example indicate that more code would be added in a full application but it is not necessary to show it to describe the concept being discussed For detailed directions on using SQL statements for a particular application development tool or SOL API see the documentation for your product Additional Documentation You can view search and print all of the product documentation from the IBM Informix Dynamic Server information center on the Web at publib boulder ibm com infocenter idshelp v115 index jsp For additional documentation about IBM Informix Dynamic Server and related products including release notes machine notes and documentation notes go to the online product library page at http www ibm com software data informix Introduction vii pubs library Alternatively you can access or install the product documentation from the Quick Start CD that is shipped with the product Compliance with Industry Standards The American National Standards Institute ANSI and the International Organization of Standardization ISO have jointly established a set of industry standards for the Structured Query Language SOL IBM Informix SOL based products are fully compliant with SQL 92 Entry Level published as ANSI X3 135 1992 which is identical to
60. our programs JDK 1 2 is also known as Java 2 Global Language Support Refer to the IBM Informix JDBC Driver Programmer s Guide for information about using Global Language Support GLS with IBM Informix JDBC Driver Documentation Conventions This section describes the following conventions which are used in the product documentation for IBM Informix Dynamic Server e Typographical conventions e Feature product and platform conventions e Example code conventions Typographical Conventions This publication uses the following conventions to introduce new terms illustrate screen displays describe command syntax and so forth Convention Meaning KEYWORD Keywords of SQL SPL and some other programming languages appear in uppercase letters in a serif font italics Within text new terms and emphasized words appear in italics Within syntax and code examples variable values that you are to specify appear in italics boldface Names of program entities such as classes events and tables environment variables file names path names and interface elements such as icons menu items and buttons appear in boldface monospace Information that the product displays and information that you enter appear in a monospace typeface KEYSTROKE Keys that you are to press appear in uppercase letters in a sans serif font This symbol indicates a menu item For example Choose Too
61. ps option 5 3 5 7 ser2class option 5 5 status option 5 3 url option 5 6 user option 5 6 version option 5 3 vm option 5 6 warn option 5 4 class files 1 1 ser files 5 1 5 5 5 9 sqlj file extension 4 2 A accessibility C 1 keyboard C 1 shortcut keys C 1 Accessor methods 3 3 4 1 4 4 BEGIN DECLARE SECTION statement 4 1 BEGIN END block 4 2 Binding of variables 1 2 Boolean options 5 7 C CLASSPATH environment variable 3 1 5 1 close method 4 6 Column aliases 4 5 Command options ifxsqlj 5 2 Compiling code 5 1 Connecting to a database 3 1 Connection context class A 2 Connection context object A 3 ConnectionManager class 3 1 3 4 A 1 ConnectionManagerjava file 3 1 Curly braces 4 2 Cursors 3 2 4 1 Copyright IBM Corp 1996 2008 D Database server names setting in database URLs A 2 Database servers 2 1 Database URLs 3 1 A 1 Databases connecting to 3 1 Default connection context 1 2 3 2 Deletes positioned 4 6 Demo01 sqlj program B 1 Demo02 sqlj program B 1 Demo03 sqlj program 3 3 B 1 Demo04 sqlj program B 1 Demo05 sqlj program B 1 Demo06 sqlj program B 1 demoUtiljava program A 3 disability C 1 Domain names setting in database URLs A 2 Dynamic SOL 4 2 E Embedded SOL traditional 4 1 END DECLARE SECTION statement 4 1 endFetch method 4 6 Errors 4 10 ESQL C 41 EXECUTE FUNCTION statement 4 2 4 7 EXECUTE PROCEDURE statement 4 2 4 7 Execution
62. rt no could also be different url jdbc 158 58 9 121 1527 items db informixserver tulua2 set_url url i ctx new ItemsCtx getConnect Declare orders rec of type OrdersRec OrdersRec orders rec Using context o ctx query orders sql o ctx orders rec SELECT order num order date po num paid date FROM orders Hi while orders rec next System out System out print ORDER NUMBER orders rec order num t t System out println ORDER DATE orders rec order date System out print PURCHASE ORDER NUMBER orders rec po num t System out println PAID DATE orders rec paid date System out System out print An int ord no orders rec order num intValue printItemRec fetchItemRec ord no System out printIn n ItemsRec fetchItemRec int ord_no throws SQLException ItemsRec items rec sql i ctx items rec SELECT item num order num stock num manu code quantity total price FROM items WHERE order num ord no E return items rec void printItemRec ItemsRec items rec throws SQLException System out print ITEM NUMBER System out print STOCK NUMBER System out print MANUFACTURER CODE System out print QUANTITY System out print TOTAL PRICE System out printIn n E Appendix A Connecting to Databases A 5 while items rec next
63. s SQLException MultiConnect mc_ob new MultiConnect try System out println Running demo program MultiConnect mc_ob runDemo Close the connection mc_ob o_ctx close mc_ob i_ctx close catch SQLException s System err println Error running demo program s System err println Error Code s getErrorCode System err println Error Message g s getMessage void runDemo throws SQLException We drop the 2 databases using the default context drop_db We create the 2 databases needed for the program using the default Connection Context sql ctx CREATE DATABASE orders db WITH LOG MODE ANSI j sql ctx CREATE DATABASE items db WITH LOG MODE ANSI ctx close String driver com informix jdbc IfxDriver String url jdbc 158 58 9 121 1527 informixserver tulua2 String user rdtest A 4 IBM Informix Embedded SOL User s Guide String password 1RDSRDS set_driver driver set_url url set_user user set_passwd password getConnect Create the schema and the tables by running the SQL scripts executeSQLScript schema sql conn close We now set up the Connection context OrdersCtx url jdbc 158 58 9 121 1527 orders db informixserver tulua2 set_url url o_ctx new OrdersCtx getConnect Change the url to reflect items database Here we are changing the database name the machine name and the po
64. se URLs on page A 1 The driver option specifies a list of JDBC drivers that can be used to interpret JDBC connection URLs for online checking Both of these options are shown in Advanced Options on page 5 4 The ifxprofp Tool Embedded SQLJ includes the ifxprofp tool The tool ifxprofp enables you to print out the information stored in internal resource ser files for debugging purposes You invoke the tool as follows java ifxprofp filename ser Here is an example of the output of the ifxprofp tool printing contents of profile Demo02_SJProfiled created 918584057644 2 9 99 10 14 AM associated context is sqlj runtime ref DefaultContext profile loader is sqlj runtime profile DefaultLoadere1f7f1941 contains no customizations original source file Demo02 sqlj contains 8 entries Chapter 5 Processing Embedded SQLJ Source Code 5 9 5 10 profile Demo02_SJProfile0 entry 0 sql CREATE DATABASE demo sqlj WITH LOG MODE ANSI h line number 59 PREPARED STATEMENT executed via EXECUTE UPDATE role is STATEMENT descriptor is null contains no parameters result set type is NO RESULT result set name is null contains no result columns IBM Informix Embedded SOLJ User s Guide Appendix A Connecting to Databases Connecting to a Database on page 3 1 describes how Embedded SQLJ programs connect to databases This appendix provides background information and information about using nondefault connection c
65. ssl address2 phone FROM customer You run the SELECT statement and populate the iterator object with the result set by using an Embedded SQLJ statement of the form sql iterator object SELECT For example CustIter cust rec sql ctx cust rec SELECT customer num fname Iname addressl address2 phone FROM customer da You retrieve data from a positional iterator into host variables using the FETCH INTO statement sql FETCH cust rec INTO customer num fname lname addressl address2 phone E The SQLJ translator checks that the types of the host variables in the INTO clause of the FETCH statement match the types of the iterator columns in corresponding positions The types of the SQL columns in the SELECT statement must be compatible with the types of the iterator These type conversions are checked at translation time if ou perform online checking For information about setting up online checking see Named Iterators The name of each Java variable of a named iterator must match the name of a column returned by your SELECT statement order is irrelevant The matching of SQL column names and iterator column names is case insensitive For example the following statement generates a named iterator class called CustRec sql iterator CustRec int customer_num String fname String Iname String company String address1 String address2 String city String state String zip
66. t by running a query Handle the results Close the iterator PARAR RR RR RR RR RR KK k e EK KIRK IK IK KA KIRK AKIRA KARRI KK KERRI K K k K Title OR Ok OX A Ok OR Ok ook OR ok OR F HF 0k ok ok X IBM CORPORATION PROPRIETARY DATA THIS DOCUMENT CONTAINS TRADE SECRET DATA WHICH IS THE PROPERTY OF IBM CORPORATION THIS DOCUMENT IS SUBMITTED TO RECIPIENT IN CONFIDENCE INFORMATION CONTAINED HEREIN MAY NOT BE USED COPIED OR DISCLOSED IN WHOLE OR IN PART EXCEPT AS PERMITTED BY WRITTEN AGREEMENT SIGNED BY AN OFFICER OF IBM CORPORATION THIS MATERIAL IS ALSO COPYRIGHTED AS AN UNPUBLISHED WORK UNDER SECTIONS 104 AND 408 OF TITLE 17 OF THE UNITED STATES CODE UNAUTHORIZED USE COPYING OR OTHER REPRODUCTION IS PROHIBITED BY LAW Demo03 sqlj Description This demonstrates simple iterator use kkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkkk import java sql import sqlj runtime SQLJ runtime classes sql iterator CustRec int String String String String String String String String String gt customer_num fname lname company addressl address2 city state zipcode phone public class Demo03 public static void main String args throws SQLException Demo03 demo03 new Demo03 try demo03 runDemo catch SQLException s System err println Error running demo program s System err println Error
67. terator class you specify a set of Java variables to match the SOL columns that your SELECT statement returns There are two types of iterators positional and named IBM Informix Embedded SOLJ User s Guide Positional Iterators The order of declaration of the Java variables of a positional iterator must match the order in which the SOL columns are returned You use a FETCH INTO statement to retrieve data from a positional iterator For example the following statement generates a positional iterator class with five columns called Custlter fsql iterator CustIter int String String String String String This iterator can hold the result set from the following SELECT statement SELECT customer num fname Iname addressl address2 phone FROM customer Named Iterators The name of each Java variable of a named iterator must match the name of a column returned by your SELECT statement order is irrelevant The matching of SOL column name and iterator column name is case insensitive You use accessor methods of the same name as each iterator column to obtain the returned data as shown in the example in A Simple Embedded SQL Program The SQLJ translator uses the iterator column names to create accessor methods Iterator column names are case sensitive therefore you must use the correct case when you specify an accessor method You cannot use the FETCH INTO statement with named iterators For example the followi
68. tions A 2 Null data 4 2 Null indicator variables 4 9 O Off line checking 5 9 On line checking 5 6 5 8 Online checking 5 5 Output directory 5 3 P Passwords setting in database URLs A 2 PATH environment variable 5 5 X 2 IBM Informix Embedded SOL User s Guide Port numbers setting in database URLs A 2 Positional iterators 3 3 4 3 Positioned updates deletes 4 6 Preprocessing source code 5 1 profilekeys class file 5 1 Property files 5 7 R README file 2 1 B 1 Reserved names 4 10 Result sets 3 2 4 3 Root output directory 5 3 rowCount method 4 5 Running Embedded SOLJ programs 5 1 S Sample programs 2 1 3 3 B 1 Schema checking 1 2 SELECT statements 3 2 SELECT AS statement 4 5 SELECT INTO statement 3 2 4 3 Semantics checking 1 2 5 8 Servers 2 1 setMaxFieldSize method 4 7 setMaxRows method 4 7 setQueryTimeou method 4 7 setUpdateCount method 4 7 shortcut keys keyboard C 1 Specifying environment variables A 2 SPL routines 4 7 SQL statements 3 2 SQL types 4 7 SQL92 Entry level 4 2 SQLChecker cache file 5 4 SQLException class 4 9 SQLException methods 4 10 SQLJ consortium 1 1 SQL runtime package 4 9 SQL translator 1 1 4 10 5 1 sqlj properties file 5 7 sqlj semantics JdbcChecker class 5 5 sqlj semantics OfflineChecker class 5 5 Stored functions 4 7 Syntax checking 1 2 5 8 T Translating source code 5 1 Type checking 1 2 4 4 4 5 Type mappings 4 7 U Unicode escape sequ
69. to the Java compiler Displays option names descriptions and current settings The list displays The name of the option The type of the option for example if it is Boolean or a selection of allowed values The current value A description of the option Whether the property is at its default or was set by either a property file or the command line No translation or compilation is performed when you specify the help option Enables the mapping of line numbers between the generated java file and the original sqlj file The linemap option is useful for debugging because it allows you to trace compilation and execution errors back to your Embedded SOLJ source code For the linemap option to be effective the name of the sqlj source code file must match the name of the class it implements Specifies the name of the property file from which to read options The ifxprofp Tool on page 5 9 explains how to use property files Displays status messages while the java ifxsqlj command is running Displays the version of Embedded SQLJ you are using No translation or compilation is performed when you specify the version option Chapter 5 Processing Embedded SQL Source Code 5 3 5 4 warn Specifies a list of flags in a comma separated string for controlling the display of warning and information messages during translation The flags are all none Turns on or off all warnings and information messages
70. ue name value In the preceding syntax e Curly brackets together with vertical lines denote more than one choice of variable talics denote a variable value Brackets denote an optional value Words or symbols not enclosed in brackets are required INFORMIXSERVER for example Important Spaces are not allowed in the database URL O Copyright IBM Corp 1996 2008 A 1 The following table describes the variable parts of the database URL Database URL Variable ip address or domain name Required Yes Description The IP address or the domain name of the computer running the Informix database server An example of an IP address is 123 45 67 89 An example of a domain name is myhost com port number Yes The port number of the Informix database server dbname The name of the Informix database to which you want to connect If you do not specify the name of a database a connection is made to the Informix database server server name Yes The name of the Informix server to which you want to connect This is the value of the INFORMIXSERVER environment variable The INFORMIXSERVER environment variable is required in the database URL unless it is included in the property list username Yes The name of the user you want to connect to the Informix database or database server as password Yes The password of the user specified by usern
71. y Files on page 577 For Boolean options those that are either on or off you can set the option simply by specifying the option name for example 1inemap You can also set the option to TRUE as in linemap true To turn off a Boolean option you must set it to FALSE for example linemap false You can also set Boolean options to yes or no or to 1 or 0 Setting Options on the Command Line Options on the command line override any options set in default files If the same option appears more than once on the command line the translator uses the final rightmost option s value Command line option names are case sensitive You can attach prefixes to options to pass the option to the Java compiler or to the Java interpreter If you do not use a prefix the option is passed to the SOLJ translator The prefixes are e E Passes compiler options to the Java compiler as shown in the following example C classpath user jdk bin J Passes interpreter options to the Java interpreter as shown in the following example J Duser language ja The options available to pass to the interpreter depend on the release and brand of Java you are using Do not use the C prefix with the d and encoding options when you specify these SOLJ translator options they are automatically passed to the Java compiler Supplying Options in Property Files You can use property files to supply options to the java ifxsqlj command The

IBM Informix Embedded SQLJ User's Guide

Contents

Download Pdf Manuals

Related Search

Related Contents