Solved

Store and show Image ms sql

Posted on 2013-12-06
7
568 Views
Last Modified: 2013-12-16
Hi!

Have a table :

CREATE TABLE [dbo].[ImageTable] (
    [ImageID]       BIGINT        IDENTITY (1, 1) NOT NULL,
    [TransactionID] BIGINT        NOT NULL,
    [LocationID]    BIGINT        NOT NULL,
    [UserID]        BIGINT        NOT NULL,
    [Organisation]  BIGINT        NOT NULL,
    [CreatedDate]   DATETIME      NULL,
    [UpdatedDate]   DATETIME      NULL,
    [TransImage]    VARCHAR (MAX) NULL
);

Open in new window


Have mad this stored procedure to store
Base64 string to Image table -> TransImage have the stored image

CREATE PROCEDURE [dbo].[P_INSERT_ImageTrans]
@UserID BIGINT, @LocationID BIGINT, @TransactionID BIGINT, @Image varchar(max)
AS

Declare @Organisation bigint
Declare @dagensdato nvarchar(30)

exec GET_Todaysdatetime @dagensdato=@dagensdato output

set @Organisation = 1

exec dbo.GET_Active_Userorganisation @UserID=1,@Organisation=@Organisation output

INSERT INTO dbo.ImageTable
    (TransImage, TransactionID, LocationID, UserID, Organisation, CreatedDate , UpdatedDate)
VALUES
    (@Image,
	 @TransactionID,
	 @LocationID, 
	 @UserID,
	 @Organisation,
	 @dagensdato,
	 @dagensdato
	 )

Open in new window


When picture is stored in the database

The TransImage field in the database contains now Bind64 string


In php i use this code to display picture

<?php

  $sql = "SELECT * FROM {$table6} WHERE ImageID=9";
  $resultraptmp = sqlsrv_query($conn,$sql); 

  if( $resultraptmp === false )
   {
       echo "Error in executing statement 7.\n";
       die( print_r( sqlsrv_errors(), true));
   }

    while($row3 = sqlsrv_fetch_array($resultraptmp)){

       $bilde = $row3['TransImage'];
    ?>
      <tr>
        <td  class="rapport1_del3" valign="top"><?php echo base64_encode($bilde); ?></td>
      </tr>    



   <?php } ?>    

Open in new window


But it only display characters and not the picture


What am i doing wrong. ???
0
Comment
Question by:team2005
  • 4
  • 3
7 Comments
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39701227
PHP base64_encode() is appropriate for encoding an image (or any binary string) for safe transport over interfaces that might not be binary safe, such as data base queries, HTTP requests, etc.  It makes the string bigger, but removes any of the risky characters like NUL, etc.  It is reversible with base64_decode() which can restore the original binary data.

One possible design would be to take the raw image file and use base64_encode() before putting it into the data base, then use base64_decode() after retrieving the image file from the data base.  If you want to send an image to the browser as part of an HTML document, you will need to deal with the image header(), so you will need to learn all about this:
http://www.faqs.org/rfcs/rfc2616.html

A more sensible design would put the image file into the server file system and put the URL of the image file into the data base.  There are a host of reasons for not putting an image into the data base, among them usability, performance and backup.
0
 
LVL 2

Author Comment

by:team2005
ID: 39712756
Hi!

My Image is stored in the database as varchar(max)

And tryed this code to display pictures, but no picture is shown ?
What is wrong ?
<?php
   error_reporting(E_ALL);
   ini_set("display_errors", 1);

require_once('RAY_EE_config.php');
require_once('Connect_databse.php');
$table6 = 'ImageTable';

  $sql = "SELECT * FROM {$table6}";
  $resultraptmp = sqlsrv_query($conn,$sql); 
?>

<table>
   <tr>
<?php


  if( $resultraptmp === false )
   {
       echo "Error in executing statement 7.\n";
       die( print_r( sqlsrv_errors(), true));
   }
    $y=0;
    while($row3 = sqlsrv_fetch_array($resultraptmp)){
       
    
       $bilde = $row3['TransImage'];


       
    ?>
        <td valign="top" class="rapport_image1"><img src='data:image/jpg;base64,'<?php echo base64_encode($bilde);?> height="160" align="left"></td>

   <?php } ?>
bilde
   </tr>
   </table>    

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39712904
Typically pictures are stored in the server file system, and just the URL is stored in the data base.  Then you can use the URL to construct the HTML <img> tag.  This is the way browsers, databases, and HTML work together to render online documents with images.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 2

Author Comment

by:team2005
ID: 39713374
Hi!

I dont have a file, i have a stored base64 string in database.

My question is how to get the base64 picture shown, using php code ?
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39714052
It will always be easier to ride the horse in the direction he is going, and the image horse does not want to ride in the database!  It is possible to drive nails with a wrench, but it's not the right tool, and you will never see a carpenter using a wrench to drive nails.  The data base is the wrong tool for image storage.  The file system is the right tool.  You want to get the image out of the data base and store it in the file system.  That is the only professional solution.  Here is what I recommend:

1. Read the base64 string out of the data base by using a SELECT query.
2. Decode the base64 string into the original image string with base64_decode().
3. Write the image string into the server file system with file_put_contents().
4. Generate the HTML <img> tag with a src= attribute pointing to the location in the file system.

Once you have done that, you have an image file in a place that can be useful.
0
 
LVL 2

Author Closing Comment

by:team2005
ID: 39721515
thanks
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39721538
Thanks for the points and thanks for using EE, ~Ray
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question