PostgreSQL Base64图像解码问题

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: System.Byte[]

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:

http://remoteserver:82/pos.asmx.GetSql?datasource=DATASOURCE&query=MYQUERY

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. http://remoteserver:82/pos.asmx.GetSql?datasource=12345&query=SELECT+*+FROM+picture+WHERE+part+=+'01000015'";

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

查看全部
dongwo1234
dongwo1234
2014/10/06 20:04
  • curl
  • postgresql
  • xml
  • php
  • 点赞
  • 收藏
  • 回答
    私信
满意答案
查看全部

1个回复