Using “BYTEA” DATATYPE WITH POSTGRESQL

Posted on February 11, 2016 by Alok Sah

Storing large object files into database is much interesting then saving path of files.
Large object data like images, document can be save into database by two simple method:
1> Save images in base encoded form that i had explained in my earlier article PHP Encoding with base64
2> Use BYTEA Data type in Postgresql.
“The BYTEA Data type allows storage of binary strings” you may say as RAW Bytes.
When you SELECT a Bytea type, PostgreSQL returns octal byte values prefixed with ‘\’ (e.g. \032). Users are supposed to convert back to binary format manually.

To Start with create column doc_image having data type “Bytea” in a table.
Now when you use file upload on your PHP projects, you will get file data in file super-global variable $_FILES
and you first have to move file to your server like i have move files to upload folder

move_uploaded_file($_FILES["file"]["tmp_name"], "/upload/".$_FILES["file"]["name"]);
// get data of image from upload folder
$dataString = file_get_contents("/upload/".$_FILES["file"]["name"]); 
$raw_data = pg_escape_bytea($dataString); //Store this string into your databse.

Use “pg_escape_bytea” function, it returns escaped string for insertion into a bytea field., and store this raw string ($raw_data) to database.

Now to retrieve that file data use “pg_unescape_bytea” function, It returns the unescaped string, possibly containing binary data.

and if you had used image file to upload then use “pg_unescape_bytea” function to convert into binary and display to browser as

header('Content-type: image/jpeg');
echo pg_unescape_bytea($fileStringfromDB);

mysqldump, An easy way to restore MySQL Database

Posted on December 8, 2015 by Alok Sah

A few days ago, I have to take my database dump which is in my local system and stored in MySQL, i tried from software tool phpMyAdmin but its take too much time and cannot succeed.
so i thought to restore my database by command line and use MySQL utility “mysqldump”.
I found its very easy and in less then a minutes its take all my databases dump.

To use it traverse to mysql bin directory.
C:\wamp\bin\mysql\mysql5.6.17\bin
to take dump there are various parameters with this command.
for a simple use type the following command

mysqldump -u root -p dbname > C:dbdump1.sql

-u is a database user, -p is for password, dbname is your database name, “>” this tells mysqldump command to restore given database to this path “C:dbdump1.sql”.

Another example

mysqldump -u[dbuser] -p[password] dbname > dump2.sql

Note: this give you an “Warning: Using a password on the command line interface can be insecure.”

All Database Dump:
if you want to take all of your database dump at on go, below is command

mysqldump --all-databases --single-transaction --user=root  > dump3.sql

if you have set any password for your database then include “–password=dbpassword” to the above command, this is again insecure.

Specify Databases:
include –databases <db1> <db2>, this allow you to take your specific databases dump

mysqldump --databases db1 db2--single-transaction --user=root --password > dump4.sql

🙂

Dynamic Table Name In Postgresql Trigger

Posted on April 16, 2015 by Alok Sah

Dynamic table name in PostgreSQL trigger used as “TG_TABLE_NAME”
“TG_TABLE_NAME” is a name of the table that caused the trigger invocation.

Like If you want to copy data from one table to another on updating first table

Then start using triggers

Lets say you have table name “first_table”

Create same table as log table to store log of first table data.

Now using PostgreSQL to create functions and trigger:

Firstly, Create a trigger on first table ON UPDATE EACH Rows like

CREATE TRIGGER trigger_name AFTER DELETE OR UPDATE ON first_table FOR EACH ROW EXECUTE PROCEDURE updation_log()

Now Create Function To Execute This Trigger

BEGIN
EXECUTE format('INSERT INTO %I values(($1).*)', TG_TABLE_NAME ||'log') USING OLD ;
RETURN NEW;
END;

format() is simply a string function like as used in C language sprintf().

This procedure explain that first table date is copied to second table defined as “TG_TABLE_NAME ||’log’ ”
if table name is first_table then data is copied to first_tablelog.

Asynchronously files upload with jQuery

Posted on February 27, 2013 by Alok Sah

Have you ever uploaded files with jquery without using any plugin.
If “NO”, then try to do it using Ajax request and Formdata objects.

Finally done it,after experiment.

To upload files please start with “html form ”


<form id="upload_form" enctype="multipart/form-data">
<input name="file_name" type="file" id="file_name"  />
<input type="button" value="Upload File" />
</form>

Initiate formData object and then appending file field to it using append() method it to file as:


var formData = new FormData();
formData.append( 'file', $( '#file_name' )[0].files[0]);

var FileUrl    = 'upload.php?action=uploadfile';
var uploadRes    = ajaxFileUpload(FileUrl,formData);

Now, to handle file , upload use ajax as function ajaxFileUpload as below :


function ajaxFileUpload(FileUrl,formData, request_timeout)
{
if(!request_timeout)
request_timeout = 50000;

$.ajax({
url: strFileUrl,  //server script to process data
type: 'POST',
timeout: request_timeout,
// Form data
data: formData,
//Options to tell JQuery not to process data or worry about content-type
cache: false,
contentType: false,
processData: false
});

}

Hope, you have got the solution !!

aloksah.org

Playing with web

Using “BYTEA” DATATYPE WITH POSTGRESQL

mysqldump, An easy way to restore MySQL Database

Dynamic Table Name In Postgresql Trigger

Asynchronously files upload with jQuery

Recent Post

Links

Categories