Name of field with more than 32 characters

webpac · Post by **webpac** » Mon 08 Oct 2007 16:27

Hello,

When I retrieve the fields names, when the name is 32 characters long, the last character is a wrong character square.

I tried with dbexpsda.dll versions 2.50.4.0 and 4.1.0.3.0.

I tried all properties of the composants and no one works.

Code: Select all

procedure Test;
var
  i : Integer;
  sqlTable : TSQLTable; 
begin
  sqlTable := TSQLTable.Create( Self );
  sqlTable.SQLConnection := FSQLConnection;
  sqlTable.TableName := 'TEST';
  sqlTable.BeforeOpen := SQLTableBeforeOpen;
  sqlTable.Open;

  for i := 0 to sqlTable.FieldCount - 1 do
  begin
    ShowMessage( sqlTable.Fields[ i ].Name );
    ShowMessage( sqlTable.Fields[ i ].DisplayName );
    ShowMessage( sqlTable.Fields[ i ].DisplayText );
    ShowMessage( sqlTable.Fields[ i ].DisplayLabel );
    ShowMessage( sqlTable.Fields[ i ].FullName );
    ShowMessage( sqlTable.Fields[ i ].FieldName );

    ShowMessage( sqlTable.FieldList[ i ].Name );
    ShowMessage( sqlTable.FieldList[ i ].DisplayName );
    ShowMessage( sqlTable.FieldList[ i ].DisplayText );
    ShowMessage( sqlTable.FieldList[ i ].DisplayLabel );
    ShowMessage( sqlTable.FieldList[ i ].FullName );
    ShowMessage( sqlTable.FieldList[ i ].FieldName );

    ShowMessage( sqlTable.FieldDefs[ i ].Name );
    ShowMessage( sqlTable.FieldDefs[ i ].DisplayName );

    ShowMessage( sqlTable.FieldDefList[ i ].Name );
    ShowMessage( sqlTable.FieldDefList[ i ].DisplayName );
end;

Thanks for your help.

Antaeus · Post by **Antaeus** » Tue 09 Oct 2007 06:51

This is a dbExpress limitation. Try to rename this field to make its name shorter.

webpac · Post by **webpac** » Tue 09 Oct 2007 07:04

Antaeus wrote:This is a dbExpress limitation. Try to rename this field to make its name shorter.

I can't rename this field, the field is created by another application.
SQL Server uses fields with 32 characters, that's logical the driver can retrieve its name.
This bug blocks the utilization of my application.
When this bug will be fixed ? I hope quickly.

Thanks.

webpac · Post by **webpac** » Wed 10 Oct 2007 07:27

I thnik, I have to explain my problem :
The user configures the connection to a database.
My application displays the list of tables in the database.
He chooses one or more tables in the list.
My application displays the tables selected and all of theirs fields.
The user chooses fields to make joins.
The user chooses fields to display.
My application create a SQL query with fields to display in SELECT section, tables in FROM section and joins in WHERE section.

This is the fonctionnality simplified of the application.

The problem is when the name of a field is longer 32 characters, the application retrieve what is the square character at the end of the name.

I'm not the owner of the database, it be created by another application and I can't modify it, I just do read-only querys.

Do you think this bug will be fixed ? I'm blocked and users ask me when the bug'll be fixed.

Antaeus · Post by **Antaeus** » Wed 10 Oct 2007 11:26

This cannot be fixed in the driver, as this is the restriction of the dbExpress technology.

You can request column names of a table from system views:

Code: Select all

  SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name='emp'

webpac · Post by **webpac** » Wed 10 Oct 2007 12:38

The request column names give a trunqued name, the 32nd caracter is missing.

I understand that the bug can't be fixed in the driver, but isn't it CoreLab the ower of the dbExpress technology ?

Antaeus · Post by **Antaeus** » Wed 10 Oct 2007 14:31

dbExpress was designed by Borland, and now it is developed by CodeGear. Core Lab just offers extended dbExpress drivers.

Have the provided code helped you to solve this problem?

webpac · Post by **webpac** » Thu 11 Oct 2007 06:40

Antaeus wrote:dbExpress was designed by Borland, and now it is developed by CodeGear. Core Lab just offers extended dbExpress drivers.

Alright, I made a mistake.

Antaeus wrote:Have the provided code helped you to solve this problem?

No, the code didn't help me, the name was trunqued, the 32nd caracter is missing.

So, I made a code, it verify if no square caracter ( chr(1) ) is inserted in the name of a field, if one is found, the application ask to the user to rename it.
It's not a properly solution, he had to know the field name exactly but the user is not yet blocked.

Antaeus · Post by **Antaeus** » Thu 11 Oct 2007 09:37

I tried two ways to retrieve field names from a table. Your way:

Code: Select all

  SQLQuery1.SQL.Text := 'SELECT * FROM very_long_field_names';
  SQLQuery1.Open;
  for i := 0 to SQLQuery1.FieldCount - 1 do begin
    st.Add(SQLQuery1.Fields[i].FieldName);
  end;
  ShowMessage(st.Text);

and the way with the system view:

Code: Select all

SQLQuery1.SQL.Text := 'SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE Table_Name=''very_long_field_names''';
  SQLQuery1.Open;
  st :=TStringList.Create();
  while not SQLQuery1.Eof do begin
    st.Add(SQLQuery1.Fields[0].AsString);
    SQLQuery1.Next;
  end;

The first way really gives the wrong result, the second way works properly. All field names are reflected correctly.

This restriction was overcame in the newer versions of dbExpress. This problem is not reproduced with BDS 2006 and Delphi 2007.

webpac · Post by **webpac** » Thu 06 Mar 2008 17:01

Hi,

is it possible to update dbexpress version and keep Delphi Version 7 without update ?

Antaeus · Post by **Antaeus** » Fri 07 Mar 2008 07:26

I do not know whether it is possible. Please refer to Borland\CodeGear support for information.