Hello.
At 14:32 2/2/2007 +0000, you wrote:
lReturnedRows is one of the function arguments, so that is fine.
I have made some progress though and that is that this problem only occurs
with this particular table, called SQL. It is a table that logs all the SQL
statements that run in my app. When I instead make this for example
sqlite_master there is no problem ever.
No idea though why this table would cause a problem. Could it be that
one of the items in that table is a reserved word?
CREATE TABLE 'SQL'
([STATEMENT_COUNT] INTEGER,
[DB] TEXT,
[QUERY_TIME] TEXT,
[QUERY_LENGTH] REAL,
[QUERY] TEXT)
Any other ideas what could be wrong with this table?
It is possible that the result set is too large to fit in the memory
constraints you have, so when one of the memory allocations in the
sqlite_get_table fails, the application crashes.
Can you run the same statement from the sqlite3 command line application
and see if it returns the expected results? and if it's very long?
In any case, I've worked a bit on the function, and here's the result:
VBSQL.h ---------------------------------
#include <windows.h>
#include <stdio.h>
#include <io.h>
#include <oleauto.h>
#include <wtypes.h>
#include "sqlite3.h"
SAFEARRAY * __stdcall sqlite_get_table(sqlite3 * , const char *, BSTR * ,
long*, VARIANT);
BSTR __stdcall sqlite_libversion(void);
int __stdcall sqlite_exec(sqlite3*, const char*, BSTR*);
VBSQL.c ---------------------------------
#include "vbsqlite.h"
#define MEMORY_ERROR "Memory allocation error"
BOOL FillVariantFromString(VARIANT* pVariant, const char* pString)
{
int nLen;
LPOLESTR pOLEString;
VariantClear(pVariant);
nLen = MultiByteToWideChar( CP_ACP, 0, pString, -1, NULL, 0);
if (nLen == 0)
return FALSE;
nLen++;
pOLEString = CoTaskMemAlloc(nLen * sizeof(WCHAR));
if (pOLEString == NULL)
return FALSE;
if (MultiByteToWideChar( CP_ACP, 0, pString, -1, pOLEString, nLen)
== 0)
{
CoTaskMemFree(pOLEString);
return FALSE;
}
pVariant->bstrVal = SysAllocString(pOLEString);
V_VT(pVariant) = VT_BSTR;
CoTaskMemFree(pOLEString);
return TRUE;
}
SAFEARRAY * __stdcall sqlite_get_table(
sqlite3 *db, /* The database on which the SQL executes */
const char *zSql, /* The SQL to be executed */
BSTR *ErrMsg, /* Write error messages here */
long* pNumberOfRows, /* Number of rows in the returned array (ALWAYS
without the headers!) */
VARIANT varIncludeHeaders /* TRUE to include headers, FALSE not to */
) /* Return the SAFEARRAY */
{
// Temp result fields
char **pSQL_Results;
char *pErrMessage= 0;
int nNumberOfColumns;
int nResult;
SAFEARRAY* pResult = NULL;
nResult = sqlite3_get_table(db, zSql, &pSQL_Results,
pNumberOfRows, &nNumberOfColumns, &pErrMessage);
if (nResult == SQLITE_OK)
{
SAFEARRAYBOUND SA_Bounds[2];
sqlite3_free(pErrMessage);
if (nNumberOfColumns == 0)
{
sqlite3_free_table(pSQL_Results);
// Return an empty array - to make sure nothing
happens:
SA_Bounds[0].cElements = 0;
SA_Bounds[0].lLbound = 0;
pResult = SafeArrayCreate(VT_VARIANT, 1, SA_Bounds);
if (pResult == NULL)
{
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know what this will
do. NEVER USE AN ARRAY when an error was returned!
}
}
else
{
//We have a resultset so transform this into a
SAFEARRAY
// Create SAFEARRAY
//SAFEARRAY FAR* resultp = NULL;
BSTR bstrTemporyStringHolder = NULL;
VARIANT tmpVariant;
BSTR bstr1 = NULL;
HRESULT hr;
LPOLESTR pTempWideDataHolder = NULL;
int intCurrentRow ; // Tempory counter for looping
long indices[] = {0,0};
VariantInit(&tmpVariant);
// Set up array bounds
SA_Bounds[0].cElements = *pNumberOfRows + 1;
SA_Bounds[0].lLbound = 0;
SA_Bounds[1].cElements = nNumberOfColumns;
SA_Bounds[1].lLbound = 0;
//Create array
pResult = SafeArrayCreate(VT_VARIANT, 2, SA_Bounds);
if (pResult == NULL)
{
*pNumberOfRows = 0;
sqlite3_free_table(pSQL_Results);
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know what this will
do. NEVER USE AN ARRAY when an error was returned!
}
// Put header, if so requested
if ((varIncludeHeaders.vt != VT_BOOL) ||
varIncludeHeaders.bVal)
{
// Start Place column headers in first row
for (indices[1]=0;
indices[1]<nNumberOfColumns; indices[1]++)
{
if
(!FillVariantFromString(&tmpVariant, pSQL_Results[indices[1]]))
{
*pNumberOfRows = 0;
SafeArrayDestroy(pResult);
sqlite3_free_table(pSQL_Results);
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know
what this will do. NEVER USE AN ARRAY when an error was returned!
}
// Safearray Column headers
hr = SafeArrayPutElement(pResult,
indices, &tmpVariant);
if (hr != S_OK)
{
*pNumberOfRows = 0;
SafeArrayDestroy(pResult);
sqlite3_free_table(pSQL_Results);
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know
what this will do. NEVER USE AN ARRAY when an error was returned!
}
}
VariantClear(&tmpVariant);
}
// Start Loop through array and fill SAFEARRAY
for (intCurrentRow=1;
intCurrentRow<=*pNumberOfRows; intCurrentRow++)
{
for (indices[1]=0;
indices[1]<nNumberOfColumns; indices[1]++)
{
const char* pString =
pSQL_Results[(intCurrentRow * nNumberOfColumns) + indices[1]];
if (pString == NULL)
continue;
if
(!FillVariantFromString(&tmpVariant, pSQL_Results[(intCurrentRow *
nNumberOfColumns) + indices[1]]))
{
*pNumberOfRows = 0;
SafeArrayDestroy(pResult);
sqlite3_free_table(pSQL_Results);
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know
what this will do. NEVER USE AN ARRAY when an error was returned!
}
hr = SafeArrayPutElement(pResult,
indices, &tmpVariant);
if (hr != S_OK)
{
*pNumberOfRows = 0;
SafeArrayDestroy(pResult);
sqlite3_free_table(pSQL_Results);
*ErrMsg =
SysAllocStringByteLen(MEMORY_ERROR,strlen(MEMORY_ERROR) );
return NULL; // Don't know
what this will do. NEVER USE AN ARRAY when an error was returned!
}
}
}
VariantClear(&tmpVariant);
}
// END Loop through array and fill SAFEARRAY
sqlite3_free_table(pSQL_Results);
}
else
{
*pNumberOfRows = 0;
*ErrMsg = SysAllocStringByteLen(pErrMessage,
strlen(pErrMessage) );
sqlite3_free(pErrMessage);
}
return pResult;
}
BSTR __stdcall sqlite_libversion()
{
return SysAllocStringByteLen(SQLITE_VERSION, strlen(SQLITE_VERSION) );
}
BSTR __stdcall sqlite3_libversion_number()
{
return SysAllocStringByteLen(SQLITE_VERSION, strlen(SQLITE_VERSION) );
}
int __stdcall sqlite_exec(
sqlite3* pDB,
const char *sql, /* SQL to be executed */
BSTR *errmsg /* Error msg written here */
)
{
char *pErrMessage = NULL;
int nResult = sqlite3_exec(pDB, sql, NULL, NULL, &pErrMessage);
if (nResult != SQLITE_OK)
{
*errmsg = SysAllocStringByteLen(pErrMessage,
strlen(pErrMessage) );
}
sqlite3_free(pErrMessage);
return nResult;
}
---------------------------
And the VB definitions for sqlite_get_table and sqlite_exec are:
Private Declare Function sqlite_get_table Lib "SQLite3VB.dll" (ByVal
DB_Handle As Long, ByVal SQLString As String, ByRef ErrStr As String, ByRef
rows As Long, ByVal bIncludeHeader As Boolean) As Variant()
Private Declare Function sqlite_exec Lib "SQLite3VB.dll" (ByVal DB_Handle
As Long, ByVal SQLString As String, ByRef ErrStr As String) As Long
Notes for sqlite_get_table:
1. Added rows which will return filled with the number of rows in the
result (without the headers!)
2. Added bIncludeHeader, which, if set to False, will not include the
column headers in the result
3. If you want, you can make it safer by returning a Long (error code), and
sending the array back as one of the variables (like rows). It's safer as
it won't be used upon error, unlike the current situation
Notes for sqlite_exec:
1. Use like you have used sqlite_get_table, but for situations when you
don't need a result set (like Update queries or Pragma commands).
2. The return value is one of the SQLITE_ constants (SQLITE_OK is 0)
Guy
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
