Cloudscape and the Derby Project
by Ken North


Listing One

D:\Cloudscape\frameworks\embedded\bin>set CLOUDSCAPE_INSTALL=D:\Cloudscape

D:\Cloudscape\frameworks\embedded\bin>java com.ihost.cs.tools.sysinfo
------------------ Java Information ------------------
Java Version:    1.4.2_05
Java Vendor:     Sun Microsystems Inc.
Java home:       C:\j2sdk1.4.2_05\jre
Java classpath:  D:\CLOUDS~1\lib\cs.jar;D:\CLOUDS~1\lib\cstools.jar
OS name:         Windows XP
OS architecture: x86
OS version:      5.1
Java user name:  north
Java user home:  C:\Documents and Settings\north.KNC
Java user dir:   D:\Cloudscape\frameworks\embedded\bin
--------- Cloudscape Information --------
[D:\Cloudscape\lib\cs.jar] 10.0.1.0 beta - (29729)
[D:\Cloudscape\lib\cstools.jar] 10.0.1.0 beta - (29729)
------------------------------------------------------
----------------- Locale Information -----------------
------------------------------------------------------
D:\Cloudscape\frameworks\embedded\bin>ij
D:\Cloudscape\frameworks\embedded\bin>set CLOUDSCAPE_INSTALL=D:\Cloudscape
D:\Cloudscape\frameworks\embedded\bin>java -Dij.protocol=jdbc:cloudscape: 
                                                      com.ihost.cs.tools.ij
ij version 10.0 (c) 1997, 2004 IBM Corp.
ij> connect 'jdbc:cloudscape:magic;create=true';
ij>


Listing Two

connect 'jdbc:cloudscape:magic' user 'user1' password 'user1';
CREATE TABLE notices (n_version DATE, n_desc VARCHAR(128), 
                                      n_type VARCHAR(5), n_data LONG VARCHAR);
CREATE TABLE scripts (s_version DATE, s_desc VARCHAR(128), 
                                      s_type VARCHAR(5), s_data LONG VARCHAR);
CREATE TABLE xschemas (x_version DATE,x_desc VARCHAR(128), 
                                      x_type VARCHAR(5), x_data LONG VARCHAR);
CREATE TABLE documents (d_version DATE,d_desc VARCHAR(128), 
                                       d_type VARCHAR(3), d_data LONG VARCHAR);


Listing Three

/* Created on Aug 31, 2004 */
package docStoreWithSQL;

/** @author north **/
/****************************************************************************
* FILE NAME:  DocToCloud.java     TITLE: Import HTML into an SQL column
* AUTHOR:  Ken North, Ken North Computing, LLC
* Program reads a web page (HTML) and stores it in an SQL column using JDBC. 
**********************************************************************
* Copyright (c) Ken North Computing, 2004. All rights reserved. Reproduction
* or translation of this work beyond that permitted in Section 117 of the
* United States Copyright Act without express written permission of the
* copyright owner is unlawful.
* The purchaser may make backup copies for his/her own use only and not for
* distribution or resale. The Author and Publisher assume no responsibility
* errors, omissions or damages caused by the use of these programs or from
* use of the information contained herein.
**********************************************************************/

import java.sql.*;
import java.io.*;
import java.util.Properties;

public class DocToCloud {
  public static String protocol = "jdbc:cloudscape:";
  public static void main (String args [])
       throws SQLException, ClassNotFoundException, IOException
  {
    Properties prop = new Properties();
    prop.put("user", "user1");
    prop.put("password", "user1");
    
    System.setProperty("cloudscape.system.home","c:\\cloudscape");
    System.out.println("DocToCloud starting with password and user set");
                        // load the Cloudscape driver 
    Class.forName("com.ihost.cs.jdbc.CloudscapeDriver");
                             //   connect to "magic" database 
    Connection conn = DriverManager.getConnection(protocol + "magic", prop);
    System.out.println("Connected to magic database");
                            // create JDBC Statement for SQL statement
    Statement stmt = conn.createStatement ();
                            // DROP the existing notices table
    try
    {
      stmt.execute ("DROP TABLE notices");
    }
    catch (SQLException e)
    {
                 // catch and ignore table not found exception
    }
                // CREATE the notices table to store standard HTML notices
    // stmt.execute ("CREATE TABLE notices (n_version DATE, 
    //                        n_desc VARCHAR(128), n_data LONG VARCHAR)");
    stmt.execute ("CREATE TABLE notices (n_version DATE, n_desc VARCHAR(128), 
                                   n_type VARCHAR(5), n_data LONG VARCHAR)");
    System.out.println("Created notices table");
                           // read HTML file using input stream
    File file = new File ("d:\\north\\articles\\ddj\\PrivacyPolicy.htm");
    InputStream istr = 
       new FileInputStream ("d:\\north\\articles\\ddj\\PrivacyPolicy.htm");
                           // Do a prepared INSERT into the n_data column
    PreparedStatement pstmt = conn.prepareStatement 
          ("INSERT INTO notices (n_version, n_desc, n_type, n_data) 
                                                     VALUES (?, ?, ?, ?)");
    pstmt.setString (1, "2004-08-31");
    pstmt.setString (2, "Privacy Policy");
    pstmt.setString (3, "HTML");
    pstmt.setAsciiStream (4, istr, (int)file.length ());
    pstmt.execute ();
    System.out.println("Inserted row into notices table");
                        // close connection, statement
    if (pstmt != null)
      pstmt.close();
    if (conn != null)
      conn.close();
    System.out.println("Closed statement and connection.");
  }
}






1


