Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007
    Posts
    9

    Unanswered: Embedded SQL / SQLDA / View of combined views

    I have a view that is a join of two other views.
    We use a source code generator to produce the abstract and interface layers in our code. For some reason, the embedded sql will not fill the sqld of the sqlda, even though the statement being passed in is just fine.

    Here is an example of how our embedded sql looks in our sqc file.
    I believe this may be a problem with a static cursor?? I can't figure out why the sqld is not filling. The views are written so that there will be a row, and only one row, for each id in the view.

    SQLDA *sqlda;
    Bool ret = True;

    pSqlStmt = pStr; // select * from ctr_dialog where id='XXXXXXX'

    EXEC SQL
    ALLOCATE DESCRIPTOR sqlda WITH MAX 25;

    EXEC SQL
    PREPARE CVI_SELECT_SINGLE FROM SqlStmt;

    EXEC SQL
    DESCRIBE OUTPUT FOR CVI_SELECT_SINGLE INTO sqlda;

    EXEC SQL
    GET DESCRIPTOR sqlda :numcols=COUNT;

    if( numcols > 25 )
    { EXEC SQL
    DEALLOCATE DESCRIPTOR sqlda;

    sqlda = NULL;

    EXEC SQL
    ALLOCATE DESCRIPTOR sqlda WITH MAX :numcols;

    EXEC SQL
    DESCRIBE OUTPUT FOR CVI_SELECT_SINGLE INTO sqlda;
    }

    fill_sqlda( sqlda );

    pCvi->sqlda = sqlda;

    CviClearSqlda( pCvi );

    sqlda = pCvi->sqlda;

    EXEC SQL
    EXECUTE CVI_SELECT_SINGLE USING DESCRIPTOR sqlda INTO DESCRIPTOR sqlda;

    pCvi->sqlda = sqlda;

    if( SQLCODE == SQLE_NOTFOUND )
    { EXEC SQL
    DEALLOCATE DESCRIPTOR sqlda;

    EXEC SQL
    DROP STATEMENT CVI_SELECT_SINGLE;

    return True; // Do not trigger error message!
    }
    else
    if( SQLCODE == SQLE_NOERROR )
    { CviPopulateFromSelect( pCvi, pCvr );

    ret = False;
    }

    EXEC SQL
    DEALLOCATE DESCRIPTOR sqlda;

    EXEC SQL
    DROP STATEMENT CVI_SELECT_SINGLE;

    if( ret == True )
    KdbErrMessage( KdbAsObj() );

    return ret;

    Here is an example of the views.
    Remember, I write code not SQL!

    // first small view - turned ANSINULL off to get rid of null warning

    create view CTR_INVOICE as select a.id as INVID, count(b.id) as INVOICE, count(c.typecharge) as CHARGE from
    container a
    left outer join invoice b on (a.id=b.containerid),
    invoice b
    left outer join invoiceitemtype c
    on (b.type=c.typecharge and c.classname='drop-off')
    group by a.id;

    // second, larger, view

    create view CTR_INFO as
    select a.id as INFOID,
    b.id as ARRID, b.label as ARRLABEL, b.supplycarrierid as ARRSUPCID,
    b.supplycarrier as ARRSUPC, b.transpcarrierid as ARRTRANCID,
    b.transpcarrier as ARRTRANC, b.originname as ARRONAME, b.destinationname as ARRDNAME,
    b.releaseno as ARRRELNO, b.redeliveryno as ARRREDNO, b.containertype as ARRCTTYPE,
    c.id as R1ID, c. perdiem as R1P, c.rateamount as R1AMT, c.freedays as R1FDAYS, c.perdiemcommission as R1PC,
    c.ratecommission as R1RC,
    d.id as RSID, d.freedays as RSFD,
    d.outgatechgtype as RSOGCT, d.outgatechg as RSOGC,
    d.outgatechg2type as RSOGC2T, d.outgatechg2 as RSOGC2,
    d.outgatechg3type as RSOGC3T, d.outgatechg3 as RSOGC3,
    d.ingatechgtype as RSIGCT, d.ingatechg as RSIGC,
    d.ingatechg2type as RSIGC2T, d.ingatechg2 as RSIGC2,
    d.ingatechg3type as RSIGC3T, d.ingatechg3 as RSIGC3,
    d.ingatechg4type as RSIGC4T, d.ingatechg4 as RSIGC4,
    d.ingatechgvalidday as RSIGCV,
    d.ingatechg2validday as RSIGC2V,
    d.ingatechg3validday as RSIGC3V,
    d.ingatechg4validday as RSIGC4V,
    d.perdiem as RSP, d.perdiemvaliddays as RSPV,
    d.perdiem2 as RSP2, d.perdiem2validdays as RSP2V,
    d.perdiem3 as RSP3, d.perdiem3validdays as RSP3V,
    d.containercondition as RSCC,
    d.dppcoverage as RSDPP,
    d.replacementvalue as RSREPV,
    e.id as RTID, e.freedays as RTFD,
    e.outgatechgtype as RSOGCT, e.outgatechg as RSOGC,
    e.outgatechg2type as RSOGC2T, e.outgatechg2 as RSOGC2,
    e.outgatechg3type as RSOGC3T, e.outgatechg3 as RSOGC3,
    e.ingatechgtype as RSIGCT, e.ingatechg as RSIGC,
    e.ingatechg2type as RSIGC2T, e.ingatechg2 as RSIGC2,
    e.ingatechg3type as RSIGC3T, e.ingatechg3 as RSIGC3,
    e.ingatechg4type as RSIGC4T, e.ingatechg4 as RSIGC4,
    e.ingatechgvalidday as RSIGCV,
    e.ingatechg2validday as RSIGC2V,
    e.ingatechg3validday as RSIGC3V,
    e.ingatechg4validday as RSIGC4V,
    e.perdiem as RSP, e.perdiemvaliddays as RSPV,
    e.perdiem2 as RSP2, e.perdiem2validdays as RSP2V,
    e.perdiem3 as RSP3, e.perdiem3validdays as RSP3V,
    e.containercondition as RSCC,
    e.dppcoverage as RSDPP,
    e.replacementvalue as RSREPV
    from container a
    left outer join arrangement b on (a.label = b.label),
    arrangement b
    left outer join rate c on (b.rateid = c.id),
    arrangement b
    left outer join rate d on (b.supplyrateid = d.id),
    arrangement b
    left outer join rate e on (b.transprateid = e.id)
    group by a.id,b.id,b.label, b.supplycarrierid,
    b.supplycarrier, b.transpcarrierid,b.transpcarrier, b.originname,
    b.destinationname,b.releaseno, b.redeliveryno, b.containertype,
    c.id, c.perdiem, c.rateamount , c.freedays, c.perdiemcommission,
    c.ratecommission, d.id, d.freedays,
    d.outgatechgtype, d.outgatechg,
    d.outgatechg2type, d.outgatechg2,
    d.outgatechg3type, d.outgatechg3,
    d.ingatechgtype, d.ingatechg,
    d.ingatechg2type, d.ingatechg2,
    d.ingatechg3type, d.ingatechg3,
    d.ingatechg4type, d.ingatechg4,
    d.ingatechgvalidday,
    d.ingatechg2validday,
    d.ingatechg3validday,
    d.ingatechg4validday,
    d.perdiem, d.perdiemvaliddays,
    d.perdiem2, d.perdiem2validdays,
    d.perdiem3, d.perdiem3validdays,
    d.containercondition,
    d.dppcoverage,
    d.replacementvalue,
    e.id, e.freedays,
    e.outgatechgtype, e.outgatechg,
    e.outgatechg2type, e.outgatechg2,
    e.outgatechg3type, e.outgatechg3,
    e.ingatechgtype, e.ingatechg,
    e.ingatechg2type, e.ingatechg2,
    e.ingatechg3type, e.ingatechg3,
    e.ingatechg4type, e.ingatechg4,
    e.ingatechgvalidday,
    e.ingatechg2validday,
    e.ingatechg3validday,
    e.ingatechg4validday,
    e.perdiem, e.perdiemvaliddays,
    e.perdiem2, e.perdiem2validdays,
    e.perdiem3, e.perdiem3validdays,
    e.containercondition,
    e.dppcoverage,
    e.replacementvalue;

    // uber view I'm trying to read from

    create view CTR_DIALOG as
    select * from
    container a, ctr_info b, ctr_invoice c
    where
    a.id=b.infoid
    and a.id=c.invid;

  2. #2
    Join Date
    Jan 2007
    Posts
    9

    OK, now what

    I created bind variables and executed the statement directly.
    I am running ASA ver 7.

    Typing the same statement I capture from the code, it runs in ISQL.
    In the interface layer though, I get returned an SQLCODE of -141.
    This states that "You misspelled the name of a table, or you did not qualify a table name with a user name" but I assure you that it is correct.

    Any known errors on V7 that might pop this error?
    SQLE_TABLE_NOT_FOUND

    Any help is appreciated!
    -Mitch

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    The user that you are running as should belong to the group that owns the referring schema, otherwise you need to qualify it by the schema owner in the tablename

  4. #4
    Join Date
    Jan 2007
    Posts
    9
    Quote Originally Posted by willy_and_the_ci
    The user that you are running as should belong to the group that owns the referring schema, otherwise you need to qualify it by the schema owner in the tablename
    Thanks willie, did that already though.
    I'm going to try and get a statement written for one view, instead of the three, and see what it does.

  5. #5
    Join Date
    Jan 2007
    Posts
    9

    Success... but why?

    I removed the underscore from the ctr_dialog view (henceforth known as ctrdialog) and rewrote them so they were a little shorter. I had to drop and recreate the view a couple of times too.

    That changed the code from the -141 to 109, which I expected because of one of the left outer joins. I accounted for this in the code and voila, I'm reading records. (Using the SQLDA and not bind variables too)

    Mitch

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •