HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.

Discussion in 'ASP General' started by ATS, Apr 11, 2007.

  1. ATS

    ATS Guest

    HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.

    Please help,

    I'm trying to write an ASP page to use ADO to run a long query against an
    Oracle 10g database, to create tables, if they do not already exist. In terms
    of ASP/ADO, that would be fine in a SQL Server Sense by a simply
    ASP/Server-Side JavaScript as such:

    var cnTemp = Server.CreateObject("ADODB.Connection");
    cnTemp.Open("~~~~String for DSN~~~~");
    rsTemp = cnTemp.Execute("~~~~ LONG Query ~~~~");

    while (rsTemp.EOF != true)
    {
    ~~~~~ Get data ~~~~~~
    rsTemp.MoveNext();
    }

    In terms of the query for SQL Server it would look something like this:

    if exists(~~~ MY TABLE)
    begin
    select 'TABLE ALREADY EXISTS' as 'RET'
    end
    else
    begin
    create table ~~~~~~
    select 'TABLE CREATED' as 'RET'
    end

    From this, the Connection object in ADO works GREAT. The returned Recordset
    object will have a single row from the multiple statements in that query
    returned as the single column called 'RET'.

    I need to do the same in Oracle 10g. I need to be able to run many
    statements inside a single ORacle query, and have it return Recordset
    compatible data, with a single column I define, like the 'RET'. But I do not
    know how to do this. As best I can determine, in ORacle I would use something
    like this:

    SET SERVEROUTPUT ON;
    declare
    if not exists (~~~~ My Table)
    then
    create table ~~~~~;
    DBMS_OUTPUT.PUT_LINE('TABLE CREATED' ;
    else
    DBMS_OUTPUT.PUT_LINE('TABLE ALREADY EXISTS' ;
    end if;
    end;

    The first problem is that the PUT_LINE command does not let me have an
    option to name the column as I desire. The 2nd problem is that to ORacle via
    the ADO Connection object, I get an error as such on the "SET SERVEROUTOUT
    ON" statement as such:

    undefined(i = 0) - [Microsoft][ODBC driver for Oracle][Oracle]ORA-06550:
    line 3, column 5: PL/SQL: ORA-00922: missing or invalid option ORA-06550:
    line 3, column 1: PL/SQL: SQL Statement ignored

    If I run the query in PL/SQL, it works, (albeit, the output returned does
    not let me name the column for the PUT_LINE as I desire.

    So, with all that said, how can I do this?

    The keys are:

    #1 A single LARGE query with multiple statements.
    #2 Returns rows of data that ADO-Recordset can access.
    #3 Lets one set the column names as desired.
    #4 Lets me run complicated IF-THEN statements.
    #5 Lets me create tables, indexes, execute grants, and create users.
    #6 Same coding for SQL Server with ASP/ADO works for Oracle. IE, SOMETHING
    has to happen inside the Oracle query to make it work. AND/OR because the
    database is Oracle, we execute some simple QUERIES from an ADO-Connection
    object ahead of time.
    #7 Under no circumstance can the ADO-Command object be used.
     
    ATS, Apr 11, 2007
    #1
    1. Advertising

  2. Why don't you use a stored procedure? You're making too many trips to the
    database using straight ADO queries.

    ATS wrote:
    > HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g.
    >
    > Please help,
    >
    > I'm trying to write an ASP page to use ADO to run a long query
    > against an Oracle 10g database, to create tables, if they do not
    > already exist. In terms of ASP/ADO, that would be fine in a SQL
    > Server Sense by a simply ASP/Server-Side JavaScript as such:
    >
    > var cnTemp = Server.CreateObject("ADODB.Connection");
    > cnTemp.Open("~~~~String for DSN~~~~");
    > rsTemp = cnTemp.Execute("~~~~ LONG Query ~~~~");
    >
    > while (rsTemp.EOF != true)
    > {
    > ~~~~~ Get data ~~~~~~
    > rsTemp.MoveNext();
    > }
    >
    > In terms of the query for SQL Server it would look something like
    > this:
    >
    > if exists(~~~ MY TABLE)
    > begin
    > select 'TABLE ALREADY EXISTS' as 'RET'
    > end
    > else
    > begin
    > create table ~~~~~~
    > select 'TABLE CREATED' as 'RET'
    > end
    >
    > From this, the Connection object in ADO works GREAT. The returned
    > Recordset object will have a single row from the multiple statements
    > in that query returned as the single column called 'RET'.
    >
    > I need to do the same in Oracle 10g. I need to be able to run many
    > statements inside a single ORacle query, and have it return Recordset
    > compatible data, with a single column I define, like the 'RET'. But I
    > do not know how to do this. As best I can determine, in ORacle I
    > would use something like this:
    >
    > SET SERVEROUTPUT ON;
    > declare
    > if not exists (~~~~ My Table)
    > then
    > create table ~~~~~;
    > DBMS_OUTPUT.PUT_LINE('TABLE CREATED' ;
    > else
    > DBMS_OUTPUT.PUT_LINE('TABLE ALREADY EXISTS' ;
    > end if;
    > end;
    >
    > The first problem is that the PUT_LINE command does not let me have an
    > option to name the column as I desire. The 2nd problem is that to
    > ORacle via the ADO Connection object, I get an error as such on the
    > "SET SERVEROUTOUT ON" statement as such:
    >
    > undefined(i = 0) - [Microsoft][ODBC driver for
    > Oracle][Oracle]ORA-06550: line 3, column 5: PL/SQL: ORA-00922:
    > missing or invalid option ORA-06550: line 3, column 1: PL/SQL: SQL
    > Statement ignored
    >
    > If I run the query in PL/SQL, it works, (albeit, the output returned
    > does not let me name the column for the PUT_LINE as I desire.
    >
    > So, with all that said, how can I do this?
    >
    > The keys are:
    >
    > #1 A single LARGE query with multiple statements.
    > #2 Returns rows of data that ADO-Recordset can access.
    > #3 Lets one set the column names as desired.
    > #4 Lets me run complicated IF-THEN statements.
    > #5 Lets me create tables, indexes, execute grants, and create users.
    > #6 Same coding for SQL Server with ASP/ADO works for Oracle. IE,
    > SOMETHING has to happen inside the Oracle query to make it work.
    > AND/OR because the database is Oracle, we execute some simple QUERIES
    > from an ADO-Connection object ahead of time.
    > #7 Under no circumstance can the ADO-Command object be used.


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 11, 2007
    #2
    1. Advertising

  3. ATS wrote:
    > #7 Under no circumstance can the ADO-Command object be used.

    This is a ridiculous requirement. ALL queries run by ADO are executed via a
    Command object: whether you explicitly create it yourself or ADO creates it
    implicitly.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 11, 2007
    #3
  4. ATS

    ATS Guest

    Re: HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10

    Thanks for the reply.
    Sorry if #7 seems ridiculous, but it is absolutely necessary, or we will
    have to do a major rewrite. Not that anything related to Oracle requires
    anything less, but surely others have used ADODB.Connection.Execute to build
    schemas and launch complicated queries in Oracle.

    Basically, by using the ADODB.Connection object, we can use its "Execute"
    method to run any query against SQL Server, including complex schema building
    queries which contain many statements, and return rowsets with columns we
    define on the fly.

    Literally we invoke the ADO like this:

    var cnTemp = Server.CreateObject("ADODB.Connection");
    var csDSN = "~~~ DSN STRING ~~~";
    cnTemp.Open(csDSN);
    var rsTemp = cnTemp.Execute("~~~ BIG COMPLEX QUERY ~~~~");
    var csRET;

    if (!rsTemp.EOF)
    {
    csRET = rsTemp("RET");
    }
    else
    {
    csRET = "ERR";
    }

    While the query inside may be big and/or complex, its output is always a
    simple single rowset, for which this code should always work. But with Oracle
    (10g), it is just kicking our buts. Surely someone at Microsoft has used ADO
    to launch big/complex Oracle queries to build schema's, and get rowsets back?

    To elaborate the kind of query we need, here is a SQL Server query example:

    begin transaction
    set NOCOUNT on
    create table #TX(TX int null)
    -- Create many tables....
    exec sp_executesql '~~ complex and dynamically created queries ~~~'

    if (~~ tables do not exist ~~~)
    begin
    -- create table...
    end
    commit transaction
    select 'RET=OK' as 'RET'

    With Oracle, we need to do similar things, but the disconnect is that we do
    not know how to get ADODB.Connection to work with Oracle for the Oracle
    equivalent DECLARE. As best we see it would be something like this:

    -- SET SERVEROUTPUT ON ---- This is supposedly how to get ORacle to send
    output back using PUT_LINE, but ADODB.Connection.Execute to ADODB.Recordset
    does not like it. We always get ORA-00922 error.
    DECLARE
    iTemp INTEGER;
    BEGIN
    execute immediate 'create table #TX(TX int null)';
    -- Create many tables....
    execute immediate '~~ complex and dynamically created queries ~~~'

    select count(*) into iTemp from DBA_USERS where USERNAME = '~~some
    table~~~';

    if (iTemp = 0)
    begin
    -- create table...
    commit;
    end
    -- How do we do the equiv of "select 'RET=OK' as 'RET'", as this will not
    work in Oracle DECLARE so that ADODB.Connection.Execute to ADODB.Recordset
    will get it?
    -- Supposedly DBMS_OUTPUT.PUT_LINE('RET=OK'); should do this, but ADODB
    does not seem to catch it. Also, it does not let us name the column as we
    desire.
    END;

    ------------------------
    So, basically, this stems to a problem where we have code using
    ADODB.Connection.Execute, and LOTS of it, and we need a way to get it to
    launch similar complex queries to Oracle. Or, we are faced with rewrites. By
    the way, we are using the Microsoft Oracle ODBC Driver for our connection:
    DRIVER={Microsoft ODBC for Oracle};. Perhaps a change to a better driver will
    help us bridge this?
     
    ATS, Apr 12, 2007
    #4
  5. Re: HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10

    ATS wrote:
    > Thanks for the reply.
    > Sorry if #7 seems ridiculous, but it is absolutely necessary, or we
    > will have to do a major rewrite. Not that anything related to Oracle
    > requires anything less, but surely others have used
    > ADODB.Connection.Execute to build schemas and launch complicated
    > queries in Oracle.
    >
    > Basically, by using the ADODB.Connection object, we can use its
    > "Execute" method to run any query against SQL Server, including
    > complex schema building queries which contain many statements, and
    > return rowsets with columns we define on the fly.


    Again, when you do this, a <GASP> Command object gets created behind the
    scenes to execute the commandtext.

    <snip>
    Again: why not encapsulate all this in a stored procedure?


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 12, 2007
    #5
  6. ATS

    ATS Guest

    Re: HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10

    Thanks for the reply,

    Sadly, we can not use stored procedures as we are trying to use this kind of
    logic to create the stored procedures, tables, users, triggers, and then some
    in the 1st place. Basically, the idea is to let the database's native SQL do
    the work of dynamically creating our schema as need be to get our product
    started with it. But the key for this approach to work is to be able to get
    feedback returned from the query that builds the schema after it analyzes the
    database. Put another way, we can not arbitrarily create anything in any
    database, even for expediting the creation of our schema, without checking
    out the database 1st.

    Sadly, we are kind of giving up on this for Oracle. It appears Oracle can
    not work with this approach. It appears that one would have to create
    something 1st and/or manually code in TONS of manualy queries to get the
    euiqvalent of what every other database product, including DB2, SQL Server,
    and now MySQL, that Oracle can not do.

    By the way. The "litness" test for this is this simple ADO setup:

    var cnTemp = Server.CreateObject("ADODB.Connection");
    cnTemp.Open("~~~~String for DSN~~~~");
    rsTemp = cnTemp.Execute("~~~~ LONG Schema Buillding Query ~~~~");

    while (rsTemp.EOF != true)
    {
    ~~~~~ Get data ~~~~~~
    rsTemp.MoveNext();
    }

    From this approach, we were hoping that someone with ADO had found a way to
    get Oracle to work with it, but nothing seems to let ADO get result sets back
    from Oracle. Including if we try "SET SERVEROUTPUT ON", which always fails if
    executed from ADODB.Connection. And without SET SERVEROUTPUT ON, the PUT_LINE
    commands will no work. But even if the PUT_LINE did work, the Oracle DECLARE
    statement do not seem to have a way to return rows as desired, with columns
    defined as we need. Example:

    set serveroutput on; <=== This fails to ADODB.Connection.Execute every time.
    declare
    iTemp INTEGER;
    begin
    DBMS_OUTPUT.PUT_LINE('RET=OK');
    -- Without serveroutput on, this does nothing.
    -- Also, there is no way to specify the columns name.
    -- Also, there appears ot be no way to run a select statement by itself to
    -- return data, without creating something like a stored procedure 1st.
    end;
     
    ATS, Apr 13, 2007
    #6
  7. Re: HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10

    ATS wrote:
    > Thanks for the reply,
    >

    Sorry I could not help. I've never worked with Oracle.
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows [MVP], Apr 14, 2007
    #7
  8. ATS

    paab Guest

    Re: HOWTO Run multiple SQL statements from ASP/ADO to...

    SET SERVEROUTPUT ON option is applicateble only as commnad of Oracle Sqlplus tool.
     
    paab, Apr 20, 2007
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Replies:
    0
    Views:
    1,335
  2. sloan

    Access Oracle 10g using ASP.NET

    sloan, Mar 10, 2006, in forum: ASP .Net
    Replies:
    1
    Views:
    2,288
  3. Replies:
    0
    Views:
    764
  4. Replies:
    0
    Views:
    371
  5. Bill McCarthy
    Replies:
    3
    Views:
    3,541
    Alexey Smirnov
    Jan 23, 2009
Loading...

Share This Page