Author Archives: bloggyadmin

Annotating your DICOM images

You can now add annotations to your images in Easy DICOM Viewer.  First, enter the annotations mode by clicking on the Add and edit annotations button on the toolbar, or use the keyboard shortcut (the T key).

In the DICOM image, right click to bring up the context menu, and select the Add text or Add line items.

Adding a text annotation

Click and drag anywhere in the image to create a text annotation.  You should see a rectangle with the words (annotation) displayed.

When you release the mouse, a bounding rectangle is displayed.

You can resize the annotation rectangle by clicking and dragging on any of the 6 yellow points.  You can also move the annotation by clicking and dragging inside the rectangle.  To change the text, double click inside the rectangle and write your text.

You can change the text color, size, and background color by changing the annotation settings on the toolbar.

Adding a line annotation

Click and drag anywhere on the image to create a line annotation.

You can click and drag on either point to change the start and end point of the line.   Adjust the line settings on the toolbar to change the line color and width.

Hiding annotations

If you don’t want to display the annotations temporarily, deselect the Display annotations item from the annotation mode button.

Deleting annotations

To delete an annotation, ensure that you’re in the annotation mode, then click on the annotation so that the bounding rectangle appears,  Press the DELETE key on the keyboard to remove the annotation.

Using the annotations

Annotations are automatically saved and displayed when you reopen the image.  You can copy the annotated images to the clipboard, or export the annotated images (PNG, JPG, BMP, GIF, etc) by selecting the appropriate action from the context menu.

 

High DPI support

We recently added high DPI support to some of our applications so that they render better when user displays are scaled to 125% or more.  We may have missed 1 or 2 items, so if you encounter any GUI elements that are oversized or undersized, we would appreciate it very much if you could let us know at support@yohz.com.

The applications we’ve added high DPI support for are:

The day SQL Image Viewer outsmarted me

Never thought this would happen, but I was stumped by my own application for a good 24 hours before realizing what was happening.  I’m getting too old for this ****

A user wrote in asking why SQL Image Viewer could identify XML content in his SQL Server table, but he could not use his database’s XML functions to query the XML data.

No problem, or so I thought.  Using a subset of the database he sent, I could see the first few bytes of the XML content and also query the size in SSMS:

Ok, so that’s about 119 Kb of data.  In SQL Image Viewer, the following is returned:

So far so good, SQL Image Viewer identified the XML content.  Now what happens when I try to use the XML function in SSMS:

SQL Server raised an error: XML parsing: line 1, character 2, illegal xml character

Ok, so what’s wrong with character 2?  Back in SQL Image Viewer, I opened the hex viewer, and the XML header looked fine:

Ok then, I decided to export the XML file to disk, and could open it in my browser.  Then it stuck me that the file was over 2 MB in size, but the blob size was only 119 Kb.

What was going on here?  In the hex viewer I scrolled to the end of the XML content, and it too was indicating the content was about 2.6 MB in size.

Why was SSMS and SQL Image Viewer reporting the blob size as 119 Kb, but when viewed in the hex viewer or exported, the content was closer to 2.6 MB in size?

This being a SQL Server database, my first thought was that row or column compression was in place.  However, the compression/decompression would have been done transparently and wouldn’t be showing up this way.  I checked anyway, and sure enough, no row/column compression was active.

Feeling rather lost, I explained the situation to the user hoping they might shed some light on what was going on.  Later, in the shower, while clearing my head (literally and figuratively), it stuck me: zlib streams.

Basically, if the blob content is a zlib compressed stream, SQL Image Viewer automatically decompresses the stream, identifies the content type, and lets you work with the decompressed data.  That was exactly what was happening here – the zlib compressed stream of 119 Kb was being uncompressed to 2.6 MB, identified as an XML file, and subsequent viewing and exporting allowed me to work with the uncompressed data.  I had totally forgotten about this feature!

This also explained why the XML functions could not work directly on the blob content – it was a zlib stream and not a XML text file.

Next time, I should compare the first few bytes as displayed in SSMS against the  values displayed in SQL Image Viewer.  It would then have been immediately clear that we were working with a compressed stream.

And that is how my own application fooled me (or my memory is just getting poorer).

Extracting and viewing PDF files in a SimpleIndex database

SimpleIndex is an application that stores PDF files in SQL Server databases.  The PDFs are stored in the General table, in the Image column.  That column is of the SQL Server image type, generally used to store binary data (or blobs).

We recently had a user that could not export the PDF files from his SimpleIndex database.    The user was very helpful to send us the original PDF file, and the PDF file content as stored in his database for comparison.  Turns out that SimpleIndex first converts the binary data in your PDF files to a unicode text string, then stores this unicode text.

This is certainly a strange way to go about storing a file in a column that has a data type that’s perfectly suited to storing binary files.  In addition to making it difficult to extract the PDF file, it doubles the storage requirements.  Whatever the reason, SQL Image Viewer could not identify and display the PDF files.

So the first thing we did was to convert the exported ‘mangled’ PDF files back to their original state.  We added this feature to our UTF8Tool application.

Now the user could export the content from his database, then use this tool to convert those files into proper PDF files.

However, the user wanted to be able to query and view the PDF files from within SQL Image Viewer.  We ended up with the easiest option – we created a second column to store the proper PDF content, then create an application to convert the mangled PDF files from the first column and store them in this new column.

It’s a very basic unpolished conversion application, and you can download and use this application freely.

IMPORTANT NOTE:  This application updates a column in your database, so please make a backup of your database first, in case things don’t turn out exactly the way you want it.

Currently, this application supports only SQL Server databases.  If you need a version that supports another database engine, or if you require some modifications, drop us a line at support@yohz.com.

Now that the user could display the PDF directly in SQL Image Viewer, we had another issue.  By default, SQL Image Viewer only displays the first page of each PDF file that it detects e.g.

This made it difficult to review each PDF file in details.  Fortunately, SQL Image Viewer (professional edition) supports custom layouts.  With custom layouts, you can choose to display a specific number of pages from your PDF files, and also the size of the pages e.g.

Now the user could easily preview more of his PDF files prior to exporting them.

In summary:

  • if you need to convert binary data from unicode files to ansi files, give our free UTF8Tool a try
  • if you want to convert a column containing binary data in unicode text format to ansi text format and store the converted data in a separate column, give this free application a try (but please back up your database first)
  • if you want to preview multiple pages of a PDF file, use the custom layouts feature in SQL Image Viewer (professional edition)
  • if you have data that SQL Image Viewer/Access OLE Export/SQL Blob Export does not recognize, send us an email at support@yohz.com

 

New product suggestion: SQL Blob Edit

We recently helped a client convert PDF files stored as a unicode string in a SQL Server binary column to actual PDF files, so that they can view the PDF files from within SQL Image Viewer.

Expanding on this idea, we could develop a product that helps users convert binary data from one form to another e.g.

  • convert images from one format to another
  • process images e.g. resize, add watermark etc
  • compress blob data using zip or lz algorithms to reduce blob size
  • link to external applications to process the blob data (e.g. ghostscript to compress PDF)

We would like to hear your thoughts  to know if such an application would actually be useful to you.  Please leave your comments below, thank you.

Extracting files from a ProgressCRM database

We recently had a user who needed to extract files from a SQL Server database created by ProgressCRM and needed help.  He was helpful enough to send us a couple of the sample files that were not extracted correctly.

It turned out that each file that was being uploaded was stored in an OLE container.  Normally, our products (SQL Image Viewer, Access OLE Export, and SQL Blob Export) can extract the embedded files, but in this case, the entire OLE container itself had a 12-byte prefix, most likely added by ProgressCRM for their internal use.

So the task was to first extract the content without the 12-byte prefix, and then let SQL Blob Export handle the extraction of the files from the OLE container.

Luckily, this was easy enough to do in a SQLServer database.  We could use the SUBSTRING command to retrieve only the content from the 13-th byte onwards e.g.

SELECT SUBSTRING(CAST(<the column containing the binary data> AS VARBINARY(MAX)), 13, DATALENGTH(content))  FROM <the table name>

So if you find that your application is uploading your files with their own prefix data, you can use the SUBSTRING function (in SQL Server) to remove the prefix so that our products can recognize the actual file content and export them for you correctly.

Improved debugging in Access OLE Export / SQL Blob Export

Sometimes it happens that you may have some items that Access OLE Export or SQL Blob Export is unable to export correctly.  This can be due to many reasons e.g.

  • the binary data has been compressed by the uploading application
  • the binary data has been encrypted by the uploading application
  • the uploading application has appended additional header bytes to the data
  • an unsupported OLE server was used to upload the file

To troubleshoot these issues, we would ask the user to send us the raw data exactly as stored in the database.  This usually involves using SQL Image Viewer to perform the extraction.

We’ve now added the option to extract the raw data using Access OLE Export / SQL Blob Export.  You can do this by selecting the debugging – create bin files for analysis item.

Now when you have problems exporting your files, selecting this option will cause a raw copy of the data to be exported together, with the .debug.bin extension.  You can then send us these .debug.bin files for further analysis.

Using UTF8Tool

UTF8Tool is an application to encode files to the UTF-8  character set and vice-versa.  You can download the application here.  This application was originally created for a user who needed to decode/encode files stored in a text column in his database.

To use the application, select the option to either decode or encode the files.  Then enter a search pattern in the Input folder option to locate your files.  If we wanted to process only pdf files, we could enter something like this:

Next, enter the folder to store the processed files in, in the Output folder option e.g.

Once you’ve entered the values, click on the Start button to process your files.  Note that any existing files in the output folder will be overwritten.

Extracting binary data from a nvarchar(max) column!

Recently we had a user that needed to extract PDF files stored in a SQL Server nvarchar(max) column.  Now, you wouldn’t normally be able to store binary files correctly in a text column, but the application they used (Deacom) first encoded the PDF file to UTF-8 text, then stored the UTF-8 text into the text column.

While SQL Image Viewer (and SQL Blob Export/Access OLE Export) could extract the UTF-8 encoded data from the table and store it in a file, the resulting PDF file cannot be opened because the data contained therein is invalid.  So we had to provide a small utility to convert the UTF-8 encoded file back to its original contents.  You can read more about the utility here.

After converting the PDF files, the user then compressed the PDF files and now wanted to upload the PDF files back into the database.  They used SQL File Import to perform the upload, but first the PDF files had be to UTF-8 encoded again, otherwise the application (Deacom) could not open the PDF files.  That can also be one using the above utility.

Finally, we had one last issue where only 8 Kb of the encoded PDF file was being uploaded.  The cause was traced to the use of the MDAC/ODBC driver to connect to SQL Server.

In our database products, when you connect to a SQL Server instance, the default is to let the application select the best available drivers.

By design, our products will use the SQL Server Native Client drivers if available, and only use MDAC/ODBC drivers when the Native Client drivers are not available.  However, MDAC/ODBC drivers limit text fields to only 8000 bytes.

When the user attempted to use the Native Client driver, the connection failed.

Installing and using the Native Client drivers then resolved the connection and upload issue.

Using Access OLE Import

Access OLE Import is a Windows application that uploads files into OLE Object-type fields in your databases.  Most of the time, we are uploading into Access databases, but it can also be  client server databases like SQL Server, PostgreSQL, MariaDB/MySQL because Access is popularly used as a front-end to these databases.

Backup, backup, backup

The first step is the most important – back up your database first.  Access OLE Import makes changes to your tables, and having a backup lets you revert to a working database if anything goes wrong.

Preparing the data file

The next step is to prepare a data file.  This is an Excel worksheet containing the data to upload.  If you only want to insert files into a table, your data file would contain a single column, where the column header defines the field name to upload into and the filecontent identifier so that Access OLE Import will upload the contents of the listed files.  E.g.

Here, we want to upload the contents of the listed files into the document field.

If we want to update existing rows, we will need to provide one or more key values e.g.

Here, we are providing the definition that documentID is an integer-type key value.

Note that you can also use Libre Office or Open Office to create the data file.  You just need to save the spreadsheet in the Excel format (xlsx or xls).

Run the Upload Wizard

Probably the easiest step.  Connect to your database, enter the name of the table you want to upload into, select the data file, upload type, object type, and you’re done.

See also: