1 """ Derby Class SimpleApp.py
   2     Add derby.jar to your CLASSPATH
   3     Ported by: Alfonso Reyes, September 2007
   4 
   5 """
   6 from RSMD import printRSMD, RSMD    # nice methods I downloaded from IBM site
   7 from java.sql import Connection;
   8 from java.sql import DriverManager;
   9 from java.sql import ResultSet;
  10 from java.sql import SQLException;
  11 from java.sql import Statement;
  12 
  13 from java.util import Properties;
  14 from java.lang import Class
  15 
  16 class SimpleApp:
  17     def __init__(self):
  18         # the default framework is embedded
  19         self.framework = "embedded";
  20         self.driver = "org.apache.derby.jdbc.EmbeddedDriver";
  21         self.protocol = "jdbc:derby:";
  22         self.username = "user1";
  23         self.password = "user1";
  24 
  25     def go(self):
  26         # parse the arguments to determine which framework is desired
  27         #parseArguments(args);
  28         """   The driver is installed by loading its class.
  29         In an embedded environment, this will start up Derby, since it is not already running.
  30 
  31         """
  32         ds = None;
  33         conn = None;
  34         props = Properties();
  35         props.put("user", self.username);
  36         props.put("password", self.password);
  37         print props
  38 
  39         # check for J2ME specification - J2ME must use a DataSource further on */
  40         javaspec = props.getProperty( "java.specification.name" );
  41 
  42         """
  43            The connection specifies create=true in the url to cause
  44            the database to be created. To remove the database,
  45            remove the directory derbyDB and its contents.
  46            The directory derbyDB will be created under
  47            the directory that the system property
  48            derby.system.home points to, or the current
  49            directory if derby.system.home is not set.
  50 
  51          """
  52         Class.forName(self.driver).newInstance();
  53         print "Loaded the appropriate driver."
  54 
  55         database = "derbyDB5"   # put here the name for your database
  56         dbStr = self.protocol + database + ";create=true"
  57         print dbStr
  58         conn = DriverManager.getConnection(dbStr, props);
  59         print "Connected to and created database derbyDB"
  60 
  61         conn.setAutoCommit(False);
  62 
  63         """    Creating a statement lets us issue commands against
  64         the connection.
  65 
  66         """
  67         s = conn.createStatement();
  68 
  69         #   We create a table, add a few rows, and update one.
  70         s.execute("create table derbyDB(num int, addr varchar(40))");
  71         print "Created table derbyDB"
  72         s.execute("insert into derbyDB values (1956,'Webster St.')");
  73         print "Inserted 1956 Webster"
  74         s.execute("insert into derbyDB values (1910,'Union St.')");
  75         print "Inserted 1910 Union"
  76         s.execute("insert into derbyDB values (1,'Wandering Oak')");
  77         print "Inserted 1 Wandering Oak"
  78 
  79         s.execute(
  80             "update derbyDB set num=180, addr='Grand Ave.' where num=1956");
  81         print "Updated 1956 Webster to 180 Grand"
  82 
  83         s.execute(
  84             "update derbyDB set num=300, addr='Lakeshore Ave.' where num=180");
  85         print "Updated 180 Grand to 300 Lakeshore"
  86 
  87         #  We select the rows and verify the results.
  88         rs = s.executeQuery("SELECT num, addr FROM derbyDB ORDER BY num");
  89 
  90         print "Verified the rows"
  91         stmt   = conn.createStatement()
  92         Query  = 'SELECT * FROM derbyDB'
  93         rs     = stmt.executeQuery( Query )
  94         rsmd   = RSMD( rs )
  95         printRSMD( rsmd, Query )
  96 
  97         rowCount = 0
  98         while ( rs.next() ) :
  99             rowCount += 1
 100             row = ( rs.getInt( 1 ), rs.getString( 2 ) )
 101             print row
 102 
 103         stmt.close()        # close stmt connection
 104         s.execute("drop table derbyDB");
 105         print"Dropped table derbyDB"
 106 
 107         # We release the result and statement resources.
 108         rs.close();
 109         s.close();
 110         print "Closed result set and statements"
 111 
 112         #  We end the transaction and the connection.
 113         conn.commit();
 114         conn.close();
 115         print "Committed transaction and closed connection"
 116 
 117         """   In embedded mode, an application should shut down Derby.
 118            If the application fails to shut down Derby explicitly,
 119            the Derby does not perform a checkpoint when the JVM shuts down, which means
 120            that the next connection will be slower.
 121            Explicitly shutting down Derby with the URL is preferred.
 122            This style of shutdown will always throw an "exception".
 123 
 124         """
 125         gotSQLExc = False;
 126         try:
 127             DriverManager.getConnection("jdbc:derby:;shutdown=true")
 128         except SQLException:
 129             print "Catching exceptions"
 130             gotSQLExc = True;
 131 
 132         if (not gotSQLExc):
 133             print "Database did not shut down normally"
 134         else:
 135             print "Database shut down normally"
 136         print("SimpleApp finished");
 137 
 138 if __name__ == '__main__':
 139     SimpleApp().go();