Repository files navigation

Small example console source codes how to connect to the Microsoft SQL Server, how to update rows and how to read the table.

1. clone this repository to your computer
2. install theMicrosoft SQL Server (as aDocker container)
3. prepare the user, the table and rows in the database
4. build and run the exampleJava code
5. compile and run the example.NET C# code
6. run the examplePHP code

• installGIT on your computer
• clone this repository to your computer by the GIT commandgit clone https://github.com/petrfaltus/ms-sql-server-connection-source-codes.git

• installdocker desktop on your computer
• refer theMicrosoft SQL Server image

The subdirectorydocker-database contains prepared Windows batches:

• 01-run-database.cmd - pulls the image and runs the containerat the first time
• 02-switch-database-OFF.cmd - stops the already existing container
• 02-switch-database-ON.cmd - starts the already existing container
• 03-inspect-database.cmd - shows details for already existing container
• 04-exec-connection-to-database-sa.cmd - executes thesqlcmd tool terminal into running database container (as the usersa)
• 04-exec-connection-to-database-testuser.cmd - executes thesqlcmd tool terminal into running database container (as the usertestuser)
• containers.cmd - lists currently running containers and list of all existing containers

For the connection to the database use either thesqlcmd tool terminal or theMicrosoft SQL Server Management Studio

Use prepared Windows batches, every SQL command terminate by the keywordGO

SELECT @@version;GO

Usersa (default passwordSyst3mAdm1n!)

Usertestuser (default passwordT3stUs3r!)

CREATEDATABASEtestdb;USE testdb;CREATE LOGIN testuser WITH PASSWORD='T3stUs3r!';CREATEUSERtestuser FOR LOGIN testuser;GRANTSELECT TO testuser;GRANT CREATE TABLE TO testuser;GRANT INSERT TO testuser;GRANTUPDATE TO testuser;GRANT ALTER TO testuser;GRANT EXECUTE TO testuser;ALTER LOGIN testuser WITH DEFAULT_DATABASE=testdb;

USE testdb;CREATETABLEanimals (  nameVARCHAR(40)NOT NULL,  legs TINYINTNOT NULL,  created DATETIME DEFAULT GETDATE(),  updated DATETIME,  remarkVARCHAR(80),  idINT IDENTITY(1,1)PRIMARY KEYNOT NULL);CREATETRIGGERanimals_updateON animals  AFTERUPDATEASBEGINSET NOCOUNTON;UPDATE animalsSET updated= GETDATE()WHERE idIN (SELECT idFROM Inserted)END;INSERT INTO animals (name, legs)VALUES ('chicken',2);INSERT INTO animals (name, legs)VALUES ('fox',4);INSERT INTO animals (name, legs)VALUES ('eagle',2);INSERT INTO animals (name, legs)VALUES ('ant',6);INSERT INTO animals (name, legs)VALUES ('horse',4);CREATEOR ALTER FUNCTION factorial(@nINT) RETURNSINTASBEGIN  IF (@n<0)    RETURN-1;  DECLARE @resultINT=1;  IF (@n<2)    RETURN @result;  DECLARE @ijkINT=2;  WHILE @ijk<= @nBEGINSET @result= @result* @ijk;SET @ijk= @ijk+1;    END;  RETURN @result;END;CREATEOR ALTER PROCEDURE add_and_subtract(@aINT, @bINT, @xINT OUT, @yINT OUT)ASBEGINSET @x= @a+ @b;SET @y= @a- @b;END;

USE testdb;SELECT*FROM animals;SELECTcount(*)FROM animals;SELECTcount(*)FROM animalsWHERE id!=1;SELECTdbo.factorial(2);SELECTdbo.factorial(3);SELECTdbo.factorial(4);DECLARE @aINT=12;DECLARE @bINT=5;DECLARE @xINT;DECLARE @yINT;PRINT'a ='+CONVERT(VARCHAR(6), @a);PRINT'b ='+CONVERT(VARCHAR(6), @b);EXECUTEdbo.add_and_subtract @a, @b, @x OUT, @y OUT;PRINT'x ='+CONVERT(VARCHAR(6), @x);PRINT'y ='+CONVERT(VARCHAR(6), @y);

• installJava JDK on your computer
• set the OS environment%JAVA_HOME% variable (must exist"%JAVA_HOME%\bin\java.exe")

• installApache Maven on your computer
• add the Maven directory (where the batchmvn.cmd locates) to the OS environment%PATH% variable

The subdirectoryjava-maven contains prepared Windows batches:

• 01-build.cmd - cleans, compiles and builds the Maven project
• 02-run.cmd - runs the built Java archive (JAR)
• 03-clean.cmd - cleans the Maven project

• installGradle Build Tool on your computer
• add the Gradle directory (where the batchgradle.bat locates) to the OS environment%PATH% variable

The subdirectoryjava-gradle contains prepared Windows batches:

• 01-build.cmd - cleans, compiles and builds the Gradle project
• 02-run.cmd - runs the built Java archive (JAR)
• 03-clean.cmd - cleans the Gradle project

• use thecsc.exe .NET C# compiler that is the part of Microsoft .NET Framework (part of OS)

The subdirectorycsharp contains prepared Windows batches:

• 01-compile.cmd - compiles the source code (contains the path definition to thecsc.exe compiler)
• 02-run.cmd - runs the Windows executable
• 03-clean.cmd - deletes the Windows executable

• installPHP on your computer
• set the OS environment%PHP_HOME% variable (must exist"%PHP_HOME%\php.exe")
• installMicrosoft ODBC Driver (or already installed as a part ofMicrosoft SQL Server Management Studio)
• installMicrosoft Drivers for PHP for SQL Server
• copyphp_pdo_sqlsrv_74_nts_x64.dll fromMicrosoft Drivers for PHP for SQL Server to the PHP directory%PHP_HOME%

To thephp.ini in the PHP directory%PHP_HOME% add lines

[PHP]extension_dir = "ext"extension=php_pdo_sqlsrv_74_nts_x64.dll[Date]date.timezone = Europe/Prague

The subdirectoryphp contains prepared Windows batch:

• 01-run.cmd - runs the code through the PHP interpreter

Now in August 2020 I have the computer withWindows 10 Pro 64bit,12GB RAM and available50GB free HDD space