Delphi dbgrid is not showing mysql varchar columns

Question

I'm using delphi XE2 and working on a mysql database project. I have a mysql database which has a table consisting of four columns.
enter image description here
I have two sample rows in this table.

enter image description here

I'm using a TDatabase, TQuery, TDatasource and a TDBGrid to connect to the databse with following source code:

dbgrid1.DataSource :=DataSource1 ;  
datasource1.DataSet :=Query1 ;  
database1.DatabaseName :='personDatabase';  
database1.AliasName :='mysqlodbc';  
database1.LoginPrompt :=false;  
database1.Connected :=true;  
query1.DatabaseName :=database1.DatabaseName;  
query1.SQL.Clear;  
query1.SQL.Add('select * from persondb.person;');  
query1.Active :=true;

the problem is when I try to select all the columns and rows (with select * from persondb.person) and show them in a dbgrid, varchar columns are not being displayed and I only get the two int columns.

enter image description here
It's like varchar columns are not show-able for example the sql select fname from persondb.person will result in two single celled row in dbgrid. the result is the same with sql select fname, lname from persondb.person which is not even logical (cause I expected a 2X2 empty table).

enter image description here

I also changed the character set of the database which was utf8 to latin1 and thought maybe the problem is there but no luck there too.
I googled hours and not even a similar problem to mine. but I leaned that the normal behavior to expect is dbgrid showing varchar fields as (memo) which everyone is trying to overcome.
so any help is appreciated.

You are using BDE for database access which is not good. Try using dbExpress instead. — LightBulb
@lightBulb, I read a little about dbExpress and found some articles about it, there is a lot to learn about dbExpress components. but right now I don't have much time and need a more quick way to overcome this problem. but thanks anyway. — Drust
@Drust, learning dbExpress requires more or less the same effort of learning BDE. BDE is deprecated for ~10 years, while dbExpress is the built-in Delphi current database access layer. There are more 3rd party also actively supported, so simply don't choose BDE. — jachguate
@Drust add this line before your first published line of code: DBGrid1.Columns.Clear; and let me know if that resolves your issue to publish an answer explaining the whole thing. — jachguate
@jachguate, It didn't worked, I suppose I should go with dbExpress and abandon the useless BDE! thank you. — Drust

user3215689 user3215689 · Accepted Answer · 2014-01-20T15:30:16

It happened to me view days ago. Using dbExpress or Ado connection instead of BDE is not a good idea, because it needs more time to learn and change the code. I use oracle (maybe similiar case with mysql). You should check your database structure.

In Oracle 11, dbgrid cannot display all columns with VARCHAR2 data type and CHAR unit. dbgrid just display data with BYTE unit. but in Oracle 9i, everything's fine.

So, the solution is change the unit (char to byte). Here is the sql statement for oracle :

ALTER TABLE USERX.TABLENAME MODIFY (COLUMNNAME VARCHAR2(50 BYTE));

Delphi dbgrid is not showing mysql varchar columns

1 Answers