Monthly Archives: April 2017

Exporting images from OLE Object fields

Leave a reply

→ This article refers to SQL Blob Viewer, which has now been renamed to SQL Image Viewer.  The techniques described in this blog is still applicable, as the functionality of the product remains the same.  Only the name has changed.

If you use OLE Object fields in your Access or SQL Server databases to store images, you know it’s convenient to just be able to click on the item and have Windows open the image using the registered viewer on your computer.  However, it becomes difficult to extract the images because Access adds additional OLE data to the stored images, thus changing their original form.

Take a Windows bitmap image,

and store it in an OLE Object field in a SQL Server linked-table.

The original file was 2,002,182 bytes in size, but has increased to 2,002,298 bytes when stored in the OLE Object field.  Access has added 116 bytes to the image.

and that the data stored in the field is different from the original bitmap file.

How can we then export this image?  One way is to open the table using Access, then double click on the data, and Access should then open the stored image in the registered OLE server for that image type, in our example Microsoft Paint.

However, this is a tedious process if we need to export a lot of images.  SQL Blob Viewer and Access OLE Export are two applications that we developed to easily export images and other data stored in OLE Object fields.  Exporting the items using SQL Blob Viewer or Access OLE Export is just a matter of writing the appropriate SQL query or selecting the right table.  SQL Blob Viewer is for users who are comfortable writing queries and want more control over how images are exported, while Access OLE Export is for users who just want to be able to select a table or write a simple query and export the images as is.

SQL Blob Viewer also displays a preview of the image when we query the table:

So if you have a ton of images that you need to export from OLE Object fields, give SQL Blob Viewer or Access OLE Export a try.

There may be situations where both products are unable to identify embedded images correctly.  This may be because the registered OLE Server for that content type is not yet supported.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

See also:

Table and index sizes in DB Doc 4

Leave a reply

Starting from DB Doc 4, table and index objects now have size properties.

For tables, the following size properties are available:

  • total size
  • table size (excludes indexes and toast sizes)
  • indexes size
  • toast size

For indexes, there is a single size property.  In all the report templates, the size properties are displayed.

In PDF and DOC reports:

 

In HTML and CHM reports:

DB Doc also displays the largest 20 tables and indexes by size in the template reports.  As usual, you can always customize the number of tables/indexes to display, and the details shown.

In PDF and DOC reports:

 

In HTML and CHM reports:

 

Take a look at the completes samples available here: http://www.yohz.com/dbdoc_samples.htm

Documenting your PostgreSQL database

Leave a reply

PostgreSQL has a pretty nifty feature to add comments to database objects, using the COMMENT function.  You can pretty much add comments to almost any objects, like tables, view, foreign keys, constraints, columns, etc.

You add a comment using the COMMENT ON … function, then retrieve the comment using the obj_description function for most objects.  For columns, you’ll need to use the col_description function.

All this is rather tedious if you plan to add/remove comments for a lot of objects.  To make this task easier, we have recently released a free product, PgComment, downloadable here.  With PgComment, you just connect to your database and the application will list out all the comments attached to your tables, columns, indexes, views, sequences, domains, and functions.

You can then easily modify the comments, and apply them to the database.  Changes are cached, and sent to the server in a single batch, allowing for faster editing locally.  With the schema displayed in a hierarchical tree, you get an overview of what’s been commented and what has not.  This makes it so much easier to document your PostgreSQL database.

Another feature of PgComment is that you can view the properties of your database objects.  For tables, you can see details like the owner, total size, index size, estimated rows etc.

For indexes, you can see details like the index definition, index size, and various other properties.

Displaying these properties should help you write better comments for your objects.

If you want to generate PDF or HTML reports of your PostgreSQL database schema, take a look at DB Doc.  With DB Doc, you can generate customizable PDF and HTML schema reports in just a few clicks.

How fast is DB Doc

Leave a reply

DB Doc is our PostgreSQL database schema documentation tool, and we recently had the opportunity to work on a fairly large production database.  The database had 7 schemas, containing over 800 tables, over 17000 columns in total, 300+ sequences, and 350+ functions.  DB Doc was ran on the server with a single Intel I5 3 GHz CPU, and the disks were consumer-grade SATA disks.

To read the entire database schema, DB Doc took 15 seconds.  To generate the HTML documentation, which comprised over 1600 HTML pages, took 7 seconds.

7 seconds!

That means it took DB Doc less than 30 seconds to read the database schema and generate a 1600 page HTML report documenting that database.

To generate a PDF report that contained almost 11000 pages took 3 minutes 45 seconds.

11000 pages in 3 minutes 45 seconds!

Understandably, we are very proud of what we have achieved with DB Doc.  If you have a PostgreSQL database that DB Doc seems to be having problems documenting, please do drop us a line at support@yohz.com.  We’re pumped to improve on the above numbers even further!

Extracting files and data from OLE Object fields

1 Reply

OLE Object fields are commonly used by applications using Access as the back-end database to store files and common document formats.  It is convenient because Windows handles how the files or documents are opened, modified, and saved via OLE servers.  However, it is difficult to extract the data from those fields because of the additional OLE information embedded together with your data.

For example, let’s create a table in Access, and store a simple Excel workbook, first as an embedded object, and second as an embedded file.  Our table structure is as follows:

We create the first record by directly embedding an Excel workbook.

For the second record, we simply attach an existing Excel workbook.

In Access, we can open both workbooks easily simply by double-clicking on them.  This is the OLE servers at work.

Now if we take a look at the size of the 2 records using SQL Image Viewer, we can see that the size of the embedded workbook is larger than the attached file.  The size of the attached file record is also larger than the original file size, because Access needs to add additional data to the file.

If you try to export the data as is, you will not be able to open the exported files, because the format itself is not Excel-compliant.  Both records in the OLE Object fields have had additional OLE wrappers added to them.

The usual way to extract the content is to open each item individually in Excel, and save them to files.  This is a tedious process if you have a lot of records you need to extract.

We have 2 products, SQL Image Viewer and Access OLE Export, that can remove the OLE wrappers for data stored in OLE Object fields, and export them to disk.

SQL Image Viewer is for technical users who are comfortable writing SQL queries to retrieve the required data.

Access OLE Export is for less technically inclined users who just want to be able to select a table and export their data quickly.

Both products can identify embedded Office document content, images, PDF content, Open Document content, and other common binary types.  Both products can also extract data from OLE Object fields used in other database engines like SQL Server, MySQL, Oracle, PostgreSQL, Firebird, SQLite, and ODBC data sources.

There may be situations where both products are unable to identify embedded content correctly.  This may be because the registered OLE Server for that content type is not yet supported.  For e.g. PDF files can have different OLE servers like Adobe Acrobat, Foxit PDF Reader. Nitro, etc.  In these cases, send us (at support@yohz.com) a sample of the embedded data, and we will add support for that OLE server type.

Download a FREE 14-day trial of SQL Image Viewer or Access OLE Export now, and see how easy it is to export your ‘trapped’ OLE Object data.

If instead you need to insert or update OLE-Object data in your databases, have a look at Access OLE Import.  Using Excel spreadsheets as the input data, you can easily add and update your embedded or linked OLE-Object data.

You can purchase our products on this page.

See also:

Why are both 32-bit and 64-bit versions of Access OLE Export installed

Leave a reply

When you install Access OLE Export, you find that both the 32-bit and 64-bit versions are installed.

This is because when you access an Access database, you will need to use the 32-bit version of Access OLE Export if you have installed the 32-bit version of Microsoft Office on your machine, or if you have the 32-bit version of the Access Database Engine 2010 Redistributable installed.  If you have installed the 64-bit version of Office, or the 64-bit version of the Access Database Engine 2010 Redistributable, you will then need to use the 64-bit version of Access OLE Export.

The Access Database Engine Redistributable itself needs to match the version of Office that’s installed.  For example, if you have the 32-bit version of Office installed, and you try to install the 64-bit version of the Engine, you are prevented from doing so.

If you selected the wrong version of Access OLE Export to access your Access database, the following message is displayed.

Simply use the other version then.  Alternatively, you can use the Access OLE Export (Access mode) shortcut which will start the appropriate version of Access OLE Export depending on which version of Office is installed on your machine.

Customizing DB Doc PDF and Word report templates

Leave a reply

The PDF and Word reports in DB Doc share the same template.  Most of the elements in the PDF template are customizable.  To customize the report, click on the Edit button next to the Report template file name.

There are broadly 2 types of elements in the report templates:

  • text memos

Text memos are text elements you use to display static text or database values.  Double click on any text element in the template to bring up the memo editor.

In the memo editor, enter any static text you want to display

or enter a database schema value.  Text memos enclosed in square brackets are schema element values, and are retrieved from the schema that is being processed.

You can mix both static text and schema element values in the same text memo.  For e.g. in the Table of Contents page, there are a lot of text memos that display the schema element type, which is a static text, followed by the schema element name.

 The DB Doc help file contains a reference for all the schema elements you can use in your reports.  Once you’ve set up your text values, you can drag the element handles to resize the element, or reposition the element in the report.

  • graphic elements

Graphical elements are those images or icons you see displayed in the report template.  To modify the image, double click on the existing image.

This brings up the Picture editor.  Click on the Load icon to load your png, bmp, or jpeg image, then click on the OK button to display that image in your report.

The report is now updated to display the image you loaded.  You can resize and reposition the image by dragging the image or image handles.

If you want to fit the image into a smaller area, select the Stretched option in the object inspector.

Once you’ve made the changes. remember to save the template.  You can save the template under a new name to create a new template if you want to preserve the default template.  Remember to select the appropriate template when you generate your PDF report.

If you need any assistance customizing the template, please do drop us a line at support@yohz.com.

Customizing the logo and copyright message in the DB Doc PDF and Word reports

Leave a reply

In DB Doc, the PDF and Word reports share the same templates.  On the Create docs page, select the PDF settings tab, and click on the Edit button.

This brings up the report editor page.

Double click on the YOUR LOGO HERE image object to display the picture editor.  Click on the Load button to load your company logo.  You can load any jpeg, png, or bmp image.

Once loaded, click on the OK button.

Your log is now displayed on the report.  Resize the logo, or reposition the logo as per your requirements.

To modify the copyright message, scroll down the report until you see the footer.  Double click on the footer to bring up the memo editor.

Modify the text, and click on the OK button.

The modified text is now displayed in your report.

To make these changes permanent, save the report template, either using the existing name, or under a new name.  If you save the template under a new name, remember to select that template when you generate the PDF report.

SQL Multi Select use case

Leave a reply

If you have manage multiple databases in your work, there would have been occasions where you needed to run the same set of queries against multiple databases or database servers.  The regular and tedious way to do this would have been to connect to each database/server, run your query, log the results, and move on to the next database/server.

SQL Multi Select removes the tedium of the above process.  Basically, you set up a list of all the servers you manage in your environment.  You then group these servers by function (each server can exist in multiple groups).  For example, you may have a group containing just your development servers, another group for your production servers etc.

Now, you can run a query, or a set of queries, against these server groups.  The results of these queries are consolidated in a single result set, so you can easily compare the results across different servers.  For ad-hoc queries, this makes it easy to run the same query on multiple database/servers.

You may also have queries you run repeatedly e.g. queries to check database sizes, server statuses etc.  You can use the SQL Multi Select command line interface (available only in the Standard and Professional editions) to run these queries in a scheduled job, and have the results saved in text files or in a SQLite database.

SQL Multi Select supports PostgreSQL, MySQL, and Oracle databases.  All server groups will contain servers of the same type, so for example, you cannot mix MySQL and Oracle servers in the same query group.

Customizing DB Doc HTML and CHM report templates

Leave a reply

The DB Doc HTML and CHM reports share the same templates.  The report is generated by means of a Pascal-like scripting engine, so every single element is customizable.  The DB Doc help file contains a class reference for every schema element.

To edit the template, click on the Edit button next to the script file name value.

This brings up the script editor, which you can modify.

The script runs roughly in this order of procedures:

Main > CopyTemplateFiles > CreateHTMLPages

The Main procedure sets up some global values for the script, and also sets up the output files depending on whether it is generating a HTML or CHM file.

The CopyTemplateFiles procedure copies the icons and stylesheets from the DB Doc template folder into the selected HTML output folder.  If you want to modify the default icons, just replace the image files in the DB Doc images template folder (e.g. C:\Program Files (x86)\Yohz Software\DB Doc\template\images\).   The HTML and CHM outputs use only the 16×16 and 32×32 images.

The main work is performed in the CreateHTMLPages procedure.  This procedure creates the individual schema elements pages by calling the relevant procedures.  To generate the pages for each type of schema element, there are usually 3 steps:

  • the CreateListItemLink procedure is called to create the table of contents navigation structure for that element type
  • if that element type consists of multiple items like tables and views, a listing function is called to create a summary page e.g. CreateTableListingPage for tables, CreateViewListingPage for views
  • the details page for that element type is called e.g. CreateSchemaPage for schemas, CreateTablePage for tables

Within each of the procedures, you can modify the script to display different values depending on your needs.  Take the CreateTablePage function for example.  At the beginning, we create the HTML file for that table, and insert the standard header elements.

We want to display some details about that table, so we create a HTML table to display those details.

Next comes the columns.  We create a HTML table, set up its columns, and then iterate through all the columns, and display each columns’ properties.

The same process is repeated for indexes, check constraints, unique constraints, triggers, and foreign keys.  You can modify any element you want simply by modifying the script.  Once the page for this table has been completed, it’s then saved as a HTML file.

Every schema element that has a HTML page is named using a specific naming convention provided by the GetObjectDetailsPage function.

Using this function, you can get the name of a HTML page for a particular schema element, simply by passing the element as a parameter to this function.  For example, in the CreateTablePage function, when listing out the foreign keys, we display a link to the reference table using the GetObjectDetailsPage function by passing the reference table as the parameter.  It does not matter that the page may not have been created yet, because when it does get created, it will use the same naming convention provided by the GetObjectDetailsPage function.

So that’s generally how DB Doc uses scripting to create your HTML and CHM reports.  If you have had any experience coding or writing scripts, you should be able to modify the scripts quite easily.  As mentioned earlier, the class reference in the help file would assist you greatly in seeing what methods are available for each schema object type.

If you need any assistance customizing the template, please do drop us a line at support@yohz.com.