Oracle SQL to VB ADO SQL

[Garratt]

(note: most of the users at work have only Excel 97 - so I'm stuck with that level of VBA as a tool).

At work we have a new system driven by a massive Oracle db. All day long reports are created. This has been setup to where a user runs an Oracle sql file in SQL*Plus. The reports spool into a text file. Once the reports are done, I have built several Excel VBA projects to read the text files, format (make pretty) the data, sort it and create summary information sheets (VBA pivot table commands, etc).

I want to sidestep the need for SQL*Plus and the .sql files by having a set of Excel VBA 'macros' that bring up a user form and pull data directly from the Oracle db. This would save a lot of steps, and speed things up as well as giving the user more flexibility. Imagine non-tech people having to be taught how to open SQL*Plus, run the right .sql file and then run a Excel macro after the spool file is finished. (question and problem nightmare).

So far I've read up on how to connect to the Oracle db using ADO. I've already made some VBA code and tested the Oracle db connection - it works fine.
(OpenConnString = "Provider=MSDAORA.1;Password=mypassowrd;Persist Security Info=True;User ID=bubba;Data Source=Mydb")

I'm going to hit a big learning curve tomorrow as the Oracle sql works a lot different than anything I've seen in VB.


-Does MS ADO support the column.table method? If so, is the formatting the same (or how?)
Select mt1.mycolumn, mt2.mycolumn3
From mytable mt1, mytable2 mt2
Where mt.mycoulmn1 = "bubba", and mt2.mycolumn3 "fred"

-Does the MS ADO support any of the Oracle commands or functions (like '=NUL')

-Considering all the funky formatting characters you have to put around an SQL statement in VB, and all the formatting crap I have to get rid of from the Oracle SQL (the spool stuff, like: RPAD, Name of Header, Length of Header) --- is there anything that can parse an Oracle SQL 'script' into VB format (or make it easier)?


With VB I'm use to making a 'virtual table' and pulling it's data out one record at a time. With Oracle sql I'm use to doing everything in one step (several select/from/where/union). My brain is hurting going from one style to the next and for all I know I can do it the same whay in VB.

Thanks for any help or any tips, or links to examples. I'll take anything.

[ahara]

Hello;

I am confused a bit:

Your quote:
I'm going to hit a big learning curve tomorrow as the Oracle sql works a lot different than anything I've seen in VB.

What do you mean exactly? Are you new to SQL for Oracle? Or is it just ADO that is new? (both?) at any rate, I think I can probably help as I am developing strictly in VB right now with Oracle 8i. I suspect you are unclear a bit on ADO from your post...it is simply an interface for developers to make easy access to oledb commands which interfaces with whichever driver you choose and connects to the db. The whole idea of this architecture is to provide components independent of the technologies they serve, and ADO does a pretty good job by allowing you to choose any driver, set a variety of properties, and pass sql that will be understood by the parser specific to that db. I use ADO command objects for example all the time to pass any type of sql string that Oracle understands, and these same command objects to pass strings to Access. But if I don't pass the right string of a syntax the db understands, the db throws an exception (which is communicated of course through ADO). So I guess my point is you will really only have to worry about passing sql that Oracle knows and a few objects and their properties supported by ADO. I would be happy to help with any of this - if you need code samples, just specify what you need. Cheers

[Garratt]

Yeah, I'm all over the place and I don't have any examples from work to go by right now (sorry).

I'm somewhat familiar with ADO. I've done a lot with it in terms of pulling selective data from a Access db's.

The Oracle .sql files we have basically paint a big picture in one shot that gets spooled. But, what I'm use to in ADO are recordsets that you "paint" with SQL strings and then loop through one record at a time, usually one table at a time.

	Set RS = Conn.Execute( "SELECT * FROM MyTable" )
	do while not rs.eof
	   msgbox RS.Value
	   rs.movenext
	loop

With everything I've done before, I never looked into another method as it made it easy to dump records into specific cells and control the formatting. With the data we use at work Excel is the best tool for working with it, so that's where my brain is most of the time.

Here's one straight question:

I've never seen "TABLE.COLUMN" used in vb ADO. Does this work in vb ADO (or how would you do it?)?

SELECT MT.Mycolumn, MT.Mycolumn2, MT3.Mycolumn18
 FROM MyTable MT, MyTable2 MT2, MyTable3 MT3
WHERE MT2.Mycolumn3 = "bob"

Another question would be about the extra 'sql commands' that Oracle SQL has. Is any of that available with VB ADO?


Any complicated VB SQL strings I'd love to see. Most of what I find when looking around are very simple examples.

Thanks!

[ahara]

Hey Garrat;

ADO is not specific to VB. As I said, I can run any query I wish. Here is an example:

dim rs as New ADODB.Recordset
dim cmdCommand as New ADODB.Command
dim sql as String

set cmdCommand.ActiveConnection = [your connection object]
cmdCommand.CommandType = adCmdText

sql = "select a.mech_name from mechanics a, labour b, workorders c where a.mech_id = b.mech_id and b.labour_id = c.labour_id and c.workorder_no=113"

cmdCommand.CommandText = sql
set rs = cmdCommand.Execute

do while not rs.EOF

     'process stuff here (populate Excel cells)

    rs.MoveNext
loop

The above uses both a recordset object and a command object to execute the sql. As you can see, you can use any sql that Oracle understands (and it certainly knows TABLENAME.FIELDNAME, or in my example the use of table aliases a, b, and c)

the other day, I had to deal with a messed up field defined as varchar2 that holds the year of automobiles. The problem is, some of them are 2 digit, some are 4. I want them all to be four so I can sort them. So I used the above code, but passed this string:

sql = "select decode(substr(vehicle_year,1,1),9, '19'||vehicle_year,0,'20'||vehicle_year),vehicle_year) from Auto_Stock"

the above uses the decode and substring functions to check the first digit of the field value. If it is 0, append '20', if it is 9, append '19', otherwise, leave it as is (cause its four digits and fine)

SQL for Oracle actually TOTALLY ROCKS!!!!!! It is very powerful - there are lots of functions and ways to pull data. You can create Views which are snapshots of data, and then select what you need from the View. In fact, I don't think there is anything I can do in VB directly with data that I cannot do with Oracle's sql. I'm gonna see if I can find a decent tutorial and post back - you are more than welcome of course to ask more questions. Hope that is not too much in one shot!! easy

[Garratt]

That helps a lot, Thanks!

You're saying that ADO passes the SQL string 'as is'? If that's true it would mean I could pretty much straight copy most of a .sql file 'as is' without thinking of ways to replicate what the Oracle SQL commands are doing (sweet).

I'll have some massive sql strings though....
I can't wait to go back to work and play.

[ahara]

Oh yeah, absolutely....these .sql files - they contain only one statement each? I have had little experience with them - I mostly used them to run batches of commands. Although VB could parse out a bunch of commands if needed too I suppose. As far as the large size, I don't know the exact limit but if it is not the same as what Oracle provides, I'm sure you could alter an ADO property to accomodate. I do know that with inserts, field values have a limit of 2000 characters, although there is an ADO way around it I believe. At any rate, you go have fun - and if there are any q's, you know where to find us! cheers

[Garratt]

I was meaning to send myself one of the .sql files home. I'll remember to do that tomorrow. I got slammed most of the day.

I was hoping for one of those files someone could take a crack at converting one to ADO sql strings so I had an example to learn from.

The files are just more complicated than I'm use to as they do so many things at one time. Plus, the Oracle commands are there too.

I'll stick one up tomorrow. Thanks again.