I am using UCANACCESS 2.0.6.1 and JACKCESS 2.0.4. I have 3 problems, but 2
of them are semi related to one another. If I go into Microsoft Access and
build my own tables and columns, I am allowed to use these 2 characters (_
and -). However, if I create the table from java and pass my SQL string
for execution. After the create table command has passed and comes back as
successful, I look at my tables and columns, which are different than what
I passed it. Here is an example:
_tableName => Z_tableName in Microsoft Access. However, I can go into
Microsoft Access and create my own table called _tableName. If I try and
use -tableName => Z_tableName
Also,
Column names are an issue. Such as: "GDD(NS)-Seeding to Emergence" =>
"GDD_NS__Seeding to Emergence"
I am going to help troubleshoot the problem with you by telling you that
either Ucanaccess or Jackcess is the problem. I know Micorosoft Access is
not. If I use a program called RazorSQL, similar to TOAD for Oracle
databases, and run nearly the exact same command with only 1 difference
then I get the table and columns in Access as I was expecting. Now the
difference between the 2 SQL strings is that (UCanaccess / Jackcess) one of
you 2 did this insanely weird, nonlogical thing of saying AUTOINCREMENT in
Microsoft Access, is replaced by COUNTER using your connection ::rolling my
eyes::. So who would like to solve my problem, before I just expect this
to be a limitation and the next time someone asks, hey do you know of any
good JDBC plugins, I can say more that "well I know this 1 software, but it
has it's good points and it's crappy points" So far everything is great
except for the 3 things I mentioned above.
-Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Now I've got a bit of connectivity from this holiday location.
You're likely right on all three point you mentioned.
Also all three the issues might be solvable at the ucanaccess layer (only I want to better analyze the counter/autoincrement issue).
I can't properly answer now because I'm far from my office, but I'm going to answer point by point next monday.
Cheers Marco
Last edit: Marco Amadei 2014-08-08
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
About the special characters bug in DDL:
it will be fixed in the next version.
For instance, if you want to create a table named "_tableName" (so starting with an underscore character) you'll be able to do it without seeing the name modified.
Nevertheless I need to escape colum and table names like "GDD(NS)-Seeding to Emergence", when I load a accdb mdb in the hsqldb mirror database, because hsqldb doesn't accept characters like '(' and ')'.
But you can use the original names in your queries and in the getXXX methods of ResultSet.
Also, if I had used the hsqldb escaping(escaped names between "), I would have forced the exact case usage in your sql for those names(and I obviously wanted to avoid that).
About the autoincrement/counter:
Notice that the statements
Thank you for all the information. The AutoIncrement is what I was concerned about, it's not currently supported therefore I had to use the Counter to make it work. But it sucks if I am sending an SQL command line "create table tt(x autoincrement primary key, txt text);" and can't figure out why it's not working properly.
Also I am glad to knwo that I wasn't going crazy with the (-) changing to (_). Although you said you didn't want to do it, I would really appreciate it if you would make the following characters "(" and ")" available. Here is how I think about it. If I go into another program like RazorSQL or Toad for Oracle, and I can send an SQL command to create a table with specific column names with these 2 characters "(" and ")", then I should also be able to do it using your program. Because typically when I am writing code, I am using a Program like RazorSQL or Toad to figure out my SQL and then I am writing code directly for that command. Moreover, those 2 characters are supported in Access and it makes it easier to read the column names.
Again thank you for all of your help.
-Mike Herder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi Mike,
perhaps there was something unclear in my answer, so sorry.
In the ddl (create table statement) you'll be able to use all special characters:
(, ),-.
without see them changed in the database column or table name.
I won't discriminate between special character and special character.
In the current mirror database I must change those names to preserve the case insensitivity in your SQL code and, obviously, the compatibility with hsqldb.
It's just an internal rappresentation you'll see in the ResultSetMetadata.
But you can already-in the current UCanAccess version- ignore this imperfection in your SQL:e.g., once a column is named "GDD(NS)-Seeding to Emergence",
you can execute without errors:
select [GDD(NS)-Seeding to Emergence] from your_table
resultSet.getString("GDD(NS)-Seeding to Emergence");
BUT
this imperfection in the ResultSetMetadata there still will be in the next versions.
To get the internal naming rappresentation, currently limited to metadata, totally trasparent to the users(in the ResultSetMetadata too), I have to put a lot of effort for this and I want to do it.
But I hope to release the first version with this final fix in October(UCanAccess 3.0.0).
Cheers Marco
P.S. Notice that the current version is the 2.0.8 and I fixed some bugs about special characters after the 2.0.6.1
Last edit: Marco Amadei 2014-08-13
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thank you for the clarification. I am glad that you are making the changes, it makes for a better plugin for database access. Unfortunately, I am under a tight deadline so I am going to have to change how I am doing things now and remove my special characters "()" and just use underscores. I will be looking forward to updating to UCANACCESS 3.0. Thank you for your time and I hope life is treating you well.
-Mike
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
So, the current column order in UCanAccess is "data", that is the jackcess default. I didn't change it but I think it's a good suggestion.
I'll add a new connection parameter (columnOrder=display) to change this default behavior.
Notice that I'm going to release the next version before the end of August but, for your tight deadline, you'll be able to download a "preview" next week from the svn trunk.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I just meant that I'll do asap the svn commit of the new feature implementation related to the column order and then of the bug fix related to the autoincrement keyword and so on, so that you'll be able to use some of them before the official release.
I'll give notice on this forum after each upgrade on the svn trunk.
Last edit: Marco Amadei 2014-08-19
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi there!
I am using UCANACCESS 2.0.6.1 and JACKCESS 2.0.4. I have 3 problems, but 2
of them are semi related to one another. If I go into Microsoft Access and
build my own tables and columns, I am allowed to use these 2 characters (_
and -). However, if I create the table from java and pass my SQL string
for execution. After the create table command has passed and comes back as
successful, I look at my tables and columns, which are different than what
I passed it. Here is an example:
_tableName => Z_tableName in Microsoft Access. However, I can go into
Microsoft Access and create my own table called _tableName. If I try and
use -tableName => Z_tableName
Also,
Column names are an issue. Such as: "GDD(NS)-Seeding to Emergence" =>
"GDD_NS__Seeding to Emergence"
I am going to help troubleshoot the problem with you by telling you that
either Ucanaccess or Jackcess is the problem. I know Micorosoft Access is
not. If I use a program called RazorSQL, similar to TOAD for Oracle
databases, and run nearly the exact same command with only 1 difference
then I get the table and columns in Access as I was expecting. Now the
difference between the 2 SQL strings is that (UCanaccess / Jackcess) one of
you 2 did this insanely weird, nonlogical thing of saying AUTOINCREMENT in
Microsoft Access, is replaced by COUNTER using your connection ::rolling my
eyes::. So who would like to solve my problem, before I just expect this
to be a limitation and the next time someone asks, hey do you know of any
good JDBC plugins, I can say more that "well I know this 1 software, but it
has it's good points and it's crappy points" So far everything is great
except for the 3 things I mentioned above.
-Mike
Also note: I have already used [], so that isn't the problem either.
I can't answer until next monday. So sorry.
Last edit: Marco Amadei 2014-08-07
Now I've got a bit of connectivity from this holiday location.
You're likely right on all three point you mentioned.
Also all three the issues might be solvable at the ucanaccess layer (only I want to better analyze the counter/autoincrement issue).
I can't properly answer now because I'm far from my office, but I'm going to answer point by point next monday.
Cheers Marco
Last edit: Marco Amadei 2014-08-08
About the special characters bug in DDL:
it will be fixed in the next version.
For instance, if you want to create a table named "_tableName" (so starting with an underscore character) you'll be able to do it without seeing the name modified.
Nevertheless I need to escape colum and table names like "GDD(NS)-Seeding to Emergence", when I load a accdb mdb in the hsqldb mirror database, because hsqldb doesn't accept characters like '(' and ')'.
But you can use the original names in your queries and in the getXXX methods of ResultSet.
Also, if I had used the hsqldb escaping(escaped names between "), I would have forced the exact case usage in your sql for those names(and I obviously wanted to avoid that).
About the autoincrement/counter:
Notice that the statements
create table tt(x autoincrement primary key, txt text);
AND
create table tt(x counter primary key, txt text);
have the same exact effect in access.
"Autoincrement" keyword is not currently supported by UCanAccess but it will be supported in the next version.
Cheers Marco
Last edit: Marco Amadei 2014-08-12
Marco,
Thank you for all the information. The AutoIncrement is what I was concerned about, it's not currently supported therefore I had to use the Counter to make it work. But it sucks if I am sending an SQL command line "create table tt(x autoincrement primary key, txt text);" and can't figure out why it's not working properly.
Also I am glad to knwo that I wasn't going crazy with the (-) changing to (_). Although you said you didn't want to do it, I would really appreciate it if you would make the following characters "(" and ")" available. Here is how I think about it. If I go into another program like RazorSQL or Toad for Oracle, and I can send an SQL command to create a table with specific column names with these 2 characters "(" and ")", then I should also be able to do it using your program. Because typically when I am writing code, I am using a Program like RazorSQL or Toad to figure out my SQL and then I am writing code directly for that command. Moreover, those 2 characters are supported in Access and it makes it easier to read the column names.
Again thank you for all of your help.
-Mike Herder
Hi Mike,
perhaps there was something unclear in my answer, so sorry.
In the ddl (create table statement) you'll be able to use all special characters:
(, ),-.
without see them changed in the database column or table name.
I won't discriminate between special character and special character.
In the current mirror database I must change those names to preserve the case insensitivity in your SQL code and, obviously, the compatibility with hsqldb.
It's just an internal rappresentation you'll see in the ResultSetMetadata.
But you can already-in the current UCanAccess version- ignore this imperfection in your SQL:e.g., once a column is named "GDD(NS)-Seeding to Emergence",
you can execute without errors:
select [GDD(NS)-Seeding to Emergence] from your_table
resultSet.getString("GDD(NS)-Seeding to Emergence");
BUT
this imperfection in the ResultSetMetadata there still will be in the next versions.
To get the internal naming rappresentation, currently limited to metadata, totally trasparent to the users(in the ResultSetMetadata too), I have to put a lot of effort for this and I want to do it.
But I hope to release the first version with this final fix in October(UCanAccess 3.0.0).
Cheers Marco
P.S. Notice that the current version is the 2.0.8 and I fixed some bugs about special characters after the 2.0.6.1
Last edit: Marco Amadei 2014-08-13
Marco,
Thank you for the clarification. I am glad that you are making the changes, it makes for a better plugin for database access. Unfortunately, I am under a tight deadline so I am going to have to change how I am doing things now and remove my special characters "()" and just use underscores. I will be looking forward to updating to UCANACCESS 3.0. Thank you for your time and I hope life is treating you well.
-Mike
Marco,
Actually there is one more question for you. I am trying to figure out how to solve this problem with an SQL Query. I am using the following Query:
rs = s.executeQuery("SELECT * FROM [" + tableName + "] WHERE [" + column + "]=\"" + selection + "\"" );
Why is it that it grabs all the data from the row I want, but it returns the column information out of order? Under Jackcess I can use:
db.setColumnOrder(ColumnOrder.DISPLAY);
Is there a way to fix the return for my query above so that all the columns come back in the appropriate order?
-Mike
So, the current column order in UCanAccess is "data", that is the jackcess default. I didn't change it but I think it's a good suggestion.
I'll add a new connection parameter (columnOrder=display) to change this default behavior.
Notice that I'm going to release the next version before the end of August but, for your tight deadline, you'll be able to download a "preview" next week from the svn trunk.
Marco,
Just by curiosity, what is a preview? Is this similar to a Beta? Thanks for getting me something a bit quicker. Hope you have a great week.
-Mike
I just meant that I'll do asap the svn commit of the new feature implementation related to the column order and then of the bug fix related to the autoincrement keyword and so on, so that you'll be able to use some of them before the official release.
I'll give notice on this forum after each upgrade on the svn trunk.
Last edit: Marco Amadei 2014-08-19
Column order = display allowed in svn trunk
Jdbc url example:
jdbc:ucanaccess://c:/db/display.accdb;COLUMNORDER=DISPLAY
AUTOINCREMENT keyword support in svn trunk
I'm going to release the 2.0.9 during this week.
create table with special character issue fixed on svn trunk
Column order setting, special characters in ddl and autoincrement issues fixed in the 2.0.9.