Example File
You can download full versions of the examples used in this section here. (Zip file)
To create the databank, sequence and table used in the following examples, run:create databank MY_DB;
create unique sequence MY_SEQUENCE;
create table MY_TABLE (
NUMBER integer default next_value of MY_SEQUENCE,
NAME char(20)
) in MY_DB;
ODBC Example
The example below connects to the data source SAMPLE with the username SYSADM and password SYSADM. It inserts a new name into the database, and retrieves the number it got.
Note that only the segment clarifying the main point is shown here. For the full example, see the download link above. CHECK is a C-macro which takes care of the error handling.int main(int argc,char *argv[])
{
char new_name[100] = "default_name";
char dsn[200],uid[200],pwd[200];
int value;
lines omitted
CHECK(SQLPrepare(hstmt,"insert into MY_TABLE(NAME) values (?);
select current_value of MY_SEQUENCE from MIMER.ONEROW",
SQL_NTS));
CHECK(SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_CHAR,
0,0,new_name,sizeof(new_name),NULL));
CHECK(SQLExecute(hstmt));
CHECK(SQLMoreResults(hstmt));
CHECK(SQLFetch(hstmt));
CHECK(SQLGetData(hstmt,1,SQL_C_SLONG,&value,0,NULL));
CHECK(SQLCloseCursor(hstmt));
printf("The new person (%s) got number %d.\n",new_name,value);
return 0;
}
What we see above is an SQLPrepare call which prepares two statements in a batch. One INSERT that inserts the new person into the database, and one SELECT which queries for the primary key assigned to the new record. Both of these statements are sent to the server in a single network package.
After the SQLExecute we are ready to take care of whatever the statements returned. First, we are positioned at the INSERT statement. Since we are not interested in the row count of the INSERT we move directly to the next statement, SQLMoreResults. As that was a SELECT statement, we expect a result set which we start fetching from. With the SQLGetData call, we know what primary key was assigned to the new record.
Embedded SQL
Unfortunately, batches aren’t allowed in Embedded SQL. The recommended approach in Embedded SQL is to embed the SQL statements in a procedure. This is not complicated but requires that the queries and statements be defined and set.
To query for the primary key using embedded SQL, we create the following procedure:create procedure MY_PROC(in P_NAME char(20),out P_NR integer)
modifies sql data
begin
insert into MY_TABLE(NAME) values (P_NAME);
select current_value of MY_SEQUENCE into P_NR from MIMER.ONEROW;
end
Calling this procedure from Embedded SQL is surprisingly easy.
For clarity, only the important parts of the sample are included, see the download link above for the full example.int main(int argc,char *argv[])
{
exec sql BEGIN DECLARE SECTION;
char new_name[100] = "default_name";
char dsn[200],uid[200],pwd[200];
int value;
exec sql END DECLARE SECTION;
exec sql WHENEVER SQLERROR goto error_exit;
if (argc>1)
strcpy(new_name,argv[1]);
get_connect_info(dsn,uid,pwd);
exec sql CONNECT TO :dsn USER :uid USING :pwd;
exec sql CALL MY_PROC(:new_name,:value);
printf("The new person (%s) got number %d.\n",new_name,value);
exec sql COMMIT;
exec sql DISCONNECT ALL;
return 0;
error_exit:
print_sqlerror();
exec sql WHENEVER SQLERROR CONTINUE;
exec sql DISCONNECT;
exit(0);
return 0;
}
Of course, this procedure can be used from ODBC as well. See the ODBC reference on advice how to call procedures using ODBC.
JDBC example
Like ODBC, JDBC supports batches, but, although the terminology used is the same, JDBC batches are a little different.
First and foremost, batches in JDBC are specifically intended only for DML statements returning an update count. This is a limitation compared to ODBC as ODBC allows a mix of DML-statements and queries.
Secondly, JDBC will not allow programmers to compile a sequence of statements in advance and use parameter markers with them. Using JDBC, we have to use the PreparedStatement object to prepare statements with parameter markers, but the PreparedStatement can only batch parameter arrays to a single statement.
In the example below we use the most straightforward method - with separate statements.import java.io.*;
import java.sql.*;
class sample3 {
public static void main(String[] arg) {
try {
Connection con;
PreparedStatement pstmt;
Statement stmt;
String url;
String name = "default_name";
int value;
BufferedReader in =
new BufferedReader(new InputStreamReader(System.in));
System.out.print("Enter URL of server: ");
url = in.readLine();
if (arg.length>0)
name = arg[0];
Class.forName("com.mimer.jdbc.Driver");
con = DriverManager.getConnection(url);
con.setAutoCommit(false);
pstmt = con.prepareStatement(
"insert into MY_TABLE(NAME) values (?)");
pstmt.setString(1,name);
pstmt.executeUpdate();
pstmt.close();
stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(
"select current_value of MY_SEQUENCE from MIMER.ONEROW");
rs.next();
value = rs.getInt(1);
rs.close();
stmt.close();
con.commit();
System.out.println(
"The new person ("+name+") got number "+value+".\n");
con.close();
} catch (SQLException e) {
System.out.println("Database ERROR! "+e.getMessage());
} catch (Exception e) {
System.out.println("ERROR! "+e.getMessage());
}
}
}
Naturally, JDBC users can also use the MY_PROC procedure, which will be more efficient than the example above. See any JDBC API reference on how to call procedures.
Benefits
Mimer SQL supports auto-incrementing column values through sequences. However, auto-incrementing column values or sequences is not included in the SQL-99 standard. Mimer SQL supports this implementation for your convenience.
Mpm Home
Printer friendly
