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] |
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.
|
Syntax |
Example |
Description/Remarks |
|
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()
|
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("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. |
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.
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. |