Way to specify schema name in data settings?

rbuser

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

nicocizik

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).

rbuser

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

rbuser

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

rbuser

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

nicocizik

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.

rbuser

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

nicocizik

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?

rbuser

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

rbuser

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