Python command: SQL

Use the SQL command in Python to access a variety of SQL functions and GainSeeker tables.

Note: It is generally not recommended that you use these commands to run SQL queries. See the SQL Query action for another alternate simpler method.

See Python commands for other commands you can use with GainSeeker.

 

Contents  [Hide]

 

Database connections

You can execute SQL queries on the current GainSeeker database connection, or you can create a new temporary connection to another database and use it to execute SQL queries.

For a temporary connection, all commands must use an index [x] to reference the temporary connection.

Commands to set up temporary database connections

Syntax

Example

Description/Remarks

sql[x].connect()

sql[0].connect()

Attempts to connect to the database using the supplied information. Returns True or False.

sql[x].connstr

sql[0].connstr = "Server=MyServer;Database=SPC;User id=SPC;Password=SPC"

Get/Set the connection string. It is strongly recommended that you encrypt your connection string using Pwd_enc.exe, although it may be useful to leave the connection as plain text while testing the connection.

sql[x].encryptconnstr

sql[0].encryptconnstr= True

Get/Set if the connection string is to be encrypted. Defaults to True. It is strongly recommended that you encrypt your connection string using Pwd_enc.exe, although it may be useful to leave the connection as plain text while testing the connection.

sql[x].loadsavedconn(name)

sql[0].loadsavedconn("SQLServerB")

Loads a saved database connection (from the System Administration module) by specifying the connection name.

sql[x].providerstr

sql[0].providerstr = "SqlClient Data Provider"

Get/Set the database provider string.


Example using a saved database connection (created in the System Administration module):

sql[0].loadsavedconn("SQLServerB")

sql[0].connect()


Example with unencrypted connection string (for initial testing):

sql[0].providerstr = "SqlClient Data Provider"

sql[0].encryptconnstr= False

sql[0].connstr = "Server=MyServer;Database=SPC;User id=SPC;Password=SPC"

sql[0].connect()


Example with encrypted connection string:

sql[0].providerstr = "SqlClient Data Provider"

sql[0].encryptconnstr= True

sql[0].connstr = "7A7C6A6D3E6BD113E89F89CCCD26026D57F408FAEA53C306EF4369F5AC0CEC0AB9090361FA84091C6C406BEBDA73BF4CD6092657340FA679AC4E13BE75E682FA7F2726316B1E0D8A3E5E"

sql[0].connect()

Commands to execute queries

Syntax

Example

Description/Remarks

sql.execute(sql)

sql.execute("insert into MYTABLE values (1, 2, 3)")

Executes an insert, update, or delete sql command and returns the number of rows affected. The command returns a -1 if there was an error.

sql[x].execute(sql)

sql[0].execute("insert into MYTABLE values (1, 2, 3)")

Executes an insert, update, or delete sql command and returns the number of rows affected. The command returns a -1 if there was an error.

sql.getcolumn(sql, columnname)

sql.getcolumn("select HSITYPE from HSICFG", "HSITYPE")

Returns a list of values, one field in a row for the specified column. The column must be part of the SELECT portion of the sql statement or an error will occur.

sql.gettable(sql)

sql.gettable("select HSITYPE from HSICFG")

Returns a query result formatted into a list where each element is a row from the query. Each row is a list of elements that represent each field that was queried in the row.

sql.rowcnt(sql)

sql.RowCount("select HSITYPE from HSICFG")

Returns the number of rows the query returns.

When using a temporary database connection

After using the sql[x].connect() command to set up a temporary connection, you can execute queries on that connection by referencing it with an index [x] (where x is a number or string).

For example, this command for the current GainSeeker database connection

sql.gettable(sql)

becomes

sql[x].gettable(sql)

for the temporary database connection.

Commands to return table names for the current configuration

The tables used to store different types of GainSeeker information can vary based on the configuration currently in use (typically determined by which user is logged in).

These commands return the table names used by the current configuration on the current GainSeeker database connection.

Example:

In the following example, the command sql.tables.ddata returns the name of the current DMS data table. It is used in a SQL query on the default GainSeeker database connection to return all rows from this table:

mydmsdata = sql.gettable("select * from " + sql.tables.ddata)

Syntax

Description/Remarks

sql.tables.actionspc

Returns the name of the current SPC Action table.

sql.tables.audittrail

Returns the name of the Audit Trail table.

sql.tables.causespc

Returns the name of the current SPC Cause table.

sql.tables.ddata

Returns the name of the current DMS Data table.

sql.tables.ddataaux

Returns the name of the current DMS Auxiliary table.

sql.tables.dnote

Returns the name of the current DMS Note table.

sql.tables.dproc

Returns the name of the current DMS Process table.

sql.tables.dstds

Returns the name of the current DMS Standards table.

sql.tables.eventdms

Returns the name of the current DMS Event table.

sql.tables.eventspc

Returns the name of the current SPC Event table.

sql.tables.filterdms

Returns the name of the current DMS Filter table.

sql.tables.filterspc

Returns the name of the current SPC Filter table.

sql.tables.filterstatsdms

Returns the name of the current DMS Statistical Filter table.

Note: This table contains DMS statistical filters and other items. To retrieve a list of the DMS statistical filters from this table, use WHERE TYPE = 99 .

sql.tables.filterstatsspc

Returns the name of the current SPC Statistical Filter table.

sql.tables.hsict

Returns the name of the current generic Control Table.

sql.tables.hsirpt

Returns the name of the current HSIRPT table (contains Dashboards, Desktops, Dynamic Reports, Inspections, Priority Lists, Column and Detail Reports, and .Launch settings).

sql.tables.vdata

Returns the name of the current SPC Data table.

sql.tables.vdataaux

Returns the name of the current SPC Auxiliary table.

sql.tables.vnote

Returns the name of the current SPC Note table.

sql.tables.vstds

Returns the name of the current SPC Standards table.