JDBC LONG datatype Oracle 7.x
Date: Fri, 18 Sep 1998 16:16:39 GMT
Message-ID: <6tu117$l5$1_at_nnrp1.dejanews.com>
Ran into some troubles populating LONG data type to Oracle 7.x from Java and thought I'd share what I found.
Regular JDBC Statement object is not able to handle over 2k in dynamically created SQL strings, both SQL*Net and ODBC with various driver's has this shortcoming as well. Actually it makes sense to send large chunks of data as streams instead of part of a mombo SQL string.
Order of prepared statement parameters are important to Oracle. The first example is of the SQL insert statement using JDBC preparedStatement object's setString and setAsciiStream methods, it worked only because the LONG value was the last parameter to be set and the size of the String in setString didn't seem to matter. setInt for the ID (primary key) was first parameter and the either setString or setAsciiStream was the 2nd parameter. LONGs are suppose to be at the end of a table's tablespace, but didn't think it should matter as far as the parameter order. Apparently it does to Oracle when using a large value with setString, "ORA-01483: invalid length for DATE or NUMBER bind variable" was the SQLException thrown and the SQLState message given. The recommended action for this SQLState is "Check your Oracle7 operating system-specific documentation for the maximum allowable length." It looks like the large string value was somehow interfering with the int value of the key that was the second parameter. I would be inclined to think this a bug of the JDBC thin-driver Oracle wrote. I was able to switch the order with setAsciiStream (recommended means over setString for LONG values, due to sheer volume of packets) without any problem. I needed to adhere to an order of parameters that had the numeric value second as it needed to be in the where clause of the SQL update preparedStatement.
The next glitch was trying to convert a String object to an InputStream, I was able to use other InputStreams like FileInputStream without a problem getting data in that way. StringBufferInputStream was deprecated and StringReader was the recommended means of sending Strings through streams only it doesn't descend from InputStream and it's only possible (that I know of) to create Reader objects from the various inputStreams not vice versa and setAsciiStream needed to be a Stream not a Reader. Found one good match on Deja News and with the help of Babelfish was able to translate it from German into almost coherent English. The solution was to create a ByteArrayInputStream with the String's getBytes() method in the constructor and use it as my InputStream.
Final glitch. Worked great except for smaller Strings. executeUpdate was returning zero rows affected, but no errors?? Don't know what the cause was, there's no flushing InputStreams like you do with OutputStreams that could have a result like this. Ended up checking the length of the String and using setString instead of setAsciiStream if it was below 2,000 characters (Example 2 - bigArt method).
/* Example 1
my packages in net.guilds.data simplify the data access and dumping ASCII to a
file, but use real connection (from threaded connection broker found on the
internet) and unaltered PreparedStatement object
*/
import java.net.*; import java.sql.*; import java.lang.*; import java.util.*; import java.io.*; import java.text.*;
import net.guilds.data.ASCIIdmp;
import net.guilds.data.ExecSql;
public class linsert {
public static void main (String [] args) {
try {
ExecSql esql = new ExecSql(DBdriver, url, user, password, 1, 1);
Connection conn = esql.getConnection();
PreparedStatement ps = esql.prepareStatement("insert into article
values(?, ?)", conn);
//Nonsense string can be REALLY big, whatever limit String object has
String lstring =
"XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
XXX XXXXXX"; System.out.println("The string is " + new
Integer(lstring.length()).toString() + " bytes long"); ps.setInt(1,
22); FileInputStream fis = new
FileInputStream("c:/var/log/java/DBconn"); ps.setAsciiStream(2, fis, fis.available());
if (ps.executeUpdate() > 0) {
System.out.println("Ascii method suceeded");
}
ps.setInt(1, 201);
ps.setString(2, lstring);
if (ps.executeUpdate() > 0) {
System.out.println("String method suceeded");
}
ps.close();
System.out.println(esql.freeConnection(conn) + " Conn Free status");
sqler = new ASCIIdmp(esql);
sqler.makeASCIID("d:/temp/test_log.txt", "select text from test_long",
"");
System.out.println("done?");
}
catch (Exception e) {
System.out.println("Exception: " + e );
}
}
//Example 2
/* call this method with article table's primary key and the really long article content as another String */
public String bigArt (String art_id, String article) {
String ret_val = "";
try {
int a_id = new Integer(1).parseInt(art_id);
Connection conn = esql.getConnection();
PreparedStatement ps = esql.prepareStatement("update article set
article = ? where art_id = ? ", conn);
ByteArrayInputStream bais = null;
if (article.length() > 2000) {
bais = new ByteArrayInputStream(article.getBytes());
ps.setAsciiStream(1, bais, bais.available());
}
else {
ps.setString(1, article);
}
ps.setInt(2, a_id);
if (ps.executeUpdate() > 0) {
ret_val = "successfully";
}
else {
ret_val = "<B><I>partially</I></B>";
}
ps.close();
esql.freeConnection(conn);
if (bais != null) {
bais.close();
}
}
catch (Exception e) {
log.writeLog("Exception in bigArt: " + e);
ret_val = "<B><I>partially</I></B><!-- " + e + "-->";
}
return ret_val;
}
private static String DBdriver ="oracle.jdbc.driver.OracleDriver";
private static String url = "jdbc:oracle:thin:_at_somehost:1526:ORCL";
private static String user = "username";
private static String password = "pwd";
private static String logPath = "c:/var/logs/java/";
private static ASCIIdmp sqler;
}
-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Sep 18 1998 - 18:16:39 CEST