Results 1 to 14 of 14

Thread: sql autocomplete control?

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    sql autocomplete control?

    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

  2. #2
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,451

    Re: sql autocomplete control?

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    Re: sql autocomplete control?

    nice, ultimately hoping for something that I can embed directly into my vb6 program like a user control or ocx

  4. #4
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,451

    Re: sql autocomplete control?

    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
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  5. #5
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,134

    Re: sql autocomplete control?

    I made one a while back that does some of what you want (I think):

    https://www.vbforums.com/showthread....Box&highlight=
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    Re: sql autocomplete control?

    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

  7. #7
    PowerPoster
    Join Date
    Jun 2013
    Posts
    5,146

    Re: sql autocomplete control?

    Quote Originally Posted by dz32 View Post
    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.

    Olaf

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    Re: sql autocomplete control?

    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

    Name:  screen-shot.jpg
Views: 166
Size:  15.2 KB
    Attached Files Attached Files
    Last edited by dz32; Nov 22nd, 2020 at 05:51 AM.

  9. #9
    Frenzied Member
    Join Date
    Apr 2012
    Posts
    1,134

    Re: sql autocomplete control?

    Aah, so that's what you meant by auto-complete. Guess I wasn't paying attention!
    If you don't know where you're going, any road will take you there...

    My VB6 love-children: Vee-Hive and Vee-Launcher

  10. #10
    New Member
    Join Date
    Nov 2020
    Posts
    11

    Re: sql autocomplete control?

    Quote Originally Posted by dz32 View Post
    I am sure this still has some behaviors to refine but ismostly there.
    There are a lot of tools (some with free-limited version).
    https://www.guru99.com/top-20-sql-management-tools.html

  11. #11
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,451

    Re: sql autocomplete control?

    What about table alias from tblPositions as tP will that list the field (it should of course)?
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  12. #12

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    Re: sql autocomplete control?

    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.
    Last edited by dz32; Nov 23rd, 2020 at 09:10 AM.

  13. #13
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Posts
    2,580

    Re: sql autocomplete control?

    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.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Jun 2015
    Posts
    400

    Re: sql autocomplete control?

    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

    something to tinker with anyway.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width