This sample program shows examples of static SQL statements and database manager API calls in the following supported languages:
The REXX language does not support static SQL, so a sample is not provided.
This sample program contains a query that selects a single row. Such a query can be performed using the SELECT INTO statement.
The SELECT INTO statement selects one row of data from tables in a database, and the values in this row are assigned to host variables specified in the statement. Host variables are discussed in detail in Using Host Variables. For example, the following statement will deliver the salary of the employee with the last name of 'HAAS' into the host variable empsal:
SELECT SALARY
INTO :empsal
FROM EMPLOYEE
WHERE LASTNAME='HAAS'
A SELECT INTO statement must be specified to return only one or zero rows. Finding more than one row results in an error, SQLCODE -811 (SQLSTATE 21000). If several rows can be the result of a query, a cursor must be used to process the rows. See Selecting Multiple Rows Using a Cursor for more information.
For more details on the SELECT INTO statement, refer to the SQL Reference.
For an introductory discussion on how to write SELECT statements, see Coding SQL Statements to Retrieve and Manipulate Data.
See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
EXEC SQL INCLUDE SQLCA; (1)
int main(int argc, char *argv[])
{ int rc = 0;
char dbAlias[15] ;
char user[15] ;
char pswd[15] ;
EXEC SQL BEGIN DECLARE SECTION; (2)
char firstname[13];
EXEC SQL END DECLARE SECTION;
/* checks the command line arguments */
rc = CmdLineArgsCheck1( argc, argv, dbAlias, user, pswd ); (3)
if ( rc != 0 ) return( rc ) ;
printf("\n\nSample C program: STATIC\n");
/* initialize the embedded application */
rc = EmbAppInit( dbAlias, user, pswd);
if ( rc != 0 ) return( rc ) ;
EXEC SQL SELECT FIRSTNME INTO :firstname (4)
FROM employee
WHERE LASTNAME = 'JOHNSON';
EMB_SQL_CHECK("SELECT statement"); (5)
printf( "First name = %s\n", firstname );
/* terminate the embedded application */
rc = EmbAppTerm( dbAlias);
return( rc ) ;
}
/* end of program : STATIC.SQC */
import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
class Static
{ static
{ try
{ Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
}
catch (Exception e)
{ System.out.println ("\n Error loading DB2 Driver...\n");
System.out.println (e);
System.exit(1);
}
}
public static void main(String argv[])
{ try
{ System.out.println (" Java Static Sample");
String url = "jdbc:db2:sample"; // URL is jdbc:db2:dbname
Connection con = null;
// Set the connection (3)
if (argv.length == 0)
{ // connect with default id/password
con = DriverManager.getConnection(url);
}
else if (argv.length == 2)
{ String userid = argv[0];
String passwd = argv[1];
// connect with user-provided username and password
con = DriverManager.getConnection(url, userid, passwd);
}
else
{ throw new Exception("\nUsage: java Static [username password]\n");
}
// Set the default context
DefaultContext ctx = new DefaultContext(con);
DefaultContext.setDefaultContext(ctx);
String firstname = null;
#sql { SELECT FIRSTNME INTO :firstname
FROM employee
WHERE LASTNAME = 'JOHNSON' } ; (4)
System.out.println ("First name = " + firstname);
}
catch( Exception e ) (5)
{
System.out.println (e);
}
}
}
Identification Division.
Program-ID. "static".
Data Division.
Working-Storage Section.
copy "sql.cbl".
copy "sqlca.cbl". (1)
EXEC SQL BEGIN DECLARE SECTION END-EXEC. (2)
01 firstname pic x(12).
01 userid pic x(8).
01 passwd.
49 passwd-length pic s9(4) comp-5 value 0.
49 passwd-name pic x(18).
EXEC SQL END DECLARE SECTION END-EXEC.
77 errloc pic x(80).
Procedure Division.
Main Section.
display "Sample COBOL program: STATIC".
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid = spaces
EXEC SQL CONNECT TO sample END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length for characters
before initial " ".
EXEC SQL CONNECT TO sample USER :userid USING :passwd (3)
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL SELECT FIRSTNME INTO :firstname (4)
FROM EMPLOYEE
WHERE LASTNAME = 'JOHNSON' END-EXEC.
move "SELECT" to errloc.
call "checkerr" using SQLCA errloc. (5)
display "First name = ", firstname.
EXEC SQL CONNECT RESET END-EXEC. (6)
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Prog.
stop run.