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 |
New or changed in GainSeeker version |
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 |
New or changed in GainSeeker version |
sql.execute(sql) |
sql.execute("insert into MYTABLE values (1, 2, 3)") Executes the specified SQL query on the current GainSeeker database connection.
sql[0].execute("insert into MYTABLE values (1, 2, 3)") Executes the specified SQL query on temporary database connection [0] . |
Executes an insert, update, or delete SQL query 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 from the current GainSeeker database connection.
sql[0].getcolumn("select HSITYPE from HSICFG", "HSITYPE") Returns a list of values from temporary database connection [0] . |
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 query or an error will occur. |
|
sql.gettable("select HSITYPE from HSICFG") Returns a query result from the current GainSeeker database connection.
sql[0].gettable("select HSITYPE from HSICFG") Returnsa query result from temporary database connection [0] . |
Returns a query result formatted into a list where each element is a row from the query result set. 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 returned by the specified query on the current GainSeeker database connection.
sql[0].RowCount("select HSITYPE from HSICFG") Returns the number of rows returned by the specified query on temporary database connection [0] . |
Returns the number of rows in the query result set. |
|
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 |
New or changed in GainSeeker version |
sql.tables.actionspc |
Returns the name of the current SPC Action table. |
8.7 |
sql.tables.audittrail |
Returns the name of the Audit Trail table. |
8.7 |
sql.tables.causespc |
Returns the name of the current SPC Cause table. |
8.7 |
sql.tables.ddata |
Returns the name of the current DMS Data table. |
|
sql.tables.ddataaux |
Returns the name of the current DMS Auxiliary table. |
|
Returns the name of the current DMS defect table. |
8.9 |
|
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. |
8.7 |
sql.tables.eventspc |
Returns the name of the current SPC Event table. |
8.7 |
sql.tables.filterdms |
Returns the name of the current DMS Filter table. |
8.7 |
sql.tables.filterspc |
Returns the name of the current SPC Filter table. |
8.7 |
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 . |
8.7 |
sql.tables.filterstatsspc |
Returns the name of the current SPC Statistical Filter table. |
8.7 |
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). |
8.7 |
Returns the name of the current Python Scripts table. |
8.9 |
|
Returns the name of the current Unique ID table for inspections. See also: How to delete the current Unique ID |
8.9 |
|
Returns the name of the current traceability priority list table. |
8.9 |
|
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. |
|