In one of the last postings I showed how one can use instances of EsEntryPoint to let SQLite call VASmalltalk and execute Smalltalk code.
In this posting I want to show, that you can use the same technique to build Smalltalk based function, which can be used when executing SQL-statements within SQLite.
The normal C model of SQLite assumes, that each additional function has its own C-address entry point. I changed this model to have ONE entry point for all defined Smalltalk functions and in a selection block I decide what to call within Smalltalk. All functions are defined as blocks with three arguments: the database-connection object, an array of parameter and the sqlite function context.
In the following code we register a function, which should calculate the size of a string.
testSmalltalkFunction01 | functionHandler aMSKSQLiteQueryResult | dbConnection execSQL: 'create table TESTME2 (aColumn1 text);'. "we define the default function callback handler with its own entry point. The default handler does calculate which block has to be actually called within Smalltalk to get the result " functionHandler := dbConnection defaultFunctionCallbackHandler. "now we register a function for SQLite at the functionHandler. The new function is named 'VASmalltalkSize'. The block-parameter for put: is the definition of the function. " functionHandler callbackBlocks at: 'VASmalltalkSize' put: [ :dbCon :functionPArray :sqliteContext | "we expect to have a string as the only parameter - caution: we are in a call-in from SQLite" | result | "the result of this function is the size of the first argument. Actually one should check that the parameter is a string" result := functionPArray first size. "then we call SQLite via this API function to define the result" dbCon apiResultInt: sqliteContext value: result ]. "here we install the defined function handler within our database connection object" dbConnection functionCallbackHandler: functionHandler; "and we register it within SQLite ... after this step SQLite can use the VASmalltalk function" apiCreateFunctionNamed: 'VASmalltalkSize' argNumber: 1 handler: functionHandler. "then we test it and the result will be '5'" aMSKSQLiteQueryResult := dbConnection apiExecQuerySQL: 'select VASmalltalkSize("Hallo");'. dbConnection close .
The feature is available with version 3.7.2-11 at vastgoodies.com