Generated "manual install" SQL is incorrect for PG.

[rmichael]

The SQL output contains "\'" (that's backslash-singlequote) to insert an apostrophe/singlequote in string values. In Postgres, the proper syntax is "''" (double singlequote).

If you give me a rough pointer to how/where this is done (ADODB?), I'll fix it and send a patch.


In the meantime, although it's just a warning, to workaround, save the SQL to a file (i.e. cut-and-paste from the post-install manual SQL textfield), and feed it through sed to psql:

bash$

Code: Select all

cat sql-commands | sed 's/\\'\''/'\'\''/g' | psql -h localhost -U ojs-test ojs-test

(Maybe this should be in the support forum?)

[asmecher]

Hi Richard,

Thanks -- if you could file a patch and a Bugzilla entry, that would be great. It's just a warning for now, but it looks as though PostgreSQL will eventually change its behavior, so best for us to follow the spec.

Regards,
Alec Smecher
Public Knowledge Project Team

[rmichael]

Hi Alec,

Ok, I've provided a proposed patch. Works for me on PG 8.2.11 (I checked, PG 7.4 uses the same '' style, so it should be fine), but that's not much testing!
I think it's best to punt and use ADODB's string escaping (it knows about PG's '' style already).

So in the end, it's just a one liner.

(BTW, I set the OJS version to 2.2.3 in the bug report as per our other discussion. Is that correct?)
http://pkp.sfu.ca/bugzilla/show_bug.cgi?id=3881

[asmecher]

Hi Richard,

Looks great, thanks -- I might actually remove or deprecate the quoteString function entirely, since once you shift the work over to PostgreSQL's qstr function, it's just wasted space on the call stack. If I do that, I'll post an updated patch on the bugzilla entry you've linked.

Regards,
Alec Smecher
Public Knowledge Project Team

[rmichael]

Ok, makes sense. BTW, it's not Postgres' qstr. qstr() is an ADODB function, the internals of which are a bit messy (and it can also do magic_quote escaping).

Cheers.

[asmecher]

Hi Richard,

Sorry, my typo; that's what I meant.

Regards,
Alec Smecher
Public Knowledge Project Team

[rmichael]

The first patch I sent was borked with spaces instead of tabs (silly cut and paste mistake). It won't apply, and will confuse new users since it will appear exactly the same, unless you "cat -A". (It stumped me for a good five minutes. Argh.)

If you're reading this thread, be sure to use the newer patch from the bug report.