. . .

MS SQL Module

Introduction

Being a concrete module of the JDBC meta module, the MS SQL can be used to access relational SQL databases via the middleware.

This documentation will focus on the usage of this specific module. Please refer to the JDBC Module documentation for common matters.

Crawling a Database

In order to crawl a database, we need to add the MS SQL module to our backend. This can be done using the Module Market in the Dashboard and should automatically add the JDBC meta module.

The crawl can be started in the Dashboard or via HTTP request:

  • For crawling via Dashboard, you can hover over the added MS SQL module and click Use. Then fill out the necessary parameters and click the button to start crawling.

  • For crawling via HTTP request, you send a request similar to this and fill out the necessary parameters as query parameters: 

    • curl -X GET "http://[YOURHOST]/yambas/rest/modules/MSSQL/MyTestApp/spec/crawler/?dbHost=mydb.example.com&dbPort=1433&..." -u [CUSTOMEREMAIL]:[CUSTOMERPWD]

The following table lists the parameters that can or need to be set in the Dashboard or as query parameters in the HTTP request.

HTTP request
query parameter

Dashboard field name

Extended description

Example

dbHost

DB IP address or hostname


  • mydb.example.com

  • localhost

dbPort

DB port


  • 1433

dbName

DB name


  • products

schemaName

DB schema name


  • dbo

userName

DB user name


Not the ApiOmat customer or user name, but the database username

  • SA

password

DB user password

Not the ApiOmat customer or user password, but the database user password

  • yourStrong(!)Password

customerName

ApiOmat Customer name

The name that you use to log in to the dashboard

  • john

proposedModuleName

crawled Module name (optional)

You can set the name that should be used for the crawled module.
If you leave it empty, the name will be a combination of "MSSQL" + DB name

  • CrawledProducts

reCrawl

TRUE to overwrite previous crawling


  • true

noPkIdColumns

ID columns of tables without PK (optional)

The PK of a table gets used as ID in ApiOmat classes.
When you use tables without PK, you might need to set the column to be used as ID manually.

  • table1:col1,col2;table2:col1

ignoreMandatoryRef

TRUE ignore errors on mandatory references

Depending on your data model, you might need to set this to true

  • true

appName

App name

The name of your backend where you want to add the crawled module to

  • MyTestApp

This might take up to a few minutes depending on the size of our database. As soon as it is finished, a notification will be displayed. The crawled module should already be added to the backend you set as "appName" parameter. You might need to refresh the Dashboard page or use the "Refresh module" button to see the module in your backend's module list.

From this point onward, the next steps are identical with any other JDBC module. You can continue by checking the crawled module as described in de JDBC meta module documentation.

Limitations

With the help of the crawling query parameter noPkIdColumns it is possible to crawl tables without primary key. You are able to update, read and delete entities of those tables.
Although ApiOmat does not support to create table entities without primary key.