178: ISAM error: Database is locked; pending change to logging mode.

Normally this error indicates that the logging mode of the database has changed using the ondblog command.
Normally making a level 0 archive solves the problem

JDBC connectivity to informix

To Run A JDBC program , following steps should be followed
1. Install the appropriate JDBC libraries according to ur Operating System/System configuration
2. Need to finalize the URL to connect to the Database
3. need to include the Directory of the JDBC library to the "CLASSPATH" environment varaiable.
4. Run the Program.

We can get appropriate JDBC driver from different Vendors.. following link will give the Details about the which Driver to Choose and all
http://developers.sun.com/product/jdbc/drivers
Download the appropriate Driver and install.

The main thing in running a JDBC program is deciding the Conncetion URL.

The URL string looks like this:
"jdbc:informix-sqli://hostname:port_num/database_name:informixserver=server;user=user;password=pwd"

where jdbc is the main protocol and informix-sqli is the sub protocol
and all other fields are self explanatory

example link :
jdbc:informix-sqli://scsibox:1526/my_data:informixserver=ol_scsibox;user=informix;password=Informix

use the Driver name as "com.informix.jdbc.IfxDriver"

so remaining all we can find from SUN help... i.e.. different classess and all..


one sample program:

import java.sql.*;
import java.io.*;
import java.util.*;
import java.lang.*;
import sqlj.runtime.ref.DefaultContext;

public class ConnectionManager
{
String DRIVER = null ; //JDBC Driver class
String DBURL = null ; //Database URL
String PWD = null, UID = null ; //Password for database account
DefaultContext ctx;
Statement stmt;
public Connection conn;
public Driver d;
String Query = null;
ResultSet rs = null;
int rc = -1;

//This block has to be changed and ConnectionManager.java recompiled

//Assign Default values for Driver loading...
public ConnectionManager(){
DRIVER = "com.informix.jdbc.IfxDriver";
DBURL = "jdbc:informix-sqli://10.128.96.66:5030/omcdb:informixserver=omc_sys1;"+
"username=informix;password=informix";
UID = "informix";
PWD = "informix";

//Establish connection
newConnection();
}

public ConnectionManager(String sDriver, String sDBUrl,String sUID,String sPWD){
DRIVER = sDriver;
DBURL = sDBUrl;
UID = sUID;
PWD = sPWD;

//Establish connection
newConnection();
}

public void newConnection()
{
try
{
d = (Driver)(Class.forName( DRIVER ).newInstance());
DriverManager.registerDriver(d);
System.out.println("Informix Driver Successfully Created....");
}
catch (Exception e)
{
System.err.println( "Could not load driver: " + DRIVER ) ;
System.err.println(e) ;
System.exit(1) ;
}

try
{
conn = DriverManager.getConnection (DBURL, UID, PWD);
//conn.setAutoCommit(false); // Turn AutoCommit off
System.out.println("Created Connection successfully to the Database");
}
catch (SQLException exception)
{
System.out.println("Error: could not get a connection");
System.err.println(exception) ;
exception.printStackTrace();
System.exit(1);
}
}


public void setSQLQuery(String query){
Query = query;
}

//This Method is only to execute statments which dont return Result set...
public int executeQuery(){
rc = -1;
try{
stmt = conn.createStatement();
rc = stmt.executeUpdate(Query);
System.out.println("Transaction successful .. Updated Rows : " + rc);
}catch(SQLException e){
System.out.println("FAILED: execution failed - statement: " + Query);
System.out.println("ERROR: " + e.getMessage());
e.printStackTrace();
}

try{
stmt.close();
}catch(SQLException e){
}

return rc;
}

//This shoulb be used with select like queries... which result Resultset...
public ResultSet fetchResultset(){
rs = null;
try{
stmt = conn.createStatement();
rs = stmt.executeQuery(Query);
System.out.println("Fetched Result Set Successfully");
}catch(SQLException e){
System.out.println("FAILED: execution failed - statement: " + Query);
System.out.println("ERROR: " + e.getMessage());
e.printStackTrace();
}

try{
stmt.close();
}catch(SQLException e){
}

return rs;
}

public void closeResultset(){
try{
rs.close();
rs = null;
}catch(SQLException e){
}

}

public DefaultContext initContext(){
ctx = DefaultContext.getDefaultContext();

if (ctx == null) {
try {
newConnection();
ctx = new DefaultContext(conn);
}
catch (SQLException e) {
System.out.println("Error: could not get a default context");
System.err.println(e) ;
System.exit(1);
}
DefaultContext.setDefaultContext(ctx);
}
return ctx;
}
}

Transaction Control

You can use two methods to specify the boundaries of transactions with SQL statements. In the most common method,
you specify the start of a multistatement transaction by executing the BEGIN WORK statement. In databases that are created
with the MODE ANSI option, no need exists to mark the beginning of a transaction. One is always in effect;
you indicate only the end of each transaction.

In both methods, to specify the end of a successful transaction, execute the COMMIT WORK statement. This statement tells the database
server that you reached the end of a series of statements that must succeed together. The database server does whatever is necessary
to make sure that all modifications are properly completed and committed to disk.

A program can also cancel a transaction deliberately by executing the ROLLBACK WORK statement. This statement asks the database
server to cancel the current transaction and undo any changes.

Informix SQL Guide

http://www.ics.uci.edu/~dbclass/ics184/htmls/Informix_guide.html

Dynamic SQL - with Informix

• Dynamic SQL allows a program to form an SQL statement during execution, so that the statement can be determined by user input. The action is performed in two steps.

  1. Preparing a statement

     It uses PREPARE statement to have the database server examine the statement text and prepare it for execution.

         EXEC SQL prepare  from '';
     

  2. Executing prepared SQL

     It uses EXECUTE statement to execute the prepared statement.

         EXEC SQL execute  from '';
     

  For instance, if you want to inquire the information of some students, you can use the following dynamic SQL for query:

      EXEC SQL BEGIN DECLARE SECTION;
     int sid;
     char sname[10];
     EXEC SQL END DECLARE SECTION;
  
     EXEC SQL prepare query_stud from 'select id,name from student where name=?';
  
     EXEC SQL execute query_stud into :sid, sname using 'Mike';
     printf("Student: (%d, %s)\n", sid, sname);
  
     EXEC SQL execute query_stud into :sid, sname using 'David';
     printf("Student: (%d, %s)\n", sid, sname);
  

  The above produces the following results:

      Student: (2, Mike)
     Student: (9, David)
  

• Embedded SQL(including cursor)

  SQL statements can be embedded in the C and COBOL program. In C, all the statement is preceded by "EXEC SQL". There is a detail example in the next section.

  A cursor is a special data object that represents the current state of a query. It is used for retrieving multiple resulting rows of query. It is used in 5 steps:

  1. Declaring a Cursor

         EXEC SQL DECLARE  CURSOR FOR ;
     

  2. Opening a Cursor

         EXEC SQL OPEN ;
     

  3. Fetching Rows

         EXEC SQL FETCH ;
     

  4. Closing a Cursor

         EXEC SQL CLOSE ;
     

  5. Freeing a Cursor

         EXEC SQL FREE ;
     

     For instance, the following program lists the information of all students.

         EXEC SQL BEGIN DECLARE SECTION;
        int sid;
        char sname[10];
        EXEC SQL END DECLARE SECTION;
     
        EXEC SQL DECLARE cursor_stud CURSOR FOR select id,name from student;
        EXEC SQL OPEN cursor_stud;
        while ( SQLCODE == 0 ) {
            EXEC SQL OPEN cursor_stud INTO :sid, :sname;
            if ( SQLCODE == 0 )
              printf("Student (%d, %s)\n", sid, sname);
         }
        EXEC SQL CLOSE cursor_stud;
        EXEC SQL FREE cursor_stud;
     

     SQLCODE is set to 0 by the database if the select statement is valid, otherwise set to 100. It is used to detect the end of data.

---

Informix Programming Guide Setting Environment

• setenv INFORMIXSERVER rodan_ius_net
• setenv PATH $INFORMIXDIR/bin:$PATH
• setenv LD_LIBRARY_PATH $INFORMIXDIR/lib:$INFORMIXDIR/lib/esql:$INFORMIXDIR/lib/dmi:$INFORMIXDIR/lib/c++