Announcement

Collapse
No announcement yet.

SQL, Access and Blobs

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

  • SQL, Access and Blobs

    Well, I did not want to be rude and hijack another related thread so I'll present my own problem.

    Bear in mind that I am relatively new to SQL Tools but have been able to make it do what I need to it to do (although I do know I'm handling indexes incorrectly, so expect another question here in the future... just a warning.)

    The requirement is to work in an Access database. My table "products" has a field called "fullFeaturesLists" which can contain a lot of text. The possibility does exist to exceed 64k in length and the possibility of UNICODE in the future.

    I have no difficulty working with regular text and numeric fields, retrieving data via sql_resultColumnString() and sql_resultColumnNumeric().

    I have working on this for over a week and am stuck. Here is the version where I stand, where I feel that I'm closest to the solution:

    The field fullFeaturesLists in Access lists the field type as "OLE".

    The SQL Dump returns:
    Code:
    Products Column 42:
            TBLCOL_COLUMN_NAME         ProductsfullFeaturesList
            TBLCOL_DATA_TYPE           -4
            TBLCOL_TYPE_NAME           LONGBINARY
            TBLCOL_CATALOG_NAME        I:\Projects\ccPPdb\ccPPdb2019a\db\testDatabase
            TBLCOL_SCHEMA_NAME         <empty>
            TBLCOL_TABLE_NAME          Products
            TBLCOL_DISPLAY_SIZE        1073741823
            TBLCOL_BUFFER_LENGTH       1073741823
            TBLCOL_DECIMAL_DIGITS      <empty>
            TBLCOL_NUM_PREC_RADIX      <empty>
            TBLCOL_NULLABLE            1
            TBLCOL_REMARKS             <empty>
            TBLCOL_DEFAULT_VALUE       <empty>
            TBLCOL_SQL_DATA_TYPE       -4
            TBLCOL_DATETIME_SUB        <empty>
            TBLCOL_CHAR_OCTET_LENGTH   1073741823
            TBLCOL_ORDINAL_POSITION    42
            TBLCOL_IS_NULLABLE         YES
            TBLCOL_DRIVERDEF_19        42
            This column has 0 Column Privileges.
    It seems that I can store data because in the one records I have been testing, Access shows "long binary data" in that field.

    I used this to store the Blob:

    Code:
              whereClause = "productsUniqueID=" + trim$(str$(insertedProductsUniqueID))
              result = SQL_UpdateBlob ( 1, "products", "fullFeaturesList", whereClause, dataProductsFullFeaturesList )
    But I never get anything back even though other sql_resultColumnString/Numberic functions return the correct data.

    Code:
         '  note:  statement = 1
         '  note:  @p.fullFeaturesList = 42
         dataProductsFullFeaturesList = sql_resultColumnBlob ( 1, statement, @p.fullFeaturesList )
    I have write writing the result to a file and no file is created so off to the trace log and yes and do I get an error? No and Yes.

    Code:
    >SQL_ResultColumnBLOB|DB 1|STMT 1|COL 42|TYPE -2|FILE "">
    <SQL_SUCCESS<
    
    >SQL_ErrorQuickOne>
    <[35442.238]  INTERNAL_LONGRESCOL      1   1   42  999000030  #0030 -970       -- [Perfect Sync][SQL Tools] ERROR_BAD_PARAM_VALUE<
    In my reading, I have seen stuff about UnBindColumn but I am unclear whether this is necessary or where to put this.

    Well, any input, direction or what to search for would be greatly appreciated.

    Cheerios!

    Dan
    Last edited by Dan Gin zel; 15 Nov 2019, 10:25 AM. Reason: Edited for clarity
    www.thecomputerarchive.com Preserving the history of companies that advanced the computer revolution

  • #2
    Hi Dan,

    I'm the author of SQL Tools. Please run your program again using SQL_Trace %TRACE_INTERNALS and send the resulting trace file to [email protected]

    It will be way too big to post here.
    "Not my circus, not my monkeys."

    Comment


    • #3
      Originally posted by Dan Gin zel View Post
      Code:
      ' note: statement = 1
      ' note: @p.fullFeaturesList = 42
      dataProductsFullFeaturesList = sql_resultColumnBlob ( 1, statement, @p.fullFeaturesList )
      I have write writing the result to a file and no file is created so off to the trace log and yes and do I get an error? No and Yes.
      What happens if you use the value 42 instead of a pointer for the column number? Not sure it would make a difference, but the ERROR_BAD_PARAM_VALUE message would make that my second choice, behind making sure the database handle (1) and statement (1) are open.

      Real programmers use a magnetized needle and a steady hand

      Comment


      • #4
        Originally posted by Bud Durland View Post
        What happens if you use the value 42 instead of a pointer for the column number? Not sure it would make a difference, but the ERROR_BAD_PARAM_VALUE message would make that my second choice, behind making sure the database handle (1) and statement (1) are open.
        Well, I gave it shot and did not see any result. Then I tried numbers below and above 42 to give it a shot.

        Then I did something interesting, I tried regular fields and was able to pull in string data from them via sql_resultColumnBlob, so I know that, fundamentally, that function is working.

        I've also tried sql_resultColumnString, sql_resultColumnMemo, sql_resultColumnBuffer and sql_resultColumnRaw, all returning the same non-result.

        Good thinking, though.

        Dan
        www.thecomputerarchive.com Preserving the history of companies that advanced the computer revolution

        Comment


        • #5
          I reduced the essentials of the program into a single test sub that I call right after opening the file. It shows some of what I have tried.

          Code:
               sub readOneRecord
          
                    local whereClause, x, t, t0, t1, t2 as string
                    local result1, result2, database, table, statement, c1, c2, n1 as long
          
                    database = 1
          
                    table = sql_TableNumber ( database, "Products", "" )
                    statement = 1
          
                    whereClause = "productsUniqueID = 1"
                    t0 = "FILE=.\cowboy.txt"
                    result1 = SQL_UpdateBlob ( database, "products", "ProductsFullFeaturesList", whereClause, t0 )
          
                    t0 = "I really want to put this text in the box."
                    result2 = SQL_UpdateBlob ( database, "products", "ProductsWebInTheBox", whereClause, t0 )
          
                    x = str$(result1) + str$(result2) + $CRLF
          
          
                    t = "SELECT * FROM products WHERE productsUniqueID = 1"
                    result1 = SQL_Statement ( database, statement, %Immed, t )
                    result2 = SQL_FetchResult ( database, statement, %next_row )
          
                    x = x + str$(table) + $CRLF + hex$(result1) + $CRLF + hex$(result2) + $crlf
          
                    c1 = sql_ResultColumnNumber ( database, statement, "ProductsUniqueID" )
                    n1 = sql_ResultColumnNumeric ( database, statement, c1)
          
                    x = x + "ResultColumnNumeric: " + str$(c1) + ": " + str$(n1) + $CRLF
          
                    c1 = sql_ResultColumnNumber ( database, statement, "ProductsManufacturerPartNumber" )
                    t1 = sql_ResultColumnString    ( database, statement, c1)
          
                    x = x + "ResultColumnString: " + str$(c1) + ": " + t1 + $CRLF
          
                    c1 = sql_ResultColumnNumber ( database, statement, "ProductsFullFeaturesList" ) + 1
                    t1 = sql_resultColumnBlob      ( database, statement, c1 )
                    x = x + "ResultColumnBlob: " + str$(c1) + ": " + t1 + $CRLF
          
                    t1 = sql_resultColumnString ( database, statement, c1 )
                    x = x + "ResultColumnString: " + str$(c1) + ": " + t1 + $CRLF
          
                    t1 = sql_resultColumnMemo      ( database, statement, c1 )
                    x = x + "ResultColumnMemo: " + str$(c1) + ": " + t1 + $CRLF
          
                    t1 = sql_resultColumnBuffer      ( database, statement, c1 )
                    x = x + "ResultColumnBuffer: " + str$(c1) + ": " + t1 + $CRLF
          
                    t1 = sql_resultColumnRaw      ( database, statement, c1 )
                    x = x + "ResultColumnRaw: " + str$(c1) + ": " + t1 + $CRLF
          
                    msgbox x
          
                    sql_traceStr " . . . . . . . . . . ."
          
               end sub
          Sorry, it's not the prettiest. It's just test-bed sandbox to try things.

          Now that I've pushed the car up the cliff, it's time to try the brakes again...
          www.thecomputerarchive.com Preserving the history of companies that advanced the computer revolution

          Comment


          • #6
            Here's another point: SQL_ResultSet returns comma-delimited data for all columns except these OLE / LongBinary fields.
            www.thecomputerarchive.com Preserving the history of companies that advanced the computer revolution

            Comment


            • #7
              Hi Dan,

              Neither of those traces looks like a SQL_Trace %TRACE_INTERNALS file. That's what I need to see. Considering the length of the regular trace, I'd suggest turning on SQL_Trace immediately before the SQL_Statement that is having a problem, not at the top of your program.

              In the second trace, everything after column 32 is failing because you need to use a larger max-column-number value in SQL_Initialize.


              "Not my circus, not my monkeys."

              Comment


              • #8
                Originally posted by Eric Pearson View Post
                ...everything after column 32 is failing because you need to use a larger max-column-number value in SQL_Initialize.
                That may be the clue. I noticed there was a regular field that wasn't working but I assumed that one error caused a chain reaction, not a limitation on the number of columns.

                Modification complete and now the test routine is working as intended!

                Thank you very much for pointing out column limit override !

                www.thecomputerarchive.com Preserving the history of companies that advanced the computer revolution

                Comment


                • #9
                  Originally posted by Eric Pearson View Post
                  In the second trace, everything after column 32 is failing because you need to use a larger max-column-number value in SQL_Initialize.
                  That just dawned on me this morning as a possible cause after my second cup of joe. I'm happy that my instincts are still there, even if delayed...
                  Real programmers use a magnetized needle and a steady hand

                  Comment


                  • #10
                    > working as intended!

                    Great! I wish all BLOB problems were that easy to fix.
                    "Not my circus, not my monkeys."

                    Comment

                    Working...
                    X