SQL Server – Read all files in directory and store in Table

Recently I was in need to analyze Salesforce debug log for one stubborn issue which was very hard to reproduce. Was able to download 1500+ debug logs on my system, however to analyze it, I decided to take help of SQL Server.

Very soon I came into challenge to read all files in a directory and store it back in SQL Server table.

Found very good article here which helped me to come up with below queries. If you need to read more in detail about below T-SQL and stored procedures please navigate to original article.

Export Documents saved as Blob / Binary from SQL Server

I tried many ways to export files or documents saved as binary (Blob) datatypes in SQL Server. However, finally came up with below solution which worked very well. Below script was used to export around 25GB of files stored in SQL Server.

To understand this, lets create a table in Database which will store files from local system into SQL Server as binary / Blob .

CREATE TABLE [dbo].[Document](
	[Doc_Num] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[Extension] [varchar](50) NULL,
	[FileName] [varchar](200) NULL,
	[Doc_Content] [varbinary](max) NULL

How to Insert Blob into Database

For demo purpose, we will save two files in our table using below queries

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'pdf', 'Salesforce Lightning.pdf',[Doc_Data].*
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\lightning.pdf', SINGLE_BLOB)  [Doc_Data]

INSERT [dbo].[Document] ([Extension] ,[FileName] , [Doc_Content] )
SELECT 'html', 'Progress.html',[Doc_Data].*
    (BULK 'C:\G2\My POC\Blog\SQL Server\Source\Progress.html', SINGLE_BLOB)  [Doc_Data]

If we try to see content in actual table, it will look like

Insert Blob into Database
Insert Blob into Database

Export Blob From SQL Server and save it as a file

For demo purpose, we want to save documents on local disc. We will use Doc_Num to be created as folder and document will be saved in that folder. To create folders using SQL Server, we will use stored procedure CreateFolder created in this post.
Note : We will create only one folder per document. If multiple nested folder needs to be created then we need to iterate through each folder and call CreateFolder stored procedure for each folder. Its explained in this blog post.

Now time to see actual T-SQL which will iterate through all documents, create folder and save Blob as a file on local disc.

DECLARE @outPutPath varchar(50) = 'C:\G2\My POC\Blog\SQL Server\Extract Blob'
, @i bigint
, @init int
, @data varbinary(max) 
, @fPath varchar(max)  
, @folderPath  varchar(max) 
--Get Data into temp Table variable so that we can iterate over it 
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )
INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [Doc_Num] , [FileName],[Doc_Content] FROM  [dbo].[Document]
--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable
WHILE @i >= 1

	 @data = [Doc_Content],
	 @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
	 @folderPath = @outPutPath + '\'+ [Doc_Num]
	FROM @Doctable WHERE id = @i
  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath
  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;  
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources
  print 'Document Generated at - '+  @fPath   

--Reset the variables for next use
SELECT @data = NULL  
, @init = NULL
, @fPath = NULL  
, @folderPath = NULL
SET @i -= 1

Variable @outPutPath stores root folder path, where folders will be created and Blob content would be exported into it as a file.

Below image shows output in action :

SQL Server Export Blob as File
SQL Server Export Blob as File

SQL Server – Search complete database for value

SQL Server search database for value

Recently, I was in need to search complete Database for some value and found below very useful script (T-SQL)

DECLARE @SearchStr nvarchar(100) = 'SEARCHSTRING'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))


DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')


    SET @ColumnName = ''
    SET @TableName = 
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    OBJECTPROPERTY(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

        SET @ColumnName =
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1) 
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName 

        IF @ColumnName IS NOT NULL

            INSERT INTO @Results
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 4000) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2

SELECT ColumnName, ColumnValue FROM @Results

If we run above script for word ‘king’ then output in below format will be displayed

SQL Server – Create Folders using T-SQL & OLE automation

SQL Server – Create Folders using T-SQL & OLE automation

SQL Server has some standard stored procedures that allow OLE automation.

First step, is to check whether Ole Automation Procedures are enabled in SQL Server or not ? It can be enabled by simply executing below T-SQL commands.

T-SQL to enable Ole Automation Procedures in SQL Server

        sp_configure 'show advanced options', 1;
	sp_configure 'Ole Automation Procedures', 1;

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive

Pagination and Switch Case in Where clause – SQL Server

After a long time i am going to write any article on SQL Server. That last article i think was before 18 months. As currently i am exploring the Salesforce and its API’s, i caught in situation to create a stored procedure and provide the offset feature, that means i needed the pagination support in my stored procedure. There is no direct keyword available in SQL Server something like LIMIT in mysql. And therefore i thought to share my solution with community. So, here we go…

I am considering that there will be table named “Employee” in which i want to search by employees firstName with pagesize and offset attribute.

Step By Step Hibernate Tutorial Using eclipse WTP

Hibernate is the ORM tool widely used in java community to persist the java object using Object Relational Mapping (ORM) concept. ORM reduces number of lines to interact with database with optimized query language which is Hibernate Query language (HQL).

In this example, we will create a simple login application using hibernate tool of eclipse. We will use eclipse WTP (Web Tools Platform), to install “Hibernate Tools”. Follow below steps :

In Eclipse IDE, menu bar, select “Help” >> “Install New Software …” put the Eclipse update site URL “http://download.jboss.org/jbosstools/updates/stable/helios”

Eclipse Install New Software - Hibernate
Eclipse Install New Software - Hibernate

Create BPA Script in Oracle Utilities to invoke UI Map

In this post, i will discuss on creating the BPA Script to invoke the UI Map.

Navigate to “Admin Menu | S | Script +” and the details as per below image:


Creating BPA Script in Oracle Utilities / ORMB / CC&B
Creating BPA Script in Oracle Utilities

Creating UI Map in Oracle Utilities

This is the third article in series to create the UI Map in Oracle Utilities. In Previous two article we have seen that how to create the Business Service, Data Area and Service Program.

Navigate to “Admin Menu | U | UI Map +“. Give the UI Map Name as “CM_PERINPUT” and select “UI Map Type” = Complete HTML Document.

Creating UI Map in Oracle Utilities / CC&B / ORMB
Creating UI Map in Oracle Utilities

Creating Business Service and Service Program in ORMB / CC&B / Oracle Utilities

In Previous article, we have seen that how to create the Data Area and benefits of using Data Area. This article will focus on creating the Business Service and Service Program in CC&B.

First we have to create the Business Service in application then we have to code it in JAVA and deploy the updated “cm.jar“ file on server.

Go to “Admin | S | Service Program +“ and enter the Service name, Description and select the “Java Based Service” as Service Type.  In this example my service name is “PERSER“.

Now go to “Admin | B | Business Service +“ and enter the Business Service name and select the previously create service “PERSER“ for Service Name using look up. In this case the Business Service Name is “CM_PERSER“.
Now go to the “Schema” and add following code and click on Save.

Step by Step tutorial for creation of Business Service, Data Area, UI Map and BPA Script in ORMB / CC&B / Oracle Utilities for beginners

Before starting this demo application, I am assuming that you already have done CC&B set up on your local and Eclipse is configured.
What I am going to do so that I can use Business Script, Data Area, UI Map and BPA Script?
In this tutorial, I will create one UI Map which will take the person name as input and display list of all the person names as per input.
Final output screen would look like:

UI Map in ORMB/CC&B/Oracle Utility with BPA Script
UI Map in ORMB/CC&B/Oracle Utility with BPA Script