Re: [Devel] postgres problem with mysql proprietary SQL queries

[Ingo Wolfsberger]

Hi

Postgres is a real db ;)

It should be possible to "create" the needed functions.
see: http://www.openrdf.org/issues/browse/SES-112

Not tested:

CONCAT:
CREATE FUNCTION "concat" (text,text) RETURNS text AS 
    'SELECT $1 || $2;' LANGUAGE 'sql';

RAND:
CREATE FUNCTION "rand" () RETURNS dp AS 
 'SELECT random();' LANGUAGE 'sql';

Please test and tell me if it works.

bye,
   Ingo
> --- Ursprüngliche Nachricht ---
> Von: Klaus Darilion <klaus.mailinglists at pernau.at>
> An: Bogdan-Andrei Iancu <bogdan at voice-system.ro>
> Kopie: devel at openser.org
> Betreff: Re: [Devel] postgres problem with mysql proprietary SQL queries
> Datum: Mon, 01 Aug 2005 12:32:01 +0200
> 
> Bogdan-Andrei Iancu wrote:
> > Hi Klaus,
> > 
> > IMHO, the best way to go is to align all DB-dependent module to the DB 
> > interface and to avoid using raw queries which depends of the DB driver.
> > 
> > first option should be to try to make lcr to use only predefined DB 
> > interface (if possible)- not sure if the mysql proprietary are used only
> > for simplicity or there is no other way.
> 
> Some parts are because of simplicity (CONCAT) and be solved in C.
> For other parts I do not know a solution in C (RAND).
> 
> klaus
> > second option, if first fails, will your option nr. 1. I really do not 
> > like it very much since the module will become aware of the underlaying 
> > DB driver, which suppose to be transparent :(.
> > 
> > regards,
> > Bogdan
> > 
> > Klaus Darilion wrote:
> > 
> >> Hi all!
> >>
> >> I tried to use the lcr module with postgres and found the following 
> >> problem, which is a generic problem.
> >>
> >> The lcr module uses SQL queries, which are mysql proprietary. (e.g. 
> >> CONCAT, RAND). These queries must be adopted to fit postgresql.
> >>
> >> There are several options how to do this:
> >> 1. The module should parse the db_url, and according to the service 
> >> (mysql, postgres, ...) the proper SQL query should be used.
> >>
> >> 2. a compilation option
> >>
> >> 3. ???
> >>
> >> Please give us your comments and other ideas to solve this problem.
> >>
> >> regards,
> >> klaus
> >>
> >>
> >>
> >> -------- Original Message --------
> >> Hi Juha!
> >>
> >> I do not have commited the lcr module as I found an open issues: The
> >> SELECT queries are not compatibel with postgresql. postgres has other
> >> syntax for CONCAT and RAND:
> >>
> >> CONCAT --> ||
> >> RAND() --> RANDOM()
> >>
> >> Thus, the lcr module would have to query the db_url if it is postgres
> or
> >> mysql and use the proper SELECT query.
> >>
> >> Attached is the lcr_mod.c patched with URI scheme and transport, and
> >> also the SELECT was changed to fit postgres. Maybe you can take a look
> >> at this problem.
> >>
> >> btw: openser crashes with the original mysql SELECT sent to postgres. I
> >> think this is a bug in the postgres module.
> >>
> >> regards,
> >> klaus
l

-- 
GMX DSL = Maximale Leistung zum minimalen Preis!
2000 MB nur 2,99, Flatrate ab 4,99 Euro/Monat: http://www.gmx.net/de/go/dsl