i am saving a string, that, you guessed it, looks like a time to my mysql database in a “varchar(45)” type field
when it saves it looks like this:
2:40 PM
when i pull it back up, it looks the same. Is that some kind of DB column break or something?
My guess is that your database is not handling the string encoding correctly.
I remember reading that there are some changes on time formats and instead of using a regular space between the time and AM/PM now sometimes a non-breaking space is used, so the UTF8 value is multi-character.
Any chance you are aware of a “parse” or something that might remove those characters?
Make sure that your string is UTF-8 and then tell the database that the content you’ll be sending is UTF-8 encoded.
db.SQLExecute("SET NAMES 'utf8'")
when i do a charactercount, it comes up with 7, which ought to be correct
weird
Of course it comes up with 7:
2:40 PM
is 7 characters long.
Sorry, maybe multi-character is not the correct definition.
Is 1 character but the UTF use more space.
Maybe multi-byte is the correct word?
That ‘space’ between number and PM is 3 bytes long, instead of 1 byte for the others.
U+202F e2 80 af NARROW NO-BREAK SPACE
Yes, a multi-byte UTF8 character.
Will you ever need to do calculations on such value? Select with an order on said time value?
Would be better to save time as digits such as “14:40:00”
You can then easily read it into a Datetime object or split into integers for hour, minute, seconds.
It is never a good idea to store a localized version of a value.
2:40 PM is a localized time value.
i literally want a “timestamp” of the time of day a test is run on a patient. it will only be stored and really probably never changed/updated or searched. it can be stored as a text string, in fact thats what i am trying to do.
Do you need to have PM/AM on it or is 14:23:56 something you can use?
Do you want to solve the encoding for your database (to avoid future problems) or just want to use ‘regular’ (single byte characters) strings?
Seems like you are re-inventing the wheel. MySQL has a timestamp column type for that kind of use.
Feel free to store it as you want…
It seems like the timestamp function is quirky- maybe sets itself or resets itself automatically (when you don’t ask it to.)
I did play with that a little. I’d like the am/pm to be displayed (currently making a work around… aka reinventing the wheel)
I know some my questions are so beginner level- am sorry for that …. Ugh
Your database, and connection, if set correctly to UTF-8 mode would make such “chars” magically disappear.
For the MySQL part:
CREATE DATABASE mydatabase CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
creates a mydatabase database whose contents are supposed to be handled as case insensitive UTF-8.
In the Xojo code, after connecting, you probably will need to:
db.ExecuteSQL("set names utf8mb4 collate utf8mb4_general_ci;")
// from now on you should be properly "talking" UTF-8 with MySQL
The database is definitely utf8mb4, I checked when you mentioned it.
Does the db.sqlexecute call change something within Xojo settings and does it need to be called with every compile/startup?
Where does this time/date information come from?
Do you need to manually enter the timestamp for a test or is it acceptable to use now() as timestamp?
In any case I would store this information as timestamp or separate date/ time columns as it makes formatting during query a breeze…and also sorting based on timestamp.
As said, it needs to be called after every connection, to ensure that all parts, client (Xojo) and Server (MySQL) are “talking” the same specs.
Your sample was like Xojo sent UTF-8, MySQL stored “whatever”, and sent back Latin1 or something. It wasn’t UTF-8 source, UTF-8 sent, UTF-8 stored, UTF-8 retrieved back.
" set names utf8mb4 collate utf8mb4_general_ci;" says to MySQL that the client is talking UTF-8 and the server should too, as a short form to:
SET character_set_client = utf8mb4 ;
SET character_set_results = utf8mb4 ;
SET character_set_connection = utf8mb4 ;
SET collation_connection = utf8mb4_general_ci;