StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I do not use DBExpress but as far as I am aware, you can execute (either by Execute or ExecuteDirect) only one SQL command at a time. In other words you cannot put the whole script into the Execute method. </p> <p>This is not related to different command syntax used by FireBird and MS SQL (^ vs. GO). You have to understand the '^' sign or 'GO' command is not a "TSQL Command"! Both are specific command delimiters used by respective application used to execute commands against the SQL engines. Instead it is difference between "Firebird Manager" (or how it's called) and "SQL Query Profiler" (or "SQL Server Management Studio").</p> <p>The solution is to use some kind of parser, split the script into a list of single commands, and TSQLConnection.Execute these commands one-by-one.</p> <p>Something like this pseudocode:</p> <pre><code>var DelimiterPos: Integer; S: String; Command: String; begin S:= ScriptFile; // ScriptFile: String - your whole script While True Do begin DelimiterPos:= Pos('^', ScriptFile); if DelimiterPos = 0 then DelimiterPos:= Length(S); Command:= Copy(S, 1, DelimiterPos - 1); SQLConnection.Execute(Command); Delete(S, 1, DelimiterPos); if Lengh(S) = 0 Then Exit; end; end; </code></pre> <p>Please note that the sample above will work correctly only in cases that the '^' sign is not used anywhere in the script but a command separator.</p> <p>As a sidenote, I am sure there are some already built components that will do that for you (like TZSQLProcessor). I am not aware of any to point you to.</p> <p>Sidenote 2: I am pretty sure, that you'll have to modify your scripts to be fully compatible with MS SQL. Eventhough Firebird and MS SQL are both SQL servers there is always difference in DML/DDL syntax.</p> <p><strong>Edit:</strong></p> <ol> <li><p>If you can "rewrite" the SQL script into the code, you could use Jedi VCL jvStringHolder component. Put each separate command as one item (of type TStrings) in <a href="http://wiki.delphi-jedi.org/wiki/JVCL_Help%3aJvStringHolder.pas" rel="nofollow">jvStringHolder</a>.</p></li> <li><p>Creating the parser is rather complicated, but not undoable. With the inspiration from SynEdit i made these clases to exactly what you need: Load the script with TSQLScript.ParseScript, then iterate through Command[index: integer] property. The SQLLexer is not full SQL Lexer, but implements keywords separation with respec to comments, brackets, code folding etc. I've also added a special syntax into comments ($ sign in comment block) that helps me put titles into the script. This is full copy-paste from one of my projects. I'm not giving any more explanation, but I hope you can get the idea and make it running in your project.</p></li> </ol> <p>unit SQLParser;</p> <pre><code>interface type TTokenKind = (tkUknown, tkEOF, tkComment, tkKeyword, tkIdentifier, tkCommentParam, tkCommentParamValue, tkCommandEnd, tkCRLF); TBlockKind = (bkNone, bkLineComment, bkBlockComment); TSQLLexer = class private FBlockKind: TBlockKind; FParseString: String; FPosition: PChar; FTokenKind: TTokenKind; FTokenPosition: PChar; function GetToken: String; procedure Reset; procedure SetParseString(Value: String); protected procedure ReadComment; procedure ReadCommentParam; procedure ReadCommentParamValue; procedure ReadCRLF; procedure ReadIdentifier; procedure ReadSpace; public constructor Create(ParseString: String); function NextToken: TTokenKind; property Position: PChar read FPosition; property SQLText: String read FParseString write SetParseString; property Token: String read GetToken; property TokenKind: TTokenKind read FTokenKind; property TokenPosition: PChar read FTokenPosition; end; implementation uses SysUtils; { TSQLLexer } constructor TSQLLexer.Create(ParseString: string); begin inherited Create; FParseString:= ParseString; Reset; end; function TSQLLexer.GetToken; begin SetString(Result, FTokenPosition, FPosition - FTokenPosition); end; function TSQLLexer.NextToken: TTokenKind; begin case FBlockKind of bkLineComment, bkBlockComment: ReadComment; else case FPosition^ of #0: FTokenKind:= tkEOF; #1..#9, #11, #12, #14..#32: begin ReadSpace; NextToken; end; #10, #13: ReadCRLF; '-': if PChar(FPosition +1)^ = '-' then ReadComment else Inc(FPosition); '/': if PChar(FPosition +1)^ = '*' then ReadComment else Inc(FPosition); 'a'..'z', 'A'..'Z': ReadIdentifier; ';': begin FTokenPosition:= FPosition; Inc(FPosition); FTokenKind:= tkCommandEnd; end else Inc(FPosition); end; end; Result:= FTokenKind; end; procedure TSQLLexer.ReadComment; begin FTokenPosition:= FPosition; if not (FBlockKind in [bkLineComment, bkBlockComment]) then begin if FPosition^ = '/' then FBlockKind:= bkBlockComment else FBlockKind:= bkLineComment; Inc(FPosition, 2); end; case FPosition^ of '$': ReadCommentParam; ':': ReadCommentParamValue; else while not CharInSet(FPosition^, [#0, '$']) do begin if FBlockKind = bkBlockComment then begin if (FPosition^ = '*') And (PChar(FPosition + 1)^ = '/') then begin Inc(FPosition, 2); FBlockKind:= bkNone; Break; end; end else begin if CharInSet(Fposition^, [#10, #13]) then begin ReadCRLF; FBlockKind:= bkNone; Break; end; end; Inc(FPosition); end; FTokenKind:= tkComment; end; end; procedure TSQLLexer.ReadCommentParam; begin Inc(FPosition); ReadIdentifier; FTokenKind:= tkCommentParam; end; procedure TSQLLexer.ReadCommentParamValue; begin Inc(FPosition); ReadSpace; FTokenPosition:= FPosition; while not CharInSet(FPosition^, [#0, #10, #13]) do Inc(FPosition); FTokenKind:= tkCommentParamValue; end; procedure TSQLLexer.ReadCRLF; begin while CharInSet(FPosition^, [#10, #13]) do Inc(FPosition); FTokenKind:= tkCRLF; end; procedure TSQLLexer.ReadIdentifier; begin FTokenPosition:= FPosition; while CharInSet(FPosition^, ['a'..'z', 'A'..'Z', '_']) do Inc(FPosition); FTokenKind:= tkIdentifier; if Token = 'GO' then FTokenKind:= tkKeyword; end; procedure TSQLLexer.ReadSpace; begin while CharInSet(FPosition^, [#1..#9, #11, #12, #14..#32]) do Inc(FPosition); end; procedure TSQLLexer.Reset; begin FTokenPosition:= PChar(FParseString); FPosition:= FTokenPosition; FTokenKind:= tkUknown; FBlockKind:= bkNone; end; procedure TSQLLexer.SetParseString(Value: String); begin FParseString:= Value; Reset; end; end. </code></pre> <p>The parser:</p> <pre><code>type TScriptCommand = class private FCommandText: String; public constructor Create(ACommand: String); property CommandText: String read FCommandText write FCommandText; end; TSQLScript = class private FCommands: TStringList; function GetCount: Integer; function GetCommandList: TStrings; function GetCommand(index: Integer): TScriptCommand; protected procedure AddCommand(AName: String; ACommand: String); public Constructor Create; Destructor Destroy; override; procedure ParseScript(Script: TStrings); property Count: Integer read GetCount; property CommandList: TStrings read GetCommandList; property Command[index: integer]: TScriptCommand read GetCommand; end; { TSQLScriptCommand } constructor TScriptCommand.Create(ACommand: string); begin inherited Create; FCommandText:= ACommand; end; { TSQLSCript } constructor TSQLScript.Create; begin inherited; FCommands:= TStringList.Create(True); FCommands.Duplicates:= dupIgnore; FCommands.Sorted:= False; end; destructor TSQLScript.Destroy; begin FCommands.Free; inherited; end; procedure TSQLScript.AddCommand(AName, ACommand: String); var ScriptCommand: TScriptCommand; S: String; begin if AName = '' then S:= SUnnamedCommand else S:= AName; ScriptCommand:= TScriptCommand.Create(ACommand); FCommands.AddObject(S, ScriptCommand); end; function TSQLScript.GetCommand(index: Integer): TScriptCommand; begin Result:= TScriptCommand(FCommands.Objects[index]); end; function TSQLScript.GetCommandList: TStrings; begin Result:= FCommands; end; function TSQLScript.GetCount: Integer; begin Result:= FCommands.Count; end; procedure TSQLScript.ParseScript(Script: TStrings); var Title: String; Command: String; LastParam: String; LineParser: TSQLLexer; IsNewLine: Boolean; LastPos: PChar; procedure AppendCommand; var S: String; begin SetString(S, LastPos, LineParser.Position - LastPos); Command:= Command + S; LastPos:= LineParser.Position; end; procedure FinishCommand; begin if Command <> '' then AddCommand(Title, Command); Title:= ''; Command:= ''; LastPos:= LineParser.Position; if LastPos^ = ';' then Inc(LastPos); end; begin LineParser:= TSQLLexer.Create(Script.Text); try LastPos:= LineParser.Position; IsNewLine:= True; repeat LineParser.NextToken; case LineParser.TokenKind of tkComment: LastPos:= LineParser.Position; tkCommentParam: begin LastParam:= UpperCase(LineParser.Token); LastPos:= LineParser.Position; end; tkCommentParamValue: if LastParam = 'TITLE' then begin Title:= LineParser.Token; LastParam:= ''; LastPos:= LineParser.Position; end; tkKeyword: if (LineParser.Token = 'GO') and IsNewLine then FinishCommand else AppendCommand; tkEOF: FinishCommand; else AppendCommand; end; IsNewLine:= LineParser.TokenKind in [tkCRLF, tkCommandEnd]; until LineParser.TokenKind = tkEOF; finally LineParser.Free; end; end; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload