Re: Using Visual Basic and ODBC to access Oracle?

In article <1994Jan12.113144_at_dallas.tymnet.bt.co.uk>, dwatts_at_dallas.tymnet.bt.co.uk (Dave Watts) writes:
|>
|> I am currently involved in the developement of a client/server system.
|> I'm interested in getting any ideas or 'things to avoid doing' from
|> anyone who has related experience...
|>
|> The architecture of the solution is:
|> ________________
|> | PC Client |
|> |(Visual Basic)|
|> |--------------| _______________
|> | MS ODBC | | |
|> |--------------| | Oracle 7 |
|> | SQL*Net | | (unix) |
|> ---------------- ---------------
|> |_____________________________________|
|>
|> (This architecture has largely been determined by the existing
|> environment into which this app. must fit. The 'Net bandwidth:
|> X-server vs PC client/server' debate has raised a few questions...)
|>
|> However, my main questions are:
|>
|> - To 'talk' to Oracle from VB would you recommend using the 'VB
|> methods' (Oracle ODBC), or would you embed SQL.

I don't think that embedded SQL is an option.

|>
|> - Is there any documentation for the Oracle ODBC driver? The MS
|> documentation is a little sketchy in this area.... I'd like to
|> know what features of Oracle it can (and can't) support etc...
|>

Err...There is the readme file with SQL*Net for Windows... Err.. Sketchy? Yup. Generally, the drivers support everything that ODBC supports. I.e. no stored procedures etc. Triggers and so on will work however, since these need no user code. To be honest, I've never tried using stored procedures with the DB_SQLPASSTHROUGH option for a query, and this might work - give it a try. (it's some months since I worked in that environment)

|> - Does the ODBC driver support Oracle 7?

The SQL*Net driver is the issue here. SQL*Net for Windows (DLL Based SQL rather than DOS TSR based SQL*NET) supports Oracle 7. The old DOS TSR supports V6 only, but this will cause problems with ODBC - GPFs.

|>
|> - Are there any particular performance issues to be aware of?

Yup. Make sure to use DB_SQLPASSTHROUGH in your ODBC Snapshots/Dynasets. (See VB Prof Manual), otherwise it will crawl. Budget on at least 8MB in your PCs or it will thrash and crawl.

|>
|> - Are there any books/references anyone can recommend in this field?
|>

Nope. None that I've read. I've head that VB=mc4 is useful however.

|> - Finally any other hints and suggestions? [other than don't do it :-)]
|>

Take a long hard look at the examples from MS that come with VB Prof 3.0. In particular, look at DBAssist (?I think thats the name - sort of Access copy). This has some genuinely useful example code !

Try to implement asynchronous mechanisms for long reports/queries to stop the App tying the whole PC up for long periods whilst it waits for your server. e.g. use a PRO*C process to run reports/long queries in response to an request inserted into the database.

Expect to have problems initially setting up networked ODBC Clients. This can be complex.

|> thanks,
|>
|> Dave
|>
|> --
|>
|> +---------------------------------------+-----------------------+
|> | Dave Watts | |
|> | BT (D&P) Tymnet Engineering Centre | |
|> | email: dwatts_at_tymnet.bt.co.uk | |
|> | tel: 0442 237163 int: +44 442 237163 | |
|> | fax: 0442 237124 int: +44 442 237124 | |
|> +---------------------------------------+-----------------------+

Good luck Dave.

Hope this helps,

Simon Stearn
(These opinions are mine, all mine .... ha ha ha ... ) Received on Thu Jan 13 1994 - 13:58:31 CET