/**
* sample1 JDBC sample application
*
*
* This simple JDBC application does the following using
* Solid JDBC driver.
*
* 1. Registers the driver using JDBC driver manager services
* 2. Prompts the user for a valid JDBC connect string
* 3. Connects to Solid using the driver
* 4. Creates a statement for one query,
*     'SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE FROM TABLES'
*   for reading data from one of the Solid system tables.
* 5. Executes the query
* 6. Fetches and dumps all the rows of a result set.
* 7. Closes connection
* To build and run the application
*
* 1. Make sure you have a working Java Development environment
* 2. Install and start Solid to connect. Ensure that the
*     server is up and running.
* 3. Append SolidDriver2.0.jar into the CLASSPATH definition used
*    by your development/running environment.
* 4. Create a java project based on the file sample1.java.
* 5. Build and run the application.
* For more information read the readme.txt file contained in the
* solidDB^® package.
*
*/

import java.io.*;

public class sample1 {

  public static void main (String args[]) throws Exception
  {
    java.sql.Connection conn;
    java.sql.ResultSetMetaData meta;
    java.sql.Statement stmt;
    java.sql.ResultSet result;
    int i;

    System.out.println("JDBC sample application starts...");
    System.out.println("Application tries to register
        the driver.");

  // this is the recommended way for registering Drivers
  java.sql.Driver d =
      (java.sql.Driver)Class.forName("solid.jdbc.SolidDriver").
           newInstance();

  System.out.println("Driver succesfully registered.");
  // the user is asked for a connect string
  System.out.println(
      "Now sample application needs a connectstring in format:\n"
      );
  System.out.println(
      "jdbc:solid://<host>:<port>/<user name>/<password>\n"
  );
  System.out.print("\nEnter the connect string >");
  BufferedReader reader =
      new BufferedReader(new InputStreamReader(System.in));
      String sCon = reader.readLine();
  // next, the connection is attempted
  System.out.println("Attempting to connect :" + sCon);
  conn = java.sql.DriverManager.getConnection(sCon);

  System.out.println("SolidDriver succesfully connected.");
  String sQuery = "SELECT TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE
      FROM TABLES";

  stmt= conn.createStatement();

  result = stmt.executeQuery(sQuery);

  System.out.println("Query executed and result set obtained.");

  // we get a metadataobject containing information about the
  // obtained result set
  System.out.println("Obtaining metadata information.");
  meta = result.getMetaData();
  int cols = meta.getColumnCount();

  System.out.println("Metadata information for columns is as
      follows:");
  // we dump the column information about the result set
  for (i=1; i <= cols; i++)
  {
    System.out.println("Column i:"+i+" "+meta.getColumnName(i)+
        "," + meta.getColumnType(i) + "," +
        meta.getColumnTypeName(i));
  }

  // and finally, we dump the result set
  System.out.println("Starting to dump result set.");
  int cnt = 1;
  while(result.next())
  {
    System.out.print("\nRow "+cnt+" : ");
    for (i=1; i <= cols; i++) {
        System.out.print(result.getString(i)+"\t");
  }
  cnt++; }
  stmt.close();
  conn.close();

  // and not it is all over
  System.out.println("\nResult set dumped. Sample application
      finishes.");

  }
}

Solid\DatabaseEngine4.1\jdbc\samples>java sample1.java
JDBC sample application starts...
Application tries to register the driver.
Driver succesfully registered.
Now sample application needs a connectstring in format:

jdbc:solid://<host>:<port>/<user name>/<password>

Enter the connect string >jdbc:solid://localhost:1313/dba/dba
Attempting to connect :jdbc:solid://localhost:1313/dba/dba
SolidDriver succesfully connected.
Query executed and result set obtained.
Obtaining metadata information.
Metadata information for columns is as follows:
Column i:1 TABLE_SCHEMA,12,VARCHAR
Column i:2 TABLE_NAME,12,VARCHAR
Column i:3 TABLE_TYPE,12,VARCHAR
Starting to dump result set.

Row 1 : _SYSTEM SYS_TABLES BASE TABLE
Row 2 : _SYSTEM SYS_COLUMNS BASE TABLE
Row 3 : _SYSTEM SYS_USERS BASE TABLE
Row 4 : _SYSTEM SYS_UROLE BASE TABLE
Row 5 : _SYSTEM SYS_RELAUTH BASE TABLE
Row 6 : _SYSTEM SYS_ATTAUTH BASE TABLE
Row 7 : _SYSTEM SYS_VIEWS BASE TABLE
Row 8 : _SYSTEM SYS_KEYPARTS BASE TABLE
Row 9 : _SYSTEM SYS_KEYS BASE TABLE
Row 10 : _SYSTEM SYS_CARDINAL  BASE TABLE
Row 11 : _SYSTEM SYS_INFO BASE TABLE
Row 12 : _SYSTEM SYS_SYNONYM BASE TABLE
Row 13 : _SYSTEM TABLES VIEW
Row 14 : _SYSTEM      COLUMNS VIEW
Row 15 : _SYSTEM      SQL_LANGUAGES  BASE TABLE
Row 16 : _SYSTEM      SERVER_INFO   VIEW
Row 17 : _SYSTEM      SYS_TYPES   BASE TABLE
Row 18 : _SYSTEM      SYS_FORKEYS   BASE TABLE
Row 19 : _SYSTEM      SYS_FORKEYPARTS BASE TABLE
Row 20 : _SYSTEM      SYS_PROCEDURES  BASE TABLE
Row 21 : _SYSTEM      SYS_TABLEMODES  BASE TABLE
Row 22 : _SYSTEM      SYS_EVENTS   BASE TABLE
Row 23 : _SYSTEM      SYS_SEQUENCES  BASE TABLE
Row 24 : _SYSTEM      SYS_TMP_HOTSTANDBY BASE TABLE
Result set dumped. Sample application finishes.

/**
* sample2 JDBC sample applet
*
*
* This simple JDBC applet does the following using
* Solid native JDBC driver. *
* 1. Registers the driver using JDBC driver manager services
* 2. Connects to Solid using the driver.
*    Used url is read from sample2.html
* 3. Executes given SQL statements
* To build and run the application
* 1. Make sure you have a working Java Development environment
* 2. Install and start Solid to connect. Ensure that
*    the server is up and running.
* 3. Append SolidDriver2.0.jar into the CLASSPATH definition used
*    by your development/running environment.
* 4. Create a java project based on the file sample2.java.
* 5. Build and run the application. Check that sample2.html
*    defines valid url to your environment.
* For more information read the readme.txt file contained in the
* solidDB^® Development Kit package.
**/

import java.util.*;
import java.awt.*;
import java.applet.Applet;
import java.net.URL;
import java.sql.*;

public class sample2 extends Applet {
  TextField textField;
  static TextArea textArea;
  String url = null;
  Connection con = null;

  public void init() {
  // a valid value for url could be
  // url = "jdbc:solid://localhost:1313/dba/dba";

  url = getParameter("url");
  textField = new TextField(40);
  textArea = new TextArea(10, 40);
  textArea.setEditable(false);

  Font font = textArea.getFont();
  Font newfont = new Font("Monospaced", font.PLAIN, 12);
  textArea.setFont(newfont);

  // Add Components to the Applet.
  GridBagLayout gridBag = new GridBagLayout();
  setLayout(gridBag);
  GridBagConstraints c = new GridBagConstraints();
  c.gridwidth = GridBagConstraints.REMAINDER;
  c.fill = GridBagConstraints.HORIZONTAL;
  gridBag.setConstraints(textField, c);
  add(textField);

  c.fill = GridBagConstraints.BOTH;
  c.weightx = 1.0;
  c.weighty = 1.0;
  gridBag.setConstraints(textArea, c);
  add(textArea);

  validate();

  try {
    // Load the Solid JDBC Driver
    Driver d =
       (Driver)Class.forName ("solid.jdbc.SolidDriver").
            newInstance();

    // Attempt to connect to a driver.
    con = DriverManager.getConnection (url);

    // If we were unable to connect, an exception
    // would have been thrown. So, if we get here,
    // we are successfully connected to the url
    // Check for, and display and warnings generated
    // by the connect.

    checkForWarning (con.getWarnings ());

    // Get the DatabaseMetaData object and display
    // some information about the connection
    DatabaseMetaData dma = con.getMetaData ();

    textArea.appendText("Connected to " + dma.getURL() + "\n");
    textArea.appendText("Driver " +
        dma.getDriverName() + "\n");
    textArea.appendText("Version " + dma.getDriverVersion()
        + "\n");
    }
    catch (SQLException ex) {
        printSQLException(ex);
    }
    catch (Exception e)
    {
    textArea.appendText("Exception: " + e + "\n");
    }
  }

  public void destroy() {
    if (con != null) {
      try {
        con.close();
      }
      catch (SQLException ex)
      {
      printSQLException(ex); }
      catch (Exception e) {
        textArea.appendText("Exception: " + e + "\n");
      }
    }
  }

  public boolean action(Event evt, Object arg)
    { if (con != null) {
        String sqlstmt = textField.getText();
        textArea.setText("");
        try {
          // Create a Statement object so we can submit
          // SQL statements to the driver
          Statement stmt = con.createStatement ();
          // set row limit
          stmt.setMaxRows(50);
          // Submit a query, creating a ResultSet object
          ResultSet rs = stmt.executeQuery (sqlstmt);

          // Display all columns and rows from the result set
          textArea.setVisible(false);
          dispResultSet (stmt,rs);
          textArea.setVisible(true);

          // Close the result set
          rs.close();

          // Close the statement
          stmt.close();
        }
        catch (SQLException ex) {
          printSQLException(ex);
        }

        catch (Exception e) {
          textArea.appendText("Exception: " + e + "\n");
        }

       textField.selectAll();
     }
     return true;
   }

  //
  // checkForWarning
  // Checks for and displays warnings. Returns true if a warning
  // existed
  //

  private static boolean checkForWarning (SQLWarning warn)
          throws SQLException
  {
    boolean rc = false;

    // If a SQLWarning object was given, display the
    // warning messages. Note that there could be
    // multiple warnings chained together
    if (warn != null) {
      textArea.appendText("\n*** Warning ***\n");
      rc = true;
      while (warn != null) {
        textArea.appendText("SQLState: " +
          warn.getSQLState () + "\n");
        textArea.appendText("Message: " +
          warn.getMessage () + "\n");
        textArea.appendText("Vendor: " +
          warn.getErrorCode () + "\n");
        textArea.appendText("\n");
          warn = warn.getNextWarning ();
        }
      }
    return rc;
  }

  //
  // dispResultSet
  // Displays all columns and rows in the given result set
  //
  private static void dispResultSet (Statement sta, ResultSet rs)
    throws SQLException
  {
    int i;
    // Get the ResultSetMetaData. This will be used for
    // the column headings
    ResultSetMetaData rsmd = rs.getMetaData ();
    // Get the number of columns in the result set
    int numCols = rsmd.getColumnCount ();
    if (numCols == 0) {
      textArea.appendText("Updatecount is
          "+sta.getUpdateCount());
      return; }

    // Display column headings
    for (i=1; i<=numCols; i++) {
      if (i > 1) {
        textArea.appendText("\t");
      }
      try {
        textArea.appendText(rsmd.getColumnLabel(i));
      }
      catch(NullPointerException ex) {
        textArea.appendText("null");
      }
    }
    textArea.appendText("\n");

    // Display data, fetching until end of the result set
    boolean more = rs.next ();
    while (more) {
      // Loop through each column, get the
      // column data and display it
      for (i=1; i<=numCols; i++) {
        if (i > 1) {
          textArea.appendText("\t");
        }
        try {
          textArea.appendText(rs.getString(i));
        }
        catch(NullPointerException ex) {
          textArea.appendText("null");
        }
      }
      textArea.appendText("\n");

      // Fetch the next result set row
      more = rs.next ();
    }
  }

  private static void printSQLException(SQLException ex)
  {
    // A SQLException was generated. Catch it and
    // display the error information. Note that there
    // could be multiple error objects chained
    // together
    textArea.appendText("\n*** SQLException caught ***\n");
  }
}

    while (ex != null) {
      textArea.appendText("SQLState: "
      ex.getSQLState () + "\n"); textArea.appendText("Message: "
      ex.getMessage () + "\n"); textArea.appendText("Vendor: "
      ex.getErrorCode () + "\n"); textArea.appendText("\n"); ex =
      ex.getNextException ();
}

/**
* sample3 JDBC sample application
* This simple JDBC application does the following using
* Solid JDBC driver. *
*
* 1. Registers the driver using JDBC driver manager services
* 2. Prompts the user for a valid JDBC connect string
* 3. Connects to Solid using the driver
* 4. Drops and creates a procedure sample3. If the procedure
*    does not exist dumps the related exception.
* 5. Calls that procedure using java.sql.Statement
* 6. Fetches and dumps all the rows of a result set.
* 7. Closes connection
* To build and run the application
* 1. Make sure you have a working Java Development environment
* 2. Install and start Solid to connect. Ensure that the
*    server is up and running.
* 3. Append SolidDriver2.0.jar into the CLASSPATH definition used
*    by your development/running environment.
* 4. Create a java project based on the file sample3.java.
* 5. Build and run the application.
* For more information read the readme.txt
* file contained in the solidDB^® Development Kit package.
*/

import java.io.*;
import java.sql.*;

public class sample3 {
  static Connection conn;
  public static void main (String args[]) throws Exception
  {
    System.out.println("JDBC sample application starts...");
    System.out.println("Application tries to register
        the driver.");
    // this is the recommended way for registering Drivers
    Driver d =
       (Driver)Class.forName("solid.jdbc.SolidDriver").
           newInstance();
    System.out.println("Driver succesfully registered.");
    // the user is asked for a connect string System.out.println(
    "Now sample application needs a connectstring in format:\n");
    System.out.println(
      "jdbc:solid://<host>:<port>/<user name>/<password>\n");
    System.out.print("\nEnter the connect string >");
    BufferedReader reader =
        new BufferedReader(new InputStreamReader(System.in));
    String sCon = reader.readLine();

    // next, the connection is attempted
    System.out.println("Attempting to connect :" + sCon);
    conn = DriverManager.getConnection(sCon);

    System.out.println("SolidDriver succesfully connected.");

    DoIt();

    conn.close();
    // and now it is all over
    System.out.println(
      "\nResult set dumped. Sample application finishes."
    );
  }

static void DoIt() {
  try {
    createprocs();
  PreparedStatement pstmt =
      conn.prepareStatement("call sample3(?)");
  // set parameter value
  pstmt.setInt(1,10);
  ResultSet rs = pstmt.executeQuery();
  if (rs != null) {
    ResultSetMetaData md = rs.getMetaData();
    int cols = md.getColumnCount();
    int row = 0;
    while (rs.next()) {
      row++;
      String ret = "row "+row+": ";
      for (int i=1;i<=cols;i++) {
        ret = ret + rs.getString(i) + " ";
      }
      System.out.println(ret);
    }
  }
  conn.commit();
  }
  catch (SQLException ex) {
    printexp(ex); }
  catch (java.lang.Exception ex) {
   ex.printStackTrace ();
  }
}

static void createprocs() {
  Statement stmt = null;
  String proc = "create procedure sample3 (limit integer)" +
                "returns (c1 integer, c2 integer) " +
                "begin " +
                " c1 := 0;" +
                " while c1 < limit loop " +
                " c2 := 5 * c1;" +
                " return row;" +
                " c1 := c1 + 1;" +
                " end loop;" +
                "end";
  }
  try {
    stmt = conn.createStatement();
    stmt.execute("drop procedure sample3");
  }
  catch (SQLException ex) {
    printexp(ex); }
  try {
    stmt.execute(proc);
  }
  catch (SQLException ex) {
    printexp(ex);
  System.exit(-1); }

  public static void printexp(SQLException ex) {
    System.out.println("\n*** SQLException caught ***");
    while (ex != null) {
      System.out.println("SQLState: " + ex.getSQLState());
      System.out.println("Message: " + ex.getMessage());
      System.out.println("Vendor: " + ex.getErrorCode());
      ex = ex.getNextException ();
    }
  }
}

/** * sample4 JDBC sample application
* This simple JDBC application does the following using
* Solid JDBC driver.
*
* 1. Registers the driver using JDBC driver manager services
* 2. Prompts the user for a valid JDBC connect string
* 3. Connects to Solid using the driver
* 4. Drops and creates a table sample4. If the table
*    does not exist dumps the related exception.
* 5. Inserts file given as an argument to database (method Store)
* 6. Reads this 'blob' back to file out.tmp (method Restore)
* 7. Closes connection
* To build and run the application
* 1. Make sure you have a working Java Development environment
* 2. Install and start Solid to connect. Ensure that
*    the server is up and running.
* 3. Append SolidDriver2.0.jar into the CLASSPATH definition used
*    by your development/running environment.
* 4. Create a java project based on the file sample4.java.
* 5. Build and run the application.
* For more information read the readme.txt file
* contained in the solidDB^® Development Kit package.
*
*/

import java.io.*;
import java.sql.*;

public class sample4 {
  static Connection conn;
  public static void main (String args[]) throws Exception {
    String filename = null;
    String tmpfilename = null;

    if (args.length < 1) {
      System.out.println("usage: java sample4 <infile>");
      System.exit(0);
    }
    filename = args[0];
    tmpfilename = "out.tmp";
    System.out.println("JDBC sample application starts...");
    System.out.println("Application tries to register
        the driver.");
    // this is the recommended way for registering Drivers
    Driver d =
        (Driver)Class.forName("solid.jdbc.SolidDriver").
            newInstance();
    System.out.println("Driver succesfully registered.");
    // the user is asked for a connect string
    System.out.println("Now sample application needs a
        connectstring in format:\n");
    System.out.println(
        "jdbc:solid://<host>:<port>/<user name>/<password>\n");
    System.out.print("\nEnter the connect string >");
    BufferedReader reader =
        new BufferedReader(new InputStreamReader(System.in));
    String sCon = reader.readLine();

    // next, the connection is attempted
    System.out.println("Attempting to connect :" + sCon);
    conn = DriverManager.getConnection(sCon);
    System.out.println("SolidDriver succesfully connected.");

    // drop and create table sample4
    createsample4();

    // insert data into it
    Store(filename);

    // and restore it
    Restore(tmpfilename);

    conn.close();
    // and it is all over
    System.out.println("\nSample application finishes."); }
    static void Store(String filename) {
      String sql = "insert into sample4 values(?,?)"; FileInputStream inFileStream ;
    try {
      File f1 = new File(filename);
      int blobsize = (int)f1.length();
      System.out.println("Inputfile size is "+blobsize);
      inFileStream = new FileInputStream(f1);
      PreparedStatement stmt = conn.prepareStatement(sql);
      stmt.setLong(1, System.currentTimeMillis());
      stmt.setBinaryStream(2, inFileStream, blobsize);
      int rows = stmt.executeUpdate();
      stmt.close();
      System.out.println(""+rows+" inserted.");
      conn.commit();
   }

  catch (SQLException ex) {
    printexp(ex);
  }
  catch (java.lang.Exception ex) {
    ex.printStackTrace (); }
  }

  static void Restore(String filename) {
    String sql = "select id,blob from sample4";
    FileOutputStream outFileStream ;
  try {
    File f1 = new File(filename);
    outFileStream = new FileOutputStream(f1);
    PreparedStatement stmt = conn.prepareStatement(sql);
    ResultSet rs = stmt.executeQuery();
    int readsize = 0;
    while (rs.next()) {
      InputStream in = rs.getBinaryStream(2);
      byte bytes[] = new byte[8*1024];
      int nRead = in.read(bytes);
      while (nRead != -1) {
        readsize = readsize + nRead;
        outFileStream.write(bytes,0,nRead);
        nRead = in.read(bytes);
      }
    }
    stmt.close();
    System.out.println("Read "+readsize+" bytes from database");
      }
   catch (SQLException ex) {
   printexp(ex); } catch (java.lang.Exception ex)
   {
   ex.printStackTrace ();
   }
  }

static void createsample4()
  { Statement stmt = null;
  String proc = "create table sample4 (" +
      "id numeric not null primary key,"+
      "blob long varbinary)";
  try {
    stmt = conn.createStatement();
    stmt.execute("drop table sample4");
  }
  catch (SQLException ex) {
    printexp(ex);
  }
  try {
    stmt.execute(proc);
  }
  catch (SQLException ex) {
    printexp(ex);
  System.exit(-1);
}
}

static void printexp(SQLException ex) {
  System.out.println("\n*** SQLException caught ***");
  while (ex != null) {
    System.out.println("SQLState: " + ex.getSQLState());
    System.out.println("Message: " + ex.getMessage());
    System.out.println("Vendor: " + ex.getErrorCode());
    ex = ex.getNextException (); } }
}