You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Alain Kuchta <al...@etegent.com> on 2012/08/28 23:15:28 UTC
Unexpected data from ResultSet.getMetaData()
Hello all,
I'm developing a tool to move data from a SQL server database to a Derby database using Java.
Both databases have been constructed using through nearly identical (as much as possible) SQL scripts run through the appropriate means for each engine. One of the initial steps of my process is to confirm that the schema of the two databases is identically before transferring data.
This has uncovered some issues.
1) I want to compare the column names in each table to ensure they match. It seems that derby does not necessarily store column names in the order they were declared at the time of the tables creation. Ex.
SQL:
CREATE TABLE someTable (colA int, colB int, colC int);
JAVA:
For (int i = 1; I < results.getMetaData().getColumnCount(); i++) {
System.out.print(results.getMetaData().getColumnName(i) + " ");
}
The output of the java code is not always "colA colB colC", for some tables it is appearing as "colB colA colC" for example.
When the table is viewed via the ij tool, the columns are in the expected order (the order they were declared).
2) in a similar vein, .getMetaData().getColumnCount() sometimes returns more columns then were specified in creation script.
Any help would be much appreciated, and allow me to avoid developing a complicated work-around.
Thanks,
Alain Kuchta
Re: Unexpected data from ResultSet.getMetaData()
Posted by Dag Wanvik <da...@oracle.com>.
Hi,
I just tried to repro this without success (using both ij describe and
JDBC databasemetadata). Does anyone have a repro of this behavior?
If you still see this behavior, please file a JIRA for it - as far as I
can tell the row ordering should be based on the column's ordinal
position in the table (cf. the query used for the database metadata in
java/engine/org/apache/derby/impl/jdbc/metadata.properties, which orders
the result set on ordinal position).
Thanks,
Dag
On 29.08.2012 05:52, David Myers wrote:
> Hi alain,
>
> I can confirm that this was an issue for me, I posted a comment on
> StackOverflow, a helpfull response came from Brayn Pendleton (who I
> guess is a Derby developper). Here is the link to the discussion.
>
> http://stackoverflow.com/questions/12089848/describe-and-select-on-table-in-derby-doesnt-use-same-column-order-as-create
>
> My solution was to get the order of the columns by storing them in a
> string array (or if you prefere a linkedList), then the 'insert'
> statement uses the same order and I get the values based on the same
> order.
>
> Oddly enough doing this 'String array' thing seems to have prevented
> the issue of different ordering also ?
>
> Can you follow bryan's advice and report details of the SYSCOLUMNS.
>
> David.
>
> ps. make a copy / backup of your script and DB to preserve the problem.
>
> On Tue, Aug 28, 2012 at 11:15 PM, Alain Kuchta<al...@etegent.com> wrote:
>> Hello all,
>>
>>
>>
>> I’m developing a tool to move data from a SQL server database to a Derby
>> database using Java.
>>
>> Both databases have been constructed using through nearly identical (as much
>> as possible) SQL scripts run through the appropriate means for each engine.
>> One of the initial steps of my process is to confirm that the schema of the
>> two databases is identically before transferring data.
>>
>>
>>
>> This has uncovered some issues.
>>
>>
>>
>> 1) I want to compare the column names in each table to ensure they match. It
>> seems that derby does not necessarily store column names in the order they
>> were declared at the time of the tables creation. Ex.
>>
>>
>>
>> SQL:
>>
>> CREATE TABLE someTable (colA int, colB int, colC int);
>>
>>
>>
>> JAVA:
>>
>> For (int i = 1; I< results.getMetaData().getColumnCount(); i++) {
>>
>> System.out.print(results.getMetaData().getColumnName(i) + “ “);
>>
>> }
>>
>>
>>
>> The output of the java code is not always “colA colB colC”, for some tables
>> it is appearing as “colB colA colC” for example.
>>
>> When the table is viewed via the ij tool, the columns are in the expected
>> order (the order they were declared).
>>
>>
>>
>> 2) in a similar vein, .getMetaData().getColumnCount() sometimes returns more
>> columns then were specified in creation script.
>>
>>
>>
>> Any help would be much appreciated, and allow me to avoid developing a
>> complicated work-around.
>>
>> Thanks,
>>
>> Alain Kuchta
>>
>>
Re: Unexpected data from ResultSet.getMetaData()
Posted by David Myers <da...@gmail.com>.
Hi alain,
I can confirm that this was an issue for me, I posted a comment on
StackOverflow, a helpfull response came from Brayn Pendleton (who I
guess is a Derby developper). Here is the link to the discussion.
http://stackoverflow.com/questions/12089848/describe-and-select-on-table-in-derby-doesnt-use-same-column-order-as-create
My solution was to get the order of the columns by storing them in a
string array (or if you prefere a linkedList), then the 'insert'
statement uses the same order and I get the values based on the same
order.
Oddly enough doing this 'String array' thing seems to have prevented
the issue of different ordering also ?
Can you follow bryan's advice and report details of the SYSCOLUMNS.
David.
ps. make a copy / backup of your script and DB to preserve the problem.
On Tue, Aug 28, 2012 at 11:15 PM, Alain Kuchta <al...@etegent.com> wrote:
> Hello all,
>
>
>
> I’m developing a tool to move data from a SQL server database to a Derby
> database using Java.
>
> Both databases have been constructed using through nearly identical (as much
> as possible) SQL scripts run through the appropriate means for each engine.
> One of the initial steps of my process is to confirm that the schema of the
> two databases is identically before transferring data.
>
>
>
> This has uncovered some issues.
>
>
>
> 1) I want to compare the column names in each table to ensure they match. It
> seems that derby does not necessarily store column names in the order they
> were declared at the time of the tables creation. Ex.
>
>
>
> SQL:
>
> CREATE TABLE someTable (colA int, colB int, colC int);
>
>
>
> JAVA:
>
> For (int i = 1; I < results.getMetaData().getColumnCount(); i++) {
>
> System.out.print(results.getMetaData().getColumnName(i) + “ “);
>
> }
>
>
>
> The output of the java code is not always “colA colB colC”, for some tables
> it is appearing as “colB colA colC” for example.
>
> When the table is viewed via the ij tool, the columns are in the expected
> order (the order they were declared).
>
>
>
> 2) in a similar vein, .getMetaData().getColumnCount() sometimes returns more
> columns then were specified in creation script.
>
>
>
> Any help would be much appreciated, and allow me to avoid developing a
> complicated work-around.
>
> Thanks,
>
> Alain Kuchta
>
>