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

Author posted by Jitendra on Posted on under category Categories SQL Server and tagged as Tags with Leave a comment on SQL Server – Read all files in directory and store in Table

T-SQL Script to read all files in directory and store back in table – SQL Server

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. Continue reading “SQL Server – Read all files in directory and store in Table”

Export Documents saved as Blob / Binary from SQL Server

T-SQL Scripts to Export Blob or Binary data stored in 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
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

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].*
FROM OPENROWSET 
    (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].*
FROM OPENROWSET 
    (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.

USE [POC]
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
BEGIN 

	SELECT 
	 @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
END

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

Circular Progress Bar with Conditional Theme – Salesforce Lightning Component

Salesforce Lightning Component – Circular Progress Bar with Conditional Theme. Ready to be used by Developers and Admins on App builder for any object. No External Javascript Library, Lightweight CSS based.

Just after two days of writing Circular Progress Bar , came up with few more requirements. So, In this post, will share updated code of Circular Progress Bar. This component can be easily used from Lightning App Builder, check this video to get an idea on how it can be used and configured.

In additional to all previous capabilities, below features are added:

  1. Conditional Theme – Let’s say before 50% progress bar should be displayed as red and after 50% green.
  2. Threshold – On basis of this value, theme will change
  3. Added one more theme – red
  4. We can show value inside component in three format now – percentage, Actual value or Mix
  5. Legend font size changes according to size of component

Continue reading “Circular Progress Bar with Conditional Theme – Salesforce Lightning Component”

How to Document Lightning Component

Demo and Example, showing process of documenting Lightning Component

Its extremely important to keep and maintain the documentation of your newly built (and all existing) Lightning component. So that it can be easy to understand and easy to reuse. It will not take much time for your Salesforce Org to flood with Lightning components.

We will take example of previous post, where I shared source code of Circular Progress Bar Lightning Component.

First, we will create a new Lightning Component which will have possible examples of using Lightning Component. Continue reading “How to Document Lightning Component”

Circular Progress Bar – Salesforce Lightning Component

Demo and Complete Source code of Circular Progress Bar, Salesforce Lightning Component

In this blog post we will create reusable Lightning Component to show progress of record using Circular Progress Bar. This component is mostly build using CSS. Javascript is used only for Lightning component support and calling Apex Class. Check video demo here on how to configure and use this component.

Note – There is updated version of this component here.

Circular Progress Bar LEX Component Capabilities

  1. Size – small, medium , large
  2. Theme – blue, orange , green
  3. Legend – Legend to display
  4. Total – Either Number Or API Name of field. Used to derive percentage of Progress Bar
  5. Actual – Either Number Or API Name of field. Used to derive percentage of Progress Bar. If Object contains percentage type of field, then Total can be blank and this field can only contain API name of field of type percentage

Continue reading “Circular Progress Bar – Salesforce Lightning Component”

SQL Server – Search complete database for value

Search complete Database in SQL Server for some 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))

SET NOCOUNT ON

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


WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
	BEGIN TRY
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )
END TRY
BEGIN CATCH
END CATCH
    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1) 
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName 
        )
         

        IF @ColumnName IS NOT NULL

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

SELECT ColumnName, ColumnValue FROM @Results

If we run above script for word ‘king’ then output in below format will be displayed Continue reading “SQL Server – Search complete database for value”

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

Tutorial to create folders in SQL Server using Transact-SQL and OLE Automation Stored Procedures

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;
	GO
	RECONFIGURE;
	GO
	sp_configure 'Ole Automation Procedures', 1;
	GO
	RECONFIGURE;
	GO

Next step is to create a stored procedure, which will use an OLE Automation procedures and create a folder on system drive Continue reading “SQL Server – Create Folders using T-SQL & OLE automation”

Calling Apex method at regular interval from Lightning Component

Importance of $A.getCallback and Modifying Components Outside the Framework Lifecycle

Recently, I was in need of Lightning Component, which can poll Apex class at regular interval and display status of some process running in background. Initially, I thought its very easy and straight forward. Just like we do in Visualforce, use window.setInterval method of Javascript and I came up with below code. Continue reading “Calling Apex method at regular interval from Lightning Component”

Automated Code review for Apex in Salesforce – Static code analysis – Video

Using open source PMD tool to generate code quality report for Apex classes

PMD is very well known source code analyzer for Java, android and many more languages. Good news for us (Salesforce developers) is , that it supports now Apex. You might be thinking how can we make PMD as part of our daily life ?

There are multiple ways

  1. We can run static code analysis standalone
  2. It can be part of ANT build to generate error reports
  3. Jenkins can use it to generate nice report around code quality
  4. Eclipse can use it as a plugin to generate report

In this blog post, we will discuss option 1, that is running it as a standalone application to generate code quality report.

First Step is to download jar file of latest PMD distribution from here. Continue reading “Automated Code review for Apex in Salesforce – Static code analysis – Video”

Get Selected HTML or Lightning component in Aura Iterator

How to detect event from Lightning Component and HTML5 components in Aura Iterator

Coming from Visualforce background, most of us are well aware about repeater component. Same way, Lightning also offers iterator component.

Detecting Selected Lightning Component in Iterator

Usage is very simple, It is used to iterate over collection and render some HTML / Lightning component dynamically as shown below

<aura:component>
  <aura:iteration items="1,2,3,4,5" var="item">
    <meter value="{!item}"/>
  </aura:iteration>
</aura:component>

In above example, each element in collection can be referred using variable “item”. Most of time we find ourselves in situation where we need to get value or component selected in iterator. As we don’t know upfront, how many items would be there, so identifying selected item at run time seems little bit tricky but believe me it easy. Continue reading “Get Selected HTML or Lightning component in Aura Iterator”