RC6: Enhancing SQLite Operations with a Unified Helper Class in RichClient6

[tmighty2]

Hi everyone!

I’ve been diving deep into the capabilities of RichClient6 and am continuously impressed by its potential for rapid application development. A huge shoutout to Olaf Schmidt for his dedication and hard work on this framework!

I'd like to discuss a potential enhancement that could simplify and streamline SQLite operations within RC6. I’ve developed a helper class that not only eases the creation of INSERT and UPDATE commands but also seamlessly extends to SELECT operations, potentially merging the functionalities of cCommand and cSelectCommand.

Here’s how the class looks for simplified INSERT operations:

Dim nCmdInsAlt As cSqliteCmd
Set nCmdInsAlt = New cSqliteCmd

With nCmdInsAlt
    .Connection = m_Cn
    .TableName = "filesinuse"
    .SetCommand "INSERT INTO filesinuse"
    .SetInt32 "productid", g_cOEM.Product
    .SetInt32 "setuptype", uSetupType
    .SetText "line", uLine
    .SetInt32 "section", uSection
    .SetInt32 "usetype", eTWSUseType.eTWSUseType_InnoSetup
    .SetText "source", sSource
    .SetText "dest", sDest
    .SetText "destdir", sDestDir
    .SetText "filename", sFilename
    .SetText "checkstring", sCheck
    .Execute
End With

And here’s the extended utility for SELECT operations:

Dim nCmdSel As cSqliteCmd
Set nCmdSel = New cSqliteCmd

With nCmdSel
    .Connection = m_Cn
    .SetCommand "SELECT *, rowid FROM filesinuse"
    .SetInt32 "productid", g_cOEM.Product
    .SetInt32 "setuptype", uSetupType
    .SetInt32 "section", uSection
    .SetInt32 "usetype", eTWSUseType.eTWSUseType_InnoSetup
    .SetText "line", uLine
    .SetText "source", sSource
    .SetText "dest", sDest
    .SetText "destdir", sDestDir
    .SetText "filename", sFilename
    .SetText "checkstring", sCheck

    Set r = .ExecuteReader
End With

This class reduces the need to hardcode SQLite strings and could enhance code readability and maintenance.
Moreover, it could potentially unify the cCommand and cSelectCommand into a single class.

Thank you all for your insights!

[Zvoni]

*snort*
.... and people don't believe me, when i say, that, basically, i only have one single hardcoded SQL-Statement in my Frontend.....

[tmighty2]

Explain, please.

[Zvoni]

Explain, please.

In my Frontend i have a lot of numeric constants, and one single SQL-Statement:
"SELECT SQLStatement FROM tbl_SQLStatements WHERE ID=?"

The SQL-Statements themselves are stored in the Database in a table, the ID's corresponding with my constants.
Example
"SELECT FirstName, LastName, Address, ZIP, City FROM Clients WHERE ID=?"
"UPDATE Clients SET FirstName=?, LastName=? WHERE ID=?"
"INSERT INTO Clients (FirstName, LastName, Address, ZIP, City) VALUES(?,?,?,?,?)"

the steps are as follows
1) Set above first statement as the SQL-Text
2) Set the Parameter, for which "real" statement i'm intersted in using the constants
3) Fire it off. You get the "real" SQL-Statement back (Can be a SELECT, an UPDATE, INSERT, DELETE, whatever - see Examples)
4) Set THIS "real" SQL-Statement as the SQL-Text
5) Since i know where in my code i am, say in a routine which wants to insert a new record into a particular table, i know which and/or how many parameters i need
6) if neccessary (see step 5) set any Parameters
7) Fire
8) Enjoy the result

That way, i only need to pay attention to the parameters, and if i decide to switch to another DBMS i don't even have to recompile my frontend,
since anything DBMS-specific (say, SQL-Dialect-stuff --> the "classic" being SELECT TOP(10) vs. SELECT .... FROM .... LIMIT 10) is outside my Frontend.

Hell, i can even change the Statements themselves, as long as i keep the "Interface" of the statement the same
example being for a SELECT: "5 Output-Columns - Integer, Integer, Text, Text, Double" and "2 Input Params - Integer, Integer"

that even happened to me some months ago:
My boss wanted a small report-tool to show our Top10-Clients with name, Address and Gross-Weight (besides other columns) of the last 12 months
After a few weeks he came to me "Could you change it from Gross-Weight to Sales Turnover?"
Didn't even need 10 seconds to change it. Just changed the Statement in the Database.
Why? Because the "Interface" remained the same --> The "changed" Column was still of Datatype Double.
(And even if it weren't of the same Datatype, it wouldn't be a Problem in this case, since i wasn't doing any calculations here)

Even the "Column-Header" of the Grid for Displaying the result was changed from "Gross-Weight" to "Sales Turnover" within 10 seconds (since i pull such stuff from a Database, too)

EDIT: Amendment to the "don't need to recompile my Frontend"-Bit above: This is due to the fact, that i code in Lazarus/Freepascal, and any DataAccess-code is in a DLL, which i load dynmically (think "Plugin"-System).

In VB6 you would need to recompile, but you would only have to add the "new" DataAccess-Stuff, as in the Connection-Stuff to the new Database.
You wouldn't need to change a single line where you actually execute your SQL's.

Add your new Connection-Stuff to your vb6-code, recompile your vb6-code,
add the SQL-Statements to the new DB in its own SQL-Dialect (IMPORTANT: Its TableName MUST be the same as well as the ID's for the Statements),
and off you go

Everything said: It's one of the reasons, you'll never find a "SELECT * FROM ... " anywhere in my projects

[Zvoni]

In VB6 you would need to recompile, but you would only have to add the "new" DataAccess-Stuff, as in the Connection-Stuff to the new Database.
You wouldn't need to change a single line where you actually execute your SQL's.

After thinking about it, you could even manage this in vb6 without recompiling, by keeping connection-related stuff (connection-string, needs authentication etc.) in an INI-File or similiar