Way to specify schema name in data settings?

edited July 2016 in DADE

Using:
- Delphi Berlin 10.1 Enterprise.
- RB 17.02 build 149 Enterprise.

Sybase SQL Anywhere 11.

- drop a TSQLConnection ("scn1") on a form, set to ODBC driver,
configure params correctly (click on "connect" - it connects).

- drop a TppReport on the form and invoke designer

- go to Data tab

-
- Session Type: dbExpressSession
- Database Name: scn1
- Database Type: other


-

The list of available table names is populated with the tables.

Select one at random (work_order_header).

Click Next.


I get a dialog box with:

"TdaMetaData.GetFieldsFromDataSet: Unable to open dataset:
work_order_header."

If I use an admin DB user, it works.

If I use a special "read only" user, it doesn't.

In regular queries with that user, "select * from work_order_header"
fails, but "select * from dbc.work_order_header" works just fine.

So - is there ANY way to specify schema name in DADE such that I can
use the nice, safe READONLY user?

That is - TdaMetaData.GetFieldsFromDataSet needs to use:
dbc.work_order_header
instead of:
work_order_header

can this be done?


TIA.

Cheers,
EdB

Comments

  • edited July 2016
    Hi Ed,

    Seeing that you are using the latest version of Delphi, one option is to
    try FireDAC rather that DBExpress to connect to your data. The FireDAC
    plugin contains logic to automatically try to use the correct schema
    name to gain access to your data.

    If you must use DBExpress, you will likely need to alter the
    daDBExpress.pas plugin to add the correct schema name to the beginning
    of each table inside the TdaSQLSession.GetTableNames (similar to the way
    it is currently done for SQLServer).

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited July 2016
    Hi Nico,

    I just spent 3 hours trying every combination I could think of to get
    FireDAC connection working (there are a lot of options there!).

    No joy.

    The FDconnection will connect - it just won't return table names.

    I guess I should have spent the time poring through daDBExpress instead.

    Thanks for info.

    EdB

  • edited July 2016
    Hi Nico,


    Definitely should have started when I wasn't so tired...

    I can see how to change GetTableNames to use the 'dbc' schema.

    I'm just not clear on how to get that change (even to test to see if it
    works) compiled and running.

    I'm not even clear on how to just change

    lSQLConnection.GetTableNames(aList, False);

    to

    lSQLConnection.GetTableNames(aList, 'dbc', False);

    and rebuild the package...

    (it's really late).

    Ideally I could create a new unit that overrides the behaviour such
    that I can use dbExpress with (or without) the dbc override based on
    either an IFDEF or including something in "uses".

    This is an ODBC connection to a particulare Sybase SQL Anywhere
    database - but most of my dbExpress data uses odbc as well - so I can't
    use
    GetDatabaseType(aDatabaseName) = dtOther to force the use of 'dbc'.

    Hmm, I could look at vendorlib if it's available.

    Anyway, I appreciate anything you can suggest.


    Cheers,
    EdB

  • edited July 2016

    I just tried this:

    copy dclRBDBE1724.dpk, rbDBE1724.dpk, daDBExpress.pas and
    daDBExpress.pas to a new folder.

    Renamed all the files, renamed the classes (eg
    TdaChildNewSQLClientDataSet), changed the references in the .dpk files
    to match new file names, and installed the dpk.

    At design time, I can see my new dbExpressNewSession entry in
    DataSettings (however, dbExpressSession is gone).


    I also made the change to: lSQLConnection.GetTableNames(aList, 'dbc',
    False);

    Still no joy.

    I'm at a loss...

    EdB



  • edited July 2016
    Hi Ed,

    There should be no need to create your own plugin etc.

    To test if adding the schema name will work, simply open the
    daDBExpress.pas file, and alter the code at the bottom of the
    TdaSQLSession.GetTableNames to the following...

    ...

    if lSQLConnection.Connected then
    {$IFDEF Delphi7}
    if GetDatabaseType(aDatabaseName) = dtMSSQLServer then
    lSQLConnection.GetTableNames(aList, 'dbo', False)
    else if GetDatabaseType(aDatabaseName) = dtSybaseASA then
    lSQLConnection.GetTableNames(aList, 'dbc', False)
    else
    {$ENDIF}
    lSQLConnection.GetTableNames(aList, False);

    end;

    Save the file and run your application as it was originally. Now when
    the table names are retrieved, the "dbc" schema name will be used by
    default.

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited July 2016

    Thanks for input.

    I'm using RX10.1, but that's easy:

    if lSQLConnection.Connected then
    {$IFDEF Delphi7}
    if GetDatabaseType(aDatabaseName) = dtMSSQLServer then
    lSQLConnection.GetTableNames(aList, 'dbo', False)
    else
    {$ENDIF}
    if GetDatabaseType(aDatabaseName) = dtSybaseASA then
    lSQLConnection.GetTableNames(aList, 'dbc', False)
    else
    lSQLConnection.GetTableNames(aList, False);

    end;

    Two issues:

    1) this TSQLConnection needs to use the odbc driver spec, so instead of
    dtSybaseASA would I not be looking at dtOther? That's going to put a
    damper on connecting to non-Sybase stuff...

    2) I actually tried this last night:

    if lSQLConnection.Connected then
    {$IFDEF Delphi7}
    if GetDatabaseType(aDatabaseName) = dtMSSQLServer then
    lSQLConnection.GetTableNames(aList, 'dbo', False)
    else
    {$ENDIF}
    lSQLConnection.GetTableNames(aList, 'dbc', False);

    end;


    Nothing I did would recompile that (heck, I couldn't even get it to
    /save/ within RX10.1) and use it. (I've been using D7 up until, oh,
    last week or so - still coming to grips with RX10.1 and how to "get
    it's attention"). How would I trace into this?

    --------

    I restarted my machine and tried to assign the Session type (I see
    dbExpressSession and dbExpressPSISession now that I've restarted).

    It still can't find the field names. Here's a section of the stack
    trace:


    [5117FB85]{dbrtl240.bpl} Data.DB.TDataSet.SetActive (Line 12508,
    "Data.DB.pas" + 12) + $7
    [2B194648]{rbDBEPSI1724.bpl}
    Dadbexpresspsi.TdaChildPSISQLClientDataSet.SetActive + $10
    [2B194C64]{rbDBEPSI1724.bpl} Dadbexpresspsi.TdaPSISQLDataSet.SetActive
    + $14
    [1D648A81]{rbDAD1724.bpl} Dametadata.TdaMetaData.GetFieldsFromDataSet +
    $65
    [1D648E35]{rbDAD1724.bpl} Dametadata.TdaMetaData.PopulateCacheFields +
    $4D
    [1D6488FA]{rbDAD1724.bpl} Dametadata.TdaMetaData.GetFields + $BA
    [1D683315]{rbDAD1724.bpl}
    Dametadatamanager.TdaMetaDataManager.GetFields + $69
    [1D68F771]{rbDAD1724.bpl} Dasql.TdaSQL.CreateFieldsForTable + $85
    [1D68F590]{rbDAD1724.bpl} Dasql.TdaSQL.CreateAvailableFieldList + $A4


    It looks like it isn't using my plugin to get fields - is it still
    still relying on daMetaData.TDaMetaData.GetFields? Maybe my quick and
    dirty plugin "cloning" missed something.

    ---------------

    I have a work-around for now. I just manually enter the SQL instead of
    using the wizards and add parameters.

    From the calling program, I use:

    ppReport1.Parameters['WONO'].Value:=edt1.Text;
    ppReport1.print;

    I'd prefer using the query editor and wizard rather manually entering
    it (or rather, end-users would) - so it would be great if I could
    resolve this.

    Maybe after an afternoon nap.

    Thanks again.

    Cheers,
    EdB






  • edited July 2016
    Hi Ed,


    Are you getting a compiler error? What error are you getting when you
    add this code?

    Are you sure you have the Source directory in your library path so the
    updated daDBExpress.pas file is being used?

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited July 2016

    Hi Nico,

    Once again sleep-deprivation sneaks in - I'd set the source path in the
    project search path, not the library path.



    With that fixed, the issue is clear:

    When the call to get field names is made to:

    procedure TdaMetaData.GetFieldsFromDataSet(aMetaTable: TdaMetaTable;
    aFields: TList);
    ...

    aFields.Clear;

    lDataSet := FSession.CreateDataSet(Self, FDatabaseName);
    lDataSet.DataName := aMetaTable.SQLName;
    ...

    aMetaTable.SQLName only has the table name, it does not include the
    "dbc." prefix.

    To this particular user, there IS NO "work_order_header" table - there
    is only "dbc.work_order_header"

    This means the call to lDataSet.Active := True; always fails.



    Any suggestion about how to resolve this - maybe a custom TdaMetaData
    property that I can set (when and where?) to prepend 'dbc.' to table
    names?

    Thanks!

    EdB

  • edited July 2016
    Hi Nico,

    Thanks for the assist on this - the main problem I have is that the
    database user in question has limited permissions with respect to what
    tables can be accessed.

    I've given up - I'm just going to create a new user and grant
    membership to the "dbc" group.

    It means that the new user will have select priviledges on ALL tables
    instead of the few the user should see - but at least there's no UPDAtE
    permissions...

    I can't think of a way to make the user appear to be a member of "dbc"
    for the particular tables (short of revoking access on a table-by-table
    basis - a nightmare in a database where tables are added/modified
    quarterly in system upgrades).



    Thanks again.

    Cheers,
    EdB

This discussion has been closed.