I'm attempting to find the size of a BLOB record in MySQL by using the Delphi wrapper for libmySQL.dll

-
I'm attempting to find the size of a BLOB record in MySQL by using the Delphi wrapper for libmySQL.dll

The BLOB record is a string value that can be very small to very large.

In MySQL Workbench the following query returns the correct value:
SELECT LENGTH(file_data) FROM files_table WHERE idfile = 146

But when I attempt it in code it (and other idfile values) returns 4271496 and I can't figure out where this erroneous value comes from. Well I don't really need to figure that out, it's just I don't see anywhere that would be a max value of anything - I've seen it also returned for SELECT MAX(... on an empty table.

So, sorry for my babbling, the code that returns the value is in the statement:
mysql_bind_set_param(bind, 0, MYSQL_TYPE_LONG, @int_data_0, 0, nil, nil);

PS: my reason for needing to determine the BLOB size is to limited the returned string size in a subsequent call to bind the BLOB

Any thoughts on what I need to do to fix? Any additional code you need to see?

Comments

  1. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 3:27 PM

    But why you are saying it is an erronous value? You should compare the value reyurned using the same IDfile value. Because lenght(file_data) will return the size of the stored information in that record, so, unless you store the same data in all records you will.alwaya get variable values. blob fields doesnt have a fixed length, just max length, so If you want to get t size of the max capacity of a field you should learn how to get the information schema a that table.
    Check this links: https://dev.mysql.com/doc/refman/8.0/en/information-schema.html

    and https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

    ReplyDelete
  2. Patrick HughesJuly 23, 2018 at 3:41 PM

    The 4271496 value is the erroneous value returned for multiple records in code. When querying in MySQL Workbench each record returns variable values.

    ReplyDelete
  3. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 3:51 PM

    Try to use 'MYSQL_TYPE_LONGLONG' (BigInt) instead of 'MYSQL_TYPE_LONG', because it seems Length( ) always returns an int64

    ReplyDelete
  4. Patrick HughesJuly 23, 2018 at 3:52 PM

    I should clarify that I misspoke - in referring to the record I meant the "file_data" field of the record.

    ReplyDelete
  5. Patrick HughesJuly 23, 2018 at 3:55 PM

    I had tried 'MYSQL_TYPE_LONGLONG' - same results.

    ReplyDelete
  6. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 4:04 PM

    Patrick Hughes Yes I got it at first :), I just gussed you where trying another thing ;)

    ReplyDelete
  7. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 4:06 PM

    Patrick Hughes Ok. Well, I havent worked directly with DLL API, so Im just trying to guess maybe there is a Data type wrong. I Hvae two questions:
    1) Ehat type of data are 'bind' and 'int_data', if intdata is integer it shoudl be declared as Int64 to see what ahppens.

    2) Why do you need to acces lowlevel api libmysqldll if there are other already solutions to access mysql like ZeosDBO, FireDAC.
    If you are willing to continue, maybe you shoudl take a Look to heidiSQL, It is a frontend tool to manage MySQL, MSSQL and PostgreSQl databases. It is wirtten in delphi, open sources and uses their own classes to use the lowlevel api.

    Take alook at ZeosDBO if you want an already made solution:
    http://zeoslib.sourceforge.net/index.php

    Take a look at the sources of heidisql, maybe it can be a good guidance for you:

    https://github.com/HeidiSQL/HeidiSQL


    ReplyDelete
  8. Patrick HughesJuly 23, 2018 at 4:31 PM

    I found the problem after logging the mysql_error. It was my prepared statement (mysql_stmt_prepare(stmt, QText1, Length(QText1)) I am constructing two different QueryTexts and I had the Length(QText1) parameter incorrectly written as Length(QText)

    Copy/Paste strikes again.

    I knew the "why" question in 2) would come up eventually. I'm working now with legacy Delphi 5 code using a non VCL application to transition into a db application.

    Thanks for your help Francisco

    ReplyDelete
  9. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 5:52 PM

    Patrick Hughes Im glad it worked Patrick. btw you should try latest Delphi community edition. It is free and full functional.
    So you dont have to still coding on a legacy IDE. If your app is a console one it will be easier.

    ReplyDelete
  10. Patrick HughesJuly 23, 2018 at 6:58 PM

    I've got Berlin Enterprise edition. Need to update a few 3rd. party components and get much more comfortable with it. This transition is going to allow me to move forward.

    ReplyDelete
  11. Francisco Armando Dueñas RodríguezJuly 23, 2018 at 8:07 PM

    Cool. Good luck!!

    ReplyDelete
  12. Dany MarmurJuly 24, 2018 at 9:44 AM

    The worse my eyesight gets the more Copy-Paste strikes. It's getting annoying and i'm grateful for the projects in newer IDE's where insight, refactoring and completion works better. Copy-Past is not good for code.

    ReplyDelete

Post a Comment