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 <[email protected]> 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 > >
