I am having an issue converting an image stored as base64 in a PostgreSQL database into an image to display on a website. The data type is bytea and I need to get the data via cURL.
I am working with an API to connect to a client's stock system which returns XML data.
I know storing images this way in a DB is not a great idea but that's how the client's system works and it can't be changed as it is a part of an enterprise solution provided by a 3rd Party.
I'm using the following to query the DB for the PICTURE field from the PICTURE table where the PART = 01000015
$ch = curl_init(); $server = 'xxxxxx'; $select = 'PICTURE'; $from = 'picture'; $where = 'part'; $answer = '01000015'; $myquery = "SELECT+".$select."+FROM+".$from.'+WHERE+'.$where."+=+'".$answer."'"; //Define curl options in an array $options = array(CURLOPT_URL => "http://xx.xxx.xx.xx/GetSql?datasource=$server&query=$myquery+limit+1", CURLOPT_PORT => "82", CURLOPT_HEADER => "Content-Type:application/xml", CURLOPT_RETURNTRANSFER => TRUE ); //Set options against curl object curl_setopt_array($ch, $options); //Assign execution of curl object to a variable $data = curl_exec($ch); //Close curl object curl_close($ch); //Pass results to the SimpleXMLElement function $xml = new SimpleXMLElement($data); //Return String echo $xml->row->picture;
The response I get from this is:
Thus if I use base64_decode() in PHP I am obviously just decoding the string "System.Byte".
I am guessing that I need to use the DECODE() function in PostgreSQL to convert the data in the query? However, I've tried loads of combinations but I'm stuck. I've had a few downvotes for questions and I'm not too sure why so if this is a bad question I'm sorry, I just really need some help with this one.
(nb:I've replaced the IP and $server with xxxxx for security)
To explain further: The client has a POS system which is based on ASP.NET and saves the data as XML files on the remote server. I have access to this data via an API which includes a SQL query function using HTTP/cURL defined as follows:
So to get the field that contains the picture data I am currently usingthe above code.
The query is in the CURL URL i.e.
However, this returns
System.Byte instead of encoded data which I can then decode in PHP.
Additional info: PostgreSQL version: PostgreSQL 9.1.3 on i686-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-51), 32-bit
Table Schema: Available here: http://i.stack.imgur.com/sc8Gw.png