DatabaseRow.Column.StringID InvalidArgumentException 2021R1

Databases
1

Hey All,

I’m looking to move my Web App to 2021R1. I’m using the same code that I used in 2020R2, but when I’m trying to grab strings from rows in a MySQL database I’m getting an InvalidArgumentException. The message it gives me is:
root{"xojo_value"}: String value does not have a specified encoding.
I was able to fix it by adding .DefineEncoding(Encodings.UTF8) to wherever I’m I’m using stringvalue. For all of this I am just putting the Strings into a Listbox. In 2020R2 this all worked fine, but in 2021R1 I have to use the DefineEncoding. So my questions are this:

  1. Is there a way to default define this encoding for StringValue(I’m sure this is far out), or a way to fix the issue I’m seeing? I just don’t want to have to put this DefineEncoding all throughout my code, as I do this a lot.

  2. (Off Topic a bit) When I looked at InvalidArgumentException in the documentation it says that it was Deprecated in 2021R1… So why would I be seeing it?

I’ll drop a bit of the code below for example.

Thanks,
CJ

UsersListBox.CellValueAt( UsersListBox.LastAddedRowIndex, 0 ) = New WebListBoxStyleRenderer(style, rs.Column("FirstName").StringValue.DefineEncoding(Encodings.UTF8))
UsersListBox.CellTagAt( UsersListBox.LastAddedRowIndex, 0 ) = rs.Column("FirstName").StringValue.DefineEncoding(Encodings.UTF8)
UsersListBox.CellValueAt( UsersListBox.LastAddedRowIndex, 1 ) = New WebListBoxStyleRenderer(style, rs.Column("LastName").StringValue.DefineEncoding(Encodings.UTF8))
UsersListBox.CellTagAt( UsersListBox.LastAddedRowIndex, 1 ) = rs.Column("LastName").StringValue.DefineEncoding(Encodings.UTF8)
2

Same here is xojo2021r1.1… Any ideas?

1 Like
3

IIRC: drop the DefineEncoding, but execute this when connecting:

db.SQLExecute("set names utf8")

That’s what my note says, anyway.

1 Like
4

I have seen this as well with JSONItem / RowSet. It’s incredibly frustrating and I’ve had a client say “Well, I don’t want to beta test this for them” - about the 2021r1.1 release.

If anyone knows how to resolve this without defining encoding literally everywhere, please let me know. We do use the SET NAMES UTF8 trick, but that does not seem to help 2021r1.1.

@Anthony_G_Cyphers it seems that trick no longer works in 2021, do you have any ideas why? :confused:

1 Like
5

Sure don’t. That’s not good. You should open a Feedback Case and report back with the Case ID. That’s something that definitely should work.

1 Like
6

Have you tried db.SQLExecute("set names 'utf8'")?
(With single quotes around utf8)

1 Like
7

Also inform the data collation when bypassing what is found in the MYSQL:

db.SQLExecute( "SET NAMES 'utf8' COLLATE 'utf8_general_ci' " )

MYSQL 8+ uses as standard 'utf8mb4_0900_ai_ci', Unicode 9.0, and I think that tables encoded as utf8mb4 standard can fail trying to use the previous utf8. Not sure, but probably some conflicts may rise.

1 Like
8

That’s a documentation bug. We’ve got it fixed online now.

1 Like
9

I’ve just finished testing the COLLATE utf8 suggestion to no success.

For what little it’s worth, this is what’s causing the exception:

const kSQL = "SELECT * FROM Users WHERE Username = ?"
var oRS as RowSet = DB.SelectSQL(kSQL, "UsernameLookup")

var jsResponse as new JSONItem
jsResponse.Value("User_ID") = oRS.Column("User_ID").StringValue

// Exception happens here, with ToString highlighted
var sResponse as String = tjsResponse.ToString

InvalidArgumentException: root{“User_ID”}: String value does not have a specified encoding.

The exception does not occur in 2020r2.1

2 Likes
10

In all fairness there is something seriously wrong with Encoding on the new JSONItem, and this really needs to be looked at.

3 Likes
11

The new JSONItem uses GenerateJSON and ParseJSON under the hood because of its speed, but yes, it is more particular about text encodings.

12

This may return a string without text encoding. So you need to check if encoding is nil and apply DefineEncoding if needed.

MBS Xojo SQL Plugin does it for you, but the MySQL Plugin in Xojo not.

And if you have correct strings from database, the JSONItem class works just fine.

13

I just wrote a blog post for this:

Tip of the day: StringValueX for database access