473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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.
Apr 11 '07 #1
7 20261
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"
Apr 11 '07 #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"
Apr 11 '07 #3
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?

Apr 12 '07 #4
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 <GASPCommand 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"
Apr 12 '07 #5
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;
Apr 13 '07 #6
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"
Apr 14 '07 #7
SET SERVEROUTPUT ON option is applicateble only as commnad of Oracle Sqlplus tool.
Apr 20 '07 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: MrTrix | last post by:
Hello: I'm having a problem formulating the code to execute a multiple line command. I'm trying to execute something like: set rowcount 100000 declare @rowct int select @rowct = 1 while...
2
by: Richard Adams | last post by:
Is it possible to execute more than one statement in SQL via MDAC ODBC? I have a fairly complex select I wanted to create a view with, but trying to send it all as one string with terminators ';'...
4
by: Raj Kotaru | last post by:
Hi, In sqlplus, I can insert a single row using: insert into employee (name, salary, hiredate) values ('xyz', '86378', sysdate); Is there a modification of the insert command that will...
4
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from...
11
by: Randy Harris | last post by:
I have been using "IN" to query tables in Oracle from MS Access very successfully. Select Field FROM MyTable IN [ODBC...etc Works great if there is only one table involved. Anyone know how I...
4
by: Jo | last post by:
Hi, In SQL Server I can batch many insert statements ie : Create one string that looks thus : szSQL = "insert into xxx (..., ..., ...) values (..., ..., ...)" szSQL += "insert into xxx (...,...
16
by: Randy Harris | last post by:
I was inspired by the recent discussion of returning multiple recordsets to ADO from a stored procedure. (Amazed is probably more accurate). I asked about how to accomplish same with Oracle and...
6
by: Ian Boyd | last post by:
Every time during development we had to make table changes, we use Control Center. Most of the time, Control Center fails. If you try to "undo all", it doesn't, and you end up losing your identity...
7
by: AboutJAV | last post by:
Hi, I was thinking of using MSMQ to handling multiple simultaneous database request to update or insert records to table. There could be hundreds of database existing connections trying to...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.