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