RE: [sqr-users] Multiple database connections

["Knapp, Richard" <KnappR@umsystem.edu>]

I have used connect and database links to accomplish referencing multiple 
databases from a single SQR program.  Here is a spot of documentation from one 
of the programs:

!       The connect command will work (connect 'user/passwd@hrrpt76') if 
!       there is a dummy table or view in HR with the same name as the
!       finance table (sysadm.ps_gm_dept_attrib).  This fools SQR's 
!       precompile check of db connectivity. But, in this case, a link has to
!       be used. This is often much slower. So, to make things as efficient
!       as possible, all the data are brought over the link in one chunk
!       and stored in arrays. AAUP is included here because about half of
!       the report is identical to the IPEDS-SA. 

They are really the same thing except the database stores the username and 
password in the case of the link.  More secure. 

Richard Knapp
Database Programmer/Analyst
Institutional Research and Planning
University of Missouri System
573-882-8856
knappr@umsystem.edu
 

-----Original Message-----
From: sqr-users-bounces+knappr=umsystem.edu@sqrug.org 
[mailto:sqr-users-bounces+knappr=umsystem.edu@sqrug.org] On Behalf Of LEBLANC 
Philippe FTC
Sent: Tuesday, November 02, 2004 10:09 AM
To: sqr-users@sqrug.org
Subject: [sqr-users] Multiple database connections

Brio Report Builder version:
===================================================================
SQR6.2.1(build 126)/PC Compaq Deskpro EN pentiumIII 733MHz/Windows 2000/Oracle 
8.1.7/ODBC 8.01.06.00
===================================================================
SQR6.2/Compaq True64/DEC AXP Digital UNIX V4.0F/Oracle 8.1.7
===================================================================

I'm trying to connect to each local database via ODBC under windows and get 
this message error:
Error on line 125:
   (SQR 3721) Bad param found on 'BEGIN-SELECT' line; Format is:
 BEGIN-SELECT [DISTINCT] [-Cnn] [-Bnn] [LOOPS=nn]
              [ON-ERROR=procedure[(arg1[,argi]...)]]
begin-select -DB$ChaineConnection on-error=TraitementErreur

Errors were found in the program file.

SQR: Program Aborting.

Report sources is:
!--------------------------------------------------------------------------------------------
! Edition : EXTR0000.sqr
!--------------------------------------------------------------------------------------------
! Appelant:  
! Objet   : EXTR0000 : Test de connections multibases
!           
!--------------------------------------------------------------------------------------------
! Param.  :
!    $G_RefTrt  : Reference du traitement 
!    $G_DateTrt : Date d'execution sous la forme 'DD/MM/YYYY HH24:MM:SS'
!    $G_NumTrt  : Numero de lot 
!--------------------------------------------------------------------------------------------
! Para_INST : 
!    $Queue_Impression  : queue d'impression
!    $Chemin_Impression : chemin d'impression
!--------------------------------------------------------------------------------------------

#define IMP LINEPRINTER
#define HP HPLASERJET

! #include 'toolssqr.inc'

!--------------------------------------------------------------------------------------------
!------------------------------
!-- DEFINITION DU SETUP
!------------------------------
Begin-Setup
        page-size 64 154
        declare printer
        type={HP}
        Symbol-Set=0N   
        left-margin=0.5
        top-margin=0.5 
        orientation=Landscape
        point-size = 8
        line-size = 8
        char-size = 5
        font = 1
End-Setup

!--------------------------------------------------------------------------------------------
!-----------------------------
!-- DEBUT DU PROGRAMME
!-----------------------------
Begin-Report

  Let $pvcs_id='$Workfile:   EXTR0000.sqr  $ - $Revision:   0.1 $'

  Do Main_Programme

End-report


!--------------------------------------------------------------------------------------------
!--------------------------------
!-- TRAITEMENT PRINCIPAL
!--------------------------------
Begin-procedure Main_Programme
Move 'Main_Programme' To $Procedure
#debug display $Procedure
        
Move 1 to #return-status
Let #LargeurPage = 150

LET $ChaineConnection = 'BaseTEST1;DSN=BaseTEST1;UID=user1;PWD=pass1'
#debug show 'Chaine de connection: ' $ChaineConnection
Do Extraction

LET $ChaineConnection = 'BaseTEST2;DSN=BaseTEST2;UID=user1;PWD=pass1'
#debug show 'Chaine de connection: ' $ChaineConnection
Do Extraction

LET $ChaineConnection = 'BaseTEST3;DSN=BaseTEST3;UID=user1;PWD=pass1'
#debug show 'Chaine de connection: ' $ChaineConnection
Do Extraction

! FIN NORMALE
Let #Return-Status = 0

COMMIT

End-Procedure


!--------------------------------------------------------------------------------------------
!------------------------------
!-- EDITION DE L'ETAT
!------------------------------
Begin-Procedure Extraction
Move 'Extraction' To $Procedure
#debug display $Procedure

move '*' to $databasename

begin-select -DB$ChaineConnection on-error=TraitementErreur
value   &database_name

    let $databasename = &database_name
    #debug show 'Base: ' $databasename

from v$parameter
where name = 'db_name'
end-select 


End-Procedure

!---------------------------------------------------------------------------------------------
!--------------------------------
!-- TRAITEMENTS DES ERREURS
!--------------------------------
Begin-Procedure TraitementErreur
! Renvoie le numero des erreurs SQL, et stoppe le programme.

If Not Isnull ($Sql-Error)
        #debug show 'Erreur Sql numero: ' $Sql-Error
end-if

STOP
End-Procedure

!---------------------------------------------------------------------------------------------
!---------------------------------------------------------------------------------------------
!---------------------------------------------------------------------------------------------

Do you have a suggestion to run my query on each database in a unique report ???

Je reste à votre disposition pour toute demande d'information complémentaire.

Cordialement, Philippe LEBLANC
France Télécom Câble
Direction Informatique
Cellule Finances & Statistiques
Assistance centralisée: 01.55.58.36.00

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users

_______________________________________________
sqr-users mailing list
sqr-users@sqrug.org
http://www.sqrug.org/mailman/listinfo/sqr-users