MacLochlainns Weblog

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

After you grant privileges to read, write, and delete files with Oracle’s dbms_java package, you must disconnect any active session to enjoy the new privileges. If you forget to disconnect and re-connect an active session, you get the following error message:

FROM     TABLE(list_files(get_directory_path('STAGE')))
               *
ERROR AT line 2:
ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION:
JAVA.security.AccessControlException: DIRECTORY permissions restricted.

The problem and fix are simple. The permissions aren’t in effect until after you disconnect and reconnect your active sessions. I put this there because when I Google’d it, there wasn’t an answer already.

While I’m on the topic of privilege errors, that aren’t really errors, I thought it would be helpful to qualify how the delete permission works because I couldn’t find it anywhere in the Oracle documentation (if somebody finds it let make a comment that enriches the discussion). Unlike the read and write permissions, the delete permission requires permissions on specific files.

If you only grant permission on like:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read,write,delete');
  6  END;
  7  /

You would get this message when trying to delete an external file:

BEGIN
*
ERROR AT line 1:
ORA-29532: JAVA CALL terminated BY uncaught JAVA EXCEPTION:
JAVA.security.AccessControlException: the Permission (JAVA.io.FilePermission
c:\data\LOG\item_import.LOG read) has NOT been granted TO IMPORTER. The PL/SQL
TO GRANT this IS dbms_java.grant_permission( 'IMPORTER',
'SYS:java.io.FilePermission', 'c:\data\log\item_import.log', 'read' )
ORA-06512: AT "IMPORTER.DELETE_FILE", line 1
ORA-06512: AT line 2

It requires that you grant permissions on individual files to have the privilege of deleting them from within Oracle. That grant would look like the following:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload\item_import.log'
  5                               ,'read,write,delete');
  6  END;
  7  /

As always, I hope this helps other developers.

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
oracle.aurora.vm.IdNotFoundException: importer : USER OR ROLE id does NOT exist
ORA-06512: at "SYS.DBMS_JAVA", line 705
ORA-06512: at line 2

The problem was simple, while not published, you must enter the user/schema name in uppercase text when calling DBMS_JAVA.GRANT_PERMISSION procedure. Here’s a proper example:

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read');
  6  END;
  7  /

If you change the IMPORTER string to lower or mixed case, you raise the aforementioned error. Quite an interesting tidbit that I missed from Oracle Database 10g until Oracle Database 12c. Hope this helps you avoid wondering what’s happening when your line 2 looks like this:

  2    DBMS_JAVA.GRANT_PERMISSION('Importer'

As always, I hope this helps others. If you like more on writing Java inside the Oracle database you can check this older post.

Oracle OpenWorld 2013 and JavaOne 2013 are over and the America’s Cup a done deal. It was a full week. I didn’t have a moment to blog because I stay out in Livermore, which adds an hour plus to the beginning and ending of my day. It’s funny but I didn’t get a sense from some that they were excited about Oracle 12c. That’s probably because they don’t appear to know too much about it yet.

The Container Database (CDB) versus Pluggable Database (PDB) has enormous impact on how we can leverage Oracle in the private or public cloud. Admittedly, the ideas and concepts take some time to understand, implement, and appreciate.

Talking to folks, I got some great ideas for future blog posts. If you have any that I should add, let me know with a comment. I think the first one will show everyone how to migrate traditional surrogate key primary key columns to Oracle 12c‘s new Identity Columns.

Having seen some presentations referencing zetta-bytes, I wondered to myself (and now blog readers): “When will the Exadata server be replaced by a Zettadata server?”

Up there on my list for new blog posts are Oracle 12c‘s new Invisible Columns and new DBMS_REDACT package. For those interested, I’ll also present at UTOUG’s Fall Symposium on October 29th, 2013 on SQL and PL/SQL New Features in the Oracle Database 12c.

It was great to see familiar faces and sad to note those missing. Somehow the energy in Dell’s demo ground didn’t quite replace the old Quest Software demo ground’s pizazz.

Once home, I had to pivot back to task. I’m copy editing the Oracle Database 12c Programming PL/SQL Programming book, which will release in February 2014.

If you’re wondering about the photo of the Golden Gate Bridge, my son Joseph took it with his new iPhone 5s. I’m looking forward to my own iPhone upgrade in November. 😉

I registered yesterday for Oracle OpenWorld 2013, and I’ll look forward to seeing friends there. Having worked in the Oracle 12c beta for a year, I’ll be interested in the presentations. Also, hearing more about Java 7 at JavaOne. On the downside, I’m missing MySQL Connect this year.

Cloud computing offers many possibilities, and container and pluggable databases are a great solution. We’ve two new acronyms with the Oracle 12c release. A containerized database is a CDB, and a pluggable database is a PDB. I’m looking forward to seeing more about the provisioning of PDBs during the conference. If you’re new to the changes, check out CDBs and PDBs in Chapter 17 in the Oracle 12c Concepts manual.

A couple of my favorite new features are Identity and Invisible Columns. If you’re unfamiliar with the new features for application development, let me recommend this Oracle White Paper. Also, for reference I’ve covered identity and invisible columns thoroughly in the Oracle Database 12c PL/SQL Programming book, which will be available in December.

Missing the MySQL Connect 2013 Bus

Unfortunately, travel budgets preclude me attending MySQL Connect 2013 this year (alas, I’ll miss the bus). 🙁 It was hard because I’d like to see what’s up with MySQL (since I was a closet MySQL user at Oracle before they acquired it). Anyway, if you’re there, make sure you check out MySQL Workbench 6 for me. Also, I’d like to thank Dave Stokes for the AWESOME review he wrote on Amazon.com for my MySQL Workbench: Data Modeling & Development book. Maybe, I’ll get to go to MySQL Connect 2014 next year.

Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.

A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!

It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.

First of all, the argument against stored programs is simply not true. SQL DML statements, like the INSERT, UPDATE, and DELETE statements should maintain ACID compliant interactions with a single table in a database. Unfortunately, the same statements create anomalies (errors) in a poorly designed database.

Stored programs provide the ability to perform ACID compliant interactions across a series of tables in a database. They may also hide database design errors and protect the data from corruption. The same can’t be said for Java or C# developers. Java and C# developers frequently fail to see database design errors or they overlook them as inconsequential. This type of behavior results in corrupt data.

It typically raises cost, errors, and overall application complexity when key logic migrates outside the database. If you’re asking why, that’s great. Here are my thoughts on why:

1. Making a Java or C# programmer responsible for managing the transaction scope across multiple tables in a database is not trivial. It requires a Java programmer that truly has mastered SQL. As a rule, it means a programmer writes many more lines of logic in their code because they don’t understand how to use SQL. It often eliminates joins from being performed in the database where they would considerably outperform external language operations.
2. Identifying bottlenecks and poor usage of data becomes much more complex for DBAs because small queries that avoid joins don’t appear problematic inside the database. DBAs don’t look at the execution or scope of transactions running outside of the database and you generally are left with anecdotal customer complaints about the inefficiency of the application. Therefore, you have diminished accountability.
3. Developing a library of stored procedures (and functions) ensures the integrity of transaction management. It also provides a series of published interfaces to developers writing the application logic. The published interface provides a modular interface, and lets developers focus on delivering quality applications without worrying about the database design. It lowers costs and increases quality by focusing developers on their strengths rather than trying to make them generalists. That having been said, it should never mask a poorly designed database!
4. Service level agreements are critical metrics in any organization because they compel efficiency. If you mix the logic of the database and the application layer together, you can’t hold the development team responsible for the interface or batch processing metrics because they’ll always “blame” the database. Likewise, you can’t hold the database team responsible for performance when their metrics will only show trivial DML statement processing. Moreover, the DBA team will always show you that it’s not their fault because they’ve got metrics!
5. Removing transaction controls from the database server generally means you increase the analysis and design costs. That’s because few developers have deep understanding of a non-database programming language and the database. Likewise, input from DBAs is marginalized because the solution that makes sense is disallowed by design fiat. Systems designed in this type of disparate way often evolve into extremely awkward application models.

Interestingly, the effective use of T-SQL or PL/SQL often identifies, isolates, and manages issues in poorly designed database models. That’s because they focus on the integrity of transactions across tables and leverage native database features. They also act like CSS files, effectively avoiding the use of inline style or embedded SQL and transaction control statements.

Let’s face this fact; any person who writes something like “spaghetti” code in the original context is poorly informed. They’re typically trying to sidestep blame for an existing bad application design or drive a change of platform without cost justification.

My take on this argument is two fold. Technologists in the organization may want to dump what they have and play with something else; or business and IT management may want to sidestep the wrath of angry users by blaming their failure on technology instead of how they didn’t design, manage, or deliver it.

Oh, wait … isn’t that last paragraph the reason for the existence of pre-package software? 😉 Don’t hesitate to chime in, after all it’s just my off-the-cuff opinion.

In prior years a daily update from Open World was possible, but this year my schedule was too full to support it. This is my compendium of thoughts about MySQL Connect, JavaOne, and Open World 2012.

MySQL Connect was great – good sessions re-enforcing the positive investments Oracle is making in the product. I’ll leave to others to qualify changes in what elements of technology are opened or closed along the road to a better MySQL. The announcement of Connector/Python 1.0 GA on Saturday was great news and as a community we owe a lot to Greet Vanderkelen.

NoSQL is a hot topic along with using JSON objects and it was interesting hearing of some unequal testing paradigms to position non-Oracle solutions to be “better” than Oracle solutions. Naturally, the MongoDB was the elephant in the room during those conversations. Some of the discussions seemed more like political rants than technical dialog. A great spot to start with NoSQL and JSON would be downloading Oracle’s MySQL 5.6 Release Candidate.

There were also more PostgreSQL conversations this year and fairly accurate comparisons between it and Oracle or MySQL from folks. It certainly looks like it may gain more ground.

Java 7 is awesome, and my favorite feature is clearly NIO2, reinforced at JavaOne. NIO2 brings static methods to interactions with external directory and file sources. It removes directories from the files class, which is long overdue. The nature of those static methods also happen to fit within the definition of Java code that lives inside the Oracle database and gives me a whole host of thoughts about potential in Oracle Database 12c.

Larry Ellison’s keynote was impressive because it gives us a clear vision of Oracle’s direction and Duncan Davies captured the keynote well in his blog. The continued presence of Red Hat and VMWare offers interesting reality checks to their key contributions to world wide implementation of the Oracle technical stack.

Issues that seem most critical to those I’ve chatted with are storage, security, tools, and development languages. A nice update on security can be found in the new edition of Hacking Exposed 7: Network Security Secrets & Solutions (7^th Edition).

On the forthcoming Oracle 12c release, Information Week just released a good summary view. The introduction of the R programming language on the Exadata Server leads me to wonder about what uses may magically appears in Oracle Enterprise Manager down the road. The TIOBE Index for September 2012 doesn’t list the R language in the top 20 programming languages but there’s always the future. No mention of Erlang programming language at any of the conferences that I caught but it’s inevitably on the horizon as application servers evolve.

Now we wait for the Oracle Database 12c release, which looks like something in the very short term. Perhaps right after the holidays …

It’s amazing the moving parts in Oracle Database 11g, and the ones that I don’t use after installation for a while often reach out to teach me something new. That’s what happened when I went to launch SQL Developer on my Windows 7 Professional 64-bit installation.

I got the message Unable to find a Java Virtual Machine, as shown in the image below. It really means unable to find a 32-bit Java 5 or Java 6 (1.6.0_4+) SDK home. Although the installation appeared fine, it pointed the SQL Developer configuration file to an incompatible 64-bit Java 7 SDK.

You fix this error by following these steps:

1. Open the sqldeveloper.conf file and check the SetJavaHome parameter value. You find the sqldeveloper.conf file in the following directory:

%ORACLE_HOME\sqldeveloper\sqldeveloper\bin

2. Install the 32-bit Java 6 (1.6.0_4+) SDK on the operating system if not installed already. You can check whether it’s installed by looking for it in the Program Files (x86) folder.
3. Change the value of the SetJavaHome parameter to point to the new 32-bit Java 6 home directory (or folder). The following change to line 18 in the sqldeveloper.conf file should fix it on your installation (provided that’s your version of the JVM).

18

SetJavaHome C:\Program Files (x86)\Java\jdk1.6.0_34

Hope this helps those who run into the same issue.

I’m posting this because of a question raised against this older post on how to configure the %CLASSPATH% to find the ojdbc6.jar file. This is the lab file I use in my Database 1 class to expose students to the moving parts of writing Java programs against the Oracle database. That’s why I choose to use a CLOB data type, which requires Oracle’s DBMS_LOB package and wrapping stored procedures.

If you want the same content for MySQL, here’s the link. The full program in either blog entry is available by clicking on the fold/unfold Java Source Code Program widget at the bottom of the respective posts.

This demonstrates how to create an Java infrastructure for reading and writing large text files to an Oracle database. The example provides:

• A FileIO.jar library that lets you enter Oracle connection parameters through a JOptionPane, and a customized JFileChooser to filter and read source files from the file system.
• A ojdbc6.jar file, which is Oracle’s library for JDBC communication with the Oracle Databases.

The steps to compiling and testing this code are qualified below:

1. Download and install the Java Software Development Kit (JSDK) for Java 6.
2. Create a C:\JavaTest folder on Windows, or a /JavaTest directory from some mount point of your choice.
3. Download and position the ojdbc6.jar and FileIO.jar files in the JavaTest directory.
4. Create a batch file to source your environment path (%PATH% on Windows and $PATH on Linux or Mac OS X) and the two Java Archive (JAR) files. A sample batch file is noted below:

set PATH=C:\Program Files\Java\jdk1.6.0_07\bin;%PATH%
set CLASSPATH=C:\JavaDev\Java6\ojdbc6.jar;C:\JavaDev\Java6\FileIO.jar;.

You can run this file by simply typing the files first name. On Linux or Mac OS X, you first need to grant it privileges with the chmod command as 755.

5. Copy the WriteReadCLOB.java code from the bottom of this posting and also put it into the JavaTest directory.
6. Compile the WriteReadCLOB.java source code with the javac utility, as shown below:

javac WriteReadCLOB.java

After you compile it, you should run it as follows:

java WriteReadCLOB

7. Before running the code, you’ll need to seed (INSERT) a row that meets the desired hard coded criteria. It requires an ITEM_TITLE value of 'The Lord of the Rings - Fellowship of the Ring' and an ITEM_SUBTITLE of 'Widescreen Edition' in the ITEM table.
8. When it runs, you’ll see the following tabbed JOptionPane.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287

// --------------------------------------------------------------------
// WriteReadCLOB.java
// by Michael McLaughlin
//
// This code demonstrates reading a large text file and displaying
// the text stream in a JLabel in a JFrame.
//
// The UPDATE and SELECT statements have dependencies on the
// create_store.sql script.
// --------------------------------------------------------------------
 
// Java Application class imports.
import java.awt.Dimension;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
 
// Generic JDBC imports.
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
 
// Oracle JDBC import.
import oracle.jdbc.driver.OracleDriver;
import oracle.jdbc.pool.OracleDataSource;
 
// Include book libraries (available at publisher website).
import plsql.jdbc.DataConnectionPane;
import plsql.fileio.FileIO;
// -------------------------------------------------------------------/
public class WriteReadCLOB extends JFrame {
  // Define database connections.
  private String host;
  private String port;
  private String dbname;
  private String userid;
  private String passwd;
 
  // Define data connection pane.
  private DataConnectionPane message = new DataConnectionPane();
 
  // Construct the class.
  public WriteReadCLOB (String s) {
    super(s);
 
    // Get database connection values or exit.
    if (JOptionPane.showConfirmDialog(this,message
          ,"Set Oracle Connection String Values"
          ,JOptionPane.OK_CANCEL_OPTION) == 0) {
 
      // Set class connection variables.
      host = message.getHost();
      port = message.getPort();
      dbname = message.getDatabase();
      userid = message.getUserID();
      passwd = message.getPassword();
 
      // Print connection to console (debugging tool).
      message.getConnection();
 
      // Create a JPanel for data display.
      ManageCLOB panel = new ManageCLOB();
 
      // Configure the JPanel.
      panel.setOpaque(true);
      setContentPane(panel);
 
      // Configure the JFrame.
      setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
      setLocation(100,100);
      pack();
      setVisible(true); }
    else
      System.exit(1); }
  // -------------------------------------------------------------------/
  private class ManageCLOB extends JPanel {
 
    // Define display variables.
    private String clobText;
    private JScrollPane scrollPane;
    private JTextArea textArea;
    // -----------------------------------------------------------------/
    public ManageCLOB () {
      // Set layout manager.
      super(new GridLayout(1,0));
 
      // Assign file read to String.
      clobText = FileIO.openFile(FileIO.findFile(this));
 
      // Insert record before querying it.
      if (clobText.length() > 0) {
        if (insertClob(host,port,dbname,userid,passwd,clobText))
          clobText = getQuery(host,port,dbname,userid,passwd);
        else
          clobText = null; }
      else
        System.exit(2);
 
      // Construct text area and format it.
      textArea = new JTextArea(clobText);
      textArea.setEditable(false);
      textArea.setFont(new Font(Font.SANS_SERIF,Font.PLAIN,14));
      textArea.setLineWrap(true);
      textArea.setRows(10);
      textArea.setSize(400,100);
      textArea.setWrapStyleWord(true);
 
      // Put the image in container, and add label to panel.
      scrollPane = new JScrollPane(textArea);
      add(scrollPane); }
    // ---------------------------------------------------------------/
    private Boolean insertClob(String host,String port,String dbname
                              ,String user,String pswd,String fileString) {
 
      try
      {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Create statement.
        CallableStatement stmt =
          conn.prepareCall("UPDATE item "+
                           "SET    item_desc = ? "+
                           "WHERE  item_title = "+
                           "'The Lord of the Rings - Fellowship of the Ring'"+
                           "AND    item_subtitle = 'Widescreen Edition'");
 
        // Set string into statement.
        stmt.setString(1,fileString);
 
        // Execute query.
        if (stmt.execute())
          conn.commit();
 
        // Close resources.
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return true; } // End of connection try-block.
 
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return false; }
        else {
          System.out.println(e.getMessage());
 
          // Return an empty String on error.
          return false; }}}
    // -----------------------------------------------------------------/
    private String getQuery(String host,String port,String dbname
                           ,String user,String pswd) {
 
      // Define method variables.
      char[] buffer;
      int count = 0;
      int length = 0;
      String data = null;
      String[] type;
      StringBuffer sb;
 
      try {
        // Set the Pooled Connection Source
        OracleDataSource ods = new OracleDataSource();
        String url = "jdbc:oracle:thin:@//"+host+":"+port+"/"+dbname;
        ods.setURL(url);
        ods.setUser(userid);
        ods.setPassword(passwd);
 
        // Define connection.
        Connection conn = ods.getConnection();
 
        // Define metadata object.
        DatabaseMetaData dmd = conn.getMetaData();
 
        // Create statement.
        Statement stmt = conn.createStatement();
 
        // Execute query.
        ResultSet rset =
          stmt.executeQuery(
            "SELECT item_desc " +
            "FROM   item " +
            "WHERE  item_title = " +
            "'The Lord of the Rings - Fellowship of the Ring'"+
            "AND    item_subtitle = 'Widescreen Edition'");
 
        // Get the query metadata, size array and assign column values.
        ResultSetMetaData rsmd = rset.getMetaData();
        type = new String[rsmd.getColumnCount()];
        for (int col = 0;col < rsmd.getColumnCount();col++)
          type[col] = rsmd.getColumnTypeName(col + 1);
 
        // Read rows and only CLOB data type columns.
        while (rset.next()) {
          for (int col = 0;col < rsmd.getColumnCount();col++) {
            if (type[col] == "CLOB") {
              // Assign result set to CLOB variable.
              Clob clob = rset.getClob(col + 1);
 
              // Check that it is not null and read the character stream.
              if (clob != null) {
                Reader is = clob.getCharacterStream();
 
                // Initialize local variables.
                sb = new StringBuffer();
                length = (int) clob.length();
 
                // Check CLOB is not empty.
                if (length > 0) {
 
                  // Initialize control structures to read stream.
                  buffer = new char[length];
                  count = 0;
 
                  // Read stream and append to StringBuffer.
                  try {
                    while ((count = is.read(buffer)) != -1)
                      sb.append(buffer);
 
                    // Assign StringBuffer to String.
                    data = new String(sb); }
                  catch (Exception e) {} }
                else
                  data = (String) null; }
              else
                data = (String) null; }
            else {
              data = (String) rset.getObject(col + 1); }}}
 
        // Close resources.
        rset.close();
        stmt.close();
        conn.close();
 
        // Return CLOB as a String data type.
        return data; }
      catch (SQLException e) {
        if (e.getSQLState() == null) {
          System.out.println(
            new SQLException("Oracle Thin Client Net8 Connection Error.",
                             "ORA-" + e.getErrorCode() +
                             ": Incorrect Net8 thin client arguments:\n\n" +
                             "  host name     [" + host + "]\n" +
                             "  port number   [" + port + "]\n" +
                             "  database name [" + dbname + "]\n"
                             , e.getErrorCode()).getSQLState());
 
          // Return an empty String on error.
          return data; }
        else {
          System.out.println(e.getMessage());
          return data; }}
        finally {
          if (data == null) System.exit(1); }}}
  // -----------------------------------------------------------------/
  public static void main(String[] args) {
    // Define window.
    WriteReadCLOB frame = new WriteReadCLOB("Write & Read CLOB Text"); }}

A couple students in one of my classes ran into a problem when competing Java threads tried to insert new rows in a table. They raised an error when they tried the DELAY keyword to avoid the race (collision) condition in an INSERT statement. It was simple to explain to them that the DELAY keyword doesn’t work with an InnoDB table. Any attempt throws the following error:

ERROR 1616 (HY000): DELAYED OPTION NOT supported FOR TABLE 'message'

Important Update: INSERT DELAYED is gone in MySQL 5.6.6 (announcement) and the whole issue comes down to synchronizing threads (some dislike the solution) or using the ON DUPLICATE KEY clause.

They retested their Java application after redefining the target table using the MyISAM engine. They found it worked but that’s a bad fix in Java (a brief Java/MySQL tutorial post). They really needed to synchronize the Java thread (line #22), leave out the DELAY keyword, and manage the table with the InnoDB engine. Here’s the modified Java code (by the way, they named their project VulcanTech if you’re wondering about the packages in the import statement):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

package vulcantech.vth.server.commands;
 
import java.io.IOException;
import java.net.Socket;
 
import vulcantech.vth.server.combeans.MessageBean;
 
public class MessageHandler implements Handler {
 
  @Override
  public void handleIt(Object... args) {
 
    MessageBean message = (MessageBean) args[0];
    Socket sock = (Socket) args[1];
 
    DatabaseConnection dbconnection = new DatabaseConnection();
 
    String update = new String("INSERT INTO message(message_timestamp, sender, recipient, message, checked) VALUES(\'" 
                  + message.getTimeStamp() + "\', \'" + message.getSender() + "\', \'" 
                  + message.getRecipient() + "\', \'" + message.getMessage() + "\', b\'0\')");
 
    synchronized (this) {
      dbconnection.executeUpdate(update);
    }
 
    try {
      sock.getOutputStream().write(1);
    } catch (IOException e) {
      e.printStackTrace();
    }
    dbconnection.close();
  }
}

Hope this helps those who encounter race conditions against MySQL when you’re writing Enterprise Java Beans (EJBs).

After updating the AVGFree virus definitions, I was surprised to find that Zend CE (Community Edition) 4.0.6 had a reported worm in the JavaServer.exe file. There was greater surprise when Zend CE 5.3.9 (5.6.0-SP1) also had the same reported worm.

This is the message identifying the worm (click on it to see a full size image), and you can read about this particular worm on the Mcafee site or the AVG threat labs site:

If you check AVGFree’s page, the actual infection isn’t a stated variant, but it appears the heuristics are a bit aggressive.

File Name: C:\Program Files (x86)\Zend\ZendServer\bin\JavaServer.exe
Infection: Win32/DH.FF860061{00000000-00080000-00000000}

Unless you have the full version of AVGFree or another security program to try and fix the file, you can only quarantine the file. Quarantine or removal disables Zend CE from working. It begs the question: “How does Zend release a core file with a worm?” or “Is AVGFree reporting a false positive?”

Update: AVGFree was providing a false positive. In addition to the checks by Zeev at Zend, I created a new test instance with Norton 360 and it likewise found no virus/worm in Zend’s JavaServer.exe file. Hopefully the post will prevent others from spending more than a Google search to sort it out.

Since I use AVGFree on all my Windows 7 VM test instances, it seemed logical to illustrate how to work around this current false positive and annoying quarantining of the core JavaServer.exe file from the Zend Server. There are two sets of tasks, the first requires removing the file from quarantine and the second eliminates future scans from quarantining the file again.

Remove the file from the Virus Vault

1. Launch AVGFree and navigate to the History menu option and choose the Virus Vault option, as shown below.

2. Click the Virus Vault option in the list of the History, which displays the following screen. Click the Infection row and then click the Restore button to remove the file from the virus vault.

3. A confirmation dialog opens and you click the Yes button to proceed.

4. The Infection row is gone When you’re returned to the History dialog. Click the Close button to complete this task.

Exclude the file from future scans

1. Select the Tools menu option and choose the Advanced settings … option, as shown below.

2. Click the Excluded files option in the list of the History, which displays the following screen. Click the Add button to select the file for exclusion. Click the Apply button to effect the change and the OK button to complete the change.

All I can say, one the AVGFree false positive was annoying and it’s dark at 3 a.m. and light the next day. 😉

Thanks to those who knew or surmised it was AVGFree’s heuristics and took the time to add a comment.