Sybase DataContext : The column prefix does not match with a table name or alias name

Question

We are dealing with sybase database in core java code. We are using org.eobjects.metamodel.DataContext to parse query.

String sCol[]=table.getColumnNames();
Query query=dataContext.query().from(table.getName()).select(sCol).toQuery();
return new QueryIterator(dataContext.executeQuery(query).iterator());

But it executing query. Same code working fine with Oracle database to parse and execute query.

Some of query example generated are :

• select City.CityName from ownername.City
• select City.CityName from City
• select CityName from ownername.City
• select CityName from City
• select ownername.City.CityName from ownername.City
• SELECT "City"."CityName" FROM ownername."City"
• select * from ownername.City

No any of above query executed. We are getting these errors :

• Not capable of parsing FROM token: "ownername"."City"
• Could not execute query: The column prefix '"City"' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.
• Not capable of parsing SELECT token: ownername.City.CityName

How can we execute query using metamodel wih SYBASE database OR is there any other way to execute sybase queries?

Answer 1

Oracle (and Microsoft) use a schema logical construct that Sybase ASE does not. In SAP/Sybase ASE, all tables and columns in a database are in the same schema. It is possible to users to have their own objects in the database though, so there is the possibility of imitating some of the schema behavior using user ownership, but it would require an extra level of effort.

For Sybase the proper query syntax would be:

SELECT [ Col1, Col2 | * ]
  FROM [dbName].[ownerName.]TABLE

In your case

SELECT CityName
 FROM dbName.ownername.City

In Sybase ASE, it's typically best practice to have all objects owned by 'dbo', so in that case you can omit the owner from the query:

SELECT CityName
 FROM dbName..City

Full query syntax and information can be found in the Query Section of the Transact SQL Users Guide in the documentation.

Answer 2

The error messages you're getting are coming from MetaModel's query parser layer. It is searching for matching column and table names in the metadata of your database before it's even firing the query.

I notice that you're using namespace "org.eobjects.metamodel". You should upgrade to Apache MetaModel ("org.apache.metamodel") if possible since a lot has been improved in MetaModel since it's introduction into Apache. Including a lot of query parser improvements.