Announcement

Collapse
No announcement yet.

MySQL 8.0.19 and UpdateBLOB()

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • MySQL 8.0.19 and UpdateBLOB()

    I've been having some troubles of late updating BLOB fields. I've sailed along flawlessly for years. But the most recent MySQL driver version has created issues for me that I don't know how to solve. I get this error message (see below). SQL_NextParamenter() is not a function I am calling so I imagine that updateBLOB must call it. I can't tell where the syntax error originates so it's difficult to see how to fix it. How can fix this?

    Click image for larger version  Name:	Capture2.JPG Views:	1 Size:	13.8 KB ID:	790621
    Another common variance of the error shows this information: SQL_NextParameter 2 0 -1 999999999 42000 1064 -- [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.19]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '????<]' at line 1.

    I've noticed that '?' appears frequently in the error messages.

    While I'm at it, I might as well post the snippet of code I'm using:

    ---------------------------------------------------------------------

    '1==> Get `id` handle and update existing record
    selectStr = "SELECT `id` FROM `transmissions` WHERE `league` = '" & lgn & "' and `username` = '" & username$ & "' and `typ` = '" & code(i) & "' and `team` = " & str$(tm_my) & ";"
    lResult& = SQL_Statement(4,4,%IMMEDIATE,selectStr)
    lResult& = SQL_ResultSet(4,selectStr,sOutput$,%RESSET_NO_HEADER)
    If lResult& = %SQL_SUCCESS OR lResult& = %SQL_SUCCESS_WITH_INFO Then
    uid& = VAL(MySQL_resClean(sOutput$))

    '==> Remove the older copy of the file and update date/time stamp
    If uid& > 0 Then
    lResult& = SQL_Statement(4,4,%IMMEDIATE,"UPDATE `transmissions` SET `file` = '***', `name` = '" & file_r(i) & "', `date` = '" & DATE$+";"+TIME$ & "' WHERE `id` = "+str$(uid&)+" ;")
    IF lResult& = %SQL_SUCCESS OR lResult& = %SQL_SUCCESS_WITH_INFO THEN
    ELSE
    errorAudit("uid="+str$(uid&))
    End iF

    Begin_Send:
    lResult& = SQL_UpdateBLOB(4,"transmissions","file", _
    "WHERE `id` = " & str$(uid&) & ";","FILE="+pwF)

    IF lResult& = %SQL_SUCCESS OR lResult& = %SQL_SUCCESS_WITH_INFO THEN

    ...

    Craig J. Slane
    Nostalga Sim Baseball

  • #2
    Hi Craig,

    > the most recent MySQL driver version has created issues

    Well THAT'S not good news. My first guess is (of course) that there is a bug in the new driver. Can you temporarily roll back to the previous one? I'd recommend looking at the recent driver release history, and check for other reports of problems with the new driver. I'd look but I'm swamped today.

    > updateBLOB must call [SQL_NextParameter]

    Correct.

    > I've noticed that '?' appears frequently in the error messages.

    SQL_UpdateBLOB builds a SQL statement (internally) that uses a "bound parameter" linking the question mark directly to a PowerBASIC variable, which allows a SQL statement (which is normally plain text) to deal with binary and/or long data.

    -- Eric (author of SQL Tools)



    "Not my circus, not my monkeys."

    Comment


    • #3
      Hi Eric,

      I will try to roll back my driver. I can't swear that the issues began with 8.0.19. Perhaps it was 8.0.18, but fairly recently. I also can't tell whether it's the driver or the server, which is now updated to 8.0.19 (with no way of rolling back). There were a couple of deprecations in recent MySQL updates but when *I* read them I can't discern how and if they affect this problem. I understand you're busy. I'm patient. I'm interested to know what you think when you can have a look. Thanks for the helpful bit how '?' works. I think that's relevant here. It may be that updateBLOB is failing to successfully bind column data.

      But I will also say that the issue is intermittent. I can often make it function properly with repeated calls to updateBLOB, which is what makes me suspect that the server itself is handling things a bit differently (or maybe it *is* the driver ... I don't have enough expertise to know). I'll roll back the driver and do some more testing today and post my findings.

      PRELIMINARY FINDINGS:
      After 30 mins of testing with driver 8.0.17 I cannot produce the error. You might be correct about the driver, but is it a "bug" or has MySQL deprecated/changed something that will permanently affect SQL Tools. That question hangs in the balance.

      Also, I found this piece that might be helpful. Apparently one has to choose between Unicode and ANSI drivers now, but that doesn't make sense since Oracle only provides one download option for 8.0.19. Confusing.

      https://insidemysql.com/tag/connector-odbc/
      Craig J. Slane
      Nostalga Sim Baseball

      Comment


      • #4
        Hi Eric,

        I just wanted to keep this issue alive. I was hoping that Oracle's MySQL 8.0.20 driver might fix this issues I was having. But no joy. So, I'm stuck on driver version 8.0.17 and dare not update beyond that because SQL_UpdateBLOB produces errors as described above.
        Craig J. Slane
        Nostalga Sim Baseball

        Comment

        Working...
        X