Cannot convert type error when using GetFieldNames

[chrispaskins]

First some background: My Delphi 7 client-server database app tries to work with whatever database the user specifies, be it Interbase, MS-SQL or Oracle. Things worked fine until I tried to run it on MS Server 2003 against a MS-SQL 2000 database. I would instantly get an A/V but that problem disappeared when I installed the latest version of dbexpsda.dll.

Happy days – and while I’m updating dll’s I thought I’d grab the latest dbexpoda.dll v2.50 in case Oracle type nasties that may affect me had similarly been fixed.

To make my multiple database type app work I need a line like:

Code: Select all

myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(False));

when I’m connecting to Oracle otherwise I get type mismatch problems with my TFloatFields permanent fields. (Interbase and MSSQL Server cope fine with TFloatFields).

Now the problem: I’ve noticed that my call to:

Code: Select all

myCRSQLConnection.GetFieldNames(tname, fieldNameList);

now returns an empty list of field names when using this new dbexpoda.dll. A trapped exception apparently is raised within the procedure with the message 'Cannot convert type.'

I don’t get this problem if I replace the v2.50 dbexpoda.dll with my faithful old v1.87 dbexpoda.dll. Over the years I’ve got quite familiar with 'Cannot convert type’ errors and I in this case found that setting coEnableBCD Option to True fixed GetFieldNames. Unfortunately, of course, all my TFloatFields kick up a fuss if I do that!

This GetFieldNames behaviour happens with Oracle 10g, 9.2, 8.1.7. PS. Please don’t ask whether I can change the database schema because we’ve got a large installed user base, also I’m keeping my TFloatFields because a quick search shows I’ve got over 1,700 of them!!

How can I get GetFieldNames to work without switching coEnableBCD option to true like I could in the good old days?? Until then I’ll use the ‘old’ dbexpoda.dll.

[Paul]

We cannot reproduce your problem with Delphi 7, Delphi 7 Update Pack 1.1, Oracle 9.2.0.1, 8.1.7, 10.1.0.2.0, DbxOda 2.50.5 Standard. Please try to run the following code.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  coEnableBCD = TSQLConnectionOption(102); // boolean
var
  myCRSQLConnection: TCRSQLConnection;
  pList: TStringList;
begin
  myCRSQLConnection := TCRSQLConnection.Create(nil);
  myCRSQLConnection.DriverName := 'Oracle (Core Lab)';
  myCRSQLConnection.GetDriverFunc := 'getSQLDriverORA';
  myCRSQLConnection.LibraryName := 'dbexpoda.dll';
  myCRSQLConnection.VendorLib := 'OCI.DLL';
  myCRSQLConnection.LoginPrompt := false;
  myCRSQLConnection.Params.Values['DriverName'] := 'Oracle (Core Lab)';
  myCRSQLConnection.Params.Values['DataBase'] := 'ora';
  myCRSQLConnection.Params.Values['User_Name'] := 'scott';
  myCRSQLConnection.Params.Values['Password'] := 'tiger';

  myCRSQLConnection.Connected := True;
  myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(False));
  pList := TStringList.Create;
  myCRSQLConnection.GetFieldNames('EMP', pList);
  Memo1.Text := pList.Text;
  pList.Free;
end;

Expected result is

EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
DEPTNO

[chrispaskins]

Hi Paul! Yep, I got those results so with renewed hope and interest I then spent a while pulling my program apart and discovered that if you execute any query using the connection before calling GetFieldNames you'll get the problem I mentioned. At first I thought the query might have been at fault but no, it seems that any query statement will do!

Try the following, all I've done to your code is add a TSQLQuery component and executed it before calling GetFieldNames, once with EnableBCD option set to False, once with it set to True.

3 memo controls show the results.

Code: Select all

procedure TForm1.Button1Click(Sender: TObject);
const
  coEnableBCD = TSQLConnectionOption(102); // boolean
var
  myCRSQLConnection: TCRSQLConnection;
  pList: TStringList;
  tq : TSQLQuery;
begin
  myCRSQLConnection := TCRSQLConnection.Create(nil);
  myCRSQLConnection.DriverName := 'Oracle (Core Lab)';
  myCRSQLConnection.GetDriverFunc := 'getSQLDriverORA';
  myCRSQLConnection.LibraryName := 'dbexpoda.dll';
  myCRSQLConnection.VendorLib := 'OCI.DLL';
  myCRSQLConnection.LoginPrompt := false;
  myCRSQLConnection.Params.Values['DriverName'] := 'Oracle (Core Lab)';
  myCRSQLConnection.Params.Values['DataBase'] := 'ora';
  myCRSQLConnection.Params.Values['User_Name'] := 'scott';
  myCRSQLConnection.Params.Values['Password'] := 'tiger';
  myCRSQLConnection.Connected := True;
  myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(False));

  pList := TStringList.Create;
  myCRSQLConnection.GetFieldNames('EMP', pList);
  Memo1.Text := pList.Text;

Now for my new bit:

Code: Select all

  tq := TSQLQuery.Create(nil);
  tq.SQLConnection := myCRSQLConnection;
  tq.SQL.Add('SELECT 1 FROM DUAL');
  tq.Open;
  tq.close;

  pList.Clear;
  myCRSQLConnection.GetFieldNames('EMP', pList); //oh dear!!
  Memo2.Text := pList.Text;

  myCRSQLConnection.Connected := False;
  myCRSQLConnection.Connected := True;
  myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(True));

  tq.Open;
  tq.close;

  pList.Clear;
  myCRSQLConnection.GetFieldNames('EMP', pList); // phew! that worked!
  Memo3.Text := pList.Text;

  tq.free;
  pList.Free;
end;

With faithful old dbexpoda.dll v1.87 I get expected field names of the EMP table in all three memo controls.

With dbexpoda.dll v2.50.5 (and v2.10 for that matter) memo2 remains empty - if you "Stop on Delphi Exceptions" you'll get the exception as well. I hope you get the same outcome!

Happily the pressure's off now - I steer clear of GetFieldNames and use a TSQLDataSet component instead but it's been an interesting episode.

Thanks for your quick response and pushing me into investigating further.

[Paul]

We reproduced your problem and now we are working with it.
As soon as we solve the problem, we'll let you know.

[chrispaskins]

My programs are acting oddly in various places with the latest dbexpoda.ddl. I'm going to go back to my old old version of the dll and hope for the best.

It would appear TSQLDataSet seems to be similarly affect by the problem:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
const
  coEnableBCD = TSQLConnectionOption(102); // boolean
var
  myCRSQLConnection: TCRSQLConnection;
  tq : TSQLQuery;
  mySQLDataSet : TSQLDataSet;

  procedure doSQLDataSetOuputToMemo(memo: TMemo);
  begin
    memo.Lines.Clear;
    try
      mySQLDataSet.Open;
      while not mySQLDataSet.Eof do
      begin
        memo.Lines.Add(mySQLDataSet.FieldByName('COLUMN_NAME').asString);
        mySQLDataSet.Next;
      end;
    except
    end;
    mySQLDataSet.Close;
  end;

begin
  myCRSQLConnection := TCRSQLConnection.Create(nil);
  myCRSQLConnection.DriverName := 'Oracle (Core Lab)';
  myCRSQLConnection.GetDriverFunc := 'getSQLDriverORA';
  myCRSQLConnection.LibraryName := 'dbexpoda.dll';
  myCRSQLConnection.VendorLib := 'OCI.DLL';
  myCRSQLConnection.LoginPrompt := false;
  myCRSQLConnection.Params.Values['DriverName'] := 'Oracle (Core Lab)';
  myCRSQLConnection.Params.Values['DataBase'] := 'ora';
  myCRSQLConnection.Params.Values['User_Name'] := 'scott';
  myCRSQLConnection.Params.Values['Password'] := 'tiger';
  myCRSQLConnection.Connected := True;
  myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(False));

  mySQLDataSet := TSQLDataSet.Create(nil);
  mySQLDataSet.SQLConnection := myCRSQLConnection;
  mySQLDataSet.SetSchemaInfo(stColumns, 'EMP', '');

  doSQLDataSetOuputToMemo(memo1);

  tq := TSQLQuery.Create(nil);
  tq.SQLConnection := myCRSQLConnection;
  tq.SQL.Add('SELECT 1 FROM DUAL');

  tq.Open;
  tq.close;

  doSQLDataSetOuputToMemo(memo2);

  myCRSQLConnection.Connected := False;
  myCRSQLConnection.Connected := True;
  myCRSQLConnection.SQLConnection.SetOption(coEnableBCD, Integer(True));

  tq.Open;
  tq.close;

  doSQLDataSetOuputToMemo(memo3);

  tq.free;
  mySQLDataSet.Free;
  myCRSQLConnection.Free;
end;

I didn't realise I was using so many different methods to extract schema information!!