---json
{
"aliases": [
{ "path": ":howto:sqlserver:connection" }
],
"page_id": "o8c6y12hs57280ieb1sy0"
}
---
====== How to start and use the HowTo SqlServer database in Tabulify ======
===== About =====
This howto shows you how to:
* start an [[:docs:system:sqlserver:sqlserver|sqlserver database]] with the ''sqlserver'' howto [[:docs:service:docker|docker service]]
* and use the ''@sqlserver'' [[docs:connection:howtos|default howto sqlserver connection]] in tabul commands.
===== Steps =====
==== Docker Installation ====
[[:docs:service:howtos|howto docker services]] are an easy way to start locally a database instance.
Because [[:docs:service:howtos|howto services]] are [[:docs:service:docker|docker services]], you should have [[https://docs.docker.com/engine/install/|docker installed]] on your computer.
==== Check the service ====
By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].
You can check if the sqlserver service is present and see its [[:docs:service:service#attributes|attributes]] with:
* the [[:docs:tabul:service:info|tabul service info]] command
* or the [[docs:conf:tabul.yml|configuration vault]]
tabul service info sqlserver
Information about the service (sqlserver)
Attribute Value Description
----------- ----------------------------------------------------- --------------------------
COMMAND The command
ENVIRONMENT {ACCEPT_EULA=Y, MSSQL_SA_PASSWORD=TheSecret1!} The environment attributes
IMAGE mcr.microsoft.com/mssql/server:2022-CU19-ubuntu-22.04 The image
IS_STARTED true The status of the service
NAME sqlserver The name of the service
ORIGIN CONF The origin of the system
PORTS 1433:1433 The ports
TYPE docker The type of the system
VOLUMES The volumes
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.services.sqlserver'
environment:
ACCEPT_EULA: Y
MSSQL_SA_PASSWORD: TheSecret1!
image: mcr.microsoft.com/mssql/server:2022-CU19-ubuntu-22.04
ports:
- 1433:1433
type: docker
If the service is not present move or delete your [[docs:conf:tabul.yml|user configuration vault file]].
==== Note on the password restriction ====
This service is based on the [[https://hub.docker.com/r/microsoft/mssql-server|Official SqlServer]]
and the password should [[https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker|follow the SQL Server default password policy]]
* at least 8 characters long
* contain characters from three of the following four sets:
* Uppercase letters,
* Lowercase letters,
* Base 10 digits,
* and Symbols.
That's why we have chosen `TheSecret1!` and a basic `welcome` password.
==== Start the sqlserver service ====
To start the docker service, you can use
* the [[:docs:tabul:service:start_command|tabul service start]] command
* or the ''docker command''
This service is based on the [[https://hub.docker.com/r/microsoft/mssql-server|Official SqlServer Docker image]].
tabul service start sqlserver
1 service was started
Name Type
--------- ------
sqlserver docker
You can use start the local database with the following [[docs:common:bash|Bash]] command :
docker run \
-e MSSQL_SA_PASSWORD=TheSecret1! \
-e ACCEPT_EULA=Y \
-p 1433:1433 \
-d \
--name sqlserver \
mcr.microsoft.com/mssql/server:2022-CU18-ubuntu-22.04
* [[docs:common:shell|CMD/Powershell]]
docker run ^
-e MSSQL_SA_PASSWORD=TheSecret1! ^
-e ACCEPT_EULA=Y ^
-p 1433:1433 ^
-d ^
--name sqlserver ^
mcr.microsoft.com/mssql/server:2022-CU18-ubuntu-22.04
==== Check that you can ping/connect to the service with the connection ====
tabul connection ping sqlserver
The connection (sqlserver) has been pinged successfully
==== Connection Info ====
By default, the [[docs:conf:tabul.yml|user configuration vault]] is created with the [[:docs:service:howtos|howto docker services]].
You can check the service with the [[:docs:tabul:service:info|tabul service info]] command
tabul connection info sqlserver
Information about the connection (sqlserver)
Attribute Value Description
-------------------------- ------------------------------------------------------------------------ --------------------------------------------------------------------
BOOLEAN_DATA_TYPE Native Boolean data type used to store boolean values
BUILDER_CACHE_ENABLED true Enable or disable the builder cache
CHAR_DEFAULT_PRECISION 1 Default CHAR precision
COMMENT The default sqlserver connection A connection description
DATABASE_MAJOR_VERSION The major version number of the database
DATABASE_MINOR_VERSION The minor version number of the database
DATABASE_PRODUCT_NAME The name of the database
DATABASE_PRODUCT_VERSION The version of the database
DATE_DATA_TYPE NATIVE Date data type used to store date values
DRIVER com.microsoft.sqlserver.jdbc.SQLServerDriver The jdbc driver class
DRIVER_NAME The driver name
DRIVER_VERSION The driver version
HOST Host Server
JDBC_MAJOR_VERSION The major version number of JDBC
JDBC_MINOR_VERSION The minor version number of JDBC
LOGIN_STATEMENTS Statements that runs after a connection has been established
MAX_CONCURRENT_THREAD The maximum number of threads that can be created against the system
MAX_NAME_IN_PATH The maximum number of names in a path
NAME sqlserver The name of the connection
NAME_QUOTING_DISABLED_CASE UPPERCASE The case to apply when quoting is disabled
NAME_QUOTING_ENABLED true Enable quoting of names
NATIVES Native Driver attributes (jdbc properties, ...)
NCHAR_DEFAULT_PRECISION 1 Default NCHAR precision
NVARCHAR_DEFAULT_PRECISION 0 Default NVARCHAR precision
ORIGIN CONF The origin of the connection
PASSWORD TheSecret1! The user password
PORT 1433 Host Port
SUPPORT_BATCH_UPDATES If the system supports batch SQL updates
SUPPORT_NAMED_PARAMETERS If the system supports named parameters in the SQL statement
TIMESTAMP_DATA_TYPE NATIVE Timestamp data type used to store timestamp values
TIME_DATA_TYPE NATIVE Time format data type to store time values
URI jdbc:sqlserver://localhost:1433;encrypt=true;trustServerCertificate=true The uri of the connection
USER sa The user name
VARCHAR_DEFAULT_PRECISION 0 Default VARCHAR precision
WORKING_PATH master.dbo. The working path (Schema for database, directory for file system)
You can also check the connection attributes with the [[docs:conf:tabul.yml|configuration vault]]
tabul data print --type text .tabul/.tabul.yml@home | yq '.spec.connections.sqlserver'
comment: The default sqlserver connection
driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
password: TheSecret1!
uri: jdbc:sqlserver://localhost:1433;encrypt=true;trustServerCertificate=true
user: sa
If the connection is not present, move or delete your [[docs:conf:tabul.yml|user configuration vault file]].
==== Use the sqlserver connection ====
You then can use the ''@sqlserver'' connection in place of any other connection
Example:
* replacing the ''@sqlite'' connection by the ''@sqlserver''
Drop all
tabul data drop --no-strict-selection --force *@sqlserver
# with sqlite
# tabul data upsert characters.csv@howto @sqlite
# with sqlserver
tabul data upsert characters.csv@howto @sqlserver
# with sqlite
# tabul data list *@sqlite
# with sqlserver
tabul data list *@sqlserver
path media_type
---------- ----------
characters table
In particularly, you can follow:
* the [[:howto:getting_started:getting_started|getting started guide]]
* and all SQL howto ([[:howto:sql_table:sql_table|Sql Table]], Sql View, ...)
select from pages where path like ':howto:sql_table%' or path like ':howto:sql_view:%' order by title asc limit 10
$title
${description | head(200, "...") } [[$path| ]]
\\
==== Stop the sqlserver service ====
Once, you don't need the sqlserver service, you can stop it:
* with the [[:docs:tabul:service:stop|tabul service stop]] command
* or with Docker
tabul service stop sqlserver
1 service was stopped
Name Type
--------- ------
sqlserver docker
You can also stop it with Docker
docker stop sqlserver