re ODBC Connection

[itzgee <itzgee@PRISMNET.COM>]

caveat:  I use oracle.  You might have subtle differences in the sql if you
use another product.

You have to set up a database link first.

You do not set up the link within your sqr, at least I don't.  I set up my
links manually, with my dba's knowledge and consent.

The standard syntax for creating a link is:

create [public or private] database link some_name_link
   connect to username identified by password
   using 'T:hostname:dbname'


If you are using Oracle SQL*Net, you can substitute
   using 'connect string'


The 'T:.....' stands for the protocol, in this case, TCP/IP.

hostname is the name of the host where the database resides.
dbname is the name of the remote database.


So, let's say you set up the following link


create public database link my_link
   connect to rstojkov identified by 18snoopy
   using 'T:my_server:my_database'


Then, when you want to access data at that location, you use the following
syntax:

select *
   from my_table@my_link
     where my_field = 'ME'

If you get tired of all that typing, you can create a synonym:

    create synonym my_self for my_table@my_link

then you can say

   select * from my_self where my_field = 'ME'

Now, a word to the wise:

    DATABASE LINKS MUST BE MAINTAINED MANUALLY!!!

    I am not a DBA, so I don't know all about this, but there are instances
where
    your links can go AWOL.  If you are migrating your database to another
server
    or if your dba does some sort of major upgrade, the database link may be
wiped
    out.

    Therefore, you need to work with your dba on this.

    You might even consider using an sqr that checks for the existence of
the link at night and warns you if it can't find it.

begin-procedure my_check

    $my_msg = 'NOT FOUND'

begin-select
my_field

    $my_msg = 'FOUND'

from my_self

end-select

    if $my_msg = 'NOT FOUND'
    let $Call_To_System = 'mail -s "Link Status"
robert.stojkovic@IO-CONSULTING.COM <'|| $my_msg
     call system using $Call_To_System #unix_status
    end-if

end-procedure


   Database links can also be slow slow slow if you try to return a lot of
rows.
    Be sure your selects are as limited as possible.   Try to nest your
selects
    if your performance is unacceptable.  Retrieve the local data, and then
call
    a procedure that retrieves the remote data.  If you do a complicated
join across
    a link, you will be like Cartman on a Big Wheel ("!?*# Can't this thing
go any faster!!!")


Gracen Duffield
Texas Department of Housing and Community Affairs
475-3839

-----Original Message-----
From: Discussion of SQR, SQRIBE Technologies's database reporting language
[mailto:SQR-USERS@list.iex.net]On Behalf Of Robert Stojkovic
Sent: Friday, September 17, 1999 11:25 AM
To: Multiple recipients of list SQR-USERS
Subject: ODBC Connection


Hello.

What would be the proper syntax to open and ODBC connection to another
database within and SQR.

Robert