system.db.runQuery

This function is used in Python Scripting.

Description

Runs a SQL query, usually a SELECT query, against a database, returning the results as a dataset. If no database is specified, or the database is the empty-string "", then the current project's default database connection will be used. The results are returned as a PyDataSet, which is a wrapper around the standard dataset that is convenient for scripting.

Client Permission Restrictions

Permission Type: Legacy Database Access

Client access to this scripting function is blocked to users that do not meet the role/zone requirements for the above permission type. This function is unaffected when run in the Gateway scope.

Syntax - Gateway

system.db.runQuery(query, database, [tx])

Parameters

Type	Parameter	Description
String	query	A SQL query, usually a SELECT query, to run.
String	database	The name of the database connection to execute against.
String	tx	A transaction identifier. If omitted, the query will be executed in its own transaction. [optional]

Returns

PyDataSet - The results of the query as a PyDataSet.

Scope

Gateway

Syntax - Vision and Perspective

system.db.runQuery(query, [database], [tx])

Parameters

Type	Parameter	Description
String	query	A SQL query, usually a SELECT query, to run.
String	database	The name of the database connection to execute against. If omitted or "", the project's default database connection will be used. [optional]
String	tx	A transaction identifier. If omitted, the query will be executed in its own transaction. [optional]

Returns

PyDataSet - The results of the query as a PyDataSet.

Scope

Vision Client, Persepctive Session

Code Examples

Assuming the following dataset:

ID	Value
3	9.87
2	67.2
1	3.55

If you executed the following code:

 table = system.db.runQuery("SELECT * FROM TEST")

Table[2] would access the third row (rows are zero-indexed), and both table[2][0] and table[2]["ID"] would access the ID value of the third row.

As further example of how to use the results of runQuery, here are seven different ways to print out the table, and their results follow. Note that some of the later methods exercise some more advanced Jython concepts such as list comprehensions and string formatting, but their intent should be obvious. Generally speaking, the more concise Jython code becomes, the more readable it is.

Code Snippet - Executing Query and Printing Its Results

table = system.db.runQuery("SELECT * FROM Test")
  
print "Printing TEST Method 1..."
for row in table:
   for col in row:
      print col,
   print ""
print ""
  
print "Printing TEST Method 2..."
for row in table:
   print row[0], row[1]
print ""
  
print "Printing TEST Method 3..."
for row in table:
   print row["ID"], row["VALUE"]
print ""
  
print "Printing TEST Method 4..."
for rowIdx in range(len(table)):
   print "Row ",str(rowIdx)+": ", table[rowIdx][0], table[rowIdx][1]
print ""
  
print "Printing TEST Method 5..."
print [str(row[0])+", "+ str(row[1]) for row in table]
print ""
  
print "Printing TEST Method 6..."
print ["%s, %s" % (row["ID"],row["VALUE"]) for row in table]
print ""
  
print "Printing TEST Method 7..."
print [[col for col in row] for row in table]
print ""

The result would be:

Printing TEST Method 1...

0 3.55

1 67.2

2 9.87


Printing TEST Method 2...

0 3.55

1 67.2

2 9.87


Printing TEST Method 3...

0 3.55

1 67.2

2 9.87


Printing TEST Method 4...

Row 0: 0 3.55

Row 1: 1 67.2

Row 2: 2 9.87


Printing TEST Method 5...

['0, 3.55', '1, 67.2', '2, 9.87']


Printing TEST Method 6...

['0, 3.55', '1, 67.2', '2, 9.87']


Printing TEST Method 7...

[[0, 3.55], [1, 67.2], [2, 9.87]]