Link to home
Create AccountLog in
Avatar of burchmarcel
burchmarcelFlag for Switzerland

asked on

MS Access insert long binary data

I need to write attachments like word, excel or pdf files into .mdb database.

After a long search I found this solution for reading the content.

conn=odbc_connect($_SESSION['mgmtsys'],'','');
if (!$conn) exit("Connection Failed: " . $conn);

$query = "set textsize 2147483647";
$query = "SELECT * ";
$query .= "FROM tblVAttach ";
$query .= "WHERE VA_VZaehler=".$_GET['verbid']."; ";

$rs=odbc_exec($conn,$query);
if (!$rs) exit("Error in SQL");


odbc_binmode($rs,ODBC_BINMODE_RETURN);
odbc_longreadlen($rs,4096);
while (odbc_fetch_row($rs)) {
   $fileHandle = fopen("temp\\".odbc_result($rs,"VABezeichnung").odbc_result($rs,"VAExt"), "wb");
  while (($chunk=odbc_result($rs,'VAAttach'))!==false) {
        fwrite($fileHandle,$chunk);
   }
  fclose($fileHandle);
   //do something with the file
}
// change to ODBC_BINMODE_CONVERT for comparison
odbc_close($conn);

}

I thought I've finished, but I didn't found an inversion of the load function. So what I need is a inversion of the following statement:
   while (($chunk=odbc_result($rs,'VAAttach'))!==false) {
        fwrite($fileHandle,$chunk);
   }

My tries:
Inserting it, after replacing ' with '', through plaintext with the sql statement gives the following error:
"odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in string in query expression"

Inserting it through odbc_execute with ? params gives the following error:
"odbc_execute(): SQL error: [Microsoft][ODBC Driver Manager] Driver does not support this function, SQL state IM001 in SQLDescribeParameter"

I would be very pleased if someone can help me.

Thx

Marcel Burch
Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

Possible suggestion - these are not really the kinds of things that normally get put into data base tables.  There are a lot of reasons to avoid BLOB-like fields, mostly related to performance and backup.  Instead consider writing the files into the server file system.  Give each file a unique name and store the name along with the URL in the data base.  Then when you need the file, it's as simple as file_get_contents().
Avatar of burchmarcel

ASKER

yes that is true, but i'm writing a "web-extension" to an existing fat client program. So I cannot change something on the settings, the .mdb is the base for both sides... :(
ASKER CERTIFIED SOLUTION
Avatar of gr8gonzo
gr8gonzo
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Also note that Access 2007 and newer database formats (.accdb) allow you to store file in a "Attachment" datatype.
This will avoid all the code involved with doing this with BLOBS

The othe advantage there is that you can:
- store multiple files per record
- browse to and select files
- open siles
- save the file back "out" of the database

Obviously (event though the attachment datatype uses BLOB data behind the scenes for smaller storage size), if you have a lot of images, the db will stil bloat.

JeffCoachman
Thanks a lot for your help. migration to ms sql is planed, but it will take some times till realisation.

the tip from gr8gonzo was very usfull, with a little change it works now.

$dataString = file_get_contents($_FILES['file']['tmp_name']);
$arrData = unpack("H*hex", $dataString);
$ds = "0x".$arrData['hex'];

Thanks a lot!!!

Marcel Burch
Thanks a lot for your advice, i'm looking forward to the change of the database.

Kind regards,

Marcel Burch