• Welcome to SQLitening Support Forum.
 

News:

Welcome to the SQLitening support forums!

Main Menu

Should/does this return a recordset?

Started by cj, October 22, 2011, 12:39:41 PM

Previous topic - Next topic

cj

I have a program for processing statements that works great, but wondered
if I am dropping recordsets which mix statements with SELECT statements.
All calls are using remote server.


BEGIN;
DROP TABLE IF EXISTS CLIENT;
CREATE TABLE IF NOT EXISTS CLIENT (NUMBER);
INSERT INTO CLIENT VALUES("HOW");
INSERT INTO CLIENT VALUES("NOW");
INSERT INTO CLIENT VALUES("BROWN");
INSERT INTO CLIENT VALUES("COW");
SELECT * FROM CLIENT;
END;


If the above code does return a recordset, I'm not processing it.
Perhaps more code is needed in my function executor below.
If a recordset is not processed, what happens the next time a recordset is returned?


FUNCTION Execute(OPTIONAL sTemp AS STRING) AS LONG
  LOCAL COL, columns, result AS LONG,s, sRow AS STRING

  IF VARPTR(sTemp) THEN 'passing something so skip reading from dialog
     s = LTRIM$(sTemp,ANY CHR$(0 TO 32))
  ELSE
    CONTROL GET TEXT ghDlg, %TEXTBOX1 TO s 'get text
    s = LTRIM$(s,ANY CHR$(0 TO 32))
    'CONTROL SET TEXT ghDlg, %TEXTBOX1, s  'put stripped string back into textbox

    IF LEN(s) = 0 THEN
      'nothing in dialog so add a select statement
      CONTROL GET TEXT ghDlg, %TEXTBOX2 TO s
      CONTROL SET TEXT ghDlg, %TEXTBOX1, "SELECT * FROM " + s
      EXIT FUNCTION
    END IF
  END IF

  IF UCASE$(LEFT$(s,7))= "SELECT " THEN
    IF slSel(s) THEN
      DisplayError
      FUNCTION = slGetErrorNumber
      EXIT FUNCTION  'select error
    END IF
  ELSEIF slEXE(s) THEN
      DisplayError
      FUNCTION = slGetErrorNumber
      EXIT FUNCTION 'other statment error
  END IF

  'LISTBOX RESET ghDlg, %LISTBOX
  LISTBOX ADD ghDlg, %LISTBOX, STRING$(60,"-")
  IF ISFALSE slGetRow THEN
    LISTBOX ADD ghDlg,%LISTBOX,"Success, but nothing to return"
    EXIT FUNCTION
  END IF

  columns = SlGetColumnCount
  IF columns=0 THEN
    ? "No columns"
    FUNCTION = slGetErrorNumber
    EXIT FUNCTION
  END IF

  DIM rowcount AS LONG
  DO
    INCR rowcount
    sRow= FORMAT$(rowcount) + "   "
    FOR COL = 1 TO columns
      sRow = sRow + slF(COL)+ $Delimiter
    NEXT
    LISTBOX ADD ghDlg, %LISTBOX, LEFT$(sRow,LEN(sRow)-1)
  LOOP WHILE slGetRow

  REM Listbox1.ListIndex = Listbox1.ListCount - 1  'VB6 way
  LOCAL ListboxCount AS LONG
  LISTBOX GET COUNT ghDlg, %LISTBOX TO ListboxCount
  LISTBOX SELECT    ghDlg, %LISTBOX,   ListboxCount
  LISTBOX UNSELECT  ghDlg, %LISTBOX,   ListboxCount
  'CONTROL HANDLE  ghDlg, %LISTBOX TO hList
  'ListboxCount = SendMessage(hList, %LB_GETCOUNT,  0, 0) 'Ask listbox for items count
  'SendMessage hList, %lb_setcaretindex, ListboxCount+1, 0  'yay!!
END FUNCTION

                           


Fred Meier

Each slSel creates a set.  Each set is assigned a number.  Each set number is open or closed.  If you issue a slSel using an open set number then error -14 will occur. Using slGetRow until end will close the set.  slCloseSet will also close a set.  Set number zero is special.  It is automatically closed when slSel is issued.

The following will not throw error -14.
   slSel "Select * from Parts"
   slSel "Select * from Parts"
   slSel "Select * from Parts", 1
   slCloseSet
   slSel "Select * from Parts", 1
   do while slGetRow(1)
   loop
   slSel "Select * from Parts", 1
   
The following will throw error -14.
  slSel "Select * from Parts", 1
   slSel "Select * from Parts", 1

cj

#2
Thank you for that information.
I may be abandoning some recorsets without knowing it..

This and more complex SELECT statements work fine.
SELECT * FROM CLIENT;

Should the following return a recordset?
Also,  without the transaction, I am not getting a recordset.


BEGIN;
DROP TABLE IF EXISTS CLIENT;
CREATE TABLE IF NOT EXISTS CLIENT (F1);
INSERT INTO CLIENT VALUES("HOW");
INSERT INTO CLIENT VALUES("NOW");
INSERT INTO CLIENT VALUES("BROWN");
INSERT INTO CLIENT VALUES("COW");
SELECT * FROM CLIENT;
END;


This works:
SELECT COUNT(*) FROM CLIENT;

This doesn't return anything?
BEGIN;
SELECT COUNT(*) FROM CLIENT;
END;

Fred Meier

Don't understand they all return records for me using the following:
   slOpen
   slExe "Begin"
   slExe "Drop Table if exists Client"
   slExe "Create Table Client (F1)"
   slExe slBuildInsertOrUpdate("Client", "How")
   slExe slBuildInsertOrUpdate("Client", "Now")
   slExe slBuildInsertOrUpdate("Client", "Brown")
   slExe slBuildInsertOrUpdate("Client", "Cow")
   slSel "Select * from Client
   reset llA
   do while slGetRow
      incr llA
   loop
   msgbox format$(llA)
   slExe "End"

   slExe "Drop Table if exists Client"
   slExe "Create Table Client (F1)"
   slExe slBuildInsertOrUpdate("Client", "How")
   slExe slBuildInsertOrUpdate("Client", "Now")
   slExe slBuildInsertOrUpdate("Client", "Brown")
   slExe slBuildInsertOrUpdate("Client", "Cow")
   slSel "Select * from Client
   reset llA
   do while slGetRow
      incr llA
   loop
   msgbox format$(llA)

   slSel "Select count(*) from Client"
   reset llA
   do while slGetRow
      incr llA
   loop
   msgbox format$(llA)

   slExe "Begin"
   slSel "Select count(*) from Client"
   reset llA
   do while slGetRow
      incr llA
   loop
   slExe "End"
   msgbox format$(llA)


Abandoning (not closing) records sets does not hurt anything in remore mode but if in local mode and you get only some of the rows then the file will remain  locked until you close the set or close the database or exit the process.   Rule is ---- if you get some of the rows in a record set, get them all or close the set.