Announcement

Collapse
No announcement yet.

Problem Building an Image Database for .jpg files?

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

  • Eric Pearson
    replied
    Craig, circling back around, I'm not aware of any reports -- ever -- of LEN malfunctioning. It's super-simple under the hood (it simply reports an existing numeric value), and LEN is so widely used that any bugs would have been found two decades ago.

    So I'd suspect something like an INTEGER that is rolling over at 32k, maybe your "000000" string needs to be longer, maybe a STRING/WSTRING thing... it could be lots of things. Without at-least-compilable code it's hard to guess, but I'll bet you it's not LEN.

    Leave a comment:


  • Stuart McLachlan
    replied
    I got thinking about my comment in Post#11:
    "I'd probably use SQLite"

    So here's a simple demo of creating an SQLIte database and saving and retrieving photos (and other info)

    IMNSHO, it certainly answers the OP's question:
    "Is there a better method for stuffing binary data into a database?"
    In particular, it becomes trivial to add, edit and delete records. (change a player's photo, remove a player ...)

    '
    Code:
    #COMPILE EXE
    #DIM ALL
    
    #INCLUDE "sqlite3.inc"
    #INCLUDE "clsSQLite.inc"
    
    GLOBAL sSQL AS STRING
    GLOBAL sErr AS STRING
    GLOBAL gstrDB AS STRING
    GLOBAL goConn AS ISQLite3Connection
    GLOBAL goCmd  AS ISQLite3Command
    GLOBAL goRdr  AS ISQLite3Reader
    
    FUNCTION PBMAIN() AS LONG
       LOCAL strFile, strBlob AS STRING
       LOCAL lngRecs, ff AS LONG
       gstrDB = "Players.db3"
       lngRecs = SetupDB()    'Create a new DB if necessary.   Pass an integer value for the optional ClearIt parameter to clear data from an existing DB
       IF lngRecs = -1 THEN EXIT FUNCTION ' problem creating/accessing database !!!
    
       'Get JPG and save as a blob
       ff = FREEFILE
       OPEN "test.jpg" FOR BINARY AS #ff
       GET$ #ff, LOF(#ff), strFIle
       CLOSE #ff
       savePhoto lngRecs + 1,strFile
    
       ? "Player " & STR$(lngRecs+ 1) & " added to database
    
       'Get photo from database and save it to disk
       strBlob = GetPhoto(lngRecs + 1)
       ff = FREEFILE
       OPEN "Player" & FORMAT$(lngRecs+1) & ".jpg" FOR BINARY AS #ff
       PUT$ #ff, strBlob
       SETEOF #ff
       CLOSE #ff
    
       ? "Photo Player" & FORMAT$(lngRecs+1) & ".jpg saved"
    END FUNCTION
    
    FUNCTION SavePhoto(PlayerID AS LONG,photo AS STRING) AS LONG
        LOCAL iBlob AS ISQLite3ParameterBlob
        iBlob = CLASS "clsSQLite3ParameterBlob"
        iBlob.value = STRPTR(Photo)
        iBlob.size = LEN(Photo)
        iBlob.Name = "@Param1"
        gocmd.parameters.add(iBlob)
        sSQL = "Insert into tblPlayers (PlayerID,PlayerName,Photo) values (" & _
        STR$(PlayerID) & ",'Player " & STR$(PlayerID) & "',@Param1);"
        goCmd.SQL = sSQL
        goCmd.execute
        gocmd.parameters.clear 'Delete the parameter - else it will mess up subsequent commands
    END FUNCTION
    
    FUNCTION GetPhoto(PlayerID AS LONG) AS STRING
        LOCAL strBlob AS STRING
        sSQL = "Select Photo,PlayerName from tblPlayers where PlayerID = " & STR$(PlayerID)
        goCmd.SQL = sSQL
        goRdr = goCmd.ExecuteReader()
        IF ISOBJECT(goRdr) THEN
            strBlob = goRdr.value(0)
            ? goRdr.value(1) & " photo retrieved from database"
        ELSE
            goConn.close
            ? "Problem reading the database!.  Exiting!"
            EXIT FUNCTION
        END IF
        FUNCTION = strBlob
    END FUNCTION
    
    FUNCTION SetupDB(OPT Clearit AS LONG) AS LONG
        LOCAL lngRecs AS LONG
        'Open or create the database
        goConn = CLASS "clsSQLite3Connection"
        IF goConn.Open(gstrDB) THEN
          goCmd = CLASS "clsSQLite3Command"
          goCmd.Connection = goConn
        ELSE
              ?  "Cannot open " & gstrDb,%MB_ICONERROR
              FUNCTION = -1
              EXIT FUNCTION
        END IF
        'If it's a new database, create the table
        sSQL = "CREATE TABLE IF NOT EXISTS tblPlayers (" &  _
            "   PlayerID INTEGER PRIMARY KEY," &  _
            "   PlayerName TEXT," &  _
            "   Photo BLOB," &  _
            "   Comments TEXT" &  _
            ");
         goCmd.SQL = sSQL
         goCmd.execute
    
        'Check existing records
        sSQL = "Select count(*) from tblPlayers"
        goCmd.SQL = sSQL
        goRdr = goCmd.ExecuteReader()
        IF ISOBJECT(goRdr) THEN
             lngRecs  = VAL(goRdr.value(0))
             IF VARPTR(ClearIt) <> 0 THEN ' clearit parameter passed.
                sSQL = "Delete  from tblPlayers;"
                goCmd.SQL = sSQL
                goCmd.execute
                lngRecs = 0
            END IF
        ELSE
            goConn.close
            ? "Problem accessing the database!.  Exiting!"
            FUNCTION = -1 : EXIT FUNCTION
        END IF
    
        FUNCTION = lngRecs
    END FUNCTION
    '
    Attached Files

    Leave a comment:


  • Craig Slane
    replied
    Thanks, Dan. You're right. Unnecessary complexity. I've changed it. You're also correct that it doesn't affect the outcome. I shall rest content in a somewhat larger file than I hoped for and just use the size of the largest .jpg file for a fixed string length. I can always use a utility to reduce the photos to the same size by adjusting the resolution quality. It's a workable solution. Thank you to all for the help. It led me to an acceptable solution. It's what I hoped for.

    Leave a comment:


  • Dan Soper
    replied
    I suspect the line:
    Code:
    SV& = SV& + LEN(FileContent) + 1
    What does the +1 do here? It looks like it's adding an extra byte between images in player_photos.db. I don't see any need for that. It's probably not the cause of your problem, but you don't want to have unnecessary complexity muddying the waters.

    You never initialise SV&. For the first image you ignore it in favour of a hard-coded 1. That will introduce an off-by-one error for all subsequent images, I think. Explicitly initialise SV& to 1, maybe?

    Leave a comment:


  • Craig Slane
    replied
    SETEOF does not seem to make any difference in this case.

    Leave a comment:


  • Craig Slane
    replied
    Alright, an update. Supposing Eric to be correct (again, appreciate the data you provided) about the overwriting, I changed FileContent to a fixed length string set to the size of the biggest .jpg in the set and recreated the database. It seems to have eliminated the overwrite issue, but of course now my database is much larger with all the slosh. I don't understand why LEN doesn't return the actual length of a binary file in string form, but that seems to be where things go haywire, or maybe it's not LEN and I need to use SETEOF here.

    Stuart, I originally used PUT$ on the whole string at once. I was tinkering with character-by-character in the example I posted just to see if it made a difference. And I do Kill active_photo.jpg outside this function.

    I will do a test with SETEOF next to see if that might work.

    Leave a comment:


  • Eric Pearson
    replied
    I agree that SETEOF is good practice, but I don't understand how extra data at the end of the file would explain the truncated image. He is building an index with location and LEN information, so any extra bytes at the tail of the file would be ignored, no?

    Leave a comment:


  • Stuart McLachlan
    replied
    Originally posted by Eric Pearson View Post
    Looking at a Binary View of the file, I see a JFIF header (which is a form of JPG) then a bunch of data, as expected... but then there's another JFIF header that shouldn't be there, and some more data.

    Added: That implies that 1) the first image was truncated when it was saved, or 2) the second image overwrote part of the first image.
    Yep, overwrites of longer exisiting binary files without SETEOF?

    Leave a comment:


  • Stuart McLachlan
    replied
    Code:
    pF = FreeFile
    Open $AppPtr+"DATB\Dbase\active_photo.jpg" For Binary as #pF
    FOR X& = 1 TO LEN(FileContent)
    PUT$ #pF, MID$(FileContent,X&,1)
    NEXT X&
    Close #pF
    If you do this a second time and the second file is shorter, you will end up with garbage at the end of the image file.
    Either KILL any existing file or SETEOF after wring the data. This shows both, only one is needed

    And why one byte at a time?

    Code:
    IF ISFILE($AppPtr+"DATB\Dbase\active_photo.jpg") THEN KILL $AppPtr+"DATB\Dbase\active_photo.jpg"
    
    pF = FreeFile
    Open $AppPtr+"DATB\Dbase\active_photo.jpg" For Binary as #pF
    
    'FOR X& = 1 TO LEN(FileContent)
    'PUT$ #pF, MID$(FileContent,X&,1)
    'NEXT X&
    ' or just
    'PUT$ #pF, FileContent
    
    SETEOF #pF
    Close #pF

    Leave a comment:


  • Dean Gwilliam
    replied
    How about workng with byte oriented commands rather than strings until you've located the false counting.

    Leave a comment:


  • Stuart McLachlan
    replied
    Originally posted by Craig Slane View Post
    Is there a better method for stuffing binary data into a database?
    I'd probably use SQLite. No need to mess around with file lengths, indirect tindexing, offsets etc.

    Just store everything in a record: the "imagehandle" as in INTEGER, filename as a TEXT field, the image file as a BLOB field and you can then store things like comments in other fields in the same record.


    Leave a comment:


  • Craig Slane
    replied
    Eric, first, thanks for checking on the binary. That's interesting. I'm using LEN to get the length of the string I GET$ from the original file and then cataloguing/indexing accordingly. I wonder if LEN doesn't always return the full length? Is that possible? Is there a better method for stuffing binary data into a database? I doubt if it's option (2) because images that come later database will read and show just fine.

    Leave a comment:


  • Eric Pearson
    replied
    Looking at a Binary View of the file, I see a JFIF header (which is a form of JPG) then a bunch of data, as expected... but then there's another JFIF header that shouldn't be there, and some more data.

    Added: That implies that 1) the first image was truncated when it was saved, or 2) the second image overwrote part of the first image.

    Leave a comment:


  • Craig Slane
    replied
    Rod, yes, this the image I get back from my database. I'm trying to figure out the "why" of the corruption. I'll have a look at that link.

    Leave a comment:


  • Rod Macia
    replied
    Image active_photo2 is a JPG but looks corrupted, is this the original source image or is this what you get back from your Database?

    Raymond W. Leech did some code to check image signature to make sure they match the extension.
    Maybe you can have a look at https://forum.powerbasic.com/forum/u...e-by-signature

    and check all your source images to make sure they are the format they are suppose to be.

    Leave a comment:


  • George Bleck
    replied
    One simple solution is, before ingestion, to generate a hash (SHA1, etc.) of the image and store that result with the image, then you hash the image data after it's re-extracted. If the hashes match... the chances are astronomically high they are exactly the same image and the problem lies elsewhere.

    Leave a comment:


  • Craig Slane
    replied
    Apologies. I was fiddling around with extensions to see if it mattered. Yes, it's a .jpg. Here's another: active_photo-2.zip

    Leave a comment:


  • Rod Macia
    replied
    Well the image in the zip file is not a jpg, it's a BMP

    Edit: actually it is a jpg but you zipped it as a BMP

    Click image for larger version  Name:	Capture22.JPG Views:	1 Size:	7.2 KB ID:	811331

    Leave a comment:


  • Craig Slane
    replied
    Thanks, Rod. Here you go. active_photo.zip

    Leave a comment:


  • Rod Macia
    replied
    Zip "active_photo.jpg" and attach the zip file so we can analyses the actual jpg

    I have seen cases where images had their extensions changed (ie: from png to jpg) and most picture viewers didn't care, but some do.

    Leave a comment:

Working...
X