MySQL bigint primary key incorrect in grid

[christappan]

Hi all,

I'm building an Alpha application on top of a database built by someone else. For most tables, the primary keys are logically-created 17-digit values of a bigint data type.

What's concerning is that Alpha grids (and other functions?) is misreading these values sometimes, usually by just the last digit. I can create a view in MySQL that duplicates the primary key but casts it as a character, and then pull up the grid and see the two values side by side--the bigint off by one digit and the character, which is the value the bigint should have been.

In the Query builder, I see that these columns are a type of "E" rather than "N", and the "nativeType" in the template definition is "longlong" rather than "long" given to the int data type. I tried a manual override of the latter and that didn't fix it.

Bottom line, I can create character-based workarounds here and there, but I'm concerned that I don't know when this flaw is going to affect a query or result set and when it's not--in grids, UXs, XBasic--and that's concerning.

Has anyone else seen anything like this before or know how to work around it?

[christappan]

Re: MySQL bigint primary key incorrect in grid

It's also failing in a command to populate a UX. I had to change the data type to character in the database table itself to get it to pull up the correct record.

This feels like a legitimate bug...

[Davidk]

Re: MySQL bigint primary key incorrect in grid

What you're seeing is rounding, but it doesn't happen on all numbers. Your numbers are too large for Alpha to handle. I think there was a post about this in the past but I can't find it. I believe the largest number you can use is 16 digits long. The largest value seems to be 9007199254740992. Anything larger and you'll get rounding.

You'll need to talk to Alpha to get an explanation of this... find out what they're doing. This occurs in the Grid and UX... so it's at the server level they're doing something.

[RichCPT]

Re: MySQL bigint primary key incorrect in grid

I don't know how you'd apply this in your situation, but when it comes to working with large numbers (or numbers with a lot of significant digits right of the decimal place or a mix of large integers and large fractions being mixed) they offer an xBasic class that uses BCD (Binary Coded Decimal) representation. It is a wrapper for the .NET Decimal class.

See Alpha's documentation:
https://documentation.alphasoftware....class%20method

From what I vaguely remember, there is a method to take a character string and store it into a "Decimal" type and from there, once you have two of these types of variables you can perform simple math on them.
I'm not sure how you'd go about getting the data in and out of the database correctly when the database has to store it as BigInt, though.
I'm using BigInt in Microsoft SQL, but the value is being generated in xBasic with data type "N". So, I may be inherently truncating my values to what Xbasic can hold in data type "N". Which is okay in my case because I am just storing a hash value. As long as the DB gives back exactly what I put in then it doesn't matter to me if I loose a couple of digits.

[christappan]

Re: MySQL bigint primary key incorrect in grid

Thanks to you both.

I did send a bug report to Alpha and at request sent my database and a simple way to replicate it, so I'm awaiting a response. I'll certainly share my findings on here. That said, if I have to force a workaround, your insights will come in helpful.

[christappan]

Re: MySQL bigint primary key incorrect in grid

Hi all. For anyone who happens across this:

From Kurt Rayner at Alpha:

Hi Chris,

The BigInt number you are returning to Alpha Anywhere is too large to be handled by XBasic without losing precision.

The maximum precision of XBasic numeric values is 15 digits. This is because we use the double precision floating point type internally. Double precision floating point is limited to 53 bits of precision, which is roughly 15 digits of decimal precision.

Since your column is really an identifier rather than a number that you would do computation with, your best bet is to convert the column to varchar (or char) and return that value from queries.

For future design, you may want to use UUIDs as unique keys (called GUIDs in Windows), as they are a type that is handled well in XBasic.

Best regards,

Kurt

Also a fun (and interesting) exercise (last digit different in each comparison):

?9007199254740992 = 9007199254740993
= .T.

?1234567891234567 = 1234567891234568
= .T.

?123456789123456 = 123456789123457
= .T.

?12345678912345 = 12345678912346
= .T.

?1234567891234 = 1234567891235
= .T.

?123456789123 = 123456789124
= .T.

?12345678912 = 12345678913
= .T.

?1234567891 = 1234567892 '10 digits
= .F.

[Davidk]

Re: MySQL bigint primary key incorrect in grid

I'm not sure you got all the info from Alpha... as your test shows. I also think these are 2 different areas of concern. Using your numbers, you may get errors in Xbasic... but the Grid and UX are not affected... at least for displaying. If you attempt to do anything with these numbers in XBasic... you're in trouble... as you show.

Overall, I understand you were stuck with these big int numbers, but they cannot be used in Alpha.

TestBigInt.PNG

[RichCPT]

Re: MySQL bigint primary key incorrect in grid

Well if you are not allowed to change the database structure then how about this idea:
a) create a view for every table in the system, in the views, use CAST( field_name, varchar(30)) as [ field_name] for the bigint columns.

b) use those views as the Source for your Grids and UX's. It is unlikely MySQL can use the view as an updateable view; MS SQL 2014 cannot, so....

c) setup a multi-tenant driver in Alpha to access the MySQL DB for the views used above and inject your own code to replace the view names with the real table names when an update command comes through the driver. (In video finder look for "multi-tenant", it is a 3-part video. in the 3rd part, Selwyn shows the xbasic code that is generated and I think he shows or says you can modify the generated SQL code to alter its behavior before any SQL statement is executed. In xbasic (in the multi-tenant drive(s) alpha generated), check for Insert, Update, Delete commands and replace the source table name (which will be a view) with the real table name. )

d) watch out for table alias names that might come through the driver (Alpha tends to drive me crazy on the table alias stuff).

NOTE: I have NOT used the multi-tenant driver; it is possible there are limitations to it or SQL commands that it cannot swallow. I was a little worried about that when I looked at it.

[Davidk]

Re: MySQL bigint primary key incorrect in grid

Be careful using the Multi-Tenant AlphaDAO Connection String feature... it's broken ... has been ever since it was released. If your TenantID field type is Character, and your SQL Statement includes a WHERE clause, it all blows up. Plus, while building the Multi-Tenant Connection String, the Test Connection button in the dialog doesn't work. No testing, no quality control.

[RichCPT]

Re: MySQL bigint primary key incorrect in grid

Chris,
For my suggestion to even have a chance of working... when you get to the AlphaDAO connection string builder window that says "Select an API", select "Custom" instead of "MultiTenant".

Like David said, there are issues. Selecting "custom" gives you access to the generated Xbasic class that you would have to fix up to correct any errors / issues. In your case you'd be stripping the class of the multi-tenant feature and putting in ability to swap the view name with the real table name when an update command is passed into it.

Generally when I looked at the generated class in terms of using it to implement a multi-tenant solution, here are the concerns I had with the class:
a) Is the "private cn" variable declared in the class sufficient for handling a case where a person might open a second connection to the database?
b) Does the class correctly handle the field type for the tenant ID: "Character" and "Uniqueidentifier", supported?
c) Why does the class not add the tenant id field as an argument instead of injecting it as a string expression into the existing SQL Where clause? Arguments would be preferred and simpler for handling different tenantID field types.
d) Does the class properly insert parenthesis around any existing WHERE expression before adding "tenantID = value and" or, if above is correct, "tenantID = @argTenantID".
e) How easy is it to get the table/view name the parsed SQL? In my app every column name is made unique by prefixing every field with a two-character table abbreviation. In my app I'd need to do a hard-coded lookup on table name(s), so I'd know what table abbreviation to prefix to prepend to the tenantID column name.
f) What to do when the SQL contains multiple tables. Presumably it is sufficient to just add the tenant ID filter to just the main table of the query. But is there a type of query a person might throw at it that needs the tenantID added as a filter on other tables in the query?

David, did I miss any issues with the multi-tenant custom driver class?