aehimself 398 Posted September 20, 2022 Hello, I'm using TurboPack SynEdit latest version, fresh from GitHub. I have an editor where the user can enter multiple SQL commands, like: SELECT * FROM MYTABLE SELECT * FROM OTHERTABLE o [...] Is there a way SynEdt can return the full block, but the current command only? So, if the cursor is staying in line 1 just return "SELECT * FROM MYTABLE" but at line 2 it would "SELECT * FROM OTHERTABLE o"? Keep in mind that commands can be split across several lines, so simply returning the current line is not a good solution. Summoning @pyscripter 🙂 Share this post Link to post
MarkShark 27 Posted September 20, 2022 Hey @aehimself SynEdit does have the SQLHighlighter which can give you some context information about the SQL, but it doesn't really have a full SQL script processor. Have you looked at the one in ZeosLib? I don't use it for my stuff as I had written one for Oracle's scripting language way back and then adopted it for the other databases with my newer products. Note that you will have to use some sort of statement terminator (which can be as simple as a semicolon or it can be super complex with Oracle's SQLPlus syntax or less complex with a standard "set terminator" type statement.) My tools are Golden (for Oracle) and GoldSqall (for Oracle, PostgresSQL, Firebird, MySQL, etc) and they all have to do exactly what you're doing. -Mark Ford Benthic Software Share this post Link to post
aehimself 398 Posted September 20, 2022 I was afraid of this so. I do use ZeosLib. The main purpose I'm interested about the current command is to parse table aliases (which Zeos is kind enough to do so) but I need to be able to provide the current command only - otherwise it only considers the first one. Even though if I can split up to separate commands, I somehow need to confirm if that is the command under the cursor... Share this post Link to post
ertank 27 Posted September 20, 2022 AFAIK, it is not common to have support for such parsing in text editor codes. BTW, what you are trying to do is not a very simple task. You probably need to build it yourself even if you find a source to use as a base. It takes time and a lot of corner case fixes for your database of choice. You should consider having per database parsing like one for SQLite another for SQL Server, etc. Also consider following examples. They are FirebirdSQL specific. WITH CTE AS (SELECT EXTRACT(WEEKDAY FROM ADATE) AS DAYOFWEEK, PAYMENTID, PAYMENTMETHODID, AVG(GENERALTOTALPRICE) GENERALTOTALPRICE FROM TRANSFERS WHERE STATIONID = 1 AND (PAYMENTMETHODID = 10 OR PAYMENTMETHODID = 20 OR PAYMENTMETHODID = 50 OR PAYMENTMETHODID = 60 OR PAYMENTMETHODID = 205) AND (TARIH >= '2022-01-01' AND TARIH <= '2022-01-31 23:59:59.999') GROUP BY 1, 2, 3) SELECT DAYOFWEEK, SUM(GENERALTOTALPRICE) FROM CTE GROUP BY 1 UNION SELECT 7, SUM(GENERALTOTALPRICE) FROM CTE GROUP BY 1 Another example; set term ^ ; execute block as begin if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'POS' and rf.RDB$FIELD_NAME = 'AGROUP')) then execute statement 'alter table pos add agroup varchar(100);'; if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'FUEL' and rf.RDB$FIELD_NAME = 'AGROUP')) then execute statement 'alter table fuel add agroup varchar(100);'; if (not exists(select 1 from RDB$RELATION_FIELDS rf where rf.RDB$RELATION_NAME = 'PAYMENT' and rf.RDB$FIELD_NAME = 'AGROUP')) then execute statement 'alter table payment add agroup varchar(100);'; end^ set term ; ^ I do not know your user base but all these are valid SQL statements for FirebirdSQL and can be placed one after another, might also include "COMMIT" statements between which is another thing to control in your code if you are to parse and execute each identified block as its own. I did not check ZeosLib codebase. If they have any "parsing" and "identifying" SQL statement blocks. That would be a good starting point. Alternative is to force your users to use simple statements with default terminator at the end. Such SQL statements are pretty easy to parse. Share this post Link to post
aehimself 398 Posted September 20, 2022 (edited) For anyone interested I solved this without writing an actual interpreter, it parses all select aliases. The backdraw is that it offers aliases to use in statements where there is no such alias therefore executing the statement will throw an exception. This annoyance is perfectly acceptable by me, so probably I'll just leave it as it is. It derives a new analyzer from Zeos's generic analyzer, and it looks like this: Unit uAEGenericSqlAnalyser; Interface Uses ZGenericSqlAnalyser, ZTokenizer, ZSelectSchema; Type TAEGenericSqlAnalyser = Class(TZGenericStatementAnalyser) public Function DefineAllSelectSchemaFromQuery(const Tokenizer: IZTokenizer; const SQL: string): TArray<IZSelectSchema>; End; Implementation Uses System.Contnrs, System.Classes; Function TAEGenericSqlAnalyser.DefineAllSelectSchemaFromQuery(Const Tokenizer: IZTokenizer; Const SQL: string): TArray<IZSelectSchema>; Var Tokens: TZTokenList; sections: TObjectList; ss: IZSelectSchema; FromTokens: TZTokenList; deleted: Boolean; Begin SetLength(Result, 0); Tokens := TokenizeQuery(Tokenizer, SQL, True); Sections := SplitSections(Tokens); Try Repeat ss := DefineSelectSchemaFromSections(Sections); If Assigned(ss) Then Begin SetLength(Result, Length(Result) + 1); Result[High(Result)] := ss; FromTokens := FindSectionTokens(Sections, 'FROM'); deleted := False; While (Sections.Count > 0) And Not deleted Do Begin deleted := TZStatementSection(sections[0]).Tokens = FromTokens; Sections.Delete(0); End; End; Until ss = nil; Finally Tokens.Free; Sections.Free; End; End; End. To use it: Procedure TForm1.GetTableName(Var inNameOrAlias: String); Var sa: TAEGenericSqlAnalyser; sch: IZSelectSchema; a: Integer; Begin sa := TAEGenericSqlAnalyser.Create; Try For sch In sa.DefineAllSelectSchemaFromQuery(SQLConnection.DbcConnection.GetTokenizer, SQLCommand) Do For a := 0 To sch.TableCount - 1 Do If sch.Tables[a].Table.ToLower = inNameOrAlias.ToLower Then Exit Else If sch.Tables[a].Alias.ToLower = inNameOrAlias.ToLower Then Begin inNameOrAlias := sch.Tables[a].Table; Exit; End; Finally FreeAndNil(sa); End; End; SQLConnection is a TZConnection object (can be replaced with TZTokenizer.Create if no connection is available), SQLCommand is the string containing all the statements. Simply call this method to "convert" an alias to a table name. It can be optimized but since it's already pretty quick, I didn't really bother. Yeah, extracting the current block would be more elegant. In 1 hours with minimal personal code, this is what I could achieve 🙂 Edited September 20, 2022 by aehimself Share this post Link to post