Derby
  1. Derby
  2. DERBY-4550

Using ij to copy data from one DB to an other

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 10.6.1.0
    • Component/s: Tools
    • Labels:
    • Issue & fix info:
      Patch Available

      Description

      It is possible to have open connections to several databases while running ij, but it is not currently possible to copy data from one DB to an other one.

      Not only such a feature would allow to copy data between Derby databases. But, ij being mostly DB agnostic, if will ease import/export from any JDBC compliant data source.

      See http://old.nabble.com/Using-IJ-to-copy-data-from-one-DB-to-an-other-one-td27598138.html

      1. DERBY-4550_2.diff
        6 kB
        Sylvain Leroux
      2. DERBY-4550_2.sql
        0.8 kB
        Sylvain Leroux
      3. DERBY-4550_3.patch
        20 kB
        Sylvain Leroux
      4. DERBY-4550_3.sql
        1 kB
        Sylvain Leroux
      5. DERBY-4550_4.patch
        21 kB
        Sylvain Leroux
      6. DERBY-4550_4.sql
        0.4 kB
        Sylvain Leroux
      7. DERBY-4550_5.patch
        23 kB
        Sylvain Leroux
      8. DERBY-4550_6.patch
        25 kB
        Sylvain Leroux
      9. DERBY-4550.diff
        3 kB
        Sylvain Leroux
      10. DERBY-4550.sql
        0.7 kB
        Sylvain Leroux

        Issue Links

          Activity

          Hide
          Sylvain Leroux added a comment -

          Here is a first attempt to solve this issue.

          Basically, I add an optional "ON" clause to the PREPARE statement, allowing to prepare the statement on an alternate connection, instead of the current one. I don't know if this is the most elegant way of doing, but it appears to work.

          The file DERBY-4550.sql contains a sample script demonstrating that.


          As Rick Hillegas suggested it on the mailing list, I will take a look at PreparedStatement.addBatch() and Statement.executeBatch() for an alternative option.


          I focused mostly on prepared statements as it was my first idea. But cursors might benefit of the same improvement.

          Show
          Sylvain Leroux added a comment - Here is a first attempt to solve this issue. Basically, I add an optional "ON" clause to the PREPARE statement, allowing to prepare the statement on an alternate connection, instead of the current one. I don't know if this is the most elegant way of doing, but it appears to work. The file DERBY-4550 .sql contains a sample script demonstrating that. As Rick Hillegas suggested it on the mailing list, I will take a look at PreparedStatement.addBatch() and Statement.executeBatch() for an alternative option. I focused mostly on prepared statements as it was my first idea. But cursors might benefit of the same improvement.
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          This is a very interesting proposal. To me the limitation in ij seems to be the inability to reference identifiers created by other connections. A more extensible solution might be to add a level to the identifier namespace:

          identifier ::= [ connectionName . ] unqualifiedIdentifierName

          Then you could write the following script:

          connect 'jdbc:derby:memory:dummy;create=true;user=fred' as fred_conn;

          create table t1( a int, b int );
          insert into t1( a, b ) values ( 1, 100 ), ( 2, 200 );

          autocommit off;

          prepare select_from_t1 as 'select * from t1';

          connect 'jdbc:derby:memory:dummy;user=alice' as alice_conn;

          create table t3( a int, b int );

          execute 'insert into t3( a, b ) values ( ?, ? )' using fred_conn.select_from_t1;

          We might want to consider the meaning of this fragment:

          set connection fred_conn;

          prepare alice_conn.second_select as 'select * from t1';

          To be conservative, we might want to start out saying that you can't create or remove identifiers in another connection's namespace.

          I think that being able to reference identifers created by other connections would be generally useful and would solve other problems besides the bulk import issue you are addressing right now.

          Show
          Rick Hillegas added a comment - Hi Sylvain, This is a very interesting proposal. To me the limitation in ij seems to be the inability to reference identifiers created by other connections. A more extensible solution might be to add a level to the identifier namespace: identifier ::= [ connectionName . ] unqualifiedIdentifierName Then you could write the following script: connect 'jdbc:derby:memory:dummy;create=true;user=fred' as fred_conn; create table t1( a int, b int ); insert into t1( a, b ) values ( 1, 100 ), ( 2, 200 ); autocommit off; prepare select_from_t1 as 'select * from t1'; connect 'jdbc:derby:memory:dummy;user=alice' as alice_conn; create table t3( a int, b int ); execute 'insert into t3( a, b ) values ( ?, ? )' using fred_conn.select_from_t1; We might want to consider the meaning of this fragment: set connection fred_conn; prepare alice_conn.second_select as 'select * from t1'; To be conservative, we might want to start out saying that you can't create or remove identifiers in another connection's namespace. I think that being able to reference identifers created by other connections would be generally useful and would solve other problems besides the bulk import issue you are addressing right now.
          Hide
          Sylvain Leroux added a comment -

          Thanks for your comments Rick.

          Using a level on indirection to reference identifiers created by another connection will definitively be more readable from an user point of view. I will work on that direction.

          As you propose, I think it would be a good starting point to state that other connection's namespace are "read only". New prepared statements (or maybe cursors or even other objects?), could only be added to the current connection namespace.

          So that fragment would be invalid:
          ij> set connection fred_conn;
          ij> prepare alice_conn.second_select as 'select * from t1';

          But what about:
          ij> set connection fred_conn;
          ij> prepare fred_conn.second_select as 'select * from t1';

          One option would be to only allow unqualified identifiers when introducing a /new/ identifier. And only using qualified identifiers for referencing /existing/ identifiers.

          Show
          Sylvain Leroux added a comment - Thanks for your comments Rick. Using a level on indirection to reference identifiers created by another connection will definitively be more readable from an user point of view. I will work on that direction. As you propose, I think it would be a good starting point to state that other connection's namespace are "read only". New prepared statements (or maybe cursors or even other objects?), could only be added to the current connection namespace. So that fragment would be invalid: ij> set connection fred_conn; ij> prepare alice_conn.second_select as 'select * from t1'; But what about: ij> set connection fred_conn; ij> prepare fred_conn.second_select as 'select * from t1'; One option would be to only allow unqualified identifiers when introducing a /new/ identifier. And only using qualified identifiers for referencing /existing/ identifiers.
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          I think that we should allow your second example:

          ij> set connection fred_conn;
          ij> prepare fred_conn.second_select as 'select * from t1';

          There ought to be a name-resolution phase in ij (or at least identifier-resolution). A connection-qualified identifier could be allowed in this situation provided that the connection is the current connection.

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Sylvain, I think that we should allow your second example: ij> set connection fred_conn; ij> prepare fred_conn.second_select as 'select * from t1'; There ought to be a name-resolution phase in ij (or at least identifier-resolution). A connection-qualified identifier could be allowed in this situation provided that the connection is the current connection. Thanks, -Rick
          Hide
          Sylvain Leroux added a comment -

          Concerning the PREPARE statement:
          ij> prepare fred_conn.second_select as 'select * from t1';

          From the user point of view, I think it could be frustrating to discover that qualified names are allowed, but only if it refers to the current connection.

          Couldn't it be more consistent to:
          1) only allow unqualified identifiers here, thus enforcing the fact that you only can create/remove an identifier in the current connection's namespace.
          2) /or/ allow qualified identifiers here, allowing implicitly to create identifiers in an other connection's namespace.

          After a second thought, the later has my favors. Since it made possible to create/use/remove a prepared statement without requiring to explicitly switch connection:

          ij> CONNECT '.....' AS alice_conn;
          ij> CONNECT '....' AS fred_conn;
          ij> PREPARE alice_conn.source_stmt AS 'SELECT * FROM T1';
          ij> EXECUTE 'INSERT INTO T2 VALUES' USING alice_conn.source.stmt;
          ij> REMOVE alice_conn.source_stmt;

          With that option, the above PREPARE statement has for meaning:
          " in the context of the connection alice_conn, prepare the statement 'SELECT * FROM T1' "

          I attach both a patch and sql example to demonstrate that option.

          Show
          Sylvain Leroux added a comment - Concerning the PREPARE statement: ij> prepare fred_conn.second_select as 'select * from t1'; From the user point of view, I think it could be frustrating to discover that qualified names are allowed, but only if it refers to the current connection. Couldn't it be more consistent to: 1) only allow unqualified identifiers here, thus enforcing the fact that you only can create/remove an identifier in the current connection's namespace. 2) /or/ allow qualified identifiers here, allowing implicitly to create identifiers in an other connection's namespace. After a second thought, the later has my favors. Since it made possible to create/use/remove a prepared statement without requiring to explicitly switch connection: ij> CONNECT '.....' AS alice_conn; ij> CONNECT '....' AS fred_conn; ij> PREPARE alice_conn.source_stmt AS 'SELECT * FROM T1'; ij> EXECUTE 'INSERT INTO T2 VALUES ' USING alice_conn.source.stmt; ij> REMOVE alice_conn.source_stmt; With that option, the above PREPARE statement has for meaning: " in the context of the connection alice_conn, prepare the statement 'SELECT * FROM T1' " I attach both a patch and sql example to demonstrate that option.
          Hide
          Sylvain Leroux added a comment -

          Attaching the third version of this patch. The following statements now use qualifiedIdentifiers:

          • PREPARE
          • EXECUTE ... USING ...
          • REMOVE
          • GET CURSOR
          • NEXT/PREVIOUS/FIRST/LAST/BEFORE FIRST/AFTER LAST/RELATIVE/ABSOLUTE/GETCURRENTROWNUMBER
          • CLOSE

          I added support for qualified identifiers on cursors as well, since I think it might be useful (as part of a different JIRA issue) to extend EXECUTE in order to accept cursors in the USING clause.

          I add some tests at the end of ij7.sql. I wasn't able to add GETCURRENTROWNUMBER in the test suite since it writes directly on the output, and somehow, the test tool does not take that output into account. I filed a JIRA issue on this (DERBY-4553).

          ToolScript test suite ran without any error.

          Show
          Sylvain Leroux added a comment - Attaching the third version of this patch. The following statements now use qualifiedIdentifiers: PREPARE EXECUTE ... USING ... REMOVE GET CURSOR NEXT/PREVIOUS/FIRST/LAST/BEFORE FIRST/AFTER LAST/RELATIVE/ABSOLUTE/GETCURRENTROWNUMBER CLOSE I added support for qualified identifiers on cursors as well, since I think it might be useful (as part of a different JIRA issue) to extend EXECUTE in order to accept cursors in the USING clause. I add some tests at the end of ij7.sql. I wasn't able to add GETCURRENTROWNUMBER in the test suite since it writes directly on the output, and somehow, the test tool does not take that output into account. I filed a JIRA issue on this ( DERBY-4553 ). ToolScript test suite ran without any error.
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          I see your point about how confusing it would be if qualified names behaved differently in some contexts. I think you are right that

          PREPARE alice_conn.source_stmt AS 'SELECT * FROM T1';

          should mean

          " in the context of the connection alice_conn, prepare the statement 'SELECT * FROM T1' "

          Off the top of my head, it seems to me that there are 3 kinds of names recognized by ij:

          1) Connection names. These are used in the CONNECT, DISCONNECT, and SET CONNECTION commands.

          2) Schema object names in the DESCRIBE commands. These are already handled by their own production, caIdentifier().

          3) Connection-scoped variables. These include the names of prepared statements, cursors, and protocols.

          I think it would be less confusing to users if all connection-scoped variables were treated as qualified identifiers.

          This brings us to the topic of what a QualifiedIdentifer is. Conceptually, I think it is just an ordered pair of names. Probably we will get into fewer maintenance issues if we model it that way. I don't think that we should store prepared statements, cursors, and protocols inside QualifiedIdentifiers. Instead, I think it would be better to use QualifiedIdentifiers to find prepared statements, cursors, and protocols using a two step process:

          i) Look up the Session by the connection name stored in the QualifiedIdentifier.

          ii) Then look inside the Session for the prepared statement, cursor, or protocol using the unqualified object name in the QualifiedIdentifer.

          Show
          Rick Hillegas added a comment - Hi Sylvain, I see your point about how confusing it would be if qualified names behaved differently in some contexts. I think you are right that PREPARE alice_conn.source_stmt AS 'SELECT * FROM T1'; should mean " in the context of the connection alice_conn, prepare the statement 'SELECT * FROM T1' " Off the top of my head, it seems to me that there are 3 kinds of names recognized by ij: 1) Connection names. These are used in the CONNECT, DISCONNECT, and SET CONNECTION commands. 2) Schema object names in the DESCRIBE commands. These are already handled by their own production, caIdentifier(). 3) Connection-scoped variables. These include the names of prepared statements, cursors, and protocols. I think it would be less confusing to users if all connection-scoped variables were treated as qualified identifiers. This brings us to the topic of what a QualifiedIdentifer is. Conceptually, I think it is just an ordered pair of names. Probably we will get into fewer maintenance issues if we model it that way. I don't think that we should store prepared statements, cursors, and protocols inside QualifiedIdentifiers. Instead, I think it would be better to use QualifiedIdentifiers to find prepared statements, cursors, and protocols using a two step process: i) Look up the Session by the connection name stored in the QualifiedIdentifier. ii) Then look inside the Session for the prepared statement, cursor, or protocol using the unqualified object name in the QualifiedIdentifer.
          Hide
          Sylvain Leroux added a comment -

          Thanks for your comments Rick,

          My first idea was to model qualifiedIdentifiers as a "pair of string". But, at some point, I was wondering if there could be some obscure corner case where the same session name will resolve to a different session object. That's why I chose to model qualified identifiers as

          {Session,String}

          .

          But, since there's no variable in ij, dynamic scoping shouldn't be an issue. I will rework that way.


          Concerning the second kind of names recognized by ij you identified:
          > 2) Schema object names in the DESCRIBE commands.
          > These are already handled by their own production, caIdentifier().
          Even if it could be useful to examine the tables in a different connection, I already set aside the DESCRIBE command, since offering such a possibility will lead to difficult situations:
          CONNECT '....' AS C;
          DESCRIBE C.T;
          Would the above statement describe the "table T of the default schema of the connection C" - or "table T of the schema C of the current connection". For compatibility the latter would have my preference. But it's a little bit confusing anyway - especially if you bring it closer to a statement allowing qualified identifier like that:

          CONNECT '....' AS C;
          DESCRIBE C.T; – Here, C is a schema
          PREPARE C.ST AS .... – Here, C is a session

          Show
          Sylvain Leroux added a comment - Thanks for your comments Rick, My first idea was to model qualifiedIdentifiers as a "pair of string". But, at some point, I was wondering if there could be some obscure corner case where the same session name will resolve to a different session object. That's why I chose to model qualified identifiers as {Session,String} . But, since there's no variable in ij, dynamic scoping shouldn't be an issue. I will rework that way. Concerning the second kind of names recognized by ij you identified: > 2) Schema object names in the DESCRIBE commands. > These are already handled by their own production, caIdentifier(). Even if it could be useful to examine the tables in a different connection, I already set aside the DESCRIBE command, since offering such a possibility will lead to difficult situations: CONNECT '....' AS C; DESCRIBE C.T; Would the above statement describe the "table T of the default schema of the connection C" - or "table T of the schema C of the current connection". For compatibility the latter would have my preference. But it's a little bit confusing anyway - especially if you bring it closer to a statement allowing qualified identifier like that: CONNECT '....' AS C; DESCRIBE C.T; – Here, C is a schema PREPARE C.ST AS .... – Here, C is a session
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          I agree that having two kinds of dot-separated names is confusing. I was only suggesting that we use qualified identifiers for connection-scoped variables. So in the example which you just gave...

          DESCRIBE C.T;

          ...I agree with you that the meaning should continue to be "the table T in the schema C of the database of the current connection".

          Maybe we shouldn't use PERIOD to separate the two parts of a qualified identifier. It may give rise to too much confusion. Maybe the separator should be some other character which can't appear in an identifier, like @. If we use @ we might want to flip the order

          variableName @ connectionName

          What do you think?

          Show
          Rick Hillegas added a comment - Hi Sylvain, I agree that having two kinds of dot-separated names is confusing. I was only suggesting that we use qualified identifiers for connection-scoped variables. So in the example which you just gave... DESCRIBE C.T; ...I agree with you that the meaning should continue to be "the table T in the schema C of the database of the current connection". Maybe we shouldn't use PERIOD to separate the two parts of a qualified identifier. It may give rise to too much confusion. Maybe the separator should be some other character which can't appear in an identifier, like @. If we use @ we might want to flip the order variableName @ connectionName What do you think?
          Hide
          Sylvain Leroux added a comment -

          I like the '@' idea.

          Quite readable indeed:
          PREPARE stmt@alice_conn AS 'SELECT * FROM T1';
          PREPARE stmt@fred_conn AS 'INSERT INTO T2 VALUES';
          EXECUTE stmt@fred_conn USING stmt@alice_conn;

          Show
          Sylvain Leroux added a comment - I like the '@' idea. Quite readable indeed: PREPARE stmt@alice_conn AS 'SELECT * FROM T1'; PREPARE stmt@fred_conn AS 'INSERT INTO T2 VALUES '; EXECUTE stmt@fred_conn USING stmt@alice_conn;
          Hide
          Sylvain Leroux added a comment -

          Attaching a new patch for this issue.

          Currently, Session objects hold reference for prepared statements, cursors/cursor statements and asynchronous statements.
          All now accept the '@' notation to design an identifier outside of the current connection. See the attached SQL file for example.

          Add some tests to ij7.sql.
          Changed a test result in ij2.out to reflect the fact that error messages now provides the session name as well as the faulty statement:
          ij> Connect 'jdbc:derby:memory:dummy;create=true';
          ij> EXECUTE stmt;
          IJ ERROR: Unable to establish prepared statement STMT@CONNECTION0

          Pass derbytool and ToolScript test suites.

          Ready for review.

          Show
          Sylvain Leroux added a comment - Attaching a new patch for this issue. Currently, Session objects hold reference for prepared statements, cursors/cursor statements and asynchronous statements. All now accept the '@' notation to design an identifier outside of the current connection. See the attached SQL file for example. Add some tests to ij7.sql. Changed a test result in ij2.out to reflect the fact that error messages now provides the session name as well as the faulty statement: ij> Connect 'jdbc:derby:memory:dummy;create=true'; ij> EXECUTE stmt; IJ ERROR: Unable to establish prepared statement STMT@CONNECTION0 Pass derbytool and ToolScript test suites. Ready for review.
          Hide
          Sylvain Leroux added a comment -

          Forgot to add QualifiedIdentifier.java in the patch.

          Show
          Sylvain Leroux added a comment - Forgot to add QualifiedIdentifier.java in the patch.
          Hide
          Rick Hillegas added a comment -

          Thanks for the patch, Sylvain. I think this is a great increment. Maybe someone will want to make PROTOCOL ids qualified identifiers too. I am running tests now.

          I made one change: I made the @ a token rather than a quoted string. Is there some reason that @ should not be a token?

          Thanks,
          -Rick

          Show
          Rick Hillegas added a comment - Thanks for the patch, Sylvain. I think this is a great increment. Maybe someone will want to make PROTOCOL ids qualified identifiers too. I am running tests now. I made one change: I made the @ a token rather than a quoted string. Is there some reason that @ should not be a token? Thanks, -Rick
          Hide
          Rick Hillegas added a comment -

          Thanks again for the patch, Sylvain. I am seeing errors in the following tests:

          derbylang/cursorerrors
          derbylang/holdCursorIJ

          and

          org.apache.derbyTesting.functionTests.tests.lang.LangScripts

          Show
          Rick Hillegas added a comment - Thanks again for the patch, Sylvain. I am seeing errors in the following tests: derbylang/cursorerrors derbylang/holdCursorIJ and org.apache.derbyTesting.functionTests.tests.lang.LangScripts
          Hide
          Sylvain Leroux added a comment -

          Hi Rick,

          Thanks for the review.
          I will look at those tests to see what's going wrong.

          Show
          Sylvain Leroux added a comment - Hi Rick, Thanks for the review. I will look at those tests to see what's going wrong.
          Hide
          Sylvain Leroux added a comment -

          Here is the 5th version of this patch.

          As I suspected first, the errors in some tests are caused by the fact that ij now reports the connection as part as the identifier when an error in encountered.

          As you noticed here, this slightly breaks the observable behavior of ij. But I think from an user point of view it is important to report the connection name as well as the unqualified identifier in order to clearly distinguish a faulty statement.

          I chose to update all the related tests in order to reflect that. Here are some examples of such changes (extract from the attached patch):
          Index: java/testing/org/apache/derbyTesting/functionTests/master/holdCursorIJ.out
          [...]
          -IJ ERROR: Unable to establish cursor
          +IJ ERROR: Unable to establish cursor JDK4@CONNECTION0

          Index: java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out
          [...]
          -IJ ERROR: Unable to establish prepared statement P1
          +IJ ERROR: Unable to establish prepared statement P1@CONNECTION0

          Now pass the following tests without any error:
          java org.apache.derbyTesting.functionTests.harness.RunSuite derbylang
          java org.apache.derbyTesting.functionTests.harness.RunSuite derbytools
          java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.lang.LangScripts
          java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.tools.ToolScripts


          Concerning a possible use of qualified identifiers in the PROTOCOL statement:

          I didn't notice a previous comment on this:
          > 3) Connection-scoped variables. These include the names of prepared statements, cursors, and protocols.

          In fact, protocols are not connection-scoped: protocols identifiers are currently bound the parser instance, not to a session object. It feels like some kind of global identifier to me. Moreover, the PROTOCOL statement could be issued before opening any connection (i.e.: before any session object was available):
          sh$ java org.apache.derby.tools.ij
          ij version 10.6
          ij> PROTOCOL 'jdbc:derby:memory:' AS memory;
          ij> CONNECT 'a;create=true;user=fred' PROTOCOL memory;
          ij> CONNECT 'b;create=true' PROTOCOL memory;

          Please note I never really used that statement, so it's quite possible I missed some important point here.


          An other possible candidate for qualified identifiers could be the DESCRIBE statement. But I think this is part of an other issue, since DESCRIBE requires some special treatment (it uses its own production caIdentifier).

          Show
          Sylvain Leroux added a comment - Here is the 5th version of this patch. As I suspected first, the errors in some tests are caused by the fact that ij now reports the connection as part as the identifier when an error in encountered. As you noticed here, this slightly breaks the observable behavior of ij. But I think from an user point of view it is important to report the connection name as well as the unqualified identifier in order to clearly distinguish a faulty statement. I chose to update all the related tests in order to reflect that. Here are some examples of such changes (extract from the attached patch): Index: java/testing/org/apache/derbyTesting/functionTests/master/holdCursorIJ.out [...] -IJ ERROR: Unable to establish cursor +IJ ERROR: Unable to establish cursor JDK4@CONNECTION0 Index: java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out [...] -IJ ERROR: Unable to establish prepared statement P1 +IJ ERROR: Unable to establish prepared statement P1@CONNECTION0 Now pass the following tests without any error: java org.apache.derbyTesting.functionTests.harness.RunSuite derbylang java org.apache.derbyTesting.functionTests.harness.RunSuite derbytools java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.lang.LangScripts java junit.textui.TestRunner org.apache.derbyTesting.functionTests.tests.tools.ToolScripts Concerning a possible use of qualified identifiers in the PROTOCOL statement: I didn't notice a previous comment on this: > 3) Connection-scoped variables. These include the names of prepared statements, cursors, and protocols. In fact, protocols are not connection-scoped: protocols identifiers are currently bound the parser instance, not to a session object. It feels like some kind of global identifier to me. Moreover, the PROTOCOL statement could be issued before opening any connection (i.e.: before any session object was available): sh$ java org.apache.derby.tools.ij ij version 10.6 ij> PROTOCOL 'jdbc:derby:memory:' AS memory; ij> CONNECT 'a;create=true;user=fred' PROTOCOL memory; ij> CONNECT 'b;create=true' PROTOCOL memory; Please note I never really used that statement, so it's quite possible I missed some important point here. An other possible candidate for qualified identifiers could be the DESCRIBE statement. But I think this is part of an other issue, since DESCRIBE requires some special treatment (it uses its own production caIdentifier).
          Hide
          Sylvain Leroux added a comment -

          By the way, since it was the original purpose of this issue, I successfully copied data from MySQL to Derby using this patch. As reference here is an example:
          ij> CONNECT 'jdbc:mysql://localhost/test?user=fred';
          ij> CONNECT 'jdbc:derby:destination;create=true';
          ij(CONNECTION1)> CREATE TABLE t(a int, b int);
          0 rows inserted/updated/deleted
          ij(CONNECTION1)> PREPARE src@connection0 as 'SELECT a,b FROM t';
          ij(CONNECTION1)> AUTOCOMMIT OFF;
          ij(CONNECTION1)> EXECUTE 'INSERT INTO t(a,b) VALUES(?,?)' USING src@connection0;
          1 row inserted/updated/deleted
          1 row inserted/updated/deleted
          1 row inserted/updated/deleted
          ij(CONNECTION1)> COMMIT;
          ij(CONNECTION1)> DISCONNECT CONNECTION0;
          ij> SHOW CONNECTIONS;
          CONNECTION1* - jdbc:derby:destination

          • = current connection
            ij> SELECT * FROM t;
            A |B
            -----------------------
            1 |100
            2 |200
            3 |300

          3 rows selected

          Show
          Sylvain Leroux added a comment - By the way, since it was the original purpose of this issue, I successfully copied data from MySQL to Derby using this patch. As reference here is an example: ij> CONNECT 'jdbc:mysql://localhost/test?user=fred'; ij> CONNECT 'jdbc:derby:destination;create=true'; ij(CONNECTION1)> CREATE TABLE t(a int, b int); 0 rows inserted/updated/deleted ij(CONNECTION1)> PREPARE src@connection0 as 'SELECT a,b FROM t'; ij(CONNECTION1)> AUTOCOMMIT OFF; ij(CONNECTION1)> EXECUTE 'INSERT INTO t(a,b) VALUES(?,?)' USING src@connection0; 1 row inserted/updated/deleted 1 row inserted/updated/deleted 1 row inserted/updated/deleted ij(CONNECTION1)> COMMIT; ij(CONNECTION1)> DISCONNECT CONNECTION0; ij> SHOW CONNECTIONS; CONNECTION1* - jdbc:derby:destination = current connection ij> SELECT * FROM t; A |B ----------------------- 1 |100 2 |200 3 |300 3 rows selected
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          I think that you left QualifiedIdentifier out of the patch.

          Regards,
          -Rick

          Show
          Rick Hillegas added a comment - Hi Sylvain, I think that you left QualifiedIdentifier out of the patch. Regards, -Rick
          Hide
          Sylvain Leroux added a comment -

          Sorry about that, Rick...

          Here is a 6th version of the patch. This time I double-checked - everything should be all right.

          M java/tools/org/apache/derby/impl/tools/ij/ConnectionEnv.java
          M java/tools/org/apache/derby/impl/tools/ij/ij.jj
          A java/tools/org/apache/derby/impl/tools/ij/QualifiedIdentifier.java
          M java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij7.sql
          M java/testing/org/apache/derbyTesting/functionTests/master/ij2.out
          M java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out
          M java/testing/org/apache/derbyTesting/functionTests/master/nonreserved.out
          M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out
          M java/testing/org/apache/derbyTesting/functionTests/master/cursorerrors.out
          M java/testing/org/apache/derbyTesting/functionTests/master/holdCursorIJ.out

          Show
          Sylvain Leroux added a comment - Sorry about that, Rick... Here is a 6th version of the patch. This time I double-checked - everything should be all right. M java/tools/org/apache/derby/impl/tools/ij/ConnectionEnv.java M java/tools/org/apache/derby/impl/tools/ij/ij.jj A java/tools/org/apache/derby/impl/tools/ij/QualifiedIdentifier.java M java/testing/org/apache/derbyTesting/functionTests/tests/tools/ij7.sql M java/testing/org/apache/derbyTesting/functionTests/master/ij2.out M java/testing/org/apache/derbyTesting/functionTests/master/implicitConversions.out M java/testing/org/apache/derbyTesting/functionTests/master/nonreserved.out M java/testing/org/apache/derbyTesting/functionTests/master/ij7.out M java/testing/org/apache/derbyTesting/functionTests/master/cursorerrors.out M java/testing/org/apache/derbyTesting/functionTests/master/holdCursorIJ.out
          Hide
          Rick Hillegas added a comment -

          Thanks for the revised patch, Sylvain. The tests ran cleanly for me except for discrepancies in DerbyNet/holdCursorIJ and DerbyNetClient/holdCursorIJ. I updated the canons for those tests. There was also an error in OnlineCompressTest, which I thought was an instability in that test: it didn't seem related to your work and the error did not recur when I ran the test standalone. Committed at subversion revision 916014.

          Thanks for this great feature. I will add a doc issue so that we don't forget to update the ij documentation accordingly.

          Show
          Rick Hillegas added a comment - Thanks for the revised patch, Sylvain. The tests ran cleanly for me except for discrepancies in DerbyNet/holdCursorIJ and DerbyNetClient/holdCursorIJ. I updated the canons for those tests. There was also an error in OnlineCompressTest, which I thought was an instability in that test: it didn't seem related to your work and the error did not recur when I ran the test standalone. Committed at subversion revision 916014. Thanks for this great feature. I will add a doc issue so that we don't forget to update the ij documentation accordingly.
          Hide
          Rick Hillegas added a comment -

          Hi Sylvain,

          Could you take a look at DERBY-4570? The test for the new functionality fails on small devices because you can't use the CONNECT command there. I think that the solution is to move the new test into its own script and have the ToolScript driver not invoke that script on small devices. Thanks-Rick

          Show
          Rick Hillegas added a comment - Hi Sylvain, Could you take a look at DERBY-4570 ? The test for the new functionality fails on small devices because you can't use the CONNECT command there. I think that the solution is to move the new test into its own script and have the ToolScript driver not invoke that script on small devices. Thanks-Rick

            People

            • Assignee:
              Sylvain Leroux
              Reporter:
              Sylvain Leroux
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development