CWindow Release Candidate 30

[José Roca]

New function and new methods.

Code Select Expand


' ========================================================================================
' Takes a null terminated wide string as input, and returns a pointer to a new wide string
' allocated with CoTaskMemAlloc. Free the returned string with CoTaskMemFree.
' Note: This is useful when we need to pass a pointer to a null terminated wide string to a
' function or method that will release it. If we pass a WSTRING it will GPF.
' If the length of the input string is 0, CoTaskMemAlloc allocates a zero-length item and
' returns a valid pointer to that item. If there is insufficient memory available,
' CoTaskMemAlloc returns NULL.
' ========================================================================================
PRIVATE FUNCTION AfxWstrAlloc (BYREF wszStr AS WSTRING) AS WSTRING PTR
   DIM nLen AS LONG = LEN(wszStr) * 2
   DIM pwchar AS WSTRING PTR
   pwchar = CoTaskMemAlloc(nLen)
   IF pwchar = NULL THEN RETURN NULL
   IF nLen THEN memcpy pwchar, VARPTR(wszStr), nLen
   IF nLen = 0 THEN *pwchar = CHR(0)
   RETURN pwchar
END FUNCTION
' ========================================================================================


Code Select Expand


' =====================================================================================
' Returns the contents of the CWSTR as a WSTRING allocated with CoTaskMemAlloc.
' Free the returned string later with CoTaskMemFree.
' Note: This is useful when we need to pass a pointer to a null terminated wide string to a
' function or method that will release it. If we pass a WSTRING it will GPF.
' If the length of the input string is 0, CoTaskMemAlloc allocates a zero-length item and
' returns a valid pointer to that item. If there is insufficient memory available,
' CoTaskMemAlloc returns NULL.
' =====================================================================================
PRIVATE FUNCTION CWStr.wchar () AS WSTRING PTR
   DIM pwchar AS WSTRING PTR
   pwchar = CoTaskMemAlloc(m_BufferLen)
   IF pwchar = NULL THEN RETURN NULL
   IF m_BufferLen THEN memcpy pwchar, m_pBuffer, m_BufferLen
   IF m_BufferLen = 0 THEN *pwchar = CHR(0)
   RETURN pwchar
END FUNCTION
' =====================================================================================


Code Select Expand


' =====================================================================================
' Returns the contents of the CWSTR as a WSTRING allocated with CoTaskMemAlloc.
' Free the returned string later with CoTaskMemFree.
' Note: This is useful when we need to pass a pointer to a null terminated wide string to a
' function or method that will release it. If we pass a WSTRING it will GPF.
' If the length of the input string is 0, CoTaskMemAlloc allocates a zero-length item and
' returns a valid pointer to that item. If there is insufficient memory available,
' CoTaskMemAlloc returns NULL.
' =====================================================================================
PRIVATE FUNCTION CBStr.wchar () AS WSTRING PTR
   DIM pwchar AS WSTRING PTR
   DIM nLen AS LONG = SysStringLen(m_bstr) * 2
   pwchar = CoTaskMemAlloc(nLen)
   IF pwchar = NULL THEN RETURN NULL
   IF nLen THEN memcpy pwchar, m_bstr, nLen
   IF nLen = 0 THEN *pwchar = CHR(0)
   RETURN pwchar
END FUNCTION
' =====================================================================================


[José Roca]

2 Aug 2017: Modified some declares in Afx_Sapi.bi
2 Aug 2017: Modified the TypeLib Browser to generate AFX_ prefixes for the abstract interfaces. Still more work to do because there are not abstract interface declarations in the FB includes.

The TypeLib Browser has two options: VBAuto and VTable, switchable clicking a toggle toolbar button. The VBAuto option generates interface declarations like the ones found in the Free Basic headers. The VTable option generates declarations using ABSTRACT methods.

[José Roca]

I'm going to add this wrapper function just for convenience:

Code Select Expand


' ========================================================================================
' Parameter:
' - pwszFileSpec: The full path and name of the file to delete.
' Return value:
'   If the function succeeds, the return value is nonzero.
'   If the function fails, the return value is zero (0). To get extended error information, call GetLastError.
' Remarks:
'   If an application attempts to delete a file that does not exist, this function fails
'   with ERROR_FILE_NOT_FOUND. If the file is a read-only file, the function fails with
'   ERROR_ACCESS_DENIED.
' ========================================================================================
PRIVATE FUNCTION AfxDeleteFile (BYVAL pwszFileSpec AS WSTRING PTR) AS LONG
   IF pwszFileSpec = NULL THEN EXIT FUNCTION
   FUNCTION = DeleteFileW(pwszFileSpec)
END FUNCTION
' ========================================================================================


[José Roca]

I'm working in a class on top of SQLite that works with Windows unicode, instead of the UTF8 encoding. The infamous Exec method, inefficient and non-unicode aware, will be limited in the class to execute SQL statements that don't return a result set, such CREATE, INSERT and UPDATE. In the class, is just a wrapper for Prepare and Step. To execute queries that return results, you will use Prepare, parsing the results with GetRow (I'm not using Step because it is a reserved Free Basic keyword).

I have the most important methods already working. Now, it is a matter of adding the bind, column, blob and backup methods.

It consists of a base class, CSQlite, that loads the sqlite3.dll, and provides general purpose methods (using it directly is optional). The other classes inherit from it.

An small test:

Code Select Expand


'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
'DIM pSql AS CSQLite = "sqlite3_64.dll"
'print pSql.m_hLib

' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Insert rows
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"

' // Prepare a query
DIM pStmt AS CSqliteStmt = pDbc.Prepare("SELECT * FROM t")
PRINT "Column count: ", pStmt.ColumnCount
' // Read the column names and values
DO
   ' // Fetch rows of the result set
   IF pStmt.GetRow = SQLITE_DONE THEN EXIT DO
   ' // Read the columns and values
   FOR i AS LONG = 0 TO pStmt.ColumnCount- 1
      PRINT pStmt.ColumnName(i)
      PRINT pStmt.ColumnText(i)
   NEXT
LOOP

PRINT
PRINT "Press any key..."
SLEEP


[Paul Squires]

Awesome Jose! Love anything sqlite related.
I write mostly business applications and I use sqlite as the database backend for the vast majority of them.

[José Roca]

I always have find annoying to have to use UTF-8 with Windows. Therefore, this version will work with UTF-16. Internally, SQLite will work with UTF-8, but you should not notice it.

[José Roca]

File reuploaded. Includes a new class, CSQLite3, a thin wrapper on top of SQLite3.dll.

Basic steps:

Code Select Expand


' Basic steps
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
'DIM pSql AS CSQLite = "sqlite3_64.dll"
'print pSql.m_hLib

' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Insert rows
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fruit')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"
IF pDbc.Exec("INSERT INTO t (xyz) VALUES ('fish')") <> SQLITE_DONE THEN AfxMsg "INSERT failed"

' // Prepare a query
DIM pStmt AS CSqliteStmt = pDbc.Prepare("SELECT * FROM t")
PRINT "Column count: ", pStmt.ColumnCount
' // Read the column names and values
DO
   ' // Fetch rows of the result set
   IF pStmt.GetRow = SQLITE_DONE THEN EXIT DO
   ' // Read the columns and values
   FOR i AS LONG = 0 TO pStmt.ColumnCount- 1
      ' // Get the value using the number of column...
      PRINT pStmt.ColumnName(i)
      PRINT pStmt.ColumnText(i)
      ' // ...or using the column name
      PRINT pStmt.ColumnText("xyz")
   NEXT
LOOP

PRINT
PRINT "Press any key..."
SLEEP


Binding:

Code Select Expand


' Binding
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
'DIM pSql AS CSQLite = "sqlite3_64.dll"
'print pSql.m_hLib

' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "Test.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId

' // Prepare a query
pStmt.hStmt = pDbc.Prepare("SELECT * FROM t")
' // Read the value
pStmt.GetRow
PRINT pStmt.ColumnText("xyz")

PRINT
PRINT "Press any key..."
SLEEP


Memory database:

Code Select Expand


' Binding
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
'DIM pSql AS CSQLite = "sqlite3_64.dll"
'print pSql.m_hLib

' // Create a new database in memory
' // I'm deleting and recreating the database for testing purposes
DIM pDbc AS CSQLiteDb = ":memory:"

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz text)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the text
pStmt.BindText(1, "fruit")
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId

' // Prepare a query
pStmt.hStmt = pDbc.Prepare("SELECT * FROM t")
' // Read the value
pStmt.GetRow
PRINT pStmt.ColumnText("xyz")

PRINT
PRINT "Press any key..."
SLEEP


Blob:

Code Select Expand


' Blob
'#CONSOLE ON
#define UNICODE
#INCLUDE ONCE "Afx/AfxWin.inc"
#INCLUDE ONCE "Afx/CSQLite3.inc"
USING Afx

' // Optional: Specify the DLL path and/or name
' // This allows to use a DLL with a different name that sqlite3.dll,
' // located anywhere, avoiding the neeed to have multiple copies of the same dll.
'DIM pSql AS CSQLite = "sqlite3_64.dll"
'print pSql.m_hLib

' // Create a new database
' // I'm deleting and recreating the database for testing purposes
DIM cwsDbName AS CWSTR = AfxGetExePathName & "TestBlob.sdb"
IF AfxFileExists(cwsDbName) THEN AfxDeleteFile(cwsDbName)
DIM pDbc AS CSQLiteDb = cwsDbName

' // Create a table
IF pDbc.Exec("CREATE TABLE t (xyz blob)") <> SQLITE_DONE THEN
   AfxMsg "Unable to create the table"
   END
END IF

' // Prepare the statement
DIM sql AS CWSTR = "INSERT INTO t (xyz) VALUES (?)"
DIM pStmt AS CSqliteStmt = pDbc.Prepare(sql)
' // Bind the blob
DIM fakeBlob AS STRING
fakeBlob = STRING(500, "A")
pStmt.BindBlob(1, STRPTR(fakeBlob), 500, SQLITE_TRANSIENT)
' // Execute the prepared statement
pStmt.Step_
PRINT "Row id was", pDbc.LastInsertRowId

' // Open the blob
DIM pBlob AS CSQLiteBlob = pDbc.OpenBlob("main", "t", "xyz", 1)
DIM nBlobBytes AS LONG = pBlob.Bytes
PRINT "Blob bytes: ", nBlobBytes
' // Read the blob
DIM strBlob AS STRING
strBlob = STRING(nBlobBytes, CHR(0))
pBlob.Read(STRPTR(strBlob), nBlobBytes)
PRINT strBlob

PRINT
PRINT "Press any key..."
SLEEP


[José Roca]

I'm going to deprecate some code that has become obsolete. These are the CWstrArray, CWStrDic and CWmiCli classes. The functions AfxStrSplit and AfxStrJoin will be modified to use CSafeArray instead of CWstrArray. Guess that nobody is using them, so it will not break existing code.

I wrote CWstrArray and CWstrDic when I temporarily stopped adding COM support to the framework. The new CSafeArray and CDicObj classes can work with any data type and are a bit more efficient that the old ones because don't need to make internal conversions of CWSTR to BSTR.

I don't think that anybody can have objections, but just in case...

[Richard Kelly]

File reuploaded. Includes a new class, CSQLite3, a thin wrapper on top of SQLite3.dll.

So, I took a look with the intention of porting my SQLite client/server over to this class. The only issue is that to use it, I would need an instance of it for every connection managed since the DB handle is kept internally.

The code in question is:

Code Select Expand



' ===========================================================================================
' Gets the database handle
' ===========================================================================================
PRIVATE PROPERTY CSQLiteDb.hDbc () AS sqlite3 PTR
   PROPERTY = m_hDbc
END PROPERTY
' ===========================================================================================

' ===========================================================================================
' Sets the database handle
' ===========================================================================================
PRIVATE PROPERTY CSQLiteDb.hDbc (BYVAL pDbc AS sqlite3 PTR)
   this.CloseDb   ' // Close the database
   m_hDbc = pDbc
END PROPERTY


It's the call to CloseDb that is causing me to pause.

If you don't mind, I'll look things over more carefully and see if there is an easy way to remove that limitation and incorporate some of the concepts I've designed in the client/server SQLite class like connection pools and results spooling.

Rick

[José Roca]

I don't mind at all. I only have written it in case I may need to use a lightweight database in the future for which ODBC or ADO will be overkill. I don't have a personal need for many of the classes that I have written. Just trying to make the framework more attractive.

[Richard Kelly]

I don't mind at all. I only have written it in case I may need to use a lightweight database in the future for which ODBC or ADO will be overkill. I don't have a personal need for many of the classes that I have written. Just trying to make the framework more attractive.

Thank you. I think the best way to support you and Paul is to build on what both of you are doing.

Rick