• Welcome, Guest. Please login.
 
June 16, 2019, 10:13:49 pm

News:

Welcome to the SQLitening support forums!


-14 Invalid set

Started by D. Wilson, March 20, 2019, 07:12:39 pm

Previous topic - Next topic

D. Wilson

I have a database with multiple tables (2). I need to load information from both tables my screen. This is my overview on how I am doing it.

1) I open the connection to the Server
2) I open the database
3) I get my record from the first database. There is only 1 record from the first database
4) I close that database
5) I open the second database
6) I retreive the records from from that database. Usually there are multiple records from the second database.
7) I close the connection to the server

I use a page up/page down to retreive the next record.
I am able to do this several times then on the forth (or so ) I get an error -14 Invalid set.

I use different numbers for the returned sets. The server is on the local computer.

Any ideas or insights would be appreciated. Code Examples would be awesome.

Fim

Have you used slCloseSet ?
/Fim W.
Fim W

cj

March 23, 2019, 12:46:03 pm #2 Last Edit: March 23, 2019, 01:27:53 pm by cj
QuoteI use a page up/page down to retreive the next record.

In SQLite the recordset is filled and scrolled.
The concept of previous/next is of the recordset.

Sounds like you are not reading to the end of the recordset which will close it.
If you issue slCloseSet like FIM says should fix the problem, but reading to end of data should be the solution.

Different set numbers should not be needed if the recordset is closed after each select by reading to the end.
The read recordset loop should be examined to see if it is being exited before reading everything.
I normally used slSelAry so never see this problem.   See the function below named FillControl.

Example filling a textbox with multiple recordsets from the SQLite database "sample.db3"  "parts" table using a global string.
If slSelAry in the below function FillControl is replaced with your read recordset loop should reveal the error.


#PBFORMS CREATED V2.01 'scroll.bas for future reference https://www.sqlitening.planetsquires.com/index.php?topic=9733.msg26332#msg26332
#COMPILE EXE
#DIM ALL
GLOBAL ghDlg AS DWORD  'global handle to dialog
GLOBAL gs AS STRING    'recordset string to fill textbox
#PBFORMS BEGIN INCLUDES
#INCLUDE ONCE "WIN32API.INC"
#PBFORMS END INCLUDES
#INCLUDE "SQLITENING.INC"
#PBFORMS BEGIN CONSTANTS
%BUTTON_SELECT = 1001
%TEXT_RESULT   = 1002
%TEXT_SQL      = 1003
#PBFORMS END CONSTANTS
#PBFORMS DECLARATIONS
'------------------------------------------------------------------------------
FUNCTION FillControl AS LONG
LOCAL sql      AS STRING
LOCAL sArray() AS STRING
'slconnect "192.168.0.2"
slopen "sample.db3"
CONTROL GET TEXT ghDlg,%TEXT_SQL TO sql    'sql statement
slselary sql,sArray(),"Q9c"                'get recordset
gs+=JOIN$(sArray(),$CRLF)                  'add to previous recordset
gs+=$CRLF + STRING$(150,".") + $CRLF       'optional
CONTROL SET TEXT ghDlg,%TEXT_RESULT, gs    'fill control
'CONTROL SEND ghDlg,%TEXT_Result,%WM_VSCROLL,%SB_BOTTOM,0  'scroll to bottom
'slClose                                    'close database
'slDisconnect                               'disconnect
END FUNCTION
'------------------------------------------------------------------------------
FUNCTION PBMAIN()
ShowDIALOG1 %HWND_DESKTOP
END FUNCTION

CALLBACK FUNCTION ShowDIALOG1Proc()
  SELECT CASE AS LONG CB.MSG
    CASE %WM_INITDIALOG
     Fillcontrol

    CASE %WM_COMMAND
      SELECT CASE AS LONG CB.CTL

       CASE %BUTTON_SELECT
          IF CB.CTLMSG = %BN_CLICKED OR CB.CTLMSG = 1 THEN
            FillControl
          END IF
      END SELECT
  END SELECT
END FUNCTION
FUNCTION ShowDIALOG1(BYVAL hParent AS DWORD) AS LONG
  LOCAL lRslt AS LONG

#PBFORMS BEGIN DIALOG %IDD_DIALOG1->->
  LOCAL hDlg  AS DWORD

  DIALOG NEW hParent, "Dialog1", 457, 206, 358, 225, %WS_POPUP OR %WS_BORDER _
    OR %WS_DLGFRAME OR %WS_CAPTION OR %WS_SYSMENU OR %WS_CLIPSIBLINGS OR _
    %WS_VISIBLE OR %DS_MODALFRAME OR %DS_3DLOOK OR %DS_NOFAILCREATE OR _
    %DS_SETFONT, %WS_EX_CONTROLPARENT OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR, TO hDlg
  CONTROL ADD BUTTON,  hDlg, %BUTTON_SELECT, "Select", 200, 200, 50, 15
  CONTROL ADD TEXTBOX, hDlg, %TEXT_RESULT, "", 5, 5, 345, 185, %WS_CHILD OR _
    %WS_VISIBLE OR %WS_HSCROLL OR %WS_VSCROLL OR %ES_LEFT OR %ES_MULTILINE _
    OR %ES_AUTOHSCROLL OR %ES_AUTOVSCROLL OR %ES_WANTRETURN, _
    %WS_EX_CLIENTEDGE OR %WS_EX_LEFT OR %WS_EX_LTRREADING OR _
    %WS_EX_RIGHTSCROLLBAR
  CONTROL ADD TEXTBOX, hDlg, %TEXT_SQL, "select rowid,MANUF,PRODUCT from " + _
    "parts limit 5", 5, 200, 190, 13
#PBFORMS END DIALOG
  ghDlg = hDlg
  DIALOG SHOW MODAL hDlg, CALL ShowDIALOG1Proc TO lRslt
#PBFORMS BEGIN CLEANUP %IDD_DIALOG1
#PBFORMS END CLEANUP
FUNCTION = lRslt
END FUNCTION'


D. Wilson

Thanks for the help!!

That is the confusing thing I am looping through the entire record set.

I am trying to track it issue down. It will process 5 or 10 correctly then flag the error on number 11.

I will relook at my code and let you know what I am finding.

CJ - thanks for the code example I will compare it to what I have done.

cj

Incorrect sql statements are caught by slSel, but a zero length sql statement will not error until slGetColumnCount with an error -14.

Added this error check:
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION

Also added:
If rownum = 0 then ERASE sRecordSet:EXIT FUNCTION ' so a big empty array is not returned when there is no data to return.


#INCLUDE "sqlitening.inc"

FUNCTION PBMAIN()
LOCAL sql,sRecordSet(),sColumnDelimiter,sReturned AS STRING
sColumnDelimiter = "  "
slopen "sample.db3","C"
sql = "select rowid,manuf,price from parts limit 3"
sReturned = GetRecordSet(sql,sRecordSet(),sColumnDelimiter)
IF LEN(sReturned) THEN ? sReturned
END FUNCTION

FUNCTION GetRecordSet(sql AS STRING,sRecordSet() AS STRING,sColumnDelimiter AS STRING) AS STRING
LOCAL colnum,rownum,columns,highelement AS LONG
IF LEN(sql) = 0 THEN ? "No select statement",%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet":EXIT FUNCTION
slsel sql,0,"E0"

IF slGetErrorNumber THEN                          'execute the sql statement
   ? sql + $CR +$CR + slGetError,%MB_SYSTEMMODAL OR %MB_ICONERROR,"GetRecordSet"
   EXIT FUNCTION
END IF

columns = slGetColumnCount                        'number of columns to return

DIM sCol(1 TO columns) AS STRING                  'avoid concatenation of current row
highelement = 50000                               'arbitrary top limit of recordset array
REDIM sRecordset(1 TO highelement) AS STRING

DO WHILE slGetRow                                 'read recordset loop
  INCR rownum                                      'actual number of reads rows
  IF rownum > highelement THEN                     'redim recordset array if needed
    highelement = highelement + 50000
    REDIM PRESERVE sRecordset(1 TO highelement)
  END IF
  FOR colnum = 1 TO columns                        'read columns of current row
   sCol(colnum) = slf(colnum)                      'place into current column array
  NEXT
  sRecordset(rownum) = JOIN$(sCol(),sColumnDelimiter)  'concatenate columns into current recordset(row)
LOOP

IF rownum = 0 THEN
    ERASE sRecordSet
    ? "No data",%MB_SYSTEMMODAL,"GetRecordSet"
    EXIT FUNCTION      'exit if no rows returned
END IF

REDIM PRESERVE sRecordset(1 TO rownum)            'shrink array to actual size
FUNCTION = JOIN$(sRecordSet(),$CR)
END FUNCTION                     

D. Wilson

I found the issue was with a grid control I was using.

I solved the problem by creating a 'system lock' flag that will prevent the user from loading another record till the other record has been loaded and the flag has been cleared