PostgreSQL Base64图像解码问题

Question

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

Answer 1

You should preferably have the server storing the data in PostgreSQL as a bytea field, then encoding to base64 to send to the client, but it sounds like you don't control the server.

The string System.Byte[] suggests it's an app using .NET, like ASP.NET or similar, and it's not correctly handling a bytea array. Instead of formatting it as base64 for output it's embedding the type name in the output.

You can't fix that on the client side, because the server is sending the wrong data.

You'll need to show the server-side tables and queries.

---

Update after query amended:

You're storing a bytea and returning it directly. The client doesn't seem to understand byte arrays and tries to output it naïvely, probably something like casting it to a string. Since the documentation says it expects "base64" you should probably provide that, instead of a byte array.

PostgreSQL has a handy function to base64-encode bytea data: encode.

Try:

SELECT 
  account, company, date_amended, 
  depot, keyfield, part, 
  encode(picture, 'base64') AS picture,
  picture_size, source
FROM picture 
WHERE part = '01000015'

The formating isn't significant, it just makes it easier to read here