SQLiteQuery("path", "query"

• Beta version, use with caution
  
• I have not yet checked whether different culture settings lead to conversion errors
  
• Only SELECT statements have been tested so far
  Limited testing on write access queries seems promising, so far
  
• Two DLL files need to be copied to the plugins folder, I intend to merge the assemblies in future
  Assemblies merged, plugin is now a single dll
  Complete folder provided, Extension Manager upload in progress
  
• Only 64 bit is currently supported, sorry. Working on 32 bit support
  Both 64 and 32 bit now supported (tested in 5935, 6081 and 6083 using WoW64)
  
• Since it is not possible to use "=" within a SMath string this has to be considered when designing a query. "IS" and is usually a suitable replacement. "IS NOT" serves as a replacement for "!="
  The boolean 'equal' (Ctrl + =) works very well (tested). Thanks Martin Kraska and Mike Kaganski for pointing out my error

• Alex.M - I have taken the liberty here to adapt the example and database form Alex' Excel I/O plugin to demonstrate the SQLite interface within a context that might be familiar to some users. I had to change some property names since SQLite ignores case, b and B have become b1 and b2 etc.
  
• Davide Carpi - The XlsxImportExport plugin demonstrated to me how to properly return results in form of a Matrix. Also thanks for the useful feedback
  
• Martin Kraska - I found the "SMath Studio Handbuch" to be a great starting point and reference
  
• Martin Kraska & Kay Graubmann - The Maxima plugin source code provided some valuable insights into SMath plugin development
  
• Mike Kaganski - His quick testing, excellent feedback and good ideas have substancially accellerated and improved the development of this plugin

• 08/09/16 - Made plugin available to download via extension manager, uploaded source code and example to SVN, fixed relative path as suggested by Davide, changed loading of linked SQLite assemblies for x86 and x64 to allow installation from extension manager for SMath below 8069
  
• 31/08/16 - Removed single argument SQLiteQuery due to inconsistencies with partial recalculation. Null entries in database return custom unit 'NULL and are now distinguishable from empty strings, simplified code. Fixed and improved the example file, added typetest table to example database
  
• 29/08/16 - Updated SQLiteQuery to use path as handle, removed SQLiteOpen and SQLite close, added automatic closing of database connections at SMath exit, typecasting bugfixes, refactoring and minor improvements, repackaged for proper plugin installation
  
• 27/08/16 - Merged assemblies, now the plugin only consists of a single dll. Correct SQLite interop is automatically loaded, 64bit and 32bit are now supported. (Tested with SMath 5935, 6081 and 6083 )
  
• 26/08/16 - Added support for multiple simultaneous database connections, returning results consistently as matrix now
  
• 25/08/16 - First release

• https://sqlite.org/about.html - SQLite overview
  
• https://www.sqlite.org/datatype3.html - More about SQLite type affinities
  
• http://www.tutorialspoint.com/sqlite/index.htm - Useful SQLite tutorial
  
• http://portableapps.com/apps/development/sqlite_database_browser_portable - Portable SQLite database file editor

1. Could you consider using a identifier (number or string) as a "handle" to open connection? For example,
   

   SQLiteOpen("connection1","path1"="open"
   SQLiteOpen("connection2","path2"="open"
   m:=SQLiteQuery("connection1","query"
   query2:=<some processing involving m>
   SQLiteQuery("connection2",query2)
   <some other processing with both connections 1 and 2>

   Or the SQLiteOpen could return the numeric handle on succcess, or an error with a description on failure.
   

2. Quote

   If no fields are returned by the database engine, the function will return the string "No Results"

   
   Imo, it would be better to return matrix(0,0) in this case. And please return a matrix in all cases, even on single return, thus eliminating the need for conditionals in the calculations. If one knows that only single return will come, this may be handled by simply using el(result,1).
   
3. It is possible to use "=" in SMath strings. You just need to prepate the string elsewhere.
   

Wrote

[size=8][b]Since it is not possible to use "=" within a SMath string this has to be considered when designing a query. "IS" and is usually a suitable replacement. "IS NOT" serves as a replacement for "!=" "a <= b" can be replaced with "(a < b ) or (a is b )"

Wrote

Could you consider using a identifier (number or string) as a "handle" to open connection?

Wrote

Imo, it would be better to return matrix(0,0) in this case. And please return a matrix in all cases, even on single return, thus eliminating the need for conditionals

Wrote

It is possible to use "=" in SMath strings.

Wrote

Actually you can have = in strings.

Wrote

For some reason, SMath Studio 0.98.6081.26558 doesn't start with the plugin, so I cannot test it. Does it handle partial recalculations? Doesn't closing in the end prevent partial recalcs? Is it possible to skip closing the connection altogether and thus skip overhead opening new connection on recalc? I.e. does it internally checks in SQLiteOpen if the connection is already open?

Wrote

You are quite right, closing the database connection in the end makes a full recalculation necessary.
Closing the database file is not necessary if the database is only read. At the latest when SMath Studio is closed the database connections are terminated. I am however not sure whether "SQLiteClose" can be safely ignored if write operations to the database have been performed.

Wrote

For some reason, SMath Studio 0.98.6081.26558 doesn't start with the plugin

Wrote

Ok, the plugin opens *if* it is placed into %ProgramFiles(x86)%\SMath Studio\Plugins. That's a wrong place for custom plugins.

On the other hand, if I put it into %appdata%\SMath\extensions\plugins\SQLite\1.0.6083.3, and put a config.ini in parent folder with "1.0.6083.3", then SMath crashes on start.

Wrote

Next question/feature request: a UI for creating DB and tables.

Wrote

But I see a problem with returning empty result sets:



Something's wrong with the approach I suggested (using matrix(0,n)). Maybe Andrey could suggest something here?

Wrote

Wrote

you may return instead SMath custom unit 'NULL.

Wrote

I see some fancy column names in the sample DB (like "h/tw" ); how do I query for them

Select `twdet/2` from Shapes;
Select 'twdet/2' from Shapes;

Wrote

I'm not very familiar with SQL, but this looks like it would be very useful! (...) AISC does make their tables available in spreadsheet format

Wrote

Mike Kaganski - His quick testing, excellent feedback and good ideas have substancially accellerated and improved the development of this plugin

Wrote

Either of the following will work:

...
Select 'twdet/2' from Shapes;

Wrote

Wrote

I'm not very familiar with SQL, but this looks like it would be very useful! (...) AISC does make their tables available in spreadsheet format

Thanks for your interest Jason. I'd quite happily convert that spreadsheet into an SQLite database if it helps your endeavours