Got a dilemma. Bookmarks has several situations where
db_exec_plsql calls result in the creation of anonymous functions
to insert URLs and/or bookmarks. However, several bookmark
attributes (titles, keywords, etc.) can contain apostrophes/single
quotes (like "Ken's Home Page") for example. The anonymous
functions generated to handle these inserts have been failing. Am
I calling something incorrectly, or is there a problem there? An
example from the log:
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Debug: QD_LOGGER =
PLPGSQL: using anonymous function
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Debug: QD_LOGGER =
PRE-QD: the SQL is
begin
:1 := url.new (
url_id => :url_id,
url_title => :url_title,
host_url => :host_url,
complete_url => :complete_url,
meta_keywords => :meta_keywords,
meta_description => :meta_description,
creation_user => :viewed_user_id,
creation_ip => :creation_ip
);
end;
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Debug: QD_LOGGER = query
dbqd.bookmarks.www.bookmark-add-one-2
.url_add from
/var/web/openacs-4/packages/bookmarks/www/bookmark-add-one-2-postgresql.xql
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Debug: QD_LOGGER =
POST-QD: the SQL is
declare
v_url_id integer;
begin
v_url_id := url__new (
:url_id,
:url_title,
:host_url,
:complete_url,
:meta_keywords,
:meta_description,
:viewed_user_id,
:creation_ip,
null
);
return v_url_id;
end;
...
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Debug: QD_LOGGER =
PLPGSQL: converted:
declare
v_url_id integer;
begin
v_url_id := url__new (
:url_id,
:url_title,
:host_url,
:complete_url,
:meta_keywords,
:meta_description,
:viewed_user_id,
:creation_ip,
null
);
return v_url_id;
end;
to: select __exec_54_url_add ()
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Notice: Querying 'create
function __exec_54_url_add () returns
varchar as '
declare
v_url_id integer;
begin
v_url_id := url__new (
''2667'',
''Perl.com: The Source for Perl -- perl
development, perl conferences'',
''http://www.perl.com/'',
''http://www.perl.com'',
null,
''The official Perl home page, run by
O''Reilly. Contains documentation, news, and
links to a variety of resources, including the Open Source Perl
conference.'',
''2217'',
''192.168.69.13'',
null
);
return v_url_id;
end;
' language 'plpgsql';'
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Notice: dbinit:
sql(localhost::openacs-4): 'create function __
exec_54_url_add () returns varchar as '
declare
v_url_id integer;
begin
v_url_id := url__new (
''2667'',
''Perl.com: The Source for Perl -- perl
development, perl conferences'',
''http://www.perl.com/'',
''http://www.perl.com'',
null,
''The official Perl home page, run by
O''Reilly. Contains documentation, news, and links to a variety of
resources, including the Open Source Perl conference.'',
''2217'',
''192.168.69.13'',
null
);
return v_url_id;
end;
' language 'plpgsql''
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Notice: Querying 'select
__exec_54_url_add ();'
NOTICE: plpgsql: ERROR during compile of __exec_54_url_add near
line 5
[25/Sep/2001:22:48:04][14567.6150][-conn1-] Error: Ns_PgExec: result
status: 7 message: ERROR: unterminated string starting on line 15
Looks to me like the '' in O'Reilly is causing that error. It
only seems to happen when a single quote is embedded in the title,
description, or whatever.
I've kludged the issue for now by changing the db_exec_plsql
call to a db_dml call, and the block to a select (since I don't
need the return value). But this'll break the Oracle side of
things, I think (I've got to set up an Oracle instance on my box at
work to test for sure). I've tried several different scenarios
(db_exec_plsql with perform, etc.), and this is the only thing I've
gotten to work reliably on PG thus far. Any ideas? Have I
overlooked something obvious?
Thanks in advance...