• Replies 1 reply
  • Subscribers 1 subscriber
  • Views 7 views
  • Users 0 members are here
Options
Related

Quering Database Table with mroe than 16000 rows

Admin
Should you be facing troubles to query a database table containing more than 16000 rows on IWS, here is an example of a VB function that can be used. This function, when placed, for instance, under Main Procedures and called passing the Table name, returns the total number of rows (even if the table is bigger than 16000 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


Enjoy!

Greets,
Ricardo Marroni

Parents
  • Here the same function but with two additional fields for the database connection name and the order field, so you can pass any valid field of your table.

    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


    This function can be called, for instance, from a VBscript on a command button. E.g.:

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


    Greets,
    Ricardo Marroni
Reply
  • Here the same function but with two additional fields for the database connection name and the order field, so you can pass any valid field of your table.

    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


    This function can be called, for instance, from a VBscript on a command button. E.g.:

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


    Greets,
    Ricardo Marroni
Children
No Data