Python command: SQL

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

The SQL Query action can help you generate Python script for the SQL query you want to execute.

For situations where a script is constantly running - such as polling a data source for new information every few minutes - you can use the GS Console Manager module to launch and/or monitor the progress of such scripts.

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

Contents [Show][Hide]

Database connections

Commands to set up temporary database connections

Commands to execute queries

When using a temporary database connection

Commands to return table names for the current configuration

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	New or changed in GainSeeker version
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	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 SQL query and returns the number of rows affected. The command returns a -1 if there was an error. Most commonly used to run an INSERT, UPDATE, or DELETE query, or to execute a stored procedure that does not return a result set. To execute a stored procedure, consult your database documentation for the appropriate syntax. The simplest method of calling a stored procedure uses the following syntax: Microsoft SQL Server and Oracle databases: EXEC stored_procedure_name MySQL and Pervasive.SQL databases: CALL stored_procedure_name
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(sql)	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] .	Executes an SQL query and returns a result set formatted as a list where each primary list element is a row from the query result set. Each of these primary list elements (one per row) contains a secondary list of the column values returned for that row. Most commonly used to run a SELECT query or to execute a stored procedure that returns a result set. To execute a stored procedure, consult your database documentation for the appropriate syntax. The simplest method of calling a stored procedure uses the following syntax: Microsoft SQL Server and Oracle databases: EXEC <stored procedure name> MySQL and Pervasive.SQL databases: CALL <stored procedure name>
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.

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	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.
sql.tables.defectlist	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.dpri	Returns the name of the current DMS Priority List table. Note: For details on parsing this table correctly for your version of GainSeeker, please contact Hertzler Systems technical support.	9.3
sql.tables.dproc	Returns the name of the current DMS Process table.
sql.tables.dprocdl	Returns the name of the current DMS Process Defect List table. Note: One process with many defects may have multiple rows in this table.	9.3
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
sql.tables.hsiscripts	Returns the name of the current Python Scripts table.	8.9
sql.tables.inspectionuniqueid	Returns the name of the current Unique ID table for inspections. See also: How to delete the current Unique ID	8.9
sql.tables.statlistdms	Returns the name of the current DMS statistics list table.	9.3
sql.tables.statlistspc	Returns the name of the current SPC statistics list table.	9.3
sql.tables.trace1 through sql.tables.trace6	Returns the name of the current traceability list tables for traceability fields 1-6.	9.3
sql.tables.tracemst	Returns the name of the current traceability list table for traceability fields 7-48.	9.3
sql.tables.tracepriority	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.vpri	Returns the name of the current SPC Priority List table. Note: For details on parsing this table correctly for your version of GainSeeker, please contact Hertzler Systems technical support.	9.3
sql.tables.vstds	Returns the name of the current SPC Standards table.