Tag Archives: PostgreSQL

SQL blob to file

This post shows you how to export and extract your SQL blob to file using SQL Blob Export.  You can download a free 14-day trial here.

Start SQL Blob Export, and click on the Export images and files item.

Enter your project details if you plan to reuse the export settings.  Otherwise, you can just accept the default values.

Enter the connection details to connect to your database server.

Select the table you want to export your blobs from.  Tables containing blob columns are highlighted in green.

You can also enter a SQL command to select the blobs you want to export.  We will use the following SQL command to retrieve our blobs.

Now you need to tell SQL Blob Export how to name the exported files.

Enter the folder to store the files in.

You can ignore the OLE Object types section if your blobs are not stored in Microsoft Access OLE Object-type fields.

In the Other types section, enter the naming convention for your files.

We use the value <ProductPhotoID:0000>_<%column%> to name our files.  <ProductPhotoID> is one of the columns we retrieved using our SQL command, and is a number.  The suffix :0000 means we want SQL Blob Export to format the number to 4 digits e.g. 14 is formatted to 0014, 234 is formatted to 0234 etc.

We can use any column names to name our exported file, as long as the column exists in the table we chose to export, or is a column retrieved by our SQL command.

The <%column%> value is a system value indicating the index of the column storing the blob.  Remember that our SQL command selects 2 blob columns (ThumbnailPhoto and LargePhoto), so our naming convention needs to be able to identify from which column the file was extracted from.

Once we have set up our export options, SQL Blob Export will extract the SQL blob to file.

One important thing you should note is that we did not tell SQL Blob Export the type of blob that’s stored in our tableSQL Blob Export can identify the blob type and use the correct file extension accordingly.  It can identify most image formats (jpg, png, tif, bmp, gif, tga, emf, wmf), Office files (doc, docs, xls, xlsx, ppt, pptx), Open Office files, PDFs, and many more.  For a complete list of recognised file types, see here.

PostgreSQL statistics

PostgreSQL has a neat set of pg_stat views from which you can obtain statistics for tables, indexes, etc.  From DB Doc 4.2 and PgComment 1.2 onwards, you can now view the database, table, and index statistics in the DB Doc reports and in PgComment object inspector.

In DB Doc, database statistics are displayed on the database properties page

table statistics are displayed on each tables’ properties page

and index statistics (idx_scan, idx_tup_read, idx_tup_fetch) are displayed as part of the index definitions.

If you choose not to display the statistics, just deselect the Display database, table, and index statistics option.

 

PgComment, our free tool to easily add/modify comments to your PostgreSQL objects, displays the statistics details in the object inspector window.

– database statistics

 

– table statistics

 

– index statistics

SQL Multi Select on Ubuntu 17.04 and Wine 2.0.1

Well, SQL Multi Select mostly runs on Ubuntu 17.04 with Wine 2.0.1.  Instructions for installing Wine 2.0.1 on Ubuntu 17.04 can be found here.

What works:

  • running multiple statements in a script
  • using scripts located on Linux file system
  • displaying images in result set
  • using the command line interface to run projects
  • connecting to PostgreSQL databases via SSL connections
  • exporting results to text files and SQLite database

What doesn’t work:

  • clicking on the summary file to open it – you’ll need to manually locate and open the file
  • help file won’t open because it’s in a chm format – use the online help instead (http://www.yohz.com/help/dbdoc/index.html)

If you come across any other functionality that doesn’t work on Ubuntu, please do drop us a line at support@yohz.com.