Getting started with Objo, little problem on execute database code

matteolovatti

Hello everyone!

i have this very simple piece of code

Var stmt As PreparedStatement = PostgresDB.Prepare("INSERT INTO brand (name, code, slug) VALUES (?, ?, ?)")

While rs.MoveToNextRow()
  stmt.Bind(0, rs.Column("brandName").StringValue)
  stmt.Bind(1, rs.Column("brandCode").IntegerValue)
  stmt.Bind(2, rs.Column("brandLink").StringValue.Replace("/", ""))
  Try
    stmt.ExecuteSQL()
  Catch err As DatabaseException
    MessageBox(err.Message)
  End Try
Wend

rs recordset is populated from a MySQL database where brandName and brandLink are text columns and brandCode a smallInt column.

In Postgres DB name and slug are varchar(255) columns and code is a int4 column

Running the code i get the records into my Postgres DB, but on column code all records are "0", I know for sure my brandCode are correctly assigned (no zero anywhere) in my MySQL database so I wonder why are not translated correctly into the Postgres DB.

Any hint?

Thanks.

Garry

Hello and welcome to Objo and thank you for such a well-prepared report. The debug screenshot before the insert was exactly what pinned this down. This is a genuine bug in Objo, not something wrong with your code. Sorry about that.

What's happening: for MySQL columns declared as an unsigned integer (smallint unsigned, int unsigned, and friends) or a plain tinyint, the MySQL driver hands Objo the value in a form our DatabaseColumn integer conversion doesn't currently recognise, so IntegerValue silently returns 0. String columns take a different path, which is why name and slug copy across correctly while every code ends up as 0.

You can carry on with your import right away by reading the column as text and parsing it:

stmt.Bind(1, Integer.Parse(rs.Column("brandCode").StringValue))

StringValue isn't affected, so this gives you the real value for every row.

The bug is logged and will be fixed in the next release: https://feedback.objo.dev/bug/1553

If you'd like to confirm the diagnosis on your side, SHOW CREATE TABLE on the MySQL table will most likely show brandCode as smallint unsigned but either way, the fix is on us.

Thanks again for the report, and don't hesitate to ask as you explore the rest of the IDE.

matteolovatti

Thanks to you @Garry ! Awesome to have a quick fix so I can go one with my App!

Garry

This has now been patched and will be in the next release.

ebloom

matteolovatti Thanks so much for reporting this. I was getting ready to write a similar report this morning. I'm having the same issue - MariaDB integers read as 0.
And @Garry - I really appreciate the quick acknowledgement and fix!