ansaurus

Question

Answer 1

A:

Hey.

I wrote this article a while back on this subject. It should help you with #2 and #3. It uses MySQL, but the basics are the same, you just need to replace the MySQL calls with the MSSQL calls.

As to #1, I would go with the obvious choice: "image".
I am not 100% sure of the differences between the two, however. It just seems obvious :)

Edit, according to this, it appears that the image datatype is deprecated. It will be removed in future versions. Not sure how much this affects you, seeing as you are using a 10 year old version, but it is worth keeping in mind.

Atli 2010-03-31 11:21:07

Not necessarily an answer but whenever I upload images to a db, I base64 encode it and then store it in a clob.When I want to show it, i'll just decode it.

Jamie 2010-03-31 11:34:17

@Jamie why is this useless encoding?

Col. Shrapnel 2010-03-31 11:37:47

@Jamie Yea, why base64? All that does is increase the size of the data...

Atli 2010-03-31 11:44:35

@Col. Shrapnel - It's not neccessarily useless. There's a good argument for it here: http://www.stoimen.com/blog/2009/04/23/when-you-should-use-base64-for-images/

Jamie 2010-03-31 11:45:47

@Jamie But this is completely different issue discussed in the article you linked to! It does encode at displaying, not at save time!

Col. Shrapnel 2010-03-31 12:17:40

@Jamie. Col. Shrapnel is right, these two scenarios are in no way comparable. - The reason you use Base64 for inline `<img>` tags is because the character representation of some of the data may mess up the HTML parsing. - That is not the case when inserting into a database. Even when inserted via a string (like a PHP script creating a SQL query) the small list of special characters that may mess up the query can easily be matches against the data and escaped.

Atli 2010-03-31 12:47:25

If you write a tutorial, at least learn them to send it to the db-server as hex; it would increase the performance.

Jacco 2010-03-31 12:54:55

@Jacco How so? I write what I know, and I know no reason why converting the data to hex before inserting it would increase performance. (Wouldn't mind being corrected, though. Increased performance is always good :])

Atli 2010-03-31 13:02:00

@Atli It is considered bad-practice to use mysql_real_escape_string on binary data. The unescaping can take up considerable resources in MySQL, because of how thing are handled. (because escaped data is handled as string, the whole string is read into memmory). Since MySQL can use stream operators on a HEX-coded data, sending data as `x'".bin2hex( $blob )."'` lessens the load on the server.(Some people advacate using base64 encoding to sidestep the unescape problem, but this inflates the stored data by aprox 33%, where the hex stream is stored as the original binary data).

Jacco 2010-03-31 14:39:27

@Jacco Ahh, I see. I was not aware of that issue. Thanks for pointing that out. - I wonder if using a prepared statements would bypass this as well, or if the driver escapes it in the same way mysql_real_escape does.

Atli 2010-03-31 16:54:10

@Jacco I'm having a hard time confirming this. I've been running tests and it seem that using HEX-coded queries uses more resources in both PHP and MySQL than the binary-string equivalents. Memory usage goes up to 24% higher and CPU timing up to 86% higher. (Granted, these are rough tests, but still.) - You wouldn't happen to have a reference to any resource on this topic?

Atli 2010-04-01 04:06:32

The URL that documented the differences has died, I've been looking for other info on the subject but can't find any good benchmarks. If my memory serves me right, the differences started to show from 2MB blobs and larger. Looking at my example, it might be the `'(...)'` still makes it a string. I guess using `x".bin2hex( $blob )."` could make the difference as could the db-engine (InnoDB vs MyISAM)

Jacco 2010-04-01 16:38:32

@Jacco OK, thanks. I've so far only been testing files up to ~1.5MiB, so that may be the reason. I'll be testing it further. Interested to find out the specifics of this - I created a new question about this, by the way. http://stackoverflow.com/questions/2558453/why-use-bin2hex-when-inserting-binary-data-from-php-into-mysql

Atli 2010-04-01 18:13:15

Answer 2

+4 A:

Hi,

The data type should be text because the best way to save an image to a database is to save its path. Let your OS do the job of storing the actual files.

simoncpu 2010-03-31 11:44:29

While I agree that this is generally the best method, this is not what he is asking for. - And to say conclusively that this is the **best** method is wrong. It is *generally* the best method, but certainly not in always.

Atli 2010-03-31 12:58:53

You may have a point, but I will say conclusively that storing an image to a database is wrong. :)

simoncpu 2010-04-27 12:23:06

Answer 3

A:

this script is easy and hope will help you:

function get_ext($key) { 
    $key=strtolower(substr(strrchr($key, "."), 1));
    // Cause there the same right?
    $key=str_replace("jpeg","jpg",$key);
    return $key;
}



    $filedir = './photo/'; // the directory for the original image 
    $maxfile = '2000000'; 
    $mode = '0666'; 
    $userfile_name = $_FILES['image']['name']; 
    $userfile_tmp = $_FILES['image']['tmp_name']; 
    $userfile_size = $_FILES['image']['size']; 
    $userfile_type = $_FILES['image']['type']; 
    if (isset($_FILES['image']['name']))  
    {
        $ext=get_ext($_FILES['image']['name']);
        $userfile_name = time()+rand(1000000000,1000000000000000).".".$ext; 
        $prod_img = $filedir.$userfile_name; 
        move_uploaded_file($userfile_tmp, $prod_img); 

    } 


    $sql = "INSERT INTO user SET  
    name = '$HTTP_POST_VARS[name]',           
    img = '$userfile_name'";
    $result = mysql_query($sql);
    if(!$result){ 
        echo('MySQL INSERT Error'); 
        break;

}

bekman 2010-03-31 12:02:38

Whilst I appreciate you giving this as help....that truly is some ugly code.I didn't know people still use $HTTP_POST_VARS....

Jamie 2010-03-31 12:11:06

-1 for the SQL Injection risk and the use of the deprecated `$HTTP_POST_VARS` array.

Atli 2010-03-31 12:50:30

why the unused `$maxfile = '2000000';` and `$mode = '0666';`? smells like 'Copy, Past, no-understand'

Jacco 2010-03-31 12:52:01

@Jamie.. how about now? i removed the $HTTP_POST_VARS part..it has no use [email protected] $maxfile = '2000000'; limits the size of image you upload in Bytes and you can change it.. and it is a "Copy, Paste" from my own website and works just fine.

bekman 2010-04-01 04:27:15

`$maxfile = '2000000';` assigns the value `2000000` to the variable `$maxfile`. The script you posted will accept any file I send it. Also, by removing the check, you crippled the script, which is vulnerable to sql-injection anyhow. In short: it is a bad example. (-1)

Jacco 2010-04-01 16:55:29

Answer 4

+1 A:

Typically on SQL Server, you would use a BLOB, Binary Large OBject, to store images. We used it for Word documents on a previous project, and it worked just fine. See this article on Database Journal for more info, although a quick Google for the BLOB type will throw up lots more examples.

Matt Gibson 2010-03-31 13:12:17

I didn't think SQL Server had a BLOB type. Or do you perhaps use it more as a general term for all binary types?

Atli 2010-03-31 13:30:09

ansaurus

tags:

views:

answers:

Image save in database

related questions