Spatial Projects SQL (spatialSQL)
for MapInfo Professional

Embedded Custom Functions

SQL, Select, Update, Add Column and Insert statements can utilise custom functions to provide added functionality and performance to applications. spatialSQL uses these extensively. This summary is not exhaustive. In all cases not appearing in MapBasic documentation, significant testing is required to ensure behaviour is consistent and error free. A test program exercising some of the Select options is available here.

	Construct	Comments
1.	select * from tablea where CustFunction(aobj, arid, acol)	Single table query; function returns logical; accepts object rowid and col references;
2.	select * from tablea where CustFunction(aobj, arid, acol)	As above. CustFunction performs update of current or other rows in tablea or another table.
3.	Constants in SQL statement OK. Variables produce runtime/compile error	Add the variable as global and test within a custom function.
4.	select CustFunction(aobj, arid, acol) from tablea	Apparently doesnt return a value to the result table although compiles and runs OK.
5.	add column tablea (col1) from tablea set to custfunction (aobj, arid, acol)	Works well but possibly lacks flexibility. Column derived from custom function is useful. See test program for example.
6.	insert into tablea (col1) Select CustFunction(aobj, arid, acol) from tableb	Not tested.
7.	select * from tablea, tableb where aobj intersects bobj and CustFunction(aobj, arid, acol, bobj, brid, bcol)	Table join must be satisfied independently of custom function. Only join record set is passed to custom function! This basic construct leads to very powerful functionality.
8.	select * from tablea, tableb where aobj intersects bobj and CustFunction(aobj, arid, acol, bobj, brid, bcol)	CustFunction uses update statement to update tablea or another selection. Both join objects available to function. Basic performance penalty of update approx 50% of select performance (but obviously dependent on function content). This method has potential of disrupting select and so must be used with caution, but is very hard to resist! See test program for details on usage.
9.	update tablea set cola=CustFunction(aobj, arid, acol)	Traditional use of custom function. Very fast. Not as flexible as Select.
	.......	If you can offer other useful examples please email to mapinfo-l@lists.directionsmag.com for public discussion.