SQL Server – Add Table If Not Exist

Some approach is possible (like do a query to retrive a row from the table that wanted to be create), but the best is to check to INFORMATION_SCHEMA.TABLES as it is the authority for database structure.

Example :

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = ‘dbo’ AND TABLE_NAME = ‘tblMyTable’))
BEGIN
CREATE TABLE [dbo].[tblMyTable]
(
[MYTABLE_ID] uniqueidentifier NOT NULL PRIMARY KEY,
[MYTABLE_ID_FIELDNAME] NVARCHAR(32) NOT NULL,
[__CREATE_TS] DATETIME NOT NULL DEFAULT (getutcdate()),
[__LAST_UPDATE_TS] DATETIME NULL
)
END

Advertisements

Fix Unable to Install Java SDK 10.0.2

Description

Need to run automation testing using Sikuli and Selenium with Intelij Community Edition 2018.
The framework require Java SE 10.0.2 JDK, while my PC running Java SE 8xx JDK.

When install the new JDK (jdk-10.0.2_windows-x64_bin.exe), windows 10 shows the confirmation screen, but then nothing happen.
I have delete every Java Installation but still the install result nothing.. even error message…

Solution

Simply extract the exe file (jdk-10.0.2_windows-x64_bin.exe).
It will extract to a folder which contain a zip file (tools.zip).
Extract again that “tools.zip” file.

Then copy it to any location.
Then point the JDK setting on your app (mine is Intelij Community Edition 2018).

All done..

SQL Server : Sample Of Upsert Query

Description

Upsert means insert or if failed insert.
Upsert operation is very common approach.

Usually insert operation will go first then update, because the server cost is lighter than update.

Here is the sample


Note : Below is simple and easy to understand. But can be tuned by passing insert statement first then check if success then if failed update. 

DECLARE @Uri varchar(30) = ‘Test-remove-AJA’;
DECLARE @TEMPLATE_ID varchar(100) = ‘3439C180-421A-4433-9BAE-3A9913E62B1B’;
DECLARE @Account varchar(100) = ‘BlaBla-3’;

IF EXISTS (SELECT * FROM [dbo].[Table1] WHERE [Uri] = @Uri AND [Account] = @Account)
UPDATE [dbo].[Table1]
SET [TEMPLATE_ID] = @TEMPLATE_ID
WHERE [Uri] = @Uri AND [Account] = @Account
ELSE
INSERT INTO [dbo].[Table1]
(
[Uri]
,[TEMPLATE_ID]
,[Account]
)
VALUES
(
@Uri
,@TEMPLATE_ID
,@Account
)

Fix SQL Server error – The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

Problem Source
This problem is happen on sql server query when a datetime field is filled with null or value lower then 1970-01-01 00:00:00.

This error will also happen when try to input zero date (0000-00-00).

Solution 
For empty date, just to omit the field (if nullable) or use “1970-01-01 00:00:00”

Using JWT (Json Web Token) and bearer token

Desc
JWT (json web token) is an authentication strategy usually implemented in API server.
Using JWT we can encode an array to be a string (token) salted with a key that should be kept in the server.
API server then can decode the token using the same key.

Source : https://coderwall.com/p/8wrxfw/goodbye-php-sessions-hello-json-web-tokens

C# : DB Timeout, Prevent Threading and Closing App

I. DB timeout

Description

DB timeout is a common problem when our database is large.
The best strategy is to tune the query.
Other possible option is to apply

Solution
1. Do query tuning.. It is the mandatory approach.
a. Avoid to use limit (eg “top” )
b. Avoid to use subquery (use left join instead)
c. Always use with(nolock)

2. Increase DB timeout
Better if to apply it only on sqlcommand that take long time to execute.

Example :

SqlCommand cmd2 = new SqlCommand(cmdQuery, conn2);
cmd2.CommandTimeout = 300; //==> in seconds

II. Prevent Threading

Sample :

 class Program

{

private static Mutex mutex = null;

 

        static void Main(string[] args)

{

const string appName = “MyAppName”;

bool createdNew;

 

mutex = new Mutex(true, appName, out createdNew);

 

if (!createdNew)

{

writeLog(appName + ” is already running! Exiting the application.”);

Environment.Exit(0);

return;

} else{

//do the normal process

}

 

 

}

}

 

III. Closing Application

Just use this code :

Environment.Exit(0);