Table of contents

MSSQL docker

Installing the MSSQL docker image:

sudo docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=yourStrong(!)Password' -p 1433:1433 -d microsoft/mssql-server-linux

Run the following command to verify that the image is running:

sudo docker ps

getting the container id

sudo docker ps | grep mssql | awk '{ print $1 }'

run sql in docker image from command line

sudo docker exec -it [container] /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P [password] -Q "[sql];"

one liner for creating a database in the container

sudo docker exec -it $(sudo docker ps | grep mssql | awk '{ print $1 }') /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P 'yourStrong(!)Password' -Q "create database yourdb;"

Running queries against MSSQL

Install dbeaver

http://dbeaver.jkiss.org/download/

Simply run the installer, next start dbeaver from the menu or command line.

Define a new MSSQL server database with:

host: localhost
port: 1433
schema: master
username: sa
password: yourStrong(!)Password

Creating a new database:

CREATE DATABASE bignibou;

Executing a simple test to see if everything is operational:

create table erik(id integer);
insert into erik(id) values(1), (2);
select * from erik;

Connecting to MSSQL from java

SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName("localhost");
ds.setPortNumber(1433);
ds.setDatabaseName("bignibou");
ds.setUser("sa");
ds.setPassword("yourStrong(!)Password");

QueryRunner runner = new QueryRunner(ds);
runner.query("select * from erik;", (rs) -> {
	while (rs.next()) {
		System.out.println(rs.getInt(1));
	}
	return null;
});

Requires the following dependencies:

https://mvnrepository.com/artifact/com.microsoft.sqlserver/mssql-jdbc
https://mvnrepository.com/artifact/commons-dbutils/commons-dbutils

Connecting using a jdbc url from java

String driver = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
Class.forName(driver);
String url = "jdbc:microsoft:sqlserver://localhost:1433;databaseName=bignibou;user=sa;password=yourStrong(!)Password";
Connection con = DriverManager.getConnection(url, "username", "password");

MSSQL queries

full text search in binary string (data type "image")

SELECT * FROM [table] where CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX),[column])) LIKE '[query]'

substring

select substring([column], 0, 11) from [table];