mercoledì 7 settembre 2016

How to create a subfolder in a SQL Server FileTable


SQL Server 2012

The scope of this post is that to explain how to create a subfolder in a SQL Server FileTable (Documents). I suppose that the FileTable already exists.

I want to create a subfolder like this:

{root}\{folder1}\{folder2}\{filename}

and to get the new hierarchyID for the path, I use this formula:

parentpath.ToString()

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  1, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  7, 6))) + '.'

 + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),

                                                  13, 4))) + '/'

This is the T-SQL code:

CREATE PROCEDURE [dbo].[SetDocumentContent]
                @IdFileData bigint,
                @file_stream image
AS
BEGIN
                DECLARE @FileName varchar(255)
                DECLARE @DocumentClassification varchar(50)
                DECLARE @ETSIdImpianto int

                DECLARE @DocumentClassification_path_locator hierarchyid
                DECLARE @ETSIdImpianto_path_locator hierarchyid
                DECLARE @FileName_path_locator hierarchyid

                -- Metadata to identify the correct document. The FileData is my internal table.            
SELECT @FileName = Name, @DocumentClassification = DCT.Description, @ETSIdImpianto=ETSIdImpianto
                               FROM FileData FD INNER JOIN DocumentClassificationType DCT
                               ON FD.IdDocumentClassificationType = DCT.Id  WHERE FD.Id = @IdFileData

                -- 1. If folder1 exists      
                SELECT @DocumentClassification_path_locator = path_locator FROM Documents
                WHERE name = @DocumentClassification AND is_directory = 1

                IF (@DocumentClassification_path_locator IS NULL)
                BEGIN
                               INSERT INTO Documents
        ( name
        , is_directory)
    VALUES
        ( @DocumentClassification
        , 1)

                               -- Get the folder1
                               SELECT @DocumentClassification_path_locator = path_locator FROM Documents
                               WHERE name = @DocumentClassification AND is_directory = 1
                END

                -- 2. If folder2 exists      
                SELECT @ETSIdImpianto_path_locator = path_locator FROM Documents
                WHERE name = CAST(@ETSIdImpianto as varchar) AND is_directory = 1

                IF (@ETSIdImpianto_path_locator IS NULL)
                BEGIN
                               INSERT INTO Documents
        ( name                                                       
        , is_directory
                                                               , path_locator)
    VALUES
        ( CAST(@ETSIdImpianto as varchar)
        , 1
                                                               , @DocumentClassification_path_locator.ToString()
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         1, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         7, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         13, 4))) + '/')

                               -- Get the folder2
                               SELECT @ETSIdImpianto_path_locator = path_locator FROM Documents
                               WHERE name = CAST(@ETSIdImpianto as varchar) AND is_directory = 1
                END

                -- 3. The FileName
                SELECT @FileName_path_locator = path_locator FROM Documents
                WHERE name = @FileName AND is_directory = 0 AND parent_path_locator = @ETSIdImpianto_path_locator

                IF (@FileName_path_locator IS NULL)
                BEGIN
                               INSERT INTO Documents
        ( name                                                       
                                                               , file_stream       
                                                               , path_locator)
    VALUES
        ( @FileName
                                                               , @file_stream
                                                               , @ETSIdImpianto_path_locator.ToString()
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         1, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         7, 6))) + '.'
        + CONVERT(VARCHAR(20), CONVERT(BIGINT, SUBSTRING(CONVERT(BINARY(16), NEWID()),
                                                         13, 4))) + '/')                           

                               SELECT @FileName_path_locator = path_locator FROM Documents
                               WHERE name = @FileName AND is_directory = 0 AND parent_path_locator = @ETSIdImpianto_path_locator
                END

Bye!

martedì 31 maggio 2016

Angular Connect 2016

Europe’s Largest Angular Conference

AngularConnect is a 2-day, multi-track conference featuring the world’s leading Angular experts, including the core Angular team at Google.

lunedì 9 maggio 2016

jsDay 2016


THE MOST USED LANGUAGE
THE MOST NEEDED CONFERENCE

May 11th-12th 2016 — Verona, Italy

From the organizers of the phpDay, the first international conference on JavaScript in Italy.

http://2016.jsday.it/  #jsDay

mercoledì 27 gennaio 2016

Using HTTP POST method with Postman

I tried the POSTMAN (https://www.getpostman.com/) for testing my REST web services. It is a good tool and when I used it with the GET method all was ok but with the POST method (I had to upload a photo), some problems occurred because I did not understand how to use the Content-Type parameter. I tried to insert it in the “Headers” tab with other parameters but it did not work.

The solution is to select the form-data option in the “Body” tab, enter a type “File” parameter and then you have to select the file for uploading.



Then if you select the “Generate code snippet” you will see the Content-Type parameter like this:

Content-Type: multipart/form-data; boundary=----WebKitFormBoundary7MA4YWxkTrZu0gW


Regards!