MS SQL client plugin description

Following plugin connects to the Microsoft SQL Server database via connection string, builds the structure of indicated table.  

MS SQL client plugin settings

There are several parameters needed to set up MS SQL Client plugin.

  • SQL Credentials - entry from Password Manager
  • Connection - database connection string
  • Table - database Table name
  • Operation - one of the possible operations

Operations

Let's take a closer look on SQL Plugin operations

Select

Import and Export fields will be the same and both represent SQL Table structure.
Select operation is basic SELECT operator that selects data from the table by mapped fields that are marked as key fields.

E.g. we have such field with Key checkbox picked

Then will have our select query look like this:

SELECT * FROM Table WHERE "id" = "VALUE";

Note

It is also possible to add Wildcard flag to the import field.
In this case "LIKE" operator will be used instead of using the "=" string comparison operator.

For more info see Microsoft SQL wildcard arguments - MS SQL Wildcard characters.

Modify

Modify operation will update records in the table if they exist otherwise create new records.
If the field is not mapped it won't be updated in the table.

Note

WHERE clause is applied to the fields marked with Key flag.
If there are no fields with Key flag then all table will be updated.

Delete

Delete operation will delete existing records from the table.
Condition here is equality with the mapped field marked with Key flag.

Note

WHERE clause is applied to the fields marked with Key flag.
If there are no fields with Key flag then all table will be deleted.

Clear and Insert

This operation will clear all the table and insert new record.

Evaluating the plugin

E.g. I have database table called "Car" with the following structure

Enter your Connection Stringand Table name. Let's stick to Select operation.

The plugin builds the following structure.

Note

Data Types are converted into Int, String and Float.

In this article

Accordion 1

export

tables

name

fields

type

name

key

structures

fields

import

return

{
    'export' 
   
       'tables'          #Python list of table lists
              [{
              'name'      'EXPORT_TABLE_NAME' #Table name

              'fields'    [{
                              'type'   'string'
                                         'int'
                                         'float'

                              'name'     'FIELD_NAME'

                              'key'      True
                                         False
                                         optional
                          }]

              }]

      'structures':     #Python list of structure dictionaries
              [{

            'name'        'EXPORT_STRUCTURE_NAME' #Structure name
             'fields' 

                          [{
                           'type'   'string'
                                      'int'
                                      'float'

                           'name'     'FIELD_NAME'

                           'key'      True
                                      False
                                      optional
                          }]

            }],
      'fields'        #Python list of structure fields

            [{        'type'       'string'
                                      'int'
                                      'float'

                        'name'        'FIELD_NAME'

                        'key'         True
                                      False
                                      optional
            }]
    },
    'import':  

    {
      'tables':         #Python list of table lists
      [{
          'name': 'EXPORT_TABLE_NAME' #Table name
          'fields' :

          [{
            'type'   'string'
                       'int'
                       'float'

            'name'     'FIELD_NAME'

            'dv'     'DEFAULT_VALUE'

            'key'      True
                       False
                       optional
          }]

      }],

      'structures':     #Python list of structure dictionaries
      [{    

          'name': 'EXPORT_STRUCTURE_NAME' #Structure name
          'fields' :

          [{
            'type'   'string'
                       'int'
                       'float'

            'dv'     'DEFAULT_VALUE'

            'name'     'FIELD_NAME'

            'key'      True
                       False
                       optional
          }]

      }],
      'fields':        #Python list of structure fields
      [{
        'type'       'string'
                       'int'
                       'float'

        'name'         'FIELD_NAME'

        'dv'     'DEFAULT_VALUE'

        'key'          True
                       False
                       optional
    }]
    },
    'return': return_structure
}