Re: Multiple SQL statement problem

evgenyv <evgenyv_at_yahoo.com> wrote in message news:<1716024.1029831128_at_dbforums.com>...
> Hi!
> A can't run the multiple sql statement with Oracle DB. I'm using ADO
> 2.7.
> What is wrong in follow example?
>
> 'm_sConnStr = "Provider=MSDAORA;Password=aaa;User ID=user;Data
> Source=ds1"
> m_sConnStr = "Provider=OraOLEDB.Oracle;Password=aaa;User ID=user;Data
> Source=ds1"
> m_Conn.Open m_sConnStr
> m_Conn.CursorLocation = adUseClient
> Dim str1 As String
> Dim str2 As String
> Dim str3 As String
>
> str1 = "select * from test1 where id = 'www' ;" & vbCrLf
> str2 = "select * from test2 where id = 'eee';"
> str3 = str1 + str2
> Set m_Rs = m_Conn.Execute( str3 ) - throw error "Invalid character" (
> but this statement does run properly from Oracle SQL+ )
> m_Rs.Close
>
> BTW in MSSQL does work properly.
>
> Thanks in advance

> BTW in MSSQL does work properly.

You are working with Oracle and not MSSQL. They are two separate products.
If I understand you correctly... you have two separate sql terminated queries concatenated as one and you want to know why it throws an exception?

I would be curious to see how it works in MSSQL. Does MSSQL send two separate resultsets back or does it merge the resultset and send back only one? If it does work... What would be the benefit of concatenating two separate sql terminated queries? How do you handle multiple resultsets in a single e.g. frmMain scope?... I am just curious since I have not worked with MSSQL.

In Oracle, every query that you send to the database is treated and parsed as one query. If you concatenate two queries with a sql terminator ";"... then Oracle will not be able to pasre it and will throw an invalid character error the moment it sees ";". This IMO, is a perfectly good logic. If you want to combine the resultset of two separate queries... then you can use UNION, UNION ALL, JOIN etc. which are part of the SQL language commands.

In sqlplus, it can *only* work if you have *EOL* terminated two queries in a script. If you do this,
SQL> select sysdate from dual;select 1 from dual; select sysdate from dual; select 1 from dual

                        *

However, in a script, sqlplus runs the first query and *only* after the first one is returned that it processes the second one.

//Rauf Sarwar Received on Tue Aug 20 2002 - 19:36:02 CEST