MySQL BIGINT return (sometimes) wrong value

Databases
1

Hi there,

I’ve debugging for hours and struggling with a BigInt column in MySQL which (sometimes) returns wrong value.

I hope someone has an idea !

This is the table (partial) definition:

CREATE TABLE `product` (
  `id` bigint NOT NULL,
  `supplierId` bigint DEFAULT '-1',
  `typeId` bigint DEFAULT '-1’,
  `categId` bigint DEFAULT '-1’,
 . . .
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Looking at the data with MySQLWorkbench or with Navicat I have the following values in the table:

id= 948008
supplierId= 16008
typeId= 1000
categId= -1

This is how I read the value:


Dim sql As String = "SELECT * FROM product WHERE id=?"
Dim rs As RowSet 
Try
  rs = db.SelectSQL(sql, 948008)
  . . .
Catch error As DatabaseException
  . . .
End Try
Dim id As Integer = rs.Column("id").Int64Value
Dim supplierId As Integer = rs.Column("supplierId").Int64Value
Dim typeId As Integer = rs.Column("typeId").Int64Value
Dim categoryId As Integer = rs.Column("categoryId").Int64Value
. . .

After reading the values, the result is:

id= 948008
supplierId= 16008
typeId= 0  <------ !!
categId= -1

AFAIK the error occurs for this column, in this table only, but DB has over 200 tables, with plenty of BigInt columns, and millions of records, there are probably other similar errors.

My config:
macOS 10.15.7 (Catalina)
MySQL DB version: 8.0.27 (MySQL Community Server - GPL), on dev system (localhost)
Xojo 2021r3.1
Xojo MySQL plugin using API 2 (not using MBS here)
Build for macOS x86 64-bit

Thanks for reading so far

2

Does it work with MBS Xojo SQL Plugin?

3

I don’t know. I’ll give it a try …
Tell me if I’m wrong but it seems SQLDatabaseMBS uses the Xojo MySQL libs, so I have to use SQLConnectionMBS instead, right ?

4

That is incorrect.

SQLDatabaseMBS and SQLConnectionMBS both use the database library you ask them to load, e.g. library for mySQL or MariaDB.

5

Ok, thanks, my bad.
Could you point me to a MBS example containing SQLDatabaseMBS connection to MySQL and using ExecuteSQL/SelectSQL API 2 ? If any.
I can change the connection methods but I don’t want to change the hundreds of existing select/execute methods.

6

-Have you tried with API 1?

-What happens if you use the same data type for the variable?
Dim typeId As **Int64** = rs.Column("typeId").Int64Value

7

Well, You could take an example to make a query:

https://www.monkeybreadsoftware.net/example-sql-sqldatabasembsmysqlfetchvalues.shtml

and adjust it to RowSet, if you prefer that class. It’s not difficult, like this here:

Dim r1 As RowSet = db.SelectSQL("SELECT * FROM test_tbl")
While Not r1.AfterLastRow
	list.AddRow r1.Column("fid").StringValue, r1.Column("fvarchar20").StringValue
	r1.MoveToNextRow
Wend
8

Thanks but that’s not the problem :wink: the problem is my connection:

Dim mbs As New SQLDatabaseMBS
mbs.SetFileOption SQLConnectionMBS.kOptionLibraryMySQL, SpecialFolder.Resource("libmysqlclient.21.dylib")
mbs.DatabaseName="mysql:127.0.0.1,3306@dbname"
mbs.UserName="root"
mbs.Password="pwd"

If mbs.Connect Then
  MessageBox("connected")
Else
  MessageBox("NOT connected ») '<--------- I always get that
End If

Is placing libmysqlclient.21.dylib in Resources a problem ?
I checked, the file is there, readable and executable.

9

Not yet.

Yes I tried Int64 and I have the same result. I also tried UInteger and UInt64, same problem.
rem: although I can’t use unsigned integers as the values can be negative.

10

As a test, what happens if you retrieve the string value and then use CLong to convert it to an Int64?

11

Note the curly quote at the end of the default value for categoryid. Could the table definition be the issue?

12

@Christian_Schmitz and @Ivan_Tellez
Thanks for your help but Stop investigating ! I found the issue.

The app is building complex queries (with joins and a lot of conditions) on the fly and in this case the query returns 2 columns with the same name ‘typeId’.
This query returns 60 columns and I didn’t scroll to the bottom not seeing the second typeId which has a value of 0.
What’s weird is that there’s no error and the rs.column(‘typeID’) is returning the value of the second column… no I have to figure this one out.

13

Hi Wayne,

Thanks, but it’s just a copy/paste glitch.
Solved in the meantime, see my previous post…
Thanks anyway !