General Database Topics

Blobs

   1:
   2:InputStream getBlobStream(String id, String fieldName, QueryDataSet query)
   3:  {
   4:    InputStream in=null;
   5:    try
   6:    {
   7:      java.io.InputStream stream;
   8:
   9:      java.sql.Statement s  = null;
  10:      java.sql.ResultSet rs = null;
  11:
  12:      java.sql.Connection c = query.getDatabase().getJdbcConnection();
  13:      s = c.createStatement ();
  14:
  15:      rs = s.executeQuery ("select " + fieldName + " from MYTABLE where
  16:ID="+id);
  17:      rs.next ();
  18:      in = rs.getBinaryStream (fieldName);
  19:      s.close ();
  20:    }
  21:    catch (Exception ex)
  22:    {
  23:      System.err.println("getBlobStream: "+ex.getMessage());
  24:    }
  25:    return in;
  26:  }
  27:
  28:
      

 1:PreparedStatement pstmt = dbConn.prepareStatement (sqlString);
 2://do this to find length.
 3:ByteArrayInputStream imageByteStream = (ByteArrayInputStream)
 4:readWriteRow.getBinaryStream("Photo");
 5:int len = imageByteStream.available();
 6:pstmt.setBinaryStream(1,readWriteRow.getBinaryStream("Photo"),len);
 7:pstmt.executeUpdate();
      

Hint: By default DataExpress uses the JDBC PreparedStatement.setObject() method to set blob parameters against a JDBC driver. If this does now work with your JDBC driver, try setting the Database component UseSetObjecForStreams property to false.

 1:    if (fieldControl1.isEditing())
 2:      try {
 3:        fieldControl1.endEdit();
 4:        } catch (Exception e2) { e2.printStackTrace(); }
 5:
 6:    try {
 7:      BufferedInputStream img = new BufferedInputStream(new
 8:FileInputStream(fieldControl1.getText()));
 9:      queryDataSet1.setBinaryStream(1,img);
10:
11:    } catch (Exception e1) { e1.printStackTrace(); }
      

Stream Tips

Streams have some additional issues that should be kept in mind.

When JB is loading the data from the DB, it doesn´t load only one row. It fetches a lot (or all ) rows, which can be seen in the grid, for example. But the INPUTSTREAM Object, which comes from the DB only exists after each fetch. So when JB fetches more then one row, only the last INPUTSTREAM is valid.

Therefore there is the need to get the Object out of the stream direct after the fetch. But this is not very straight forward.. So one way is to add another querydataset which only select one row with the Object. Add a noresolvable column to the original qds. - Add a navigation-listener to the qds. In the listener check if the nonresolvalble column is null. if it is null, i execute the second query to read the object into the nonresolvable column. Add an QueryResolver and bind it to the Query. Add an Inserting and updating event in the QueryResolver. Some sample code looks like this:

 1: void qrFilterOperation_updatingRow(ReadWriteRow row, ReadRow oldRow,
 2:ResolverResponse response) throws DataSetException {
 3:   try {
 4:
 5:row.setInputStream("OBJECT",readWriteBLOB.getStoreStream(row.getObject("REFOBJECT")));
 6:    }
 7:    catch (IOException ioe) {
 8:       ioe.printStackTrace();
 9:     }
10:  }
11:
12:  void qrFilterOperation_insertingRow(ReadWriteRow row,
13:ResolverResponse response) throws DataSetException {
14:    try {
15:
16:row.setInputStream("OBJECT",readWriteBLOB.getStoreStream(row.getObject("REFOBJECT")));
17:    }
18:    catch (IOException ioe) {
19:       ioe.printStackTrace();
20:     }
21:  }
22:
      

 1:      Connection jdbcConnection = database.getJdbcConnection();
 2:      PreparedStatement ps = jdbcConnection.prepareStatement(
 3:        "insert into Exams( Exam_ID, Cargo) values( ?, ?)");
 4:      ps.setInt(1, 1);
 5:      FileInputStream is = new FileInputStream("C:\\autoexec.bat");
 6:      int isLen = 93880;
 7:      ps.setAsciiStream(2, is, isLen);
                             

DB120: (From the JB6 Tips and Hints)

Cannot save or change BLOB fields?

Turns out there are several different behaviors/requirements from various SQL servers for searched updates on blob fields like VARCHAR. BDE SQL links will not include such fields in the where clause of a searched update. We will probably make this same change in the future. There are three possible solutions for now (number 1 or 2 are more desirable):

1. Set the QueryResolver.UpdateMode property to UpdateMode.KEY_COLUMNS. QueryResolver component must be added to QueryDataSet's container and set on the QueryDataSet's Resolver property.
2. Before calling Database.saveChanges(), set the Column.Searchable property to false. This must always be done after the QueryDataSet's query is run (from QueryDataSet.open() or QueryDataSet.executeQuery()) method because the Column.Searchable property will be overridden by MetaData inspection when the query is run.
3. Set QueryDataSet.MetaDataUpdate to NONE. This forces you to set all the appropriate MetaData info on the column component including Column.RowId, and Column.Searchable properties.

   1:    java.sql.Connection c = null;
   2:    java.sql.Statement s = null;
   3:    java.sql.ResultSet rs = null;
   4:    ...insert some code here for establishing the connection c...
   5:    try {
   6:      java.io.InputStream stream;
   7:      byte[] readBuffer = new byte[...make this big enough to accomodate your
   8:largest blob...];
   9:
  10:      startTime = System.currentTimeMillis ();
  11:      s = c.createStatement ();
  12:      rs = s.executeQuery ("select BLOB_FIELD from A_TABLE");
  13:      while (rs.next ()) {
  14:         stream = rs.getBinaryStream ("BLOB_FIELD");
  15:         int bytesRead;
  16:         while ((bytesRead = stream.read (readBuffer)) != -1) {
  17:            ...ok, you've now got bytesRead blob bytes in the readBuffer, do
  18:as you will with them...
  19:            ...note: the entire blob stream may not be read in one read, but
  20:the stream will be read until eof (-1)...
  21:        }
  22:      }
  23:      s.close ();
  24:      endTime = System.currentTimeMillis ();
  25:      elapsedTime = endTime - startTime;
  26:    }
  27:    catch (java.io.IOException e) {
  28:      ...
  29:    }
  30:    // This finally clause will be executed even if "return" was called in
  31:case of any exceptions above.
  32:    finally {
  33:      System.out.println ("Closing database resources and rolling back any
  34:changes we made to the database.");
  35:
  36:      // Now that we're all finished, let's release database resources.
  37:      try { if (rs!=null) rs.close (); } catch (java.sql.SQLException e) {
  38:showSQLException (e); }
  39:      try { if (s!=null) s.close (); } catch (java.sql.SQLException e) {
  40:showSQLException (e); }
  41:
  42:      // Before we close the connection, let's rollback any changes we may
  43:have made.
  44:      try { if (c!=null) c.rollback (); } catch (java.sql.SQLException e) {
  45:showSQLException (e); }
  46:      try { if (c!=null) c.close (); } catch (java.sql.SQLException e) {
  47:showSQLException (e); }
  48:
  49:     // If you don't close your database objects explicitly as above,
  50:     // they may be closed by the object's finalizer, but there's
  51:     // no guarantee if or when the finalizer will be called.
  52:     // In general, object finalizers are not called on program exit.
  53:     // It's recommended to close your JDBC objects explictly,
  54:     // but you can use System.runFinalizersOnExit(true), as at the beginning
  55:     // of this method main(), to force finalizers to be called before
  56:     // program exit.
  57:    }
                             

Connection Examples

   1:import java.net.URL;
   2:import java.sql.*;
   3:public class jdbc1 {
   4: public static void main(String[] argv) {
   5:  try {
   6:   Class.forName("borland.jdbc.Broker.RemoteDriver");  //loads the
   7:driver
   8:  }
   9:  catch (ClassNotFoundException ex) {
  10:      System.out.println ("\n*** ClassNotFoundException caught ***\n");
  11:  }
  12:
  13:  try {
  14:   java.sql.Connection con=
  15:      DriverManager.getConnection("jdbc:BorlandBroker://205.227.215.126/dmv",
  16:      "sa", "");
  17:
  18:   java.sql.Statement stmt = con.createStatement();
  19:   java.sql.ResultSet rs = stmt.executeQuery("SELECT License_No,
  20:        Last_Name from dmv");
  21:   while (rs.next())
  22:   {
  23:    // print the values for the current row.
  24:    String i = rs.getString("License_No");
  25:    String s = rs.getString("Last_Name");
  26:    System.out.println("ROW = " + i + " " + s );
  27:   }
  28:  } catch (SQLException ex) {
  29:       System.out.println ("\n*** SQLException caught ***\n");
  30:
  31:   while (ex != null) {
  32:           System.out.println ("SQLState: " +ex.getSQLState ());
  33:           System.out.println ("Message:  " + ex.getMessage());
  34:           System.out.println ("Vendor:   " +ex.getErrorCode ());
  35:           ex = ex.getNextException ();
  36:           System.out.println ("");
  37:                     }
  38:  }
  39: }
  40:}
  41:
      

You can add a listener to your connection using the following code:
   1:
   2:   database.addConnectionUpdateListener(new ConnectionUpdateAdapter() {
   3:      public void connectionClosed(ConnectionUpdateEvent event) {
   4:        System.out.println("Database is closed.");
   5:      }
   6:      public void connectionOpening(ConnectionUpdateEvent event) {
   7:        System.out.println("Database is opening.");
   8:      }
   9:    }) ;
      

You can use JDBC code to get the tables and columns. Here is a code fragment that assumes you have an open JDBC connection named "connection" and that you have a listbox named "listbox":

   1:    DatabaseMetaData md = null;
   2:    ResultSet r = null;
   3:
   4:    try {
   5:        md = connection.getMetaData();
   6:        r = md.getTables("", "", "%", null);
   7:        while (r.next()) {
   8:            listbox.addItem(r.getString(3));
   9:        }
  10:    } catch (java.sql.SQLException error) {
  11:    }
  12:
      

JDBC General

How do I get a return value from a stored procedure which inserts into a table?

If you use JDBC2.0,you must use RegisterOutParameter method of CallableStatement,

   1:Connection conn = DriverManager.getConnection
   2:("jdbc:oracle:oci8:@<hoststring>", "scott", "tiger");
   3:CallableStatement cs = conn.prepareCall ("begin ? := foo(?); end;");
   4:cs.registerOutParameter(1,Types.CHAR);
   5:cs.setString(2, "aa");
   6:cs.executeUpdate();
   7:
   8:String strReturn = cs.getString(1);
      

DataExpress Notes

I want to display the date-time colums with my country locale: Spain. It works on one computer but not another.

If you set the displayMask or editMask property, JBuilder won't change it if you move to a different locale. But if you let those properties take their default values, the defaults will vary according to your machine's locale. So the first thing to check here is that the operating system locale for the computer the application is deployed on is Spanish. It probably is. Locale set on a column over-rides the the locale for the dataset as a whole.

Locale set for a dataset over-rides the locale for JBuilder as a whole. It may be feasible in this application to set the locale for the DataSetView rather than for individual columns (assuming it's okay to use the default Spanish format for other datatypes, such as numbers). I don't see why this would make a difference, but it might.

Locale information comes from the java.text.resources.DateFormatZoneData... classes. We look for a class for the specified language and country (DateFormatZoneData_es_ES.class). If we don't find it, we work up the hierarchy, looking for just the language (DateFormatZoneData_es.class) and then for the default class (DateFormatZoneData.class), which contains English data. (This is all standard Java resource bundle behavior, of course, not JBuilder's invention.) The Deployment Wizard won't deploy them with the application, on the assumption that they are already present in the JRE on the target machine. Maybe the classes are not being found on the target machine?

I would like to know if there is a way to make absolutely sure that data (results of old queries) cached by QDS objects is made available for gc. Is there a way to do this ?

If the detail DataSet in question has a MasterLink property set and has a fetchAsNeeded set to true, I do see potential for memory leakage when using MemoryStore (the default). DataStore does not have this problem, but it does not make sense for an applet. The problem I see is that refreshing just a detail group nulls out the values allocated, but the arrays used for each Column of storage are not shrunk. If you refresh/empty the whole DataSet, the arrays and all your memory should come back. I can tell you how to do this, but it means that rows from all detail groups (for every master) will be released. This may not be desirable for you, but here is what you could do:

   1:DataSetView view = new DataSetView();
   2:view.setStorageDataSet(qsdD);
   3:view.empty();  // Since the view sees all details, empty will release all
   4:               // rows and the row arrays will shrink.
   5:view.close();   // Its important to close views when you are done with them,
   6:because the associated StorageDataSet will have a listener reference to all
   7:open DataSetViews.
   8:
      

Note that I am "guessing" that this is your problem. If you do not set the MasterLinkDescriptor.FetchAsNeeded property to true, then this is not the problem you are encountering. Note that there are several tools available for detecting memory leaks like JProbe and Optimizeit. The VM also has some basic memory usage reports.

DataSetException: "no unique identifers"

borland.jbcl.dataset.DataSetException: DataSet has no unique row identifiers. Note: For QueryDataSets you need to disable metaDataUpdate.ROWID in the MetaData Update property, in addition to setting the RowId property on a column.

Dataexpress creates update, delete and insert SQL based on the properties set. None of these operations can be performed without a valid "WHERE" clause which requires that there be a "unique row identifier". Generally Dataexpress "discovers" this information via the driver, but there are occassions when the driver does not support providing this information, or you have turned this discovery off (via metaDataUpdate.ROWID or the MetaData update property). To solve this problem the row identifier must be added, either by way of normal metadata discovery or via setting the property manually via the column properties "rowID" is set to true.

Setup Instructions

Inside the QueryDataSet you have to mark the columns that are the key values. To do this select the QueryDataSet (QDS) and hit the '+' to the right of it, this will expand and give you the columns included with the QDS. Click on the columns that represent the key values and then select the rowid property in the inspector and set it to true. Next in the QDS go into the metaDataUpdate property and only select the table item.

RowStatus Discovery

The following code does not work as you would have expected:

   1:If ((dataSet.getStatus() & RowStatus.UPDATED) != 0) {
   2:        rowStatus = dataSetView.getStatus();
   3:        System.out.print("row: " + rowNum + ", rowStatus: " + rowStatus + ",
   4:rowStatus type - " );
   5:
   6:        if (rowStatus == com.borland.dx.dataset.RowStatus.DELETED )
   7:           System.out.println("Deleted");
   8:        else if (rowStatus==com.borland.dx.dataset.RowStatus.INSERTED )
   9:           System.out.println("Inserted");
  10:        else if (rowStatus==com.borland.dx.dataset.RowStatus.LOADED )
  11:           System.out.println("Loaded");
  12:        else if (rowStatus==com.borland.dx.dataset.RowStatus.ORIGINAL )
  13:           System.out.println("Original");
  14:        else if
  15:(rowStatus==com.borland.dx.dataset.RowStatus.PENDING_RESOLVED )
  16:           System.out.println("Pending_Resolved");
  17:        else if (rowStatus==com.borland.dx.dataset.RowStatus.UPDATED )
  18:           System.out.println("Updated");
  19:        else
  20:           System.out.println("unknown:"+rowStatus);   // some status codes are not listed
  21:in the RowStatus documentation

      

RowStatus is a bitwise setting so it can contain multiple values therefore the following code

  22:if ( (rowStatus & RowStatus.UPDATED) ==
  23:        com.borland.dx.dataset.RowStatus.UPDATED )  {
  24:    requestsQueryDataSet.getOriginalRow(requestsQueryDataSet,orig);
  25:    // other code here
  26:    }
  27:}
      

No rows affected

I'm utilizing a GridControl, QueryDataSet and an Oracle JDBC connection. The problem is that periodically I get a 'No row was affected by resolution > query:' error when I save changes.

The resolver is using a "searched delete" to locate and delete the row. There is an UpdateMode property setting on the QueryResolver used for resolving. It determines which columns are used for locating a row for updates or deletes. By default, all searchable columns are used. To set this property you must drop a QueryResolver component from the tool pallete and wire the Resolver property on your Table/QueryDataSet. There are reason you get the "no rows affected" error is because an "exact" match could not be located. This can be caused by several things:

1. Someone else deleted/updated the row.
2. Rounding/precision loss on numeric columns from a previous update/insert. Use weaker update mode UpdateMode.KEY_COLUMNS).
3. Server has a calc field. Original insert causes calc field to be updated. Secondary update/delete cannot locate row because server updated the calc field. Use weaker update mode (UpdateMode.KEY_COLUMNS or UpdateMode.CHANGED_COLUMNS).

Call java.sql.DriverManager.setLogStream(System.out) to see what your JDBC driver is doing.

Having problems resolving a multi table QueryDataSet

You need to look at setResolvable(false) on the fields of the table you don't want to resolve. This is the important thing. You want to set all the non resolving columns in the "non-updating" table as setResolvable(false). You also must have an index on the dataset so metadata finds it. Set ROWID to true only for the columns in the "updating" table that are the key there.