Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Re: Perl, Oracle, and Xquery

by Moron (Curate)
on Mar 07, 2007 at 18:37 UTC ( #603669=note: print w/replies, xml ) Need Help??


in reply to Perl, Oracle, and Xquery

Maybe I am misunderstanding this, but are you trying to prepare more than one SQL statement in one go? You have to split it up and get rid of any delimiters (statement-terminating semicolons?) that might be in some way special to Xquery as opposed to obeying the pure SQL syntax.

-M

Free your mind

Replies are listed 'Best First'.
Re^2: Perl, Oracle, and Xquery
by jozxyqk (Initiate) on Mar 07, 2007 at 18:40 UTC
    Even if I just prepare the statement "xquery" on a line alone (which, at least in the SQLPlus program, is an indicator that xquery follows), I get the "no such SQL command" error.

    DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute) [for Statement "xquery"] at oracledbitest.pl line 62.

      "XQUERY" is a command to set a mode in the SQL interpreter SQL*Plus. So it is not send to the backend database engine.
      Once you have set XQUERY mode then the following statement ('.....') will be embedded in a SELECT XQUERY statement by SQL*Plus before it is send to the database engine.

      So if you would want to do something similar from within Perl you would need to enter in your prepare method a text like this:

      SELECT XQUERY('......' RETURNING CONTENT) FROM dual;
      NB: xquery SQL extensions are not supported by all databases
        Thank you, that was a much more helpful explanation than the one from Moron. I was not aware of this additional step.
      It ain't lying. xquery is not an SQL command. As I already explained, you have to extract each PURE SQL statement from your XQuery script before submitting them individually via DBI.

      -M

      Free your mind

        So I need to take the xquery and translate it back into SQL? There's no easier way to just parse xquery naturally in Perl? It doesn't have to be with DBI/DBD.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: note [id://603669]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (4)
As of 2022-12-04 21:13 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found

    Notices?