DbExpress unsupported operation

rbuser

Hello,

For a new app I work with an Interbase Server and I uses DbExpress with D7
and RB7 ent.
At design time every thing work fine, but at runtime I always have a
DbExpress Unsupported Operation.
I don't understand why.
With the DbExpress demo provided with Rb7 I exacly have the same problem.

Could anyone help me ?

Thanks.

Marc

digitalmetaphors

What does the SQL look like? Hit Ctrl-Click over the lowest child level
detail dataview. This is the magic linking SQL that is sent to the interbase
server. The other SQL (SQL view in query deisgner) is specific to the
dataview if it is in a state where it is not visually linked. Now, let's try
to determine if the SQL is valid by taking RB out of the picture. Copy this
SQL into a TIBQuery using IBExpress on a form and see if it executes. Then
try it on a TSQLClientDataset using dbExpress. If the queries run, then dump
the data to a TDBGrid to verify the results.

If you still get the error, then can you construct a query using our
dbExpress demo and use the Employee.gdb sample database? We inserted the RB
end user tables on our Employee.gdb database. Send us an ascii format report
template (RTM file) with the dataviews created with our demo and we should
be able to view the SQL and execute it on our Employee.gdb database. Send it
to support@digital-metaphors.com


Cheers,

Jim Bennett
Digital Metaphors

digitalmetaphors

Yes, RB needs a non-unidirectional dataset. This means that you have to use
the TSQLClientDataset which can be traversed in either direction. The other
datasets are unidirectional and can't be used with RB. That is most likely
the problem you are encountering. See the release notes in the release.doc
in the installation folder for more information.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Hi Jim,
well the dbexpress demo can be built but I can't do anything with it :
as I want to launch the report explorer I have a dbexpress unsupported
operation.
I inserted too end user tables on my employee.gdb.
I also have another test database but the problem still persists.
The IBExpress demo works perfectly and it really seems that there is
a problem with dbExpress.
I tried TSimpleDataSet and the combination of TSqlQuery+TDataSetProvider and
TClientDataSet.
In the first way I can't launch the report explorer at all.
In the second way I can obtain the report designer but if I want to build a
SQL I get the error.

As I am a new user of RB maybe I forget something.

Regards.

Marc

rbuser

I know Jim, sorry for my poor english, but the problem is still the same

rbuser

I send an error, neither dbExpress and IBExpress demos provided by RB work.

IbExpress :
TdaMetaData.GetFieldsfromDataSet: Unable to open dataset: X error message
when trying to build a query.

dbExpress :
Unsupported Operation

So actually no Interbase demo work.

digitalmetaphors

In Delphi 7, using RB 7.01's installed demos, both the IBExpress and
DBExpress demos worked just fine. What version of Interbase are you using?
Cna you execute a simple query by itself on a TForm on the Employee sample
database without RB involved? We run our demos before each release of RB and
I also ran them today to reverify that they work. I simply opened the demo
projects and ensured that the connection object pointed to the Employee.gdb
where I have the end user tables defined. Did you run the SQL script as it
is formatted in our Interbase demo directory? The SQL script should not have
to be modified to get our demos to work correctly.

To clarify, I should mention that I was talking about using
TSQLClientDataset, but I forgot that in D7, the TSimpleDataset replaces the
TSQLClientDataset:) You are using the correct data access components.


Cheers,

Jim Bennett
Digital Metaphors

rbuser

I found something :
RB dbExpress demo :
If I change the properties of the TSimpleDataSet.DataSet.CommandType to
ctQuery (and the CommandText to the right Sql text)
I can now launch the report explorer.
But that's all.
If I want to build a sql no tables are available (at run time) and (at
Design Time) the tables are now available but an
"unable to open DataSet..." message appears if I want to choose fields.

PS : An ugly message appear when I close the designer (unable to focus a
window...)

Marc

rbuser

Interbase verion : 6.5
No problem tu execute a simple query on employee.gdb
Yes I run the SQL script from the Interbase demo directory.
It's really strange, after hours of work I still don't understand why
at design time it's ok and not at run time -> always those enervating
messages : unsupported operation (with dbExpress) and Unable to open
dataset (with IBExpress) !

Thanks

Marc

rbuser

It appears that RB7 doesn't work at all with Interbase 6.5.
All the RB demo with Interbase don't work at run-time.
(the BDE one provide no error message but no tables are available for
building a query)
I'am very disappointed and a bit angry.

Regards

Marc

rbuser

I think I isolated the bug in daMetaData.pas

*** dbExpress Unsupported Operation exception ***

procedure TdaMetaData.GetTables(aTables: TList);
begin

if (FCache.Active) and (FCache.Populated) then
FCache.GetTables(aTables)

else if (FDataDictionary = nil) then
begin
GetTablesFromSession(aTables);//here if I do not use a datadictionnary

FCache.SetTables(aTables);
end

else if (FDataDictionary <> nil) then
begin
GetTablesFromDataDictionary(aTables);

if (FDataDictionary.ValidateTableNames) then
ValidateTableNames(aTables);//here if I use a DataDictionnary

FCache.SetTables(aTables);
end;

end; {procedure, GetTables}


Point to this procedure :

{ TdaMetaData.GetTablesFromSession }
procedure TdaMetaData.GetTablesFromSession(aTables: TList);
var
lTableNames: TStrings;
liIndex: Integer;
lTable: TdaMetaTable;
lsRawTableName: String;
lsTableName: String;
lsTableAlias: String;
lsExtension: String;
lbFileBased: Boolean;
lDataSet: TdaDataSet;
begin

lTableNames := TStringList.Create;

FSession.GetTableNames(FDatabaseName, lTableNames);//Here dbExpress
unsupported operation

Regards

Marc

rbuser

Sorry Jim, I think it is a Delphi bug (my version is 7 build 4.453)

TSqlConnection.GetTableNames does'nt work.
(I haven't try yet with TIbConnection)
Have you heard about this ?
Have you got an idea to resolve this problem ?

Sorry again.
Marc

digitalmetaphors

No, I have not heard of this behavior. Can you connect to the Employee.gdb
sample database using dbExpress without RB components being involved? I am
using D7.0 build 4.453 as well.

Using RB's dbExpress end user demo, set Designer.AllowDatasettingsChange to
true. Disconnect the datadictionary and set UseDataDictionary to false. Then
at runtime, go into the Data tab and check the datasettings to be sure that
it is pointing to the correct SQLConnection on the form. It should be called
euSQLConnection. Are the Designer.Datasettings consistent at runtime vs.
delphi design time?


Cheers,

Jim Bennett
Digital Metaphors

rbuser

Yes I can connnect correctly to employee.gdb without RB.
Yes the designer.datasettings are ok at runtime and design time.

The bug :
If I build a small app using a TSqlConnection pointing to employee.gdb and I
do this below :

procedure TForm1.Button1Click(Sender: TObject);
begin
sqlconnection1.GetTableNames(listbox1.items,false);
end;

I have a dbexpress exception 'unsupported operation'.
I think it is a bug in the delphi SqlExpr.pas openschema procedure.
I don't know yet how to handle this.
You should have other customers with the same problem.

the procedure TdaSQLSession.GetTableNames(const aDatabaseName: String;
aList: TStrings);
in daDbExpress have to be modified or the delphi sqlexpr.pas

Thanks
Regards
Marc

rbuser

I rapidly modified The GetTableNames procedure in daDbExpress.pas like below
for tests.
The dbExpress demo with Interbase now run (I can see the tables) but :
The DataDictionary doesn't work at all and (more important)
It is impossible to generate a query !
It seems that the SQL generate is completly out (ie :
)(many spaces).
If I choose a table and click on Finish on the wizard the result is a
collection of exceptions...
What's happen !!!

Marc


procedure TdaSQLSession.GetTableNames(const aDatabaseName: String; aList:
TStrings);
var
lSQLConnection: TSQLConnection;
Sqlds :TSqlDataSet;

const
TableSQL =
'SELECT * FROM RDB$RELATIONS ' +
'WHERE ' +
' (RDB$SYSTEM_FLAG <> 1 OR RDB$SYSTEM_FLAG IS NULL) AND ' +
' (RDB$VIEW_BLR IS NULL) ' +
' ORDER BY RDB$RELATION_NAME';

begin
{get the database}
lSQLConnection := TSQLConnection(GetDatabaseForName(aDatabaseName));

{connection must be active to get table names}
if not lSQLConnection.Connected then
lSQLConnection.Connected := True;

{get list of table names from a table object}

With lSQLConnection do
If Connected then
Begin
If AnsiCompareText(DriverName,'INTERBASE')=0 then
Begin
Sqlds :=TSqlDataSet.Create(Nil);
Try
With Sqlds do
begin
Close;
SqlConnection:=lSQLConnection;
CommandType :=ctQuery;
CommandText :=TableSql;
Open;
First;
While Not EOF do
Begin
aList.Add(FieldByName('RDB$RELATION_NAME').AsString);
Next;
end;
end;

Finally
SqlDs.Close;
FreeAndNil(SqlDs);
end;
end{If INTERBASE}
Else GetTableNames(aList, False);
end;{With lSQLConnection}

{ if lSQLConnection.Connected then
lSQLConnection.GetTableNames(aList, False);}

end; {procedure, GetTableNames}

rbuser

Sample of SQL statement generated :

SELECT * FROM CUSTOMER__________________________ WHERE ''c'' <> ''c''

EDatabaseError : Cannot open.
I spend too much time modifying RB, is there another way to find
information, help or *.pas already debugged ?

Thanks.
Marc

digitalmetaphors

Are you using the TSQLConnection's connection builder?

I dropped a TSQLConnection on a form. I added a button and a memo. I
configured the connection object to point to the employee.gdb Interbase
database. In the button click event handler, I coded

procedure TForm1.Button1Click(Sender: TObject);
begin
MySQLConnection.GetTableNames(Memo1.Lines, False);
end;


When I run this project, it works to populate the memo with the table names
when I press the button. I don't think it is a bug in their code, but rather
a configuration issue or you have different versions of the dlls that
dbExpress relies on for the drivers. I installed the latest Interbase
version that is on my Delphi 7 install cd.

There are two dlls that are named for use in my dbxDrivers.ini file for
Interbase:
GDS32.dll is dated 11/28/2001 in Windows/System32 directory.
dbexpint.dll is dated 8/9/2002 in my Delphi7/bin directory.

Here are the property settings from my dfm for the TSQLConnection object.

object MySQLConnection: TSQLConnection
ConnectionName = 'IBEndUser'
DriverName = 'Interbase'
GetDriverFunc = 'getSQLDriverINTERBASE'
LibraryName = 'dbexpint.dll'
LoginPrompt = False
Params.Strings = (
'DriverName=Interbase'
'BlobSize=-1'
'CommitRetain=False'

'Database=c:\program files\common files\borland shared\data\emplo' +
'yee.gdb'
'LocaleCode=1033'
'Password=masterkey'
'RoleName=RoleName'
'ServerCharSet=ASCII'
'SQLDialect=1'
'Interbase TransIsolation=ReadCommited'
'User_Name=sysdba'
'WaitOnLocks=True')
VendorLib = 'GDS32.DLL'
Left = 64
Top = 32
end

My dbxDrivers.ini file looks like this for Interbase:

[Interbase]
GetDriverFunc=getSQLDriverINTERBASE
LibraryName=dbexpint.dll
VendorLib=GDS32.DLL
BlobSize=-1
CommitRetain=False
Database=database.gdb
ErrorResourceFile=
LocaleCode=0000
Password=masterkey
RoleName=RoleName
ServerCharSet=
SQLDialect=1
Interbase TransIsolation=ReadCommited
User_Name=sysdba
WaitOnLocks=True



Cheers,

Jim Bennett
Digital Metaphors