posted April 08, 2003 12:02 AM            

quote:

---

Alfonso Olivares Ramos
Hello Nathan i try the code, and is great, but one little
thing happend, when i try to make select when only there is
one register in the table , i get a GPF (General Protection
Fault) other way, works great
i'm using PBwin 7.00 and XP Pro

---

You're not the only one! I've selecting a table and after 4 rows being returned it will GPF, but it's inside
the SQLite.DLL, i'm currently debugging it and will of course post revised code once i've got it working.

posted April 08, 2003 06:31 AM            

Does anyone know what that is in PB?

Is it something like this? :-

FUNCTION sqlite_callback(BYVAL pArg AS LONG PTR, BYVAL nArg AS LONG, BYVAL szArg AS LONG PTR, BYVAL szCol AS LONG PTR) AS LONG

posted April 08, 2003 08:58 AM            

See the thread on this at http://www.hellobasic.com/cgi-bin/forum/YaBB.pl?board=general;action=display;num=1046512614

posted April 29, 2005 01:31 PM            

quote:

---

You're not the only one! I've selecting a table and after 4 rows being returned it will GPF, but it's inside
the SQLite.DLL, i'm currently debugging it and will of course post revised code once i've got it working.

---

Hi!

I am having this exact same issue when using the sqlite exec()
function. Four iterations of the callback loop, then boom! GPF.

It's smells like memory de/allocation issue, but I can't seem
to debug it. Tried various other data types as params, no luck.

Anyone else run into this?

Thanks in advance for any replies - code to follow;

Terence

#COMPILE EXE
#DIM ALL
#INCLUDE "WIN32API.INC"
#INCLUDE "my_sqlite_headers.bas"
DECLARE FUNCTION 	sqlite3_open CDECL LIB "sqlite3.dll" ALIAS "sqlite3_open" (zFilename AS ASCIIZ, hSQLite AS LONG) AS LONG
DECLARE SUB 		sqlite3_close CDECL LIB "sqlite3.dll" ALIAS "sqlite3_close" (BYVAL hSQLite AS LONG)
DECLARE FUNCTION    sqlite3_exec CDECL LIB "sqlite3.dll" ALIAS "sqlite3_exec" (BYVAL hSQLite AS LONG, szSql AS ASCIIZ, BYVAL sqlite_callback AS LONG, cbParam AS ANY, lpErrMsg AS LONG) AS LONG
'==============================================================================
FUNCTION sqlite_callback(BYVAL Param AS LONG PTR, BYVAL NumColumns AS LONG, BYVAL arrColumnData AS DWORD PTR, BYVAL arrColumnNames AS LONG PTR) AS LONG
	'The 1st parameter = the 4th parameter in the sqlite3_exec() call.
	'The 2nd parameter = number of columns in the query result.
	'The 3rd parameter = an array of strings holding the values for each column.
	'The 4th parameter = an array of strings holding the names of each column.
	MSGBOX "Param: " & format$(@Param) & $CRLF & _
		"Num Columns: " & FORMAT$(NumColumns) & $CRLF & _
		"arrColumnData(): " & FORMAT$(arrColumnData) & $CRLF & _
		"arrColumnNames(): " & FORMAT$(arrColumnNames)
	LOCAL pData1 AS ASCIIZ PTR : pData1 = @arrColumnData[0]
	LOCAL pData2 AS ASCIIZ PTR : pData2 = @arrColumnData[1]
	LOCAL pData3 AS ASCIIZ PTR : pData3 = @arrColumnData[2]
	MSGBOX @pData1 & " " & @pData2 & " " & @pData3
	FUNCTION = 0
END FUNCTION
'==============================================================================
FUNCTION PBMAIN
	LOCAL hDB AS LONG 'handle to database
	LOCAL iErr AS LONG
	LOCAL zDBName AS ASCIIZ * 260 : zDBName = ".\test_sqlite_app.db"
	LOCAL zSql AS ASCIIZ * 4096
	LOCAL cbParam AS LONG
	IF sqlite3_open(zDBName, hDB) THEN MSGBOX "Error!"
	zSql = "SELECT * from NAMES"
	sqlite3_exec(hDB, zSql, CODEPTR(sqlite_callback), cbParam, iErr)
	sqlite3_close (hDB)
	MSGBOX "DONE"
END FUNCTION
'==============================================================================

posted April 29, 2005 01:54 PM            

quote:

---

'The 3rd parameter = an array of strings holding the values for each column.
'The 4th parameter = an array of strings holding the names of each column.

---

What's the format of these "arrays" supposed to be?

The callback function shown shows the passed params as "LONG PTR".

Is is supposed to be an array of ASCIIZ PTR? (if so, are you responsible to de-allocate?)
How about an array of BSTR handle? (ditto re de-allocation)
Maybe even a pointer to a PowerBASIC array descriptor (of some type, probably string)?

That function would be a lot easier to use if were an array of ASCIIZ strings, but that won't work if you need to get any 'binary' data from the database.

posted April 29, 2005 02:45 PM            

quote:

---

'The 3rd parameter = an array of strings holding the values for each column.
'The 4th parameter = an array of strings holding the names of each column.

What's the format of these "arrays" supposed to be?

The callback function shown shows the passed params as "LONG PTR".

Is is supposed to be an array of ASCIIZ PTR? (if so, are you responsible to
de-allocate?)
How about an array of BSTR handle? (ditto re de-allocation)
Maybe even a pointer to a PowerBASIC array descriptor (of some type, probably
string)?

That function would be a lot easier to use if were an array of ASCIIZ strings,
but that won't work if you need to get any 'binary' data from the database.

---

Hello Mr Mattias;

Thanks for input.

The third and fourth parameters are returning C string arrays.

Here is the prototype of the sqlite3_exec() and notes from the sqlite docs:

===============================================================
int sqlite3_exec(
  sqlite3*,                     /* An open database */
  const char *sql,              /* SQL to be executed */
  sqlite3_callback,             /* Callback function */
  void *,                       /* 1st argument to callback function */
  char **errmsg                 /* Error msg written here */
);
** A function to executes one or more statements of SQL.
**
** If one or more of the SQL statements are queries, then
** the callback function specified by the 3rd parameter is
** invoked once for each row of the query result.  This callback
** should normally return 0.  If the callback returns a non-zero
** value then the query is aborted, all subsequent SQL statements
** are skipped and the sqlite3_exec() function returns the SQLITE_ABORT.
**
** The 4th parameter is an arbitrary pointer that is passed
** to the callback function as its first parameter.
**
** The 2nd parameter to the callback function is the number of
** columns in the query result.  The 3rd parameter to the callback
** is an array of strings holding the values for each column.
** The 4th parameter to the callback is an array of strings holding
** the names of each column.
**
** The callback function may be NULL, even for queries.  A NULL
** callback is not an error.  It just means that no callback
** will be invoked.
**
** If an error occurs while parsing or evaluating the SQL (but
** not while executing the callback) then an appropriate error
** message is written into memory obtained from malloc() and
** *errmsg is made to point to that message.  The calling function
** is responsible for freeing the memory that holds the error
** message.   Use sqlite3_free() for this.  If errmsg==NULL,
** then no error message is ever written.
**
** The return value is is SQLITE_OK if there are no errors and
** some other return code if there is an error.  The particular
** return value depends on the type of error.
**
** If the query could not be executed because a database file is
** locked or busy, then this function returns SQLITE_BUSY.  (This
** behavior can be modified somewhat using the sqlite3_busy_handler()
** and sqlite3_busy_timeout() functions below.)
*/
===============================================================

Instead of using PB MSGBOX's for debugging, I modified the code to write each
iteration of the callback to a file, which I have appended below. Note: the
database file I am using is a simple test containing Lastname, Firstname and
Note (three columns).

The maddening thing is that the callback always works for four iterations (there
are ten records in the database), then GPF's. I have tried modifying the order
the records are retrieved in (using ORDER BY clause) and that works - the first
four records returned are different, then boom.

You can notice in the fourth (final) iteration, the value of the pointer to the
data array suddenly changes. I don't know the significance of this other than
it happens just before GPF. Could be completely unrelated; however in my very
limited exposure working with pointers, it almost looks like the range of the
variable is too small and the value has "rolled over", but I have tried using
DWORDS PTR's to no avail.

Perhaps someone might recommend a more robust debugging strategy I might pursue?
My experience is limited to debug statements - which get me nowhere in terms of
tracing this particular bug.

thanks again - Terence.

------------------------------
Param: 0
Num Columns: 3
arrColumnData(): 8615444
arrColumnNames(): 8615432
lastname = AXEL
firstname = GREG
Note = TEST NOTE
------------------------------
Param: 0
Num Columns: 3
arrColumnData(): 8615444
arrColumnNames(): 8615432
lastname = JONES
firstname = BILL
Note = TEST NOTE
------------------------------
Param: 0
Num Columns: 3
arrColumnData(): 8615444
arrColumnNames(): 8615432
lastname = KRUGER
firstname = ALEX
Note = TEST NOTE
------------------------------
Param: 0
Num Columns: 3
arrColumnData(): 1240416
arrColumnNames(): 8615432
lastname = MCDONNELL
firstname = TERENCE
Note = TEST NOTE
------------------------------

posted April 29, 2005 04:50 PM            

--don

posted April 29, 2005 04:59 PM            

That the base pointer changes is not surprising.. often C programmers need to reallocate a work buffer... which is why the pointer value changes.

When you do a c "realloc" the original pointer is no longer valid. If in your callback you are trying to use one of those invalid pointers in a subsequent call, that's when you get a GPF.

That is, you should be able to do something like..

LOCAL pAZ AS ASCIIZ PTR
  pAZ =  Param3 
  FOR N = 1 to Param2 (number of columns)
    Mystring = @pAZ      ' move string from returned buffer to another place
    pAZ      = pAZ + 4   ' INCR will not work here; advance to next string pointer
  NEXT

What you can't do is assume any pointer you get on one call will still be valid in a future call. (Not enough code shown to tell how you are handling all the returned rows).

MCM

posted May 20, 2005 03:36 PM            

Just an fyi for those using sqlite.

I have not been able to get callback-based version of the code
to work properly, so like others, I went to "get_table" method
to return a "recordset".

In my case, I just wrote a small function that calls get_table
and returns a multi-dimensional array (row/column).

I have been testing with a database with 1 table, 3 indices.

Table has ~ 100 thousand rows, about 2k per row. DB files is
about 100 MB. Performance is pretty good if you use your
indexes wisely and be sure to use transactions on inserts.

Not as fast as P-tree, but as my relational-database friends
always tell me:

Flat file databases run like race cars - but you have to drive
a race car to work every day and they're expensive to fix.

With a relational database, you can't go as fast, but one day
you can drive a van to work, another day a jeep, or a pickup, etc.

Anyway - here is code I used to add, drop, and query records -
obviously won't work since you don't have the db file, but may
be instructive for others looking for direction.

Let me know if you find glaring errors;

thanks for your help Michael, Don and others... Terence.

#COMPILE EXE
#DIM ALL
#INCLUDE "WIN32API.INC"
DECLARE FUNCTION 	sqlite3_open CDECL LIB "sqlite3.dll" ALIAS "sqlite3_open" (zFilename AS ASCIIZ, hSQLite AS LONG) AS LONG
DECLARE SUB 		sqlite3_close CDECL LIB "sqlite3.dll" ALIAS "sqlite3_close" (BYVAL hSQLite AS LONG)
DECLARE FUNCTION	sqlite_get_table CDECL LIB "sqlite3.dll" ALIAS "sqlite3_get_table" (BYVAL hSQLite AS LONG, szSql AS ASCIIZ, lpTable AS LONG, nRow AS LONG, nColumn AS LONG, lpErrMsg AS LONG) AS LONG
DECLARE FUNCTION	sqlite_free_table CDECL LIB "sqlite3.dll" ALIAS "sqlite3_free_table" (BYVAL lpTable AS LONG) AS LONG
DECLARE FUNCTION	sqlite3_exec CDECL LIB "sqlite3.dll" ALIAS "sqlite3_exec" (BYVAL hSQLite AS LONG, szSql AS ASCIIZ, BYVAL sqlite_callback AS LONG, cbParam AS LONG, lpErrMsg AS LONG) AS LONG
'==============================================================================
FUNCTION pbQuery(hDB AS LONG, sSql AS STRING, arrResults() AS STRING) AS LONG
	REGISTER i AS LONG
	LOCAL lpTable    AS LONG PTR     ' Array of fields returned from get_table (starts with column names)
	LOCAL nRows      AS LONG         ' Number of rows in returned recordset
	LOCAL nColumns   AS LONG         ' Number of columns in returned recordset
	LOCAL lpErrorSz  AS LONG         ' Error msg
	LOCAL zField     AS ASCIIZ PTR   ' Field return from get_table (element in lpTable array)
	LOCAL iFields    AS LONG         ' Number of fields returned by get table
	LOCAL iRow       AS LONG
	LOCAL iCol       AS LONG
	LOCAL iResult    AS LONG         ' Number of rows or error returned by my function
	IF sqlite_get_table(hDB, BYVAL STRPTR(sSQL), lpTable, nRows, nColumns, lpErrorSz) = 0 THEN 'Succeeded
		IF nRows = 0 THEN GOTO Bail
		iFields = ((nRows+1) * nColumns)-1
		REDIM arrResults(nRows, nColumns-1)
		FOR i = 0 TO iFields
			zField = @lpTable[i]
			arrResults(iRow, iCol) = @zField
			INCR iCol : IF iCol = nColumns THEN iCol = 0
			IF (i+1) MOD nColumns = 0 THEN INCR iRow
		NEXT i
	ELSE
		iResult = -1 : GOTO Bail
	END IF
	FUNCTION = nRows
Bail:
	sqlite_free_table lpTable
END FUNCTION
'==============================================================================
FUNCTION PBMAIN
	REGISTER i       AS LONG         ' Loop counter
	REGISTER j       AS LONG
	LOCAL hDB        AS LONG         ' Handle to the database
	LOCAL sDBName    AS STRING       ' Database Name
	LOCAL sSql       AS STRING       ' SQL to execute
	LOCAL iErr       AS LONG         ' Error number
	LOCAL sOutput    AS STRING
	'---Set the database file...
	sDBName = ".\testdb.db"
	'---Open the database
	IF sqlite3_open(BYVAL STRPTR(sDBName), hDB) THEN MSGBOX "Error in Open Database"
	'	LOCAL iRecCount AS LONG, ff AS LONG : ff = FREEFILE
	'	ERRCLEAR : OPEN "Test_100k.csv" FOR INPUT AS #ff
	'		IF ERR THEN MessageBox 0, "File Open Error...", "File Error", %MB_OK + %MB_ICONERROR + %MB_TASKMODAL : EXIT FUNCTION
	'		FILESCAN #ff, RECORDS TO iRecCount
	'		DIM arrFile(iRecCount-1) AS STRING
	'		LINE INPUT #ff, arrFile()
	'	CLOSE #ff
	'
	'	MSGBOX "File Loaded"
	'---Insert new records into database...
	'	sSql = "BEGIN TRANSACTION;"
	'	sqlite3_exec(hDB, BYVAL STRPTR(sSql), 0, 0, 0)
	'		FOR i = 0 TO UBOUND(arrFile)
	'			sSql = ""
	'			sSql = "INSERT INTO DEMO VALUES ("
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 1) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 2) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 3) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 4) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 5) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 6) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 7) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 8) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 9) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 10) & "',"
	'			sSql = sSql & "'" & PARSE$(arrFile(i), 11) & "');"
	'			IF sqlite3_exec(hDB, BYVAL STRPTR(sSql), 0, 0, 0) = 0 THEN
	'				'SUCCESS
	'			ELSE
	'				INCR iErr
	'				ITERATE FOR
	'			END IF
	'		NEXT
	'	sSql = "END TRANSACTION;"
	'	sqlite3_exec(hDB, BYVAL STRPTR(sSql), 0, 0, 0)
	''---Delete records from database...
	'	sSql = "DELETE FROM NAMES WHERE LASTNAME LIKE 'TESTLASTNAME%';"
	'	IF sqlite3_exec(hDB, BYVAL STRPTR(sSql), 0, 0, 0) = 0 THEN 'SUCCESS
	'	ELSE 
	'		MSGBOX "INSERT BAD"
	'	END IF

run_query:
	DIM arrResults(-1,-1) AS STRING
	sSql = INPUTBOX$("Please enter your sql here...")
'     Select * from demo where lastname = 'Mcdonnell';
	SELECT CASE pbQuery(hDB, sSql, arrResults())
		CASE < 0
			MSGBOX "An error occured processing your query..."
		CASE = 0
			MSGBOX "Query returned 0 rows..."
		CASE ELSE
			LOCAL sMsg AS STRING
			sMsg = "Query has returned " & FORMAT$(UBOUND(arrResults,1)) & " rows - would you like to view results?"
			IF MessageBox(0, BYVAL STRPTR(sMsg), "Query", %MB_YESNO + %MB_ICONINFORMATION + %MB_TASKMODAL) = %IDYES THEN
				FOR i = 0 TO UBOUND(arrResults,1)
					FOR j = 0 TO UBOUND(arrResults,2)
						sOutput = sOutput & " | " & arrResults(i, j)
					NEXT
					MSGBOX sOutput
					sOutput = ""
				NEXT
			END IF
	END SELECT
	sMsg = "Would you like to run another query?"
	IF MessageBox(0, BYVAL STRPTR(sMsg), "Query", %MB_YESNO + %MB_ICONINFORMATION + %MB_TASKMODAL) = %IDYES THEN GOTO run_query
	sqlite3_close (hDB) : MSGBOX "DONE"
EXIT FUNCTION
ErrTrap:
END FUNCTION
'==============================================================================

posted October 31, 2005 03:30 PM            

I’m trying to do a little modification to hide the pointer and asciiz stuff for my INC file.

the declare is:
sqlite3_open CDECL LIB "sqlite3.dll" ALIAS "sqlite3_open" (zFilename AS ASCIIZ, hSQLite AS LONG) AS LONG

This works:
sDB = "MyDB.sdb"
if sqlite3_open(byval strptr(sDB), hDB) then

I want to wrap this so I can call it: sqlOpen( “MyDB.sdb”, hDB )

This doesn’t work!
function sqlOpen( byval sFileSpec as string, byval hSQLite as long ) as long
function = sqlite3_open( byval strptr(sFileSpec), hSQLite )
end function

???

posted November 09, 2005 09:13 PM            

quote:

---

Originally posted by james klutho:
Thanks for your SQLite post. I love its simplicity.

---

Hi James;

Can't tell you how happy I am that someone might benefit from
some small piece of code I have posted, considering the
innumerable benefits I have received from snippets that others
on the forum have posted.

Can't claim pride of authorship, however - only derived/rendered
from posts by Nathan E. (especially on this topic), Don D and
others (Michael M., et. al.).

I, too, am anxiously awaiting others posting source on working
with the sqlite db because at present (IMHO) the PB interface is
pretty weak (for ex - still can't get callback version of code
to work).

Let us know if you make any headway...

Regards - Terence.