You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Johannes Lichtenberger (JIRA)" <ji...@apache.org> on 2014/09/05 16:21:28 UTC
[jira] [Updated] (DERBY-6728) Reading from a Clob fails.

     [ https://issues.apache.org/jira/browse/DERBY-6728?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Johannes Lichtenberger updated DERBY-6728:
------------------------------------------
    Description: 
In the Java-Code below the reading from a CLOB-column fails. In no other tested relational database it seems to fail (PostgreSQL / DB2 / MSSQL). It somehow seems to be related to reading the maximum integer primary key value beforehand.

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class DerbyClobTest
{
	public static void main(String[] args)
	{
		try
		{
			Connection conn = DriverManager
					.getConnection("jdbc:derby://localhost:1527/database;user=derby;password=derby");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM APP");

			while (rs.next())
			{
				PreparedStatement l_stmt = null;
				int l_iMaxLid = -1;

				try
				{
					l_stmt = conn.prepareStatement("SELECT MAX(LID) FROM APP");

					ResultSet l_rs = l_stmt.executeQuery();

					try
					{
						if (l_rs.next())
							l_iMaxLid = l_rs.getInt(1);
					}
					finally
					{
                                                l_rs.close();
						l_rs = null;
					}
				}
				finally
				{
                                        l_stmt.close();
					l_stmt = null;
				}

				PreparedStatement stmtNew = conn
						.prepareStatement("INSERT INTO APP(LID, TXTBODY) VALUES(?, ?)");

				stmtNew.setInt(1, l_iMaxLid);

				final Clob clob = rs.getClob("TXTBODY");

				if (clob == null)
					return;

				final String str = clob.getSubString(1, (int) clob.length());

				// Do something with str.

				stmtNew.setClob(2, clob);

				stmtNew.executeUpdate();
				stmtNew.close();
			}
		}
		catch (Exception except)
		{
			except.printStackTrace();
		}
	}
}


  was:
In the Java-Code below the reading from a CLOB-column fails. In no other tested relational database it seems to fail (PostgreSQL / DB2 / MSSQL). It somehow seems to be related to reading the maximum integer primary key value beforehand.

import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

public class DerbyClobTest
{
	public static void main(String[] args)
	{
		try
		{
			Connection conn = DriverManager
					.getConnection("jdbc:derby://localhost:1527/ixixtsumImport;user=derby;password=derby");
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM APP");

			while (rs.next())
			{
				PreparedStatement l_stmt = null;
				int l_iMaxLid = -1;

				try
				{
					l_stmt = conn.prepareStatement("SELECT MAX(LID) FROM APP");

					ResultSet l_rs = l_stmt.executeQuery();

					try
					{
						if (l_rs.next())
							l_iMaxLid = l_rs.getInt(1);
					}
					finally
					{
                                                l_rs.close();
						l_rs = null;
					}
				}
				finally
				{
                                        l_stmt.close();
					l_stmt = null;
				}

				PreparedStatement stmtNew = conn
						.prepareStatement("INSERT INTO APP(LID, TXTBODY) VALUES(?, ?)");

				stmtNew.setInt(1, l_iMaxLid);

				final Clob clob = rs.getClob("TXTBODY");

				if (clob == null)
					return;

				final String str = clob.getSubString(1, (int) clob.length());

				// Do something with str.

				stmtNew.setClob(2, clob);

				stmtNew.executeUpdate();
				stmtNew.close();
			}
		}
		catch (Exception except)
		{
			except.printStackTrace();
		}
	}
}



> Reading from a Clob fails.
> --------------------------
>
>                 Key: DERBY-6728
>                 URL: https://issues.apache.org/jira/browse/DERBY-6728
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.11.1.1
>            Reporter: Johannes Lichtenberger
>
> In the Java-Code below the reading from a CLOB-column fails. In no other tested relational database it seems to fail (PostgreSQL / DB2 / MSSQL). It somehow seems to be related to reading the maximum integer primary key value beforehand.
> import java.sql.Clob;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.Statement;
> public class DerbyClobTest
> {
> 	public static void main(String[] args)
> 	{
> 		try
> 		{
> 			Connection conn = DriverManager
> 					.getConnection("jdbc:derby://localhost:1527/database;user=derby;password=derby");
> 			Statement stmt = conn.createStatement();
> 			ResultSet rs = stmt.executeQuery("SELECT * FROM APP");
> 			while (rs.next())
> 			{
> 				PreparedStatement l_stmt = null;
> 				int l_iMaxLid = -1;
> 				try
> 				{
> 					l_stmt = conn.prepareStatement("SELECT MAX(LID) FROM APP");
> 					ResultSet l_rs = l_stmt.executeQuery();
> 					try
> 					{
> 						if (l_rs.next())
> 							l_iMaxLid = l_rs.getInt(1);
> 					}
> 					finally
> 					{
>                                                 l_rs.close();
> 						l_rs = null;
> 					}
> 				}
> 				finally
> 				{
>                                         l_stmt.close();
> 					l_stmt = null;
> 				}
> 				PreparedStatement stmtNew = conn
> 						.prepareStatement("INSERT INTO APP(LID, TXTBODY) VALUES(?, ?)");
> 				stmtNew.setInt(1, l_iMaxLid);
> 				final Clob clob = rs.getClob("TXTBODY");
> 				if (clob == null)
> 					return;
> 				final String str = clob.getSubString(1, (int) clob.length());
> 				// Do something with str.
> 				stmtNew.setClob(2, clob);
> 				stmtNew.executeUpdate();
> 				stmtNew.close();
> 			}
> 		}
> 		catch (Exception except)
> 		{
> 			except.printStackTrace();
> 		}
> 	}
> }



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)