java.util.regex.Pattern.error

[Nada Benyamin]

Hi, I'm trying to connect to MS Access database with many tables and views. Many of these opbjects have special characters in the name like (, ) * and many other characters.

Here is part of my Scala code that i'm using to connect to the database:

import java.sql.DriverManager
import java.sql.Connection
import java.sql.Statement
import java.sql.ResultSet

import net.ucanaccess.jdbc.UcanaccessConnection
import net.ucanaccess.jdbc.UcanaccessSQLException
import net.ucanaccess.jdbc.UcanaccessSQLException.ExceptionMessages

object Main {
def main(args: Array[String])
{

    try {

     val url = "jdbc:ucanaccess://C:/Nada/NewIssues/PIACodeMaintenanceDatabaseEmptysg.mdb"
     val user = "test"
     val psw = "test"
     val tbl = "Main Data Table - HR Titles" 
     var conn: Connection =DriverManager.getConnection(url, user, psw);

     val statement: Statement  = conn.createStatement()
     val resultSet: ResultSet = statement.executeQuery("Select [HR Job Title] as hr,  [PIA Job Title] as pia from ["  + tbl + "]")

     } catch {....}

Note that I have enclosed the table I'm selecting from between square brackets and it works properly, but the error I'm getting is complaining about objects (views and tables) that are in my database but I'm not trying to access in my code.

Here is the error message:

java.util.regex.PatternSyntaxException: Unmatched closing ')' near index 15
HR UPDATE - H) STATUS UPDATE
^
at java.util.regex.Pattern.error(Unknown Source)
at java.util.regex.Pattern.compile(Unknown Source)
at java.util.regex.Pattern.<init>(Unknown Source)
at java.util.regex.Pattern.compile(Unknown Source)
at java.lang.String.replaceAll(Unknown Source)
at net.ucanaccess.converters.SQLConverter.replaceWhiteSpacedTableNames0(SQLConverter.java:531)
at net.ucanaccess.converters.SQLConverter.replaceWhiteSpacedTables(SQLConverter.java:510)
at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:368)
at net.ucanaccess.converters.SQLConverter.convertSQL(SQLConverter.java:467)
at net.ucanaccess.jdbc.UcanaccessStatement.convertSQL(UcanaccessStatement.java:57)
at net.ucanaccess.jdbc.UcanaccessStatement.executeQuery(UcanaccessStatement.java:206)
at com.td.wealth.newissues.Main$.main(Main.scala:31)
at com.td.wealth.newissues.Main.main(Main.scala)

Is there away to avoid this?

Thanks

[Marco Amadei]

mmm... something odd. It may happen at the connection time: what if you comment the executeQuery part of your code?

[Nada Benyamin]

Hi Marco, thanks for your quick response.
I commented executeQuery, and it runs. I don't get the error.

[Marco Amadei]

Please print out the generated sql that causes the error and post it.

[Nada Benyamin]

I added the last line to print the sql,

object Main {
def main(args: Array[String])
{

    try {

     val url = "jdbc:ucanaccess://G:/Inf_Sys/Programmers/Nada/NewIssues/PIACodeMaintenanceDatabaseEmptysg.mdb"
     val user = "test"
     val psw = "test"
     val tbl = "Main Data Table - HR Titles" 
     var conn: Connection =DriverManager.getConnection(url, user, psw);

     val statement: Statement  = conn.createStatement()
     println ("Select [HR Job Title] as hr,  [PIA Job Title] as pia from ["  + tbl + "]")

here is the generated sql:

Select [HR Job Title] as hr, [PIA Job Title] as pia from [Main Data Table - HR Titles]

[Marco Amadei]

This query works perfectly for me(I've created a table named Main Data Table - HR Titles with the two columns shown) . Which ucanaccess version are you using? May you try the same query through the ucanaccess console and send along the output?

[Nada Benyamin]

I'm using UCanAccess 3.0.2.
The query works for me too if I don't have other tables in the database with special characters in the name. There is a view which in my database name "HR Update - H) Status Update". This is is where it is failing for me. I'm not even trying to access this view.

[Marco Amadei]

You know Nada, without reproducing and seeing the issue, it's impossible to help you. I would suggest to post in attachment the db or an its minimal subset (of both tables and data) so that we can reproduce the situation.

[Nada Benyamin]

I recreated the problem in a blank db and ran the code. It didn't fail. It maybe an issue with the database, it is old and contains many tables and view.
To resolve this I will create a blank db and create a link to table that I need.
Thanks,
Nada