Hello,
I’m having some trouble with getting to the statements used by a TDAMemTable.
Both the TDADataSetCollection and TDASQLCommandCollection have TDAStatementCollection, but in both cases these are both always empty.
I’m trying to do something like this:
TDARemoteDataAdapter(aDAMemTable).Schema.DataSets[0].Statements.Count
But in this example the count is always 0 and should show 2.
Am I doing something wrong?
Thx,
Dirk
Hi,
Statements collection is server-side thing and it was removed by security reasons when schema is requested from clients.
you can change default behavior via the DataService.ReturnFullSchema property
Thank you very much, it’s working now!
And the next question is off course, can I also control from the client what statement the server should use, runtime?
Thx Dirk
Hi,
you can pass a custom parameter via login string from client to server.
by other hand, you can use IMultiDbLoginService.Login:
IMultiDbLoginService = interface(IBaseLoginService)
['{2C6D5764-01CE-447A-8264-27210B2C7371}']
function Login(const aUserID: ROUTF8String; const aPassword: ROUTF8String; const aConnectionName: ROUTF8String; out aUserInfo: UserInfo): Boolean;
end;
as you can see, it contains aConnectionName parameter.
via
IMultiDbLoginServiceV5 = interface(IMultiDbLoginService)
['{5A78AB01-2097-4473-A4D5-78980FFD90E4}']
function GetConnectionNames: StringArray;
function GetDefaultConnectionName: ROUTF8String;
end;
you can receive info about known connections so you shouldn’t use statements collection.
Hello, I do not think that the connection alone is enough, I would really need to change the statement.
And even during the run of the application, multiple datasets can be pointing to the same table in a schema, but with a different statement. As this used to be possible in DA3.
What would be the best approach if this isn’t possible anymore?
Thx
Dirk
Hi.
I’d suggest to have raw tables in schema and have one statement per database type (MSSQL, MYSQL, etc).
with the DynamicSelect feature you can get only required fields.
with the DynamicWhere feature you can apply any filter
with the DASQL feature you can generate complex request from different tables.
as a result, you can get requests of any difficulty
Ok, so working with different statements is no longer an option then?
yes
Ok thanks and have a nice and safe weekend!
Just one last question, I promise 
Is it normal that the name of the connection is not showing?
As an example, this is always emmpty:
Schema.DataSets[0].Statements[1].Name
And this shows the connection name and not the statement name:
Schema.DataSets[0].Statements[1].Displayname
Thx
Dirk
I can’t reproduce this:
DASM:
Delphi:
as expected:
function TDAStatement.GetDisplayName: string;
begin
if Connection <> '' then
result := Connection
else
if ConnectionType <> '' then
result := '[' + ConnectionType + ']'
else
if Name <> '' then
result := Name
else
result := '<unnamed>';
end;
hi Evgeny,
we talked about this before:
in DA3 we are able to supply a statement name which executes the right statement for a table
for us this is an extremely powerfull and elegant way to have different result sets of the same table or view or schema defined sql
most of the time this can be solved with params, but there are multiple circomstances where this can’t be done elegantly using params
and it’s way cleaner to have a for example product table with a statement that returns everything possible and a statement that only fetches a small number of fiels and fills the rest of the fiels with sql constants who doesn’t need to be fetched…
or result sets based on a language …
so the question really is if the DA3 behavious is or can be supported in DA4
this is the major showstopper for us to migrate DA3 to DA4
and implementing a statement behavior using a ‘statement selection parameter’ which then returns the right sql (inside the sql definition) is far more error prone, cause you can’t directly validate each possible sql statement
i use statements also to retrieve from different db views
so i can’t use the 3 methods above
furthermore if we can’t use the statement name selection, then why is it still present?
what purpose does it serve?
Hi,
I think, it all can be solved with custom GetData method what can accept additional parameter(s) like statement name or language.
you put statement name / language into session variable and when Schema asks for SQL in OnGetSQL event, you can return correct statement suitable for stored value.
by other hand, with DASQL you also can generate complicated statement from client-side so you don’t need to hardcore them into schema.
Note: ofc, some server-side DynamicWhere statements are still required by security reasons
if we need client side sql then i don’t need DA anylonger…
in case of the language, we have 3 languages over here and we need to be able to fetch on demand, so login won’t cut it
i see the power in being able to use a selection based on statements… and the drawbacks…
originally statement name wasn’t present at all in DAN, so when we create DASM from DAN, it doesn’t store this field.
AFAIR, we have added statement name to DASM by your request some time ago…
you mix direct SQL to DB and DASQL.
DASQL can use only tables from TDASchema because only these tables are “known” for DASQL.
so your DASQL request
SELECT CustomerID, Name FROM Customers WHERE Name = 'Miller'
will be generated and executed like
select ID, CustomerName from table1 WHERE CustomerName = 'Miller'
.
if Customers table in schema is VIEW or UnionTable, final request will be more complicated.
i think you mean not present in DA4?
ok i misinterpreted DASQL then
but i like to seperate all things sql as much as possible
and to be honest, dynamicwhere is hard to construct if it is a complex where clause and some where clauses can’t be done like where (select from) …
we have now DA10. DA4 was actual is about 15 years ago
for server-side code, you can use IDALegacyWhereSupport interface that allow to set Where clause directly
by da4 i mean the dataabstract service