[solved] MySQL table field defined as Char(1)

[jcmontherock]

Hello,
I have a MySQL table which contains a field defined as Char(1). I use TSQLQuery to select data. To get the field value I am using:

Code: Pascal  [Select][+][-]

1. String := SQLQuery.Fields[Column].AsString
2.  

This does not work: the return value is empty. There is no "AsChar" property. Is somebody knows how to do that ?

[Zvoni]

What's your SELECT look like?

[jcmontherock]

SELECT * FROM datable;

[dseligo]

SELECT * FROM datable;

Now Zvoni will tell you that you shouldn't use 'select *'. 

But that isn't your problem.
I use char(1) all the time and it works perfectly well.
Show more code, this surely isn't your code:

Code: Pascal  [Select][+][-]

1. String := SQLQuery.Fields[Column].AsString

You don't have variable named 'String' and you are missing semicolon at the end.
So, show your actual code, it will help to spot your error.

[dseligo]

This does not work: the return value is empty.

One more thing: are you absolutely sure you have something written in the that field? Maybe the field is empty in the database (i.e. you didn't insert anything in that field).

[Zvoni]

SELECT * FROM datable;

Now Zvoni will tell you that you shouldn't use 'select *'. 

Exactly because of that (and other reasons)
SQLQuery.Fields[Column] is ordinal Access (Column being integer)
Do you know that your "Char(1)" Field is the "column"-th Field?
Remember, ordinal Access is zero-based

Code: Pascal  [Select][+][-]

1. MyString:=SQLQuery.FieldByName('MyCharField').AsString;
2.  

[dseligo]

SELECT * FROM datable;

Now Zvoni will tell you that you shouldn't use 'select *'. 

Exactly because of that (and other reasons)
SQLQuery.Fields[Column] is ordinal Access (Column being integer)
Do you know that your "Char(1)" Field is the "column"-th Field?
Remember, ordinal Access is zero-based

Code: Pascal  [Select][+][-]

1. MyString:=SQLQuery.FieldByName('MyCharField').AsString;
2.  

You are right of course, but I wanted to say that it also has to work with 'select *' and accessing fields by their indexes. If it doesn't, he made an error.
Of course, it is easier to maintain and have less errors if you use 'select field1, field2, ...' and then 'FieldByName('field1').AsString'.

[Zvoni]

You are right of course, but I wanted to say that it also has to work with 'select *' and accessing fields by their indexes. If it doesn't, he made an error.
Of course, it is easier to maintain and have less errors if you use 'select field1, field2, ...' and then 'FieldByName('field1').AsString'.

Correct!
Though, Query.FieldByName('MyCharField') still works with "SELECT * FROM Table"
As a Rule of Thumb: Avoid ordinal/indexed Field-Access.
Your column is the 6th Column, you access it with Query.Fields[5],
and later another DBA INSERTS a new Column in Position 4, and KABOOM!

EDIT: To explain my aversion to "SELECT * FROM":
Imagine you have something like an Address-Book (or User-Accounts).
You use "SELECT * FROM" all through your code.
Everything looks nice.

On a sunny day, another developer decides: "Hey, let's add a BLOB-Column, so users can upload a picture of themselves"

I can guarantee your Hotline-phone will not stand still ("Why is the network so slow today?")

Another example is the company i work for: Our Mainframe is an IBM iSeries, which runs a DB2 with some 5000 Tables (Yes, you read it right: "Five Thousand")
I often have to write queries spanning some 10-20 Tables (INNER JOIN, LEFT JOIN, ROW_NUMBERS, you name it), and it's a RARITY in our System if a Table has LESS (!!!) than 50 Columns.
Our recordholder is a Table with some 400 Columns......

Bottom Line: At the latest when you have to join multiple tables, a SELECT * FROM will blast your performance to smitherens

[jcmontherock]

.FieldByName('MyCharField').AsString;

does not work better.

[Zvoni]

Then either your Field is empty, the Field has a different name or your SELECT doesn't work.
Have you checked the Result of your SELECT in MySQL Workbench?

EDIT: You have setup the TSQL-Trinity correctly (Connection, Transaction, Query)?

[jcmontherock]

The "Trinity" is OK and as well, all other fields value shown.

[LacaK]

What is charset/collation of column?

[jcmontherock]

"utf8mb4" and "utf8mb4_general_ci".
Fields are not empty: they contains one letter. I see them with HeidiSQL.

[Zvoni]

And did you set the Charset of the Connection?
https://www.freepascal.org/docs-html/fcl/sqldb/tsqlconnection.charset.html

Code: Pascal  [Select][+][-]

1. ....
2. MySQLConnection.User:='UserName';
3. MySQLConnection.Password:='SomePassword';
4. MySQLConnection.CharSet:='utf8mb4';
5. MySQLConnection.Open;
6.  

[jcmontherock]

I put "utf8" in my connection and it did not work for fields defined with char(1). All other fields, including those with accented char, were ok, but now with "utf8mb4" it all works.
I will explore to learn what is the difference between these 2 encoding mode.

 Thanks a lot.