Cross-database support

[vinyip918]

Hello,

I have been trying to take advantage of the fact Mantis uses ADODB, by porting Mantis to use another database platform. The database I am testing on is Sybase iAnywhere ASA 9.0.1. I was able to get most of Mantis up-in-running by changing dbtype in config, except a query that is unsupported by ASA:

SELECT TOP 1 username FROM mantis_user_table WHERE username='test'

This is the query run when you try to create a user - it's a check to detect if an username exists. Mantis limits the query to 1 row, and so in ASA syntax it is the TOP 1 clause to limit it. It returns an error with a non-deterministic message. It means ASA is unsure of which row to get back. The query in MySQL syntax works though.

Therefore I think it's more of a bug too - we should change this query to add an ORDER BY username clause to guarantee that the database knows which row to return.

Let me know what you guys think.

Vincent

[William]

FWIW the "TOP 1" clause needs work to.

http://developer.mimer.com/validator/parser92/

[thraxisp]

Could you file this as a bug in the tracker at http://bugs.mantisbt.org/ ?

[paul]

SELECT TOP 1 username FROM mantis_user_table WHERE username='test'

This is the query run when you try to create a user - it's a check to detect if an username exists. Mantis limits the query to 1 row, and so in ASA syntax it is the TOP 1 clause to limit it. It returns an error with a non-deterministic message. It means ASA is unsure of which row to get back.

Er,
a) username has a unique index on it
b) it can only ever return 1 row

The "TOP 1" probably isn't technically required for mantis, but I really can't see why sybase would puke on this. MSSQL, which i believe used to be linked to sybase, doesn't fall over on this query. I really can't see why sybase would not like..

What database error does the database actually return if you run this query manually?

[cyanescent]

I have also been trying to get rc1 working with sybase ASE (no success yet). Although sybase used to be part of MSSQL it does not support the sql syntax "SELECT TOP 1" nor the "LIMIT BY" clauses typically found in many database queries. mssql and sybase syntax has moved apart considerably.

Usually you prepend the query with another query like "SET ROWCOUNT 1" and reset it after with "SET ROWCOUNT 0"

Also to note, sybase does not have a "DESCRIBE <table_name>" syntax either, so in the latest release I've twigged the database.api to do something like

"SELECT name as field FROM syscolumns WHERE id IN (SELECT id FROM sysobjects WHERE name = '<table_name>')

hope that helps someone else ;-)