Month: February 2017

deploy node web application to IIS

Giridhar PrakashLeave a comment

Requirement: Need to deploy node web application to on premise IIS 8.5.

Steps:
1. Create a App Pool user and assign the user to IIS_IUSRS group. It’s easy to maintain permissions when we work with windows groups. This is a special group created for IIS and it makes life easy when we move applications to different windows servers.
2. Create Virtual directory for your application and give full permissions to IIS_IUSRS on this directory.
3. Now create a website or application for the virtual directory and set the app pool identity created in step 1.
4. Now download IISNode msi here. please pay attention on documentation relation to url rewrite.
5. After you install msi, run the batch file with admin privileges “C:\Program Files\iisnode\setupsamples.bat”.
6. Open IIS manager and expand default website, you should see samples are deployed under node application. This gave me pretty good understanding for me.
7. I have two different node web applications
Node application is serving both static and Api.
Node application is serving api only and static files are served by IIS.

Node Serving both static file and APi

Create web.config file in the virtual directory and add these settings.
rewrite: Any request comes to web site will be served by app.js
handlers: iisnode is a http handler which will execute app.js using node.

Note: what if don’t have app.js in the root directory? please see next section.
Please remove any hardcode port number from the index.js. port should passed from process “server.listen(process.env.PORT);”

<configuration>
<system.webServer>
<handlers>
<add name="iisnode" path="app.js" verb="*" modules="iisnode" />
</handlers>
<rewrite>
<rules>
<rule name="sample">
<match url="/*" />
<action type="Rewrite" url="app.js" />
</rule>
</rules>
</rewrite>
</system.webServer>
</configuration>
view raw web.config hosted with ❤ by GitHub

Node Serving APi and IIS serving static content

Create web.config file in the virtual directory and add these settings.
default document: IIS serves default document from subfolder (new). index.html will load all css and other files.
rewrite: url that ends with api will be served by index.js which is under subfolder (server).

Note: Please remove any hardcode port number from the index.js. port should passed from process “server.listen(process.env.PORT);”

<configuration>
<system.webServer>
<handlers>
<add name="iisnode" path="server/index.js" verb="*" modules="iisnode" />
</handlers>
<rewrite>
<rules>
<rule name="sample">
<match url="api/*" />
<action type="Rewrite" url="server/index.js" />
</rule>
</rules>
</rewrite>
<defaultDocument enabled="true">
<files>
<add value="new/index.html" />
</files>
</defaultDocument>
</system.webServer>
</configuration>
view raw web.config hosted with ❤ by GitHub

Hope this helps.

Using SQL Variables inside query in clause

Giridhar PrakashLeave a comment

Problem: I need to use a variable which should be used inside the in clause.


Case 1:
DECLARE @customerIds VARCHAR(200) = '123,456,789'
SELECT * From Customers Where Id in (????)
Case 2:
DECLARE @customerNames VARCHAR(200) = 'abc, def, ghi'
SELECT * From Customers Where FirstName in (????)

Solution Case 1:
Since already have comma separated integers, we should parse the variable as integer table.


CREATE FUNCTION [dbo].[StringSplitInt]
(
	@Idlist varchar(800),
	@delim char(1)
)
RETURNS @returntable TABLE
(
	Id int not null
)
AS
BEGIN
	DECLARE @list varchar(800) = RTRIM(LTRIM(@Idlist)) + @delim;
	DECLARE @idString varchar(10), @id INT;
	WHILE (LEN(@list) > 0)
	BEGIN
		SET @idString = SUBSTRING(@list, 1, CHARINDEX(@delim, @list,1)-1);
		SET @list = SUBSTRING(@list, LEN(@idString)+2, LEN(@list) - LEN(@idString)-1)
		INSERT @returntable(Id)
		SELECT CAST(@idString AS INT)
	END
	RETURN
END

Go

DECLARE @customerIds VARCHAR(200) = '123,456,789'
SELECT * From Customers Where Id in (SELECT Id from StringSplitInt(@customerIds))

Solution for Case 2:
Now we parse the string variable to table


CREATE FUNCTION [dbo].[StringSplitString]
(
	@Idlist varchar(800),
	@delim char(1)
)
RETURNS @returntable TABLE
(
	Id VARCHAR(MAX) COLLATE Latin1_General_CI_AI
)
AS
BEGIN
	DECLARE @list varchar(800) = RTRIM(LTRIM(@Idlist)) + @delim;
	DECLARE @idString varchar(100);
	WHILE (LEN(@list) > 0)
	BEGIN
		SET @idString = SUBSTRING(@list, 1, CHARINDEX(@delim, @list,1)-1);
		SET @list = SUBSTRING(@list, LEN(@idString)+2, LEN(@list) - LEN(@idString)-1)
		INSERT @returntable(Id)
		SELECT @idString
	END
	RETURN
END

Go

DECLARE @customerNames VARCHAR(200) = 'abc, def, ghi'
SELECT * From Customers Where FirstName in (Select Id from StringSplitString(@customerNames))

Note: You might get Collation mismatch error between the FirstName and Id Column returned by function. (I need to read a little more about this). I solved this problem by defining function column with our SQL Server collation setting.