How to store/retrieve pictures in an ODBC Database ?

[Flype]

MySQL and others can store directly some binary data like pictures...

here is the table description :

Code: Select all

CREATE TABLE `album` (
  `photo` blob NOT NULL
)

here is a new picture :
this is a binary data representing 1x1 yellow pixel in jpg format

Code: Select all

INSERT INTO `album` ( `photo` ) 
VALUES (0xffd8ffe[...........] ) ;

here is my simplified pb code :

Code: Select all

If InitDatabase()
Else
  Debug "ODBC not available or too old"
  End
EndIf

If OpenDatabase(0,"test","root","")
Else
  Debug DatabaseError()
  End
EndIf

If DatabaseQuery("SELECT LENGTH(photo) FROM album WHERE 1;")
  If NextDatabaseRow()
    Debug GetDatabaseString(0)
  EndIf
Else
  Debug DatabaseError()
EndIf

If DatabaseQuery("SELECT photo FROM album WHERE 1;")
  If NextDatabaseRow()
    a$ = GetDatabaseString(0)
    Debug a$
    Debug Len(a$)
  EndIf
Else
  Debug DatabaseError()
EndIf

CloseDatabase(0)

So, how to retrieve the photo and then display it on a window ?
By the way, MySQL BLOB's are 65536 bytes long as PB Strings.
But the length are differents in my exemple...

Someone can explain me ?

[Max.²]

MySQL and others can store directly some binary data like pictures...

here is the table description :

Code: Select all

CREATE TABLE `album` (
  `photo` blob NOT NULL
)

here is a new picture :
this is a binary data representing 1x1 yellow pixel in jpg format

Code: Select all

INSERT INTO `album` ( `photo` ) 
VALUES (0xffd8ffe000104a46494600010101006000600000ffdb004300080606070605080707070909080a0c140d0c0b0b0c1912130f141d1a1f1e1d1a1c1c20242e2720222c231c1c2837292c30313434341f27393d38323c2e333432ffdb0043010909090c0b0c180d0d1832211c213232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232323232ffc00011080001000103012200021101031101ffc4001f0000010501010101010100000000000000000102030405060708090a0bffc400b5100002010303020403050504040000017d01020300041105122131410613516107227114328191a1082342b1c11552d1f02433627282090a161718191a25262728292a3435363738393a434445464748494a535455565758595a636465666768696a737475767778797a838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae1e2e3e4e5e6e7e8e9eaf1f2f3f4f5f6f7f8f9faffc4001f0100030101010101010101010000000000000102030405060708090a0bffc400b51100020102040403040705040400010277000102031104052131061241510761711322328108144291a1b1c109233352f0156272d10a162434e125f11718191a262728292a35363738393a434445464748494a535455565758595a636465666768696a737475767778797a82838485868788898a92939495969798999aa2a3a4a5a6a7a8a9aab2b3b4b5b6b7b8b9bac2c3c4c5c6c7c8c9cad2d3d4d5d6d7d8d9dae2e3e4e5e6e7e8e9eaf2f3f4f5f6f7f8f9faffda000c03010002110311003f00f62a28a2bf0d3d33ffd9 ) ;

here is my simplified pb code :

Code: Select all

If InitDatabase()
Else
  Debug "ODBC not available or too old"
  End
EndIf

If OpenDatabase(0,"test","root","")
Else
  Debug DatabaseError()
  End
EndIf

If DatabaseQuery("SELECT LENGTH(photo) FROM album WHERE 1;")
  If NextDatabaseRow()
    Debug GetDatabaseString(0)
  EndIf
Else
  Debug DatabaseError()
EndIf

If DatabaseQuery("SELECT photo FROM album WHERE 1;")
  If NextDatabaseRow()
    a$ = GetDatabaseString(0)
    Debug a$
    Debug Len(a$)
  EndIf
Else
  Debug DatabaseError()
EndIf

CloseDatabase(0)

So, how to retrieve the photo and then display it on a window ?
By the way, MySQL BLOB's are 65536 bytes long as PB Strings.
But the length are differents in my exemple...

Someone can explain me ?

The good news though is that it is possible.

Bad news, I can only try to give you some generics, as I don't have access to my PB PCs until tomorrow.

I came across this problem when I tried to get the preview image from iSEDQuickPDF, which is returned as a string, too, to be displayed/stored on disk and simply used CatchImage with a pointer to the string variable. Was not too much of a hassle.

I will look up a sample tomorrow, if you still need it.

[Flype]

yes i still need it max²

but i'm searching...
i've seen that if i write my request with BINARY key word it seem's to work, but not really in fact. i'm not so far i think...
the goal is, as you perfectly undestood, to use these commands :

Code: Select all

UseJPEGImageDecoder()
CatchImage(0,@PointerReturnedByGetDataBase)
ImageGadget(0,0,0,0,0,UseImage(0))

sql side, using something like that :

Code: Select all

SELECT BINARY photo FROM album WHERE 1;

thanx for your future example...

[Max.²]

Again good as bad news. :roll:

I guess the problem you had was differently from what I thought. The following code works. Please note that I used a column idx to specify which of my test images to retrieve. "3.bmp" was the initial bitmap I uploaded to the server, "3a.bmp" is the downloaded.

Code: Select all

;poke the hexstring into the buffer, 2 characters  of HexString are one value in memory
;procedure from PB / english forum - we need that as helper
Procedure.l hex2dec(h$)
 
  h$=UCase(h$)
  For r=1 To Len(h$)
    d<<4 : a$=Mid(h$,r,1)
    If Asc(a$)>60
      d+Asc(a$)-55
    Else
      d+Asc(a$)-48
    EndIf
  Next

  ProcedureReturn d
   
EndProcedure 


If InitDatabase()
Else
  Debug "ODBC not available or too old"
  End
EndIf

If OpenDatabase(0,"test","root","")
Else
  Debug DatabaseError()
  End
EndIf

If DatabaseQuery("SELECT LENGTH(photo) FROM album WHERE idx=3;")
  If NextDatabaseRow()
    Debug GetDatabaseString(0)
  EndIf
Else
  Debug DatabaseError()
EndIf
;CallDebugger
If DatabaseQuery("SELECT photo FROM album WHERE idx=3;")
  If NextDatabaseRow()
    a$ = GetDatabaseString(0)
    Debug a$
    Debug Len(a$)
    
    BufferSize  =Len(a$)/2
    *Buffer = AllocateMemory(BufferSize)
    
    j=-1
    For i=1 To Len(a$)+2 Step 2 
      j=j+1
      Dec = Hex2Dec(Mid(a$,i,2))
      PokeB(*Buffer+j, Dec)     
    Next i 
    
    Result = CatchImage(0, *Buffer) 
    Result = SaveImage(0,"c:\3a.bmp",#PB_ImagePlugin_BMP)
    
    
  EndIf
Else
  Debug DatabaseError()
EndIf

CloseDatabase(0)

Now there is still one problem. If you try this code on large images, it will return screwed data, because not the whole result is returned to the variable a$ (you noticed already), but a maximum of 4096 bytes.

I can imagine two things;

1. maybe it's a problem with a limitation of GetDatabaseString
2. could be that some specific options have to be set for the ODBC connections. I tried a bit, but without success.

You might want to try out the direct code for connecting to MySQL databases floating around in this forum. I assume it would work then.

Maybe I try a bit with ODBC later, but cannot promise much.

[Fangbeast]

I'll play with your direct connect code tomorrow Max and see if I can get it to store and get pictures larger than 4k.

The only problem then is MySQL's string length which is the same as PB's string length. I think someone said MySQL blob is 64K?

I remember that PB's string buffer can be changed (code in the forum somewhere) and I am thinking of a multi-blob split index to handle larger images perhaps.

[Max.²]

I'll play with your direct connect code tomorrow Max and see if I can get it to store and get pictures larger than 4k.

The only problem then is MySQL's string length which is the same as PB's string length. I think someone said MySQL blob is 64K?

I remember that PB's string buffer can be changed (code in the forum somewhere) and I am thinking of a multi-blob split index to handle larger images perhaps.

Beside Blob, you also got Mediumblob and Longblob. Longblob should be somewhere in the GB range, so it is plenty even without a split.

The procedure to increase the maximum size of strings worked well for me so far. SetStringManipulationBufferSize it was called, I think.

[Fangbeast]

Shooting in the dark here. SQL queries are right . Database is setup as an id field, description field and longblob field. I imported a 50k jpeg intot he database with a utility and veified it was there.

Cannot retreive the pic because I am not usre of the code below. Anyway, have a play. Standard queries work with this code. And I have verified that I am getting data back. If I use the "length" command, I get a "Commands out of synch error, you can not use this command now".

Code: Select all

;============================================================================================================================
; Enable the image decoders
;============================================================================================================================

UseJPEGImageEncoder() 

;============================================================================================================================
; Declare the library and SQL compression communication values
;============================================================================================================================

#libmysql               = 1 
#MySQL_CLIENT_COMPRESS  = 32

;============================================================================================================================
; Declare data types used in sql return queries
;============================================================================================================================

dbHnd.l 
SQL.s 
row.s 
i.l 
j.l 
affRows.l 
fieldNum.l 
rowsNum.l 

;============================================================================================================================
; new list for SQL data return
;============================================================================================================================

NewList SqlData.s()

;============================================================================================================================
; All of my global variables
;============================================================================================================================

Global CFF_MySQL_Init.l, CFF_MySQL_ERRNO.l,CFF_MySQL_ERROR.l, CFF_MySQL_Real_Connect.l, CFF_MySQL_Real_Query.l 
Global CFF_MySQL_Store_Result.l, CFF_MySQL_Field_Count.l, CFF_MySQL_Use_Result.l, CFF_MySQL_Fetch_Row.l 
Global CFF_MySQL_Fetch_Lengths.l, CFF_MySQL_Free_Result.l, CFF_MySQL_Close.l, CFF_MySQL_Ping.l

;============================================================================================================================
; Declare the current directory path
;============================================================================================================================

CurrentDir.s = Space(512)

Result = GetCurrentDirectory_(Len(CurrentDir), @CurrentDir)  ; Get the current directory

If Right(CurrentDir, 1) <> "\"
  CurrentDir + "\"
EndIf

;============================================================================================================================
; Set the string maniplation buffer size (Must be called)
;============================================================================================================================

Procedure SetStringManipulationBufferSize(Bytes)
  PBStringBase.l = 0
  PBMemoryBase.l = 0
  !MOV eax, dword [PB_StringBase]
  !MOV [esp + 4],eax
  !MOV eax, dword [PB_MemoryBase]
  !MOV [esp + 8],eax
  HeapReAlloc_(PBMemoryBase, #GMEM_ZEROINIT, PBStringBase, Bytes)
  !MOV dword [_PB_StringBase], eax
EndProcedure

;a$ = "s" + Space(1048574) + "e" ; Now assign a string to be exactly 1048576 bytes (1 MB).

;Debug Str(Len(a$)) + " " + Left(a$, 1) + " " + Right(a$, 1)   ; Prove it by showing Len and start/end chars of string.

;============================================================================================================================
; Initialise the MySQL library and functions
;============================================================================================================================

Procedure MySQL_Init() 
  Shared CurrentDir.s
  If OpenLibrary(#libmysql, CurrentDir.s + "libmySQL.dll") 
    CFF_MySQL_Init          = IsFunction(#libmysql, "mysql_init") 
    CFF_MySQL_Ping          = IsFunction(#libmysql, "mysql_ping")
    CFF_MySQL_ErrNo         = IsFunction(#libmysql, "mysql_errno") 
    CFF_MYSQL_Error         = IsFunction(#libmysql, "mysql_error") 
    CFF_MySQL_Real_Connect  = IsFunction(#libmysql, "mysql_real_connect") 
    CFF_MySQL_Real_Query    = IsFunction(#libmysql, "mysql_real_query") 
    CFF_MySQL_Store_Result  = IsFunction(#libmysql, "mysql_store_result") 
    CFF_MySQL_Field_Count   = IsFunction(#libmysql, "mysql_field_count") 
    CFF_MySQL_Use_Result    = IsFunction(#libmysql, "mysql_use_result") 
    CFF_MySQL_Fetch_Row     = IsFunction(#libmysql, "mysql_fetch_row") 
    CFF_MySQL_Fetch_Lengths = IsFunction(#libmysql, "mysql_fetch_lengths") 
    CFF_MySQL_Free_Result   = IsFunction(#libmysql, "mysql_free_result") 
    CFF_MySQL_Close         = IsFunction(#libmysql, "mysql_close") 
    ProcedureReturn CallFunctionFast (CFF_MySQL_Init, dbHnd) 
  EndIf 
EndProcedure 

;============================================================================================================================
; Ping the remote server
;============================================================================================================================

Procedure MySQL_Ping_Server(dbHnd)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Ping, dbHnd)
EndProcedure

;============================================================================================================================
; Get the query error message
;============================================================================================================================

Procedure.s MySQL_GetError(db_ID, Requester) 
  Protected Errormsg.s, i.l, Error.l 
  If CallFunctionFast(CFF_MySQL_ErrNo, db_ID) > 0 
    *Error = CallFunctionFast(CFF_MySQL_Error, db_ID) 
    Errormsg = PeekS(*Error) 
    If Requester                                                                        ; A value of 1 means show the error
      MessageRequester("SQL Error", Errormsg, #PB_MessageRequester_Ok)
    EndIf 
  EndIf 
  ProcedureReturn Errormsg 
EndProcedure 

;============================================================================================================================
; Connect to the remote database
;============================================================================================================================

Procedure MySQL_Real_Connect(dbHnd, host.s, user.s, password.s, db.s, port.l, options.l) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Connect, dbHnd, host, user, password.s, db, port, 0, options) 
EndProcedure 

;============================================================================================================================
; Generate the sql query
;============================================================================================================================

Procedure MySQL_Real_Query(dbHnd, Query.s) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Real_Query, dbHnd, Query, Len(Query)) 
EndProcedure 

;============================================================================================================================
; Store the returned result for use
;============================================================================================================================

Procedure MySQL_Store_Result(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Store_Result, dbHnd) 
EndProcedure 

;============================================================================================================================
; Count the number of returned fields
;============================================================================================================================

Procedure MySQL_Field_Count(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Field_Count, dbHnd) 
EndProcedure 

;============================================================================================================================
; Use the returned results
;============================================================================================================================

Procedure MySQL_Use_Result(dbHnd) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Use_Result, dbHnd) 
EndProcedure 

;============================================================================================================================
; Fetch the affected row
;============================================================================================================================

Procedure MySQL_Fetch_Row(*mysqlResult)
  ProcedureReturn CallFunctionFast(CFF_MySQL_Fetch_Row, *mysqlResult) 
EndProcedure 

;============================================================================================================================
; Fetch the lengths of the affected rows
;============================================================================================================================

Procedure MySQL_Fetch_Lengths(*mysqlResult) 
  ProcedureReturn CallFunctionFast (CFF_MySQL_Fetch_Lengths, *mysqlResult) 
EndProcedure 

;============================================================================================================================
; Free the results
;============================================================================================================================

Procedure MySQL_Free_Result(*mysqlResult) 
  ProcedureReturn CallFunctionFast(CFF_MySQL_Free_Result, *mysqlResult) 
EndProcedure 

;============================================================================================================================
; Close the database connection
;============================================================================================================================

Procedure MySQL_Close(dbHnd) 
  CallFunctionFast(CFF_MySQL_Close, dbHnd) 
EndProcedure 

;============================================================================================================================
; Re-use this section to get the sql data into a structure
;============================================================================================================================

Procedure GetSqlData(Result)                                                     ; If the query worked, get the data
  Shared dbHnd
  ClearList(SqlData.s())                                                          ; Always remember to clear the list at program start
  If Result                                                                       ; If the query didn't work, give an error
    MySQL_GetError(dbHnd, 1)
  Else 
    *mysqlResult = MySQL_Use_Result(dbHnd)                                        ; If data returned, use the result
    If *mysqlResult = 0
      If MySQL_Field_Count(dbHnd)                                                 ; No fields are returned so that's an error
        MySQL_GetError(dbHnd, 1) 
      Else
        MySQL_GetError(dbHnd, 1)                                                  ; Fields are returned, so no error but query didn't return data 
      EndIf 
    Else
      affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)             ; How many rows affected
      fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)        ; How many fields
      rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)          ; How many rows
      Repeat                                                                     ; Sequentially process all returned data
        *mysqlRow = MySQL_Fetch_Row(*mysqlResult) 
        If *mysqlRow <> 0 
          *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)          
          row.s = "" 
          For j = 1 To fieldNum                                                   ; Length of given field
            length = PeekL(*mysqlLen + 4 * (j - 1))            
            fieldptr = PeekL(*mysqlRow + 4 * (j - 1)) 
            If fieldptr > 0 
              content.s = PeekS(fieldptr, length) 
            Else 
              content = "NULL"                                                    ; Zero pointer returned means empty field
            EndIf 
            row.s = row.s + content + ";"                                         ; Content (individual collumns)
          Next j
          AddElement(SqlData.s())                                               ; Add the returned data to our linked list
          SqlData.s() = row.s
        EndIf 
      Until *mysqlRow = 0 
      Result.l = MySQL_Free_Result(*mysqlResult)
    EndIf
  EndIf
EndProcedure

;============================================================================================================================
; Buffer helper
;============================================================================================================================

Procedure.l hex2dec(h$)

  h$ = UCase(h$)
  For r = 1 To Len(h$)
    d <<4 : a$ = Mid(h$, r, 1)
    If Asc(a$) > 60
      d + Asc(a$) - 55
    Else
      d + Asc(a$) - 48
    EndIf
  Next

  ProcedureReturn d
   
EndProcedure 

;============================================================================================================================
; Try to initialise the database environment (Procedure MySQL_Init())
;============================================================================================================================

  dbHnd = MySQL_Init()

  If dbHnd
    LibOpened = 1
  Else
    LibOpened = 0
    Message = MessageRequester("Critical Error", "libmysql.dll not found in programs startup directory") 
    End
  EndIf


;============================================================================================================================
; Start of the program
;============================================================================================================================

  SetStringManipulationBufferSize(1048576)  ; Set the buffer size for all strings to 1 MB.

  If MySQL_Real_Connect(dbHnd, "localhost", "root", "", "test", 3306, #MySQL_CLIENT_COMPRESS) <> 0

;    Result = MySQL_Real_Query(dbHnd, "SELECT LENGTH(image) FROM forkle WHERE id=0;")

;    If Result                                                                     ; Test the return code for the query
;      MySQL_GetError(dbHnd, 1)                                                     ; Get the error code
;    EndIf
        
    Result = MySQL_Real_Query(dbHnd, "SELECT image FROM forkle WHERE id=0;")

    If Result                                                                     ; Test the return code for the query
      MySQL_GetError(dbHnd, 1)                                                     ; Get the error code
    Else                                                                          ; Otherwise do the following
      GetSqlData(Result)                                                           ; Get the data from the seerver

      FirstElement(SqlData.s())                                                    ; There should be only one return but...
      ;----------------------------------------------------------------------------------------------
      BufferData.s = StringField(SqlData.s(), 1, ";")

      BufferSize   = Len(BufferData.s) / 2:Debug Str(BufferSize)
      *Buffer      = AllocateMemory(BufferSize)
     
      j = -1
      
      For i = 1 To Len(BufferData.s) + 2 Step 2
        j = j + 1
        Number = Hex2Dec(Mid(BufferData.s, i, 2))
        PokeB(*Buffer + j, Number)
      Next i
     
      Result = CatchImage(0, *Buffer)
      Result = SaveImage(0, "D:\Foom.jpg", #PB_ImagePlugin_JPEG)
      ;----------------------------------------------------------------------------------------------
    EndIf

  EndIf

End

[Max.²]

Shooting in the dark here. SQL queries are right . Database is setup as an id field, description field and longblob field. I imported a 50k jpeg intot he database with a utility and veified it was there.

Cannot retreive the pic because I am not usre of the code below. Anyway, have a play. Standard queries work with this code. And I have verified that I am getting data back. If I use the "length" command, I get a "Commands out of synch error, you can not use this command now".

Thanks for the code.

Apparantly, the returned data is binary already when using the commands from libmysql, while you get a hexadecimal string representation using ODBC.

I changed the GetSQLData procedure to deal with the binary data and it works. Don't forget to remove the Image handling from the main program.

Code: Select all

Procedure GetSqlData(Result)                                                     ; If the query worked, get the data
  Shared dbHnd
  ClearList(SqlData.s())                                                          ; Always remember to clear the list at program start
  If Result                                                                       ; If the query didn't work, give an error
    MySQL_GetError(dbHnd, 1)
  Else
    *mysqlResult = MySQL_Use_Result(dbHnd)                                        ; If data returned, use the result
    If *mysqlResult = 0
      If MySQL_Field_Count(dbHnd)                                                 ; No fields are returned so that's an error
        MySQL_GetError(dbHnd, 1)
      Else
        MySQL_GetError(dbHnd, 1)                                                  ; Fields are returned, so no error but query didn't return data
      EndIf
    Else
      affRows   = CallFunction(#libmysql,"mysql_affected_rows",dbHnd)             ; How many rows affected
      fieldNum  = CallFunction(#libmysql,"mysql_num_fields", *mysqlResult)        ; How many fields
      rowsNum   = CallFunction(#libmysql,"mysql_num_rows", *mysqlResult)          ; How many rows
      Repeat                                                                     ; Sequentially process all returned data
        *mysqlRow = MySQL_Fetch_Row(*mysqlResult)
        If *mysqlRow <> 0
          *mysqlLen = MySQL_Fetch_Lengths(*mysqlResult)         
          row.s = ""
          For j = 1 To fieldNum                                                   ; Length of given field
            length = PeekL(*mysqlLen + 4 * (j - 1)) 
            Debug length          
            fieldptr = PeekL(*mysqlRow + 4 * (j - 1))
            Debug fieldptr
            *Buffer      = AllocateMemory(length)
            If fieldptr > 0
              CopyMemory(fieldptr,*Buffer,length)
              Result = CatchImage(0, *Buffer)
              Result = SaveImage(0, "c:\1c.jpg", #PB_ImagePlugin_JPEG)
            Else
              content.s = "NULL"                                                    ; Zero pointer returned means empty field
            EndIf
            row.s = row.s + content + ";"                                         ; Content (individual collumns)
          Next j
          AddElement(SqlData.s())                                               ; Add the returned data to our linked list
          SqlData.s() = row.s
          Debug sqldata()
          Debug Len(sqldata())
        EndIf
      Until *mysqlRow = 0
      Result.l = MySQL_Free_Result(*mysqlResult)
    EndIf
  EndIf
EndProcedure

[Fangbeast]

Could not get it to work with the rest of my code. Threw up an error about "Image object not initialised"

Also, It's not good to put the image saving (single name) into that procedure. It was intended to be generic, able to be called to save any data. But, I suppose the problem is,

1. How can we incorporate this generic procedure into saving blobs or strings.
2. Can we get the blob section to return a name from a linked field (very likely if the user saved one with the image data as another field (image name)
3. Can we modify this generic routine to catch the image in memory and throw it into an image gadget. That would be very useful for many programs. Home inventory and shopping springs to mind.

Ahh, so much work, so little brain.

[Max.²]

Could not get it to work with the rest of my code. Threw up an error about "Image object not initialised"

IMO could be several problems; in what format is the image stored on your server? If it is not Bitmap, you also need the decoder plugin.

Also, It's not good to put the image saving (single name) into that procedure. It was intended to be generic, able to be called to save any data. But, I suppose the problem is,

My intention is not to work on a specific program, but to solve a specific problem. That is a time issue for me.

Sorry if I get lengthy about that, but it appears to me as if the tendency to demand a final solution is increasing in this forum, though it is likely that most people who answer here are not affected by the problem at all and still put time and efforts into solving it.

1. How can we incorporate this generic procedure into saving blobs or strings.

- There is a mysql API command that returns the type of a field
- Binary data can be transformed into a hexa string representation

Or use the CAST/CONVERT Functions to return a different type.

2. Can we get the blob section to return a name from a linked field (very likely if the user saved one with the image data as another field (image name)

Sorry, that I don't understand.

3. Can we modify this generic routine to catch the image in memory and throw it into an image gadget. That would be very useful for many programs. Home inventory and shopping springs to mind.

Just use a CatchImage & UseImage combination instead of this CatchImage & SaveImage combo.

[Fangbeast]

"but it appears to me as if the tendency to demand a final solution is increasing in this forum"

True, there are a lot of people here who do that. I am not one of them. Am providing enough (hopefully) code to help others get started. My standalone gui client for MySQL (based on your direct connect code) was a start for beginners.

When I understand blob handling, I will update and re-upload the client with more functionality. Also have a tutorial to update on PureArea.

Was not looking to you to solve everything, was posing general questions.

Regards.

[Flype]

i'm not looking for a final solution to. :roll:

for now, i'm wondering if the maximum of 4096 bytes returned by
GetDatabaseString() function come from Purebasic or from ODBC or from MySQL ?

I play with ODBC option ( Allow Big Result ) and MySQL ones, with no better results.

Fred if you have any idea about that, you are welcome.

[berklee]

The only thing I'd mention as a something of a SQL guy is that if you're going to use a LONG BLOB for your table column, don't use SELECT * when you call the table. Instead, call the actual column/row you want to cut down on server resources.

[Max.²]

i'm not looking for a final solution to. :roll:

Guess you guys got that remark in the wrong throat, like I did the one from fangbeast.

for now, i'm wondering if the maximum of 4096 bytes returned by
GetDatabaseString() function come from Purebasic or from ODBC or from MySQL ?

I browesed the forum and there was stated by Fred that there shouldn't be an issue with PB.

Probably best is to try out to establish a ODBC connection with a different program (like Access; I tried OpenOffice but that crashed on the connection) or to try it with a VARCHAR field >4096 in the database.

I play with ODBC option ( Allow Big Result ) and MySQL ones, with no better results.

Same for me, and I did try a lot of combos and also had a look at what VB/MySQL used for options.

BTW, what I find interesting is, that the default page size of MySQL is 4096 bytes....



Fred if you have any idea about that, you are welcome.[/quote]

[Fangbeast]

"Guess you guys got that remark in the wrong throat" ?? How many throats do I have?? Sorry Max, I don't understand.

Anyway, to cut a long story short, I have been playing with the blob idea with a friend of mine working in a large business (huge corporate database) and our conclusions are that storing pictures in this fashion is inefficient and very space wasting.

We have analysed several database fo size and speed after populating with pictures and decided that isn't not a good idea. At least, not for us.

Assuming that if we would use pictures at some stage, we would use a virtual bin of some sort. At the moment, I have no plans to implement it for this reason. If someone comes up with a compelling reason (and an easy way to do it), modify the MySQL gui client i put into tips and tricks (or anything else) re-upload it for others.

Regards,