Call us! 512-349-0334 or (877) INDUSOFT

Querying an SQL Database Table with more than 16000 rows In InduSoft Web Studio

If you are facing difficulties querying a SQL database table containing more than 16,000 rows in InduSoft Web Studio, here is a VBScript function that may help. This function, when used as a Procedure and called by passing a table name to it, returns all rows from the specified table, even if the table contains more than 16,000 rows.

 

Function iDBGetTotalRows(strTable)
Dim i, numCur, sql, rows, rowsTotal
‘select first 16000 lines
sql = “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as RowNum FROM ” & strTable & “) seq WHERE seq.RowNum <= 16000”
numCur = $DBCursorOpenSQL(“DB1”, sql)
rows = $DBCursorRowCount(numCur)
$DBCursorClose(numCur)
rowsTotal = rows
Do While rows>=16000
‘select next 16000 lines
sql = “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ID) as RowNum FROM ” & strTable & “) seq WHERE seq.RowNum > ” & rowsTotal
numCur = $DBCursorOpenSQL(“DB1”, sql)
rows = $DBCursorRowCount(numCur)
$DBCursorClose(numCur)
rowsTotal = rowsTotal + rows
Loop
iDBGetTotalRows = rowsTotal
End Function

 

Here we have the same function, but with two additional parameters for the database connection name and the order by field. This allows you to pass a valid column name from your table, and have the returned rows ordered by that column in an ascending fashion.

Function iDBGetTotalRows(strDB, strTable, strOrderField)
Dim i, numCur, sql, rows, rowsTotal
‘select first 16000 lines
sql = “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ” & strOrderField &_
“) as RowNum FROM ” & strTable & “) seq WHERE seq.RowNum <= 16000”
numCur = $DBCursorOpenSQL(strDB, sql)
rows = $DBCursorRowCount(numCur)
$DBCursorClose(numCur)
rowsTotal = rows
Do While rows>=16000
‘select next 16000 lines
sql = “SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ” & strOrderField &_
“) as RowNum FROM ” & strTable & “) seq WHERE seq.RowNum > ” & rowsTotal
numCur = $DBCursorOpenSQL(strDB, sql)
rows = $DBCursorRowCount(numCur)
$DBCursorClose(numCur)
rowsTotal = rowsTotal + rows
Loop
iDBGetTotalRows = rowsTotal
End Function

The iDBGetTotalRows function can be called from any VBScript interface, for instance, from a VBScript on a command animation. E.g.:

Dim tableRows
tableRows = iDBGetTotalRows(“DB1”, “Table_1”, “ID”)
MsgBox “The database table has ” & tableRows & ” rows.”

Comments are closed.