Derby
  1. Derby
  2. DERBY-2859

Misleading error message if you supply too many arguments to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Minor Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: None
    • Component/s: SQL, Tools
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer, Repro attached

      Description

      If you supply too many arguments to SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE, you get an error message telling you that SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE is an unknown procedure. Instead, you should get an error message which tells you that the signature can't be matched to a known overload of the procedure:

      ij> call SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE
      (
      'select * from lobtable where updateCount in( 2, 4 )',
      'LOBTABLE',
      '/export/home/rick/junk/test-export-table1.dat',
      null,
      null,
      null,
      '/export/home/rick/junk/test-export-query1-lobs.dat'
      );
      ERROR 42Y03: 'SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE' is not recognized as a function or procedure.

        Issue Links

          Activity

          Rick Hillegas created issue -
          Hide
          Rick Hillegas added a comment -

          Triaged for 10.5.2: Assigned normal urgency, noted that repro is available, recommended to newcomers.

          Show
          Rick Hillegas added a comment - Triaged for 10.5.2: Assigned normal urgency, noted that repro is available, recommended to newcomers.
          Rick Hillegas made changes -
          Field Original Value New Value
          Urgency Normal
          Issue & fix info [Newcomer, Repro attached]
          Priority Major [ 3 ] Minor [ 4 ]
          Kathey Marsden made changes -
          Labels derby_triage10_5_2
          Gavin made changes -
          Workflow jira [ 12406862 ] Default workflow, editable Closed status [ 12799498 ]
          Hide
          Mano added a comment -

          I am using 10.2 version. I am having the same issue. i have been working around with issue from quit a time now.
          Let me add my add my code and error here. It might give you proper idea.
          Any help is really appreciated. Thanks.

          java.sql.PreparedStatement statement = connect.prepareStatement("CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(?,?,?,?,?,?,?)");
          statement.setString(1, null);
          statement.setString(2, "xyz");
          statement.setString(3, "c:/derbytest/xyz.csv");
          statement.setString(4, null);
          statement.setString(5, null);
          statement.setString(6, null);
          statement.setString(7, "c:/derbytest/xyz.dat");

          statement.executeUpdate();
          statement.close();

          Here is the error: Caused by: org.apache.derby.client.am.SqlException: 'SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE' is not recognized as a function or procedure. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown Source)
          at org.apache.derby.client.net.StatementReply.readPrepare(Unknown Source)
          at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source)
          at org.apache.derby.client.am.Statement.readPrepare(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) ... 3 more

          Show
          Mano added a comment - I am using 10.2 version. I am having the same issue. i have been working around with issue from quit a time now. Let me add my add my code and error here. It might give you proper idea. Any help is really appreciated. Thanks. java.sql.PreparedStatement statement = connect.prepareStatement("CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(?,?,?,?,?,?,?)"); statement.setString(1, null); statement.setString(2, "xyz"); statement.setString(3, "c:/derbytest/xyz.csv"); statement.setString(4, null); statement.setString(5, null); statement.setString(6, null); statement.setString(7, "c:/derbytest/xyz.dat"); statement.executeUpdate(); statement.close(); Here is the error: Caused by: org.apache.derby.client.am.SqlException: 'SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE' is not recognized as a function or procedure. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown Source) at org.apache.derby.client.net.StatementReply.readPrepare(Unknown Source) at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source) at org.apache.derby.client.am.Statement.readPrepare(Unknown Source) at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) ... 3 more
          Rick Hillegas made changes -
          Link This issue relates to DERBY-378 [ DERBY-378 ]
          Hide
          Rick Hillegas added a comment -

          Hi Mano,

          This procedure was added to Derby in release 10.3.1.4 by DERBY-378. See https://svn.apache.org/viewvc/db/derby/code/tags/10.3.1.4/RELEASE-NOTES.html?view=co . Thanks-Rick

          Show
          Rick Hillegas added a comment - Hi Mano, This procedure was added to Derby in release 10.3.1.4 by DERBY-378 . See https://svn.apache.org/viewvc/db/derby/code/tags/10.3.1.4/RELEASE-NOTES.html?view=co . Thanks-Rick
          Hide
          Mano added a comment -

          Thanks for the reply. Let me try with upgraded version.

          Show
          Mano added a comment - Thanks for the reply. Let me try with upgraded version.
          Hide
          Mano added a comment -

          I am still getting the same error. Please take a look. Can you also make sure my procedure format is correct. I have also tried export_Query, same result. Please comment. Thanks.

          Apache Derby Network Server - 10.3.1.4 - (561794) started and ready to accept connections on port 1527 at 2013-11-19 18:42:41.965 GMT

          Exception in thread "main" java.sql.SQLSyntaxErrorException: 'SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE' is not recognized as a function or procedure.
          at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
          at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
          at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source)
          at com.pari.db.DerbyConnection.<init>(DerbyConnection.java:95)
          at com.pari.db.DerbyConnection.main(DerbyConnection.java:191)
          Caused by: org.apache.derby.client.am.SqlException: 'SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE' is not recognized as a function or procedure.
          at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source)
          at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown Source)
          at org.apache.derby.client.net.StatementReply.readPrepare(Unknown Source)
          at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source)
          at org.apache.derby.client.am.Statement.readPrepare(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source)
          at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source)
          at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source)
          ... 3 more

          Show
          Mano added a comment - I am still getting the same error. Please take a look. Can you also make sure my procedure format is correct. I have also tried export_Query, same result. Please comment. Thanks. Apache Derby Network Server - 10.3.1.4 - (561794) started and ready to accept connections on port 1527 at 2013-11-19 18:42:41.965 GMT Exception in thread "main" java.sql.SQLSyntaxErrorException: 'SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE' is not recognized as a function or procedure. at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source) at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatement(Unknown Source) at com.pari.db.DerbyConnection.<init>(DerbyConnection.java:95) at com.pari.db.DerbyConnection.main(DerbyConnection.java:191) Caused by: org.apache.derby.client.am.SqlException: 'SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE' is not recognized as a function or procedure. at org.apache.derby.client.am.Statement.completeSqlca(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePrepareError(Unknown Source) at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(Unknown Source) at org.apache.derby.client.net.NetStatementReply.readPrepare(Unknown Source) at org.apache.derby.client.net.StatementReply.readPrepare(Unknown Source) at org.apache.derby.client.net.NetStatement.readPrepare_(Unknown Source) at org.apache.derby.client.am.Statement.readPrepare(Unknown Source) at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(Unknown Source) at org.apache.derby.client.am.PreparedStatement.prepare(Unknown Source) at org.apache.derby.client.am.Connection.prepareStatementX(Unknown Source) ... 3 more
          Hide
          Rick Hillegas added a comment -

          Hi Mano,

          Another thing I notice is that you are invoking the procedure with 7 arguments but it only takes 6. Thanks-Rick

          Show
          Rick Hillegas added a comment - Hi Mano, Another thing I notice is that you are invoking the procedure with 7 arguments but it only takes 6. Thanks-Rick
          Hide
          Mano added a comment -

          Thanks for the reply.
          I read it from one of the posts from Mr. Suresh Thalamati.

          Derby Reference Guide :
          On page 126: SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE example:
          CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE(
          'APP', 'STAFF', 'c:\data\staff.del', ', ', '"','UTF-8', 'c:\data\pictures.dat')

          If that is wrong. Could you please suggest me an example like above.

          Show
          Mano added a comment - Thanks for the reply. I read it from one of the posts from Mr. Suresh Thalamati. Derby Reference Guide : On page 126: SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE example: CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( 'APP', 'STAFF', 'c:\data\staff.del', ', ', '"','UTF-8', 'c:\data\pictures.dat') If that is wrong. Could you please suggest me an example like above.
          Hide
          Rick Hillegas added a comment - - edited

          Ha Mano,

          Ah. This JIRA deals with SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE, a procedure which takes 6 parameters. But now I see that you are talking about SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE, a procedure which takes 7 parameters. The following program works correctly for me using both 10.3.3 and 10.10.1:

          import java.sql.*;
          
          public class y
          {
              public static void main( String[] args ) throws Exception
              {
                  Connection  conn = DriverManager.getConnection( "jdbc:derby:db;create=true" );
          
                  conn.prepareStatement( "create table t( a int, b clob )" ).execute();
          
                  PreparedStatement   ps = conn.prepareStatement( "insert into t values ( ?, ? )" );
                  ps.setInt( 1, 1 );
                  ps.setString( 2, "abc" );
                  ps.setInt( 1, 2 );
                  ps.execute();
                  ps.setString( 2, "def" );
                  ps.execute();
                  ps.close();
          
                  PreparedStatement   ps2 = conn.prepareStatement
                      ( "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" );
                  ps2.setString( 1, "APP" );
                  ps2.setString( 2, "T" );
                  ps2.setString( 3, "t.dat" );
                  ps2.setString( 4, null );
                  ps2.setString( 5, null );
                  ps2.setString( 6, null );
                  ps2.setString( 7, "t.clobs" );
                  ps2.execute();
                  ps2.close();
              }
          
          }
          

          Hope this helps,
          -Rick

          Show
          Rick Hillegas added a comment - - edited Ha Mano, Ah. This JIRA deals with SYSCS_UTIL.SYSCS_EXPORT_QUERY_LOBS_TO_EXTFILE, a procedure which takes 6 parameters. But now I see that you are talking about SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE, a procedure which takes 7 parameters. The following program works correctly for me using both 10.3.3 and 10.10.1: import java.sql.*; public class y { public static void main( String[] args ) throws Exception { Connection conn = DriverManager.getConnection( "jdbc:derby:db;create=true" ); conn.prepareStatement( "create table t( a int, b clob )" ).execute(); PreparedStatement ps = conn.prepareStatement( "insert into t values ( ?, ? )" ); ps.setInt( 1, 1 ); ps.setString( 2, "abc" ); ps.setInt( 1, 2 ); ps.execute(); ps.setString( 2, "def" ); ps.execute(); ps.close(); PreparedStatement ps2 = conn.prepareStatement ( "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" ); ps2.setString( 1, "APP" ); ps2.setString( 2, "T" ); ps2.setString( 3, "t.dat" ); ps2.setString( 4, null ); ps2.setString( 5, null ); ps2.setString( 6, null ); ps2.setString( 7, "t.clobs" ); ps2.execute(); ps2.close(); } } Hope this helps, -Rick
          Hide
          Mano added a comment -

          Hi Rick,
          It is not working for datatype BLOB. I have tried the same example with blob type. It gives me same error.
          My code:
          Connection conn = DriverManager.getConnection( "jdbc:derby:db;" );
          conn.prepareStatement( "create table t( a int, b blob )" ).execute();

          PreparedStatement ps = conn.prepareStatement( "insert into t values ( ?, ? )" );
          ps.setInt( 1, 1 );
          File image = new File("C:/Users/mmenam/Desktop/Menam.png");
          byte[] imageData = new byte[(int) image.length()];
          ps.setBlob(2, new SerialBlob(imageData));
          ps.execute();
          ps.close();

          PreparedStatement ps2 = conn.prepareStatement("CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" );
          It is throwing exception from this line itself.

          CLOB is working for me too. I am only using BLOB(large/medium). Thanks for your time.

          Show
          Mano added a comment - Hi Rick, It is not working for datatype BLOB. I have tried the same example with blob type. It gives me same error. My code: Connection conn = DriverManager.getConnection( "jdbc:derby:db;" ); conn.prepareStatement( "create table t( a int, b blob )" ).execute(); PreparedStatement ps = conn.prepareStatement( "insert into t values ( ?, ? )" ); ps.setInt( 1, 1 ); File image = new File("C:/Users/mmenam/Desktop/Menam.png"); byte[] imageData = new byte [(int) image.length()] ; ps.setBlob(2, new SerialBlob(imageData)); ps.execute(); ps.close(); PreparedStatement ps2 = conn.prepareStatement("CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" ); It is throwing exception from this line itself. CLOB is working for me too. I am only using BLOB(large/medium). Thanks for your time.
          Hide
          Rick Hillegas added a comment -

          Thanks, Mano. Can you try the following program, which runs successfully for me using both the 10.11 trunk and the old 10.3.3.0 release. The program prints out the database version and the version of Derby that is being used. Could you let us know what those printed versions are? Thanks-Rick

          import java.sql.*;
          import javax.sql.rowset.serial.SerialBlob;
          
          public class yy
          {
              public static void main( String[] args ) throws Exception
              {
                  Connection  conn = DriverManager.getConnection( "jdbc:derby:db;create=true" );
          
                  ResultSet   rs = conn.prepareStatement
                      ( "values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )" ).executeQuery();
                  rs.next();
                  System.out.println( "Database version is " + rs.getString( 1 ) );
                  rs.close();
                  System.out.println( "Version of Derby jars is " + conn.getMetaData().getDatabaseProductVersion() );
          
                  conn.prepareStatement( "create table t( a int, b blob )" ).execute();
          
                  PreparedStatement   ps = conn.prepareStatement( "insert into t values ( ?, ? )" );
                  ps.setInt( 1, 1 );
                  ps.setBlob( 2, new SerialBlob( new byte[] { (byte) 1, (byte) 2, (byte) 3 } ) );
                  ps.setInt( 1, 2 );
                  ps.execute();
                  ps.setBlob( 2, new SerialBlob( new byte[] { (byte) 4, (byte) 5, (byte) 6 } ) );
                  ps.execute();
                  ps.close();
          
                  PreparedStatement   ps2 = conn.prepareStatement
                      ( "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" );
                  ps2.setString( 1, "APP" );
                  ps2.setString( 2, "T" );
                  ps2.setString( 3, "t.dat" );
                  ps2.setString( 4, null );
                  ps2.setString( 5, null );
                  ps2.setString( 6, null );
                  ps2.setString( 7, "t.blobs" );
                  ps2.execute();
                  ps2.close();
              }
          
          }
          
          Show
          Rick Hillegas added a comment - Thanks, Mano. Can you try the following program, which runs successfully for me using both the 10.11 trunk and the old 10.3.3.0 release. The program prints out the database version and the version of Derby that is being used. Could you let us know what those printed versions are? Thanks-Rick import java.sql.*; import javax.sql.rowset.serial.SerialBlob; public class yy { public static void main( String[] args ) throws Exception { Connection conn = DriverManager.getConnection( "jdbc:derby:db;create=true" ); ResultSet rs = conn.prepareStatement ( "values syscs_util.syscs_get_database_property( 'DataDictionaryVersion' )" ).executeQuery(); rs.next(); System.out.println( "Database version is " + rs.getString( 1 ) ); rs.close(); System.out.println( "Version of Derby jars is " + conn.getMetaData().getDatabaseProductVersion() ); conn.prepareStatement( "create table t( a int, b blob )" ).execute(); PreparedStatement ps = conn.prepareStatement( "insert into t values ( ?, ? )" ); ps.setInt( 1, 1 ); ps.setBlob( 2, new SerialBlob( new byte[] { (byte) 1, (byte) 2, (byte) 3 } ) ); ps.setInt( 1, 2 ); ps.execute(); ps.setBlob( 2, new SerialBlob( new byte[] { (byte) 4, (byte) 5, (byte) 6 } ) ); ps.execute(); ps.close(); PreparedStatement ps2 = conn.prepareStatement ( "CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE_LOBS_TO_EXTFILE( ?,?,?,?,?,?,? )" ); ps2.setString( 1, "APP" ); ps2.setString( 2, "T" ); ps2.setString( 3, "t.dat" ); ps2.setString( 4, null ); ps2.setString( 5, null ); ps2.setString( 6, null ); ps2.setString( 7, "t.blobs" ); ps2.execute(); ps2.close(); } }

            People

            • Assignee:
              Unassigned
              Reporter:
              Rick Hillegas
            • Votes:
              1 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:

                Development