Forum OpenACS Q&A: Re: Problem Installing The Jabber Package

Posted by Bjoern Kiesbye on

I have uploaded the file again hope it will work this time.

As well I have uploaded a tarball that includes a Jabber package and a nsjabber module which both support postgres. It's in early testing state, so if you like you can test it.

I ran into the problem with the db_exec_plsql Tom mentioned above, changing them to db_dml statements which execute a begin ... end; Block would work for oracle, but postgres wont execute a Select statement inside a dml call. And I don't think it's possible to execute pgpsql code directly you always have to compile a function first and then trigger it by calling it with a Select statement.

An approach would be to change all plsql procedures to functions (oracle) which return dummy values, so they can be called inside Select statements as well (select func1 from dual). Does any one know how much more time a db_select would take compared to a db_exec_plsql?

Posted by Tom Ayles on
Hi Bjoern,

I've downloaded your new version, and it works fine - thx! Regarding the pl/sql problem, whilst PostgreSQL requires that the functions are called using a SELECT statement, Oracle won't allow you to invoke a function that performs DML from within a SELECT (ORA-14551), which pretty much leaves us between a rock and a hard place as far as a simple solution to dual database compatibility is concerned.

Anyway, I've decided to go ahead with using the various components of the package for a project I'm working on, and will probably be doing quite a bit of work with it. For now, my priority is to get the exact functionality I need up and running on Oracle, later on I'll figure out how best to deal with the PostgreSQL issue. I might end up just having a version of nsjabber for each DB, but I'd prefer to end up with a single set of code that once compiled would run on either.

Thanks again for making this update available,

Posted by martin hebrank on
Why don't you just put a check in the C code to change the SQL for each DB?

I added the extern char * DB_DRIVER, then in tcl_jclient.c:

DB_DRIVER = strdup ( ((cache  = Ns_ConfigGetValue("ns/db/pool/jabber" , "driver"))?cache:"postgres") );

Then, where SQL is used:
if (strcmp(DB_DRIVER,"postgres") == 0) {
    snprintf(query, 4096, "select jb_update_status ('%s' , %s' , '%s' , '%s');", state, jb->resrc, jb->jid , jb->service);
  else {
    snprintf(query, 4096, "begin jb_update_status ('%s' , %s' , '%s' , '%s');  end;", state, jb->resrc, jb->jid , jb->service);

Works for me. . .

Of course, this is all stuff I did because when I first started looking at it I couldn't find any references to the jabber package even being actively developed. . .

** Martin

Posted by martin hebrank on
I downloaded the postgres packages. . .didn't work right away, I'm looking into it now, but noticably, the -drop sql files are missing, so when it breaks it's not easy to clean out the db and start again. I'm fixing that stuff now.