CA IDMS IUA EIUA

Expand all | Collapse all

Processing IDMS User-Defined Setname in SQL Server Linked

  • 1.  Processing IDMS User-Defined Setname in SQL Server Linked

    Posted 11-16-2008 03:06 PM
    Server

    The following SQL statement works in OCF where ""CSC-CMCSEV"" is a
    user-defined setname. The owner of the set is a Calc record with Calckey
    ""CSC_CASE_ID"".

    SELECT *
    FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID =3D 16

    OCF 16.0 IDMS PAGE 1 LINE 1 DICT=3DQALDICT 1/86
    SYST0005
    SELECT *
    FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID =3D 16
    *+
    *+  CSC_CASE_ID  CMCSEV_CASE_EVENT_SEQ_NO  CMCSEV_CASE_EVENT_VERSION_NO
    *+  -----------  ------------------------  ----------------------------
    *+           16                         1                             1
    *+
    *+ CMCSEV_OCOME_SORT_SEQ_ID  CMCSEV_SCHED_HEARING_DTE
    *+ ------------------------  ------------------------
    *+                        1                  20080618


    I have defined an SQL Server Linked Server using the ODBC provider:
    Microsoft OLE DB Provider For ODBC Drivers and
    accessing a Data Source created by the CA-IDMS ODBC Administration tool
    as a ""System""-type ODBC Data Source using CA-IDMS ODBC driver. =20


    The following SQL statement works in SQL Server Linked Server but
    returns too many record:

    SELECT *
    FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
    [IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
    WHERE C.CSC_CASE_ID =3D 16;

    The following SQL statement gives me an error as expected since the
    setname ""CSC-CMCSEV"" is not recognized by SQL Server Linked Server.

    SELECT *
    FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
    [IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID =3D 16;

    Msg 4145, Level 15, State 1, Line 4
    An expression of non-boolean type specified in a context where a
    condition is expected, near 'AND'.

    I am trying to investigate if SQL Server support SQL PASSTHRU option
    that does not edit the SQL Syntax and hence allow the setname to
    passthru.
    I can't find any SQL Server Linked Server option that touches on SQL
    PASSTHRU

    Does anyone know how to process setname in SQL Server Linked Serve?=20

    Regards,
    Paul Mak=20
    Database Administrator - IDMS

    EDS, an HP company

    Applications Services, Data Engineering Capability - Sydney
    Level 3, 36-46 George Street,=20
    Burwood, NSW 2134, AUSTRALIA

    Tel: +61 2 90125434
    Fax: +61 2 90126612
    Mobile: +61 419 398 116
    E-mail: paul.mak@eds.com <mailTo:alex.leeflang@eds.com>=20

    We deliver on our commitments so you can deliver on yours.
    "
    IDMS 3rd-party providers forum
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    SMTP
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    SMTP








    Normal

    Normal
    Re: Processing IDMS User-Defined Setname in SQL Server Linked Server
    "Paul,

    With SQL Server you can use OPENQUERY to send your IDMS SQL via the
    Linked Server. The syntax is something like this;


    SELECT * FROM OPENQUERY
    (IDMS_DEV,
    'SELECT *
    FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID = 16')


    Joe S Cates, Database Analyst II
    Systems Architecture and Operations
    Database Management/Unix Administration
    Montgomery County Public Schools
    Rockville, MD 20850
    301-279-3697
    joe_cates@mcpsmd.org


  • 2.  Re:Processing IDMS User-Defined Setname in SQL Server Linked

    Posted 11-16-2008 03:06 PM
    Server

    The following SQL statement works in OCF where ""CSC-CMCSEV"" is a
    user-defined setname. The owner of the set is a Calc record with Calckey
    ""CSC_CASE_ID"".

    SELECT *
    FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID = 16

    OCF 16.0 IDMS PAGE 1 LINE 1 DICT=QALDICT 1/86
    SYST0005
    SELECT *
    FROM NSQLCOURT.""CASE-C"" C, NSQLCOURT.""CRIM-CASE-EVENT"" B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID = 16
    *+
    *+  CSC_CASE_ID  CMCSEV_CASE_EVENT_SEQ_NO  CMCSEV_CASE_EVENT_VERSION_NO
    *+  -----------  ------------------------  ----------------------------
    *+           16                         1                             1
    *+
    *+ CMCSEV_OCOME_SORT_SEQ_ID  CMCSEV_SCHED_HEARING_DTE
    *+ ------------------------  ------------------------
    *+                        1                  20080618


    I have defined an SQL Server Linked Server using the ODBC provider:
    Microsoft OLE DB Provider For ODBC Drivers and
    accessing a Data Source created by the CA-IDMS ODBC Administration tool
    as a ""System""-type ODBC Data Source using CA-IDMS ODBC driver.


    The following SQL statement works in SQL Server Linked Server but
    returns too many record:

    SELECT *
    FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
    [IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
    WHERE C.CSC_CASE_ID = 16;

    The following SQL statement gives me an error as expected since the
    setname ""CSC-CMCSEV"" is not recognized by SQL Server Linked Server.

    SELECT *
    FROM [IDMS_DEV]..[NSQLCOURT].[CASE-C] C,
    [IDMS_DEV]..[NSQLCOURT].[CRIM-CASE-EVENT] B
    WHERE ""CSC-CMCSEV""
    AND C.CSC_CASE_ID = 16;

    Msg 4145, Level 15, State 1, Line 4
    An expression of non-boolean type specified in a context where a
    condition is expected, near 'AND'.

    I am trying to investigate if SQL Server support SQL PASSTHRU option
    that does not edit the SQL Syntax and hence allow the setname to
    passthru.
    I can't find any SQL Server Linked Server option that touches on SQL
    PASSTHRU

    Does anyone know how to process setname in SQL Server Linked Serve?

    Regards,
    Paul Mak
    Database Administrator - IDMS

    EDS, an HP company

    Applications Services, Data Engineering Capability - Sydney
    Level 3, 36-46 George Street,
    Burwood, NSW 2134, AUSTRALIA

    Tel: +61 2 90125434
    Fax: +61 2 90126612
    Mobile: +61 419 398 116
    E-mail: paul.mak@eds.com <mailTo:alex.leeflang@eds.com>

    We deliver on our commitments so you can deliver on yours.
    "
    IDMS Public Discussion Forum
    IDMS-L@LISTSERV.IUASSN.COM
    SMTP
    IDMS-L@LISTSERV.IUASSN.COM
    IDMS-L@LISTSERV.IUASSN.COM
    SMTP








    Normal

    Normal
    Re: 0518 on Obtain First in Set
    "ADS does do a bind under the covers, even though the dialog does not
    code one.
    My initial response would be that the dialog and its maps are referring
    to a record that has been removed from the subschema. A more wicked
    variation on the problem is, that the loadlib/area contains a likenamed
    subschema, but without the diaglogs records.

    A regeneration of the dialog would prove/disprove that, but if it has
    errors, then the app would be disabled until the errors are fixed. So,
    regeneration in production should not be taken lightly.

    Lutz Petzold
    TDM UDB/IDMS Support
    401-782-2265
    Page 860 366 0865 or Telalert



    This e-mail may contain confidential or privileged information. If
    you think you have received this e-mail in error, please advise the
    sender by reply e-mail and then delete this e-mail immediately.
    Thank you. Aetna
    "
    IDMS Public Discussion Forum
    IDMS-L@LISTSERV.IUASSN.COM
    SMTP
    IDMS-L@LISTSERV.IUASSN.COM
    IDMS-L@LISTSERV.IUASSN.COM
    SMTP








    Normal

    Normal
    Re: 0518 on Obtain First in Set
    "ADS does do a bind under the covers, even though the dialog does not=0D=0Ac=
    ode one=2E=0D=0AMy initial response would be that the dialog and its maps a=
    re referring=0D=0Ato a record that has been removed from the subschema=2E =
    A more wicked=0D=0Avariation on the problem is, that the loadlib/area conta=
    ins a likenamed=0D=0Asubschema, but without the diaglogs records=2E=0D=0A=
    =0D=0AA regeneration of the dialog would prove/disprove that, but if it has=
    =0D=0Aerrors, then the app would be disabled until the errors are fixed=2E =
    So,=0D=0Aregeneration in production should not be taken lightly=2E=0D=0A=
    =0D=0ALutz Petzold=0D=0ATDM UDB/IDMS Support=0D=0A401-782-2265=0D=0APage 86=
    0 366 0865 or Telalert=0D=0A =0D=0A =0D=0A=0D=0AThis e-mail may contain con=
    fidential or privileged information=2E If=0Ayou think you have received thi=
    s e-mail in error, please advise the=0Asender by reply e-mail and then dele=
    te this e-mail immediately=2E=0AThank you=2E Aetna
    "
    IDMS 3rd-party providers forum
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    SMTP
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    IDMSVENDOR-L@LISTSERV.IUASSN.COM
    SMTP








    Normal

    Normal
    Re: 0518 on Obtain First in Set
    "ADS does not BIND a record unless it's named in the dialog using DML.