Thanks for that, will have a look at the altered C code, but not sure
yet if I can compile. Just have never done it yet.
As I posted earlier I think I have found the trouble.
Due to a simple typo I enclosed the queries in that table SQL in
double quotes, where it should have been single quotes.
At work now, but will see if I can test in a bit, but I think that is it.
> 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).
I think this was already in Todd's dll.
RBS
> 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]
> -----------------------------------------------------------------------------
>
>
>
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
