Referencing tables in other db's

[Nobody/Anonymous]

Hi!
First of all I think jsqlparser is great!
I have a small issue, when referencing a table in another db. When I write something like:
select * from table1 a join otherdb..table2 b on  a.id=b.id
It doesn't seem to like the two dots ".." in otherdb..table2.

[Leonardo Francalanci]

when the two dots syntax can be used? I've never seen it

[ThomasA]

Sybase syntax uses 2 dots for database references, as in:

SELECT column FROM database..table

[ThomasA]

Hmm, this got me thinking and actually I found that sybase behaves a bit funny, hehe… see these examples:

SELECT column FROM table -- OK
SELECT column FROM schema.table  -- OK
SELECT column FROM database..table -- OK (using default schema, dbo, notice 2 dots)
SELECT column FROM database.schema.table  -- OK
SELECT column FROM database..schema.table -- FAIL, use only 1 dot when specifying schema

I have not seen this syntax anywhere other than in Sybase SQL.

[Anonymous]

hi thomasa-aloc,

i noticed that you'd marked OK next to SELECT column FROM database.schema.table
i am having a problem parsing that simply statement, i get an exception
Caused by: net.sf.jsqlparser.parser.ParseException: Encountered " "." ". "" at line 1, column 35.

do you have any suggestion on how to get the parser to parse this statement?

[ThomasA]

Hi
The examples I gave was only to show others what syntax is actually allowed in Sybase in hope that someone would incorporate this into jsqlparser. You could patch it yourself if you need this. For a simple (bad practice) solution you could implement a preprocessor before using jsqlparser, where you replace 'database.schema.table' with something like 'databaseMYDOTschema.table' as 1 dot is valid syntax. Afterwards you could post process it and replace 'MYDOT' with '.'.

But the best thing would be to have it added to the grammar file, but it seems like there is not much work being done on the project these days :-(

~thomasa-aloc

[Anonymous]

thanks for the reply.

i was able to modify the grammar that was included with jsqlparser and added the extra field needed for database.schema.table

Table Table():
{
Table table = null;
String name1 = null;
String name2 = null;
String name3 = null;
}
{
(LOOKAHEAD(5)
name1=RelObjectName() "." name2=RelObjectName() "." name3=RelObjectName()  { table = new Table(name1, name2, name3); }
|
LOOKAHEAD(3)
name1=RelObjectName() "." name2=RelObjectName()  { table = new Table(name1, name2); }
|
name1=RelObjectName() { table = new Table(null, name1); }
)
{
return table;
}
}

i then ran javacc to generate the new code…now i'm having trouble parsing case statements :(