Home > SQR > -DBconnection string in SQR

-DBconnection string in SQR

-DBconnection string option in Begin-Select, allows you to combine data from multiple databases in one program. An example for this is, Your SQR report which will be running from Finance database, needs name of employee from HRMS database. Create a procedure with -dbconnectionstring as show below.

Begin-Procedure Get_Empl_Name
#debugy show ‘In Procedure : Get_Empl_Name’

let $dbname = ‘DBNAME’
let $dbconnstr = ‘DSN=’ || $DbName || ‘;UID=USERID;PWD=PASSWORD’

Begin-Select -DB$dbconnstr
EMPLID
NAME
     show &emplid ‘ – ‘ &name
FROM PS_PERSONAL_DATA
WHERE EMPLID = $emplid
End-Select
End-Procedure !Get_Empl_Name

For this to work, we need to establish DB Link between the two databases. In the above example, the Finance database should have a DB Link established with HRMS database. The User-id and password, given in the connection string is the user id and password established while creating DB Link. We can assign appropriate access for this userid in DB Link.

Advertisements
Categories: SQR Tags:
  1. Mikael McLeod
    August 28, 2008 at 4:21 pm

    Although this is listed as a valid parameter in the SQR manuals, I get the following error message when ever I use the -DB code presented earlier. Please advise?

    Error on line 50:
    (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$dbconnstr

    Errors were found in the program file.

  2. balaglobal
    August 29, 2008 at 5:28 pm

    Mikael,
    show me how you are setting $dbconnstr varibale. I think there is a problem with your $dbconnstr variable.

    Regards,
    Bala.

  3. Toney
    November 11, 2008 at 4:22 pm

    Was there a response to the below? I am encountering the same problem. Thank..

    Although this is listed as a valid parameter in the SQR manuals, I get the following error message when ever I use the -DB code presented earlier. Please advise?

    Error on line 50:
    (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$dbconnstr

    Errors were found in the program file.

  4. balaglobal
    November 11, 2008 at 7:00 pm

    Toney,
    show me how you are setting $dbconnstr varibale. I think there is a problem with your $dbconnstr variable.

    Regards,
    Bala.

  5. Kishore
    March 18, 2010 at 5:36 pm

    I think if we have dblink in place between HR & FN. I’d rather simply suffix the table with @dblink. Which works using AE-SQL objects & SQRs as well.

    ! SQR running against Orcale DB
    ! @DBLINK = @HR89DV

    Begin-Select
    EMPLID
    NAME
    show &emplid ‘ – ‘ &name
    FROM PS_PERSONAL_DATA@HR89DV
    WHERE EMPLID = $emplid
    End-Select
    End-Procedure !Get_Empl_Name

    • Manuja
      October 21, 2010 at 2:45 pm

      Hi Kishore,

      As per your suggestion, when i tried to give, the following error arised.

      Error on line 102:
      (SQR 3728) SQL expression not ended, perhaps missing &name.
      from sysadm.ps_earnings_tbl@OTDBSIT

      Errors were found in the program file.

      SQR for PeopleSoft: Program Aborting.

      can u pls help me in dis regard..

      My code is:

      Begin-Select
      erncd erncd
      effdt effdt

      show ‘&erncd=’ erncd
      show ‘&effdt=’ effdt
      from sysadm.ps_earnings_tbl@OTDBSIT
      End-Select

      • Kishore
        November 12, 2010 at 4:25 pm

        try this:

        Begin-Select
        erncd
        effdt

        show &erncd
        show &effdt

        from sysadm.ps_earnings_tbl@OTDBSIT
        End-Select

  6. Manuja
    October 21, 2010 at 2:53 pm

    Hi bala,

    my dbconnection string is:

    Begin-Procedure GetRecfldSit
    let $dbname = ‘OTDBSIT’
    let $dbconnstr = ‘DSN=’ || $dbName || ‘;UID=natarv2;PWD=nissan123’

    Begin-Select -DB$dbconnstr
    erncd erncd
    effdt effdt

    show ‘&erncd=’ erncd
    show ‘&effdt=’ effdt
    from sysadm.ps_earnings_tbl
    End-Select

    End-Procedure GetRecfldSit

    Still, i am getting the error as -DB bad param in Begin-Select .. pls, help me out in this regard ASAP..

    Thanks,
    manuja

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: