On Wed, Feb 19, Wiktor Wodecki wrote:

On Tue, Feb 18, 2003 at 11:49:56PM +0100, Martin Edenhofer wrote:

...

...

SELECT count(*) FROM ticket as st, queue as sq, personal_queues as suq, ticket_state tsd, ticket_lock_type slt ...

the keyword "as" is not accepted by SAPDB and its right. IMHO is this keyword not allowed here. Is it working if you use the same statement without "as"?

SELECT bla FROM relation1 AS rel1 is a valid SQL statement since SQL92. I'm suprised SAPDB doesn't support this...it does, I found the aproperiate entry in die SAB DB LIBRARY (http://www.sapdb.org/7.4/htmhelp/e2/55683ab81fd846e10000000a11402f/frameset...., see Reference Manual -> Basic Elements -> Names -> Reference Name). Wolfgang, could you please check if this works for you and provide us a working SABDB SQL example? We could see if we can lay low and keep it generic or if we have to write specific modules for specific databases

Maybe that is fare to simple, but did you try "AS" in stead of "as" in the statement? Maybe SAPDB is stricter with this. Just an idea. take care Stefan Wintermeyer -- Stefan Wintermeyer You'll be getting more than just a lawyer, Mr. Simpson. You'll also be getting this exquisite faux pearl necklace, a $99 value, as our gift to you. (Lionel Hutz) "The Simpsons"

Hi, On Wed, 19 Feb 2003, Stefan Wintermeyer wrote:

...

...

...

the keyword "as" is not accepted by SAPDB and its right. IMHO is this keyword not allowed here. Is it working if you use the same statement without "as"?

SELECT bla FROM relation1 AS rel1 is a valid SQL statement since SQL92. I'm suprised SAPDB doesn't support this...it does, I found the aproperiate entry in die SAB DB LIBRARY (http://www.sapdb.org/7.4/htmhelp/e2/55683ab81fd846e10000000a11402f/frameset...., see Reference Manual -> Basic Elements -> Names -> Reference Name). Wolfgang, could you please check if this works for you and provide us a working SABDB SQL example? We could see if we can lay low and keep it generic or if we have to write specific modules for specific databases

Maybe that is fare to simple, but did you try "AS" in stead of "as" in the statement? Maybe SAPDB is stricter with this. Just an idea.

I've tried it: Message: [SAP AG][LIBSQLOD SO][SAP DB]Syntax error or access violation;-3008 POS(216) Invalid keyword or missing delimiter. (SQL-42000)(DBD: st_prepare/SQLPrepare err=-1), SQL: 'SELECT t.queue_id, a.ticket_id, a.id, ast.name, a.incoming_time, q.name, q.escalation_time, t.tn FROM article a, article_sender_type ast, queue q, ticket t, ticket_state tsd, ticket_lock_type slt, group_user AS ug WHERE tsd.id = t.ticket_state_id AND slt.id = t.ticket_lock_id AND ast.id = a.article_sender_type_id AND t.id = a.ticket_id AND q.id = t.queue_id AND q.group_id = ug.group_id AND tsd.name in ( 'open', 'new', 'pending auto close-', 'pending auto close+', 'pending reminder' ) AND slt.name in ( 'unlock', 'tmp_lock' ) AND ug.user_id = 1 AND ast.name = 'customer' AND t.ticket_answered != 1 AND q.escalation_time != 0 ORDER BY t.ticket_priority_id, a.incoming_time DESC' so it's the same for "as" and "AS" :-( It works if AS is avoided and this shouldn't break the sense of the statement? The reference manual is for SAPDB 7.4. I'm using 7.3.0.29. Perhaps this could be a problem, but I don't think so. AS is working in SELECT bla AS blub but not behind FROM or WHERE. I found some other problems after fixing those: - you use "SELECT count(*) AS count" but count is a reserved identifier within SAPDB - LIMIT [n] is not possible in SAPDB and is a proprietary PostgreSQL and MySQL addon IMHO CU, Wolfgang

Hi, On Wed, Feb 19, Sebastian Wormser wrote:

judging from the problems arising with only three databases (mysql, postgesql and sapdb), I think there is no way to provide a single statement that fulfills the requirements of all databases.

I can give you two more databases, where you will have the same problems: DB2 & Informix. Probably Oracle will also bark.

IMO there are two ways to solve the problem:

Provide for each statement different versions for each db and implement something like gettext for sql. Then you can define a db and the software automagically uses the correct sql syntax.

The other way would be to create data type definition schemes for each database. In the functions you would use no sql any more, but abstract commands that will be interpreted by some kind of sql parser, that would translate the abstract command into correct SQL for the specific database.

I am not quite sure wich of these solutions will be the best, maybe you have opinions about it or other ideas.

I would vote for a third options: Stay away from commands / options that are marked as a addon special to this database. SQL92 does not define very much, but the common base runs on all databases. The problem for special syntax should only arise when you create the database and the initial tables, which might need special keywords that are different in every database. This can be handled with different create scripts and should be no problem. The system operation however should be based on a common standard, which SQL92 is. Every database on the market today supports SQL92. greets, Daniel Rahn -- SuSE Linux AG | phone: +49 (0) 911 74053 0 Enterprise Support Services | fax: +49 (0) 911 74053 674 Deutschherrnstr. 15-19 | http://www.suse.de/en/support 90443 Nuernberg | GERMANY

Hello, On Wed, Feb 19, 2003 at 12:01:30PM +0100, Daniel Rahn wrote:

...

Provide for each statement different versions for each db and implement something like gettext for sql. Then you can define a db and the software automagically uses the correct sql syntax.

...

The other way would be to create data type definition schemes for each database. In the functions you would use no sql any more, but abstract commands that will be interpreted by some kind of sql parser, that would translate the abstract command into correct SQL for the specific database.

uhm, I really dislike the parser idea, this would mean a lot of work, however once implemented usage would be fairly easy. I'l take a look at google later on to see if I can find something which would save us some work. What would be a really ugly hack but would be fairly easy to implement is the gettext thingy. We just need to take care that we might need several SQL statements for some rdbms in some places. We have to take care of that, so a functional approach might also do...

The system operation however should be based on a common standard, which SQL92 is. Every database on the market today supports SQL92.

not a good idea, in my company there a "big plans" (grin) for OTRS and I don't want to send several SELECT just because if would also work on some curious other databases I'll never use :-) -- Regards, Wiktor Wodecki

On Wed, Feb 19, 2003 at 01:45:06PM +0100, Daniel Rahn wrote:

And what might be the big features that you need and that would end in multiple SQL statements instead of one? Don't get me wrong. I just want to know what it is that you need. Joins etc. are defined in the standard and are no problem, subselects aren't either. What do you need exactly?

I don't have anything specific in mind, I just know the limitations of MySQL regarding to subselects (okay, in the latest 4.1 alpha alpha they implemented them), this might bite in the future. I just want to get sure that we don't run into problems somewhere in the future. OTOH joins are very very ressources consuming (I've seen a large join by a webdesigner who killed the informix optimizer...). It isn't really an issue now, but I can imagine that I'd need to split up some of the bigger joins into multiple SELECT to enahnce performance on our system. -- Regards, Wiktor Wodecki

On Wed, Feb 19, 2003 at 01:40:49PM +0100, Wiktor Wodecki wrote:

What would be a really ugly hack but would be fairly easy to implement is the gettext thingy. We just need to take care that we might need several SQL statements for some rdbms in some places. We have to take care of that, so a functional approach might also do...

good morning guys, while looking for something useful I found this: http://ali.as/devel/sqlpm/sql.pm.html. It provides an abstract database layer which translates requests to sql itself. This would work, however I expect to see enormous performance lacks there. I'd still rather do a modular approach where the requested module provides functions for various requests. Of course those functions need to be documented... -- Regards, Wiktor Wodecki

Hi, On Tue, 18 Feb 2003, Martin Edenhofer wrote:

...

But now I get many errors because many statements are doing things like this:

SELECT count(*) FROM ticket as st, queue as sq, personal_queues as suq, ticket_state tsd, ticket_lock_type slt ...

the keyword "as" is not accepted by SAPDB and its right. IMHO is this keyword not allowed here.

Is it working if you use the same statement without "as"?

it seems so. The modified statement doesn't throw an error any longer. But I was not able to log in as root yet, because I didn't have the time to modify all statements, which are performed while login. So I cannot really decide if the system works. CU, Wolfgang

Hi, On Wed, 19 Feb 2003, Martin Edenhofer wrote:

...

it seems so. The modified statement doesn't throw an error any longer. But I was not able to log in as root yet, because I didn't have the time to modify all statements, which are performed while login. So I cannot really decide if the system works.

Hmm... Wolfgang, is it possible to get a shell account on a machine with SAPDB?

So I could play a little bit!

not so easy :-( My test-environment is based on SL 8.1 and is not accessible from the internet. All my public available systems are a little bit critical for such playing. But it should be not so difficult to install SAPDB on one of your systems. If you use 8.1 you can just use the distributed RPMs and I can provide my package for DBD:ODBC and dataload files for OTRS. CU, Wolfgang

Hi Martin, On Wed, 19 Feb 2003, Martin Edenhofer wrote:

Ok. ;-) I also need a short introduction into SAPDB (sql shell, create user, ...).

in addition (or replacement) to my personal mail here is a attachment containing a shell-script which should be able to initialize a SAPDB database instance for OTRS. It must be called as a user of the group "sapdb" NOT root (root will be refused by SAPDB). CU, Wolfgang