[solved] MySQL table field defined as Char(1) lazarus / 16-Databases ---------------------------------------------------------------------- jcmontherock · 2022-05-03 09:24 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i String := SQLQuery.Fields[Column].AsString This does not work: the return value is empty. There is no "AsChar" property. Is somebody knows how to do that ? ---------------------------------------------------------------------- Zvoni · 2022-05-03 09:27 What's your SELECT look like? ---------------------------------------------------------------------- jcmontherock · 2022-05-03 09:35 SELECT * FROM datable; ---------------------------------------------------------------------- dseligo · 2022-05-03 09:54 Quote from: jcmontherock on May 03, 2022, 11:35:36 amSELECT * 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2022-05-03 09:58 Quote from: jcmontherock on May 03, 2022, 11:24:25 amThis 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 · 2022-05-03 09:58 Quote from: dseligo on May 03, 2022, 11:54:07 amQuote from: jcmontherock on May 03, 2022, 11:35:36 amSELECT * 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MyString:=SQLQuery.FieldByName('MyCharField').AsString; ---------------------------------------------------------------------- dseligo · 2022-05-03 10:11 Quote from: Zvoni on May 03, 2022, 11:58:48 amQuote from: dseligo on May 03, 2022, 11:54:07 amQuote from: jcmontherock on May 03, 2022, 11:35:36 amSELECT * 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i MyString:=SQLQuery.FieldByName('MyCharField').AsString;  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 · 2022-05-03 10:15 Quote from: dseligo on May 03, 2022, 12:11:30 pmYou 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 · 2022-05-03 12:38 .FieldByName('MyCharField').AsString; does not work better. ---------------------------------------------------------------------- Zvoni · 2022-05-03 13:29 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 · 2022-05-03 14:12 The "Trinity" is OK and as well, all other fields value shown. ---------------------------------------------------------------------- LacaK · 2022-05-04 17:50 What is charset/collation of column? ---------------------------------------------------------------------- jcmontherock · 2022-05-05 09:21 "utf8mb4" and "utf8mb4_general_ci". Fields are not empty: they contains one letter. I see them with HeidiSQL. ---------------------------------------------------------------------- Zvoni · 2022-05-05 10:05 And did you set the Charset of the Connection? https://www.freepascal.org/docs- html/fcl/sqldb/tsqlconnection.charset.html Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ....MySQLCo nnection.User:='UserName';MySQLConnection.Password:='SomePassword';MyS QLConnection.CharSet:='utf8mb4';MySQLConnection.Open; ---------------------------------------------------------------------- jcmontherock · 2022-05-05 12:58 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. ---------------------------------------------------------------------- Zvoni · 2022-05-05 13:06 Quote from: jcmontherock on May 05, 2022, 02:58:24 pmI put "utf8" in my connection it did not work, but with "utf8mb4" now it works. I will explore to learn what is the difference between these 2 encoding mode.  Thanks a lot. utf8 can store up to 3 byte character utf8mb4 can store up to 4 byte characters --> utf8 is a subset of utf8mb4 The question is: Do you need utf8mb4? and check the charset in your my.ini/my.cnf they should be equal ---------------------------------------------------------------------- jcmontherock · 2022-05-05 13:18 The strange thing is that the table was created with utf8, but it seems that with mysql 8 the default encoding is utf8mb4, even if you set utf8 at table creation, so one char takes max. 4 bytes instead 3 in utf8. see: https://dev.mysql.com/blog-archive/mysql-8-0-when-to-use- utf8mb3-over-utf8mb4/ ---------------------------------------------------------------------- Zvoni · 2022-05-05 13:46 Quote from: jcmontherock on May 05, 2022, 03:18:41 pmThe strange thing is that the table was created with utf8, but it seems that with mysql 8 the default encoding is utf8mb4, even if you set utf8 at table creation, so one char takes max. 4 bytes instead 3 in utf8. see: https://dev.mysql.com/blog-archive/mysql-8-0-when-to-use- utf8mb3-over-utf8mb4/ That's why i said to check your my.ini/my.cnf AFAIK, the charset can be set there, too. See: https://stackoverflow.com/questions/3513773/change-mysql-default- character-set-to-utf-8-in-my-cnf