db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Kristian Waagan <Kristian.Waa...@Sun.COM>
Subject Re: Java stand alone application question?
Date Mon, 01 Jun 2009 20:44:42 GMT
Jim Crowell wrote:
> I am writing a Java Stand-Alone Application which is trying to use the Derby
> Data Base.
> 
> It looks like my Java code is setting up the DB Table OK but when I do the
> table insert
> I have problems. No errors occur but a Java method I wrote to display the
> table yields no inserts.

Hi Jim,

I admit I haven't looked in detail at your complete mail (it's getting 
late here at JavaOne...), but I do see a few issues.

If you connect to Derby without specifying a user name, it will use APP 
by default. APP will also be the selected schema. This means that your 
ij query is trying to find the table APP.AB.
To fix this, you can either connect with the user ME (with default 
settings you don't have to actually create the user, nor set the 
password), you can connect with any user you want and then run "SET 
SCHEMA ME", or specify the table with the schema name (ME.AB).

When it comes to ij, you can play around with just executing normal 
statements before trying to get the prepared statements working. For 
ad-hoc activities, I find prepared statements a bit awkward to work with 
in ij.
For instance, just type in the SQL (assuming the schema is set to ME);
INSERT INTO AB VALUES ('abc', 'def'), ('aaa', 'bbb');
SELECT * FROM AB;

For the Java JDBC insert code, I would suggest going back to using a 
prepared statement, something like this:
PreparedStatement psInsert = conn.prepareStatement(
         "INSERT INTO AB VALUES (?,?)");
     for (int i...) {
         psInsert.setString(1, keyName);
         psInsert.setString(2, segments[i]);
         psInsert.executeUpdate();
     }
psInsert.close();
// If autocommit is disabled, commit the transaction.
conn.commit();


Hope this helps,
-- 
Kristian

> 
> Therefore, I tried to use the 'ij' tool to see if the inserts are there or
> not.
> 
> The following Java method was used to create the table in my Java App.
> 
> public static void createTable(
> 	Connection conn,
> 	String tableName) {
> 	//	If the given Table does not exist in the Data Base...
> 	if (!tableExist(conn, tableName)) {
> 		try {
> 			statement = conn.createStatement();
> 			statement.execute(
> 				"create table "+tableName+"(" +
>     	        		"nameKey varchar(30) NOT NULL PRIMARY KEY, "+
>     	        		"value varchar(300))");
>     	        		conn.commit();
> 			} catch (SQLException sqle) {
> 				printSQLException(sqle);
> 			}	//	End 'catch'...
> 		}	//	End 'tableExist : false IF...
> 	else {
> 		JFormFrame.fyi("JDerby_IO.createTable:",
> 		"Given table ["+tableName+"] already exist in the "+
> 		QUOTE+dbName+QUOTE+" Derby Data Base.."+EOP+
> 		displayTableNames(conn));
> 	}	//	End 'tableExist : false ELSE...
> }	//	End 'createTable' Method...
> 
> Being new to ‘ij’ I have struggled trying to learn how to display the table
> generated above.
> 
> The following 'ij' presentation is derived from a sql script file.
> 
> The connection is successful and the table generated by the above code [ab]
> is seen with the
> "SHOW TABLES" 'ij' command but is not seen by the subsequent 'ij' / 'sql'
> commands.
> 
> ij version 10.5
> ij> 	RUN 'C:\SLPLUS\CONNECT.SQL';
> ij> -- ENTERED 'connect.ij' SCRIPT FILE...
> PROTOCOL 'jdbc:derby:';
> ij> -- connect 'C:\SLPlus\db\BJCROW4936SYNYaBcDeF';
> connect 'C:\SLPlus\db\BJCROW4936SYNYaBcDeF';
> ij> -- show tables...
> SHOW TABLES;
> TABLE_SCHEM         |TABLE_NAME                    |REMARKS             
> ------------------------------------------------------------------------
> SYS                 |SYSALIASES                    |                    
> SYS                 |SYSCHECKS                     |                    
> SYS                 |SYSCOLPERMS                   |                    
> SYS                 |SYSCOLUMNS                    |                    
> SYS                 |SYSCONGLOMERATES              |                    
> SYS                 |SYSCONSTRAINTS                |                    
> SYS                 |SYSDEPENDS                    |                    
> SYS                 |SYSFILES                      |                    
> SYS                 |SYSFOREIGNKEYS                |                    
> SYS                 |SYSKEYS                       |                    
> SYS                 |SYSROLES                      |                    
> SYS                 |SYSROUTINEPERMS               |                    
> SYS                 |SYSSCHEMAS                    |                    
> SYS                 |SYSSTATEMENTS                 |                    
> SYS                 |SYSSTATISTICS                 |                    
> SYS                 |SYSTABLEPERMS                 |                    
> SYS                 |SYSTABLES                     |                    
> SYS                 |SYSTRIGGERS                   |                    
> SYS                 |SYSVIEWS                      |                    
> SYSIBM            |SYSDUMMY1                     |                    
> ME                  |AB                            |                    
> 
> 21 rows selected
> ij> autocommit off;
> ij> prepare abInsert as 'INSERT INTO ab VALUES(?, ?, ?)';
> ERROR 42X05: Table/View 'AB' does not exist.
> ij> execute abInsert using 'VALUES
>    (''abc'', ''def'')';
> IJ ERROR: Unable to establish prepared statement ABINSERT
> ij> commit;
> ij> select * from ab;
> ERROR 42X05: Table/View 'AB' does not exist.
> ij>
> 
> The above script was designed to show some attempts I have made to insert
> some dummy items into the table.
> 
> The following Java method shows the code I am using to insert 6 rows
> into the 'Address Book' table with each 'keyName' being a unique primary
> key:
> 
> public static void insertAddressBookName(
> 		Connection conn,
> 		String nameKey,
> 		String [] segments) {
> 		ArrayList<String> list = new ArrayList<String>(maxSegments);
> 		int nSegments = segments.length;
> 		try {
> 			Statement sta = conn.createStatement();
> 			int count = 0;
>     		for (int i=0; i<maxSegments; i++) {
>     			String keyName = nameKey+abKeyCodes[i];
> 			//	String v = " VALUES ("+keyName+COMMA+segments[i]+")";
>     			int c =
>     				sta.executeUpdate("INSERT INTO AB"+
> 				" VALUES ('"+keyName+"','"+segments[i]+"')");
>     			count += c;
>     			System.out.println("c|count|keyName|value = "+
>     			c+”|”+count+”|”+keyName+”|”+segments[i]);
>     			conn.commit();
>     		}	//	End 'segments' LOOP...
>     		sta.close();
> }	//	End ‘insertAddressBookName’ Method...
> 
> My initial code used the 'Prepared Statement' approach but I changed to the
> above
> more direct but less effective method during my debug process.
> 
>  Any suggestions on what I am missing?
> 
> 
> 
> -----
> Regards,
> Jim...


Mime
View raw message