VASmalltalk: SQLite using Smalltalk defined functions …

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

This entry was posted in Smalltalk and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s