Re: [Slashcode-development] RFC: SQL Placeholders

[Eric D. <eri...@ja...>]

Why not use VARCHAR2 in Oracle? From what I remember of Oracle, that is a much
better variable than VARCHAR

Stephen Clouse wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> As of late I have been working on getting Slash2 to work with Oracle (going
> ahead on my own since there wasn't much interest (read: none) a while back when
> I asked).  So far it's been quite successful -- the initial database creates and
> loads without error.  Now it's down to messing with the DB interface
> itself.  The biggest show-stopper so far is while loading the templates in:
>
> DBD::Oracle::db do failed: ORA-01704: string literal too long
>
> because it's trying to embed the entire string into the SQL statement, which you
> can't do in Oracle if it's over 4000 bytes (the boundary between VARCHAR and
> LOB).  The variable has to be explicitly typed as a LOB when preparing the
> statement, and that requires the use of DBI placeholders for bind
> values.  However, there isn't a single placeholder in the current code.  I found
> this rather surprising.
>
> Aside from the obvious technical issue above, there's a performance one as
> well.  Oracle isn't able to cache any of the SQL with the variables embedded
> inside, so performance will suffer when I finally do manage to get it
> working.  This is actually an issue with MySQL as well, since Slash2 currently
> makes quite a bit of use of prepare_cached, a use that's practically worthless
> (and a large waste of memory) without using placeholders.
>
> My proposal is to get the SQL internals using placeholders and bind values to
> facilitate current and future porting, as well as making the current MySQL core
> substantially cleaner and more memory-efficient.  I've spent all day looking
> over the Slash::DB::Utility interface and the sql* methods and I've devised an
> extension to the current interface that would make the changes to the current
> code minimal (an additional argument to sql* calls with embedded variables, and
> some fairly minor recoding where such calls are).  The conversion would almost
> be painless.
>
> Even so, this is a pretty radical change to the code, and I wouldn't go ahead
> with it without some kind of "blessing" from the core developers and ensuring
> that this is a direction they want to head.  However, my main goal is seeing
> this thing run on Oracle, and I don't see any way of getting it there without
> A) these changes to allow Oracle more seamless plugging into the existing code,
> or B) a substantial rewrite Slash::DB::* for Oracle, which would almost be a
> code fork since the interface would have to be radically different.  That's the
> last place I want to head.
>
> There are other porting issues I've come across, but they're pathetic compared
> to this.  Comments are welcome.
>
> - --
> Stephen Clouse <the...@wa...>
> warpcore.org Founder, Chief Megalomaniac, and Evil Overlord
>
> -----BEGIN PGP SIGNATURE-----
> Version: PGP 6.5.8
>
> iQA+AwUBOs/Lt91EXk7JbKbMEQKX8gCYsdgaJOPlKxDvE+XB3g7EBLielACeLYzY
> vleJuQHrdCaBJn4gtYttfow=
> =y90I
> -----END PGP SIGNATURE-----
>
> _______________________________________________
> Slashcode-development mailing list
> Sla...@li...
> http://lists.sourceforge.net/lists/listinfo/slashcode-development

--
Back up my hard disk? I can't find the reverse switch!

Eric Dannewitz - Adventurer, saxophonist, good-timer (crook? quite possibly),
clarinetist, manic self-publicist, part-time flautist(flutist?), macintosher, and
often thought to be completely out to lunch. http://www.jazz-sax.com