Tech Tip: Getting a picture from a SQL database into a 4D BLOB variable using ODBC Pro

PRODUCT: 4D ODBC | VERSION: 11.3 | PLATFORM: Mac & Win

Published On: February 18, 2009

Getting a picture from an external SQL database into a blob variable by using 4D ODBC Pro plug-in is a little bit different than for the other data types in 4D. For most of them you do not need to specify the size of the blob variable in advance. For pictures, the blob size has to be set before getting the picture.

In this example, we have an external SQL database with a table named "Employee" and a picture field named "Picture":

$result:=ODBC_SQLAllocConnect ($connection)
$result:=ODBC_SQLConnect ($connection;"testSQL";"sa";"")

If ($result=SQL_SUCCESS )|($result=SQL_SUCCESS_WITH_INFO)
    $result:=ODBC_SQLAllocStmt($connection;$stmt)
       $sql:="SELECT Picture FROM Employee"
       $result:=ODBC_SQLPrepare ($stmt;$sql)
       $result:=ODBC_SQLDescribeCol ($stmt;1;colName;dataType;colSize;decimalDigits;nullable)
       SET BLOB SIZE(myBlob;colSize)
       $result:=ODBC_SQLExecute ($stmt)
       $result:=ODBC_SQLBindCol ($stmt;1;->myBlob)
       $result:=ODBC_SQLFetch ($stmt)
       $result:=ODBC_SQLFreeStmt ($stmt;SQL_UNBIND )
       $result:=ODBC_SQLDisconnect ($connection)
End if
$result:=ODBC_SQLFreeConnect ($connection)