Has anyone stumbled across any sql auto complete textbox type controls for vb6 (open source or free preferred)
I am thinking of building one unless there is one available.
wish list:
- syntax highlight keywords and intellisense (scintinilla would be perfect)
- pass it a odbc connection object to extract tables and fields
- parse the sql as you go to get active table names and suggest available fields
I wrote something that does this all in .Net and display all info on a tables columns (type,sizing,nullability, if identitym defaul values, computed column spec, calculated field formula and maksing), Parent and child table references, Indexs (name, type, file group or partition function used, key columns, included columns and Unique propert), Any check constraints, any triggers, any procs or functions that touch the table, any views that use the table. Also a way to search at DB for a field, text in a procedure or function and other info on the server
As to what you might be implying here there are tools out there that do that via SSMS of course both free and paid
It could be done same way I did it... you query all the meta data table of the database you are working on. That is all I did for this... not saying it will be fast unless you do it up front (when you first connect to the DB) and cache the results in memory and use that for you auto complete function
Thanks Colin thats a very nice control. I will add it to my library and see if I can use some of it for this project
And since it uses ADO-Rs for the "lookup-lists"...
You can retrieve an Rs (with Table + Field-Infos) from most OleDB-Drivers via the ADODB.Connection.OpenSchema-method:
Set Rs = Cnn.OpenSchema(adSchemaColumns)
Then using the appropriate Filter- and Sort-Methods on that Rs, to restrict Field-Listings to a single Table.
I am sure this still has some behaviors to refine but ismostly there.
its a bit parsing heavy right now. There may be some clever ways to lighten up the load.
behaviors so far:
- syntax highlight sql keywords and quoted strings
- load tables and fields from database for intellisense/auto suggest
- auto suggest tables after keywords FROM and JOIN (including , list of tables)
- auto suggest field names after tableName dot
- do not trigger within quoted strings
- CTRL Space auto complete partial table name or tableName.fieldName
- CTRL Space at anytime will bring up table list
- try to trigger auto suggest list at proper times with proper context automatically, but suppress at inappropriate times
to do:
- bug in ocx: if last char of table name is numeric intellisense for fields will not fire
- detect utilized tables and if 1, auto suggest unqualified field names when sql context would be user specifying fields
these rules will be tricky...select fieldName before from table specified..cant auto suggest. fields after where or in update
should be doable in some manner..maybe only on ctrl space so it wasnt trigger happy and annoying. Have to study the behavior
of other sql auto suggest editors.
dependencies:
- open source Scintilla syntax highlight control written in C (300kb - 1mb depending on version/build used)
- open source scivb2.ocx wrapper to access it written in VB6 (420kb)
- https://github.com/dzzie/scivb2
- http://www.scintilla.org
that would be a good behavior to add as well I had not thought of it yet.
Its an interesting problem, how to keep/understand context of a programmatic statement so that a smart editor can auto suggest the proper things. Especially when any portion of the statement can edited at any time
And then to try to do it efficiently (I havent really tried to do this yet)
luckily we are only handling a single statement not a whole program and sql statements generally arent the most giant things so some inefficiencies in the parsing will go unnoticed. but sql does have a lot of nuances and features.
I have never studied the industrial grade answer to this level of parsing which is probably a full AST tree.
Then questions like when do you trigger a full reparse? how much do you handle on each keypress or on a delete. Do you need events for NewWord maybe more efficient? And then to what primitives do you need like isInQuotedString or getPrevKeyWord. In a multiline scenario maybe you can just reevaluate the line that changed for performance.
The scintilla control does offer very powerful advanced editor capabilities designed exactly to support these tasks. So at least we start from a solid base. While the OCX does not expose all of it since it is so huge, it is all accessible through windows messages.
So far I am about 6 hrs in to this project which includes some scintilla bug hunting/research. (I have spent considerable time inside the ocx and have been using it since 2014 though.)
To do it super proper would be a big project but would be a valuable component to have around. Then there is finding the right balance point on the time/value curve for level of need. No idea how far the project will go. The whole stack is open source so anyone can chip in
leofar thanks for the link I was not aware of any of those tools they will be good for ideas as well.
The problem with language parsers in editors is that these should understand broken syntax and recover. So this usually ends with some Error nodes in the AST. Also the problem of incremental reparsing is unique to these as normal compiler parsers don’t deal with reparsing or incremental parsing at all.
Most popular/robust editor incremental parsers are tree-sitter based parsers.
I added table alias support. not sure how far i will take this since my needs are not huge and it could quickly get complex.
I ended up creating a repo for it you can find here: https://github.com/dzzie/sql-autocomplete