Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
Error in SQLScript.. |
Thu, May 31 2018 10:03 PM | Permanent Link |
Ian Branch | Hi Team,
Playing with TEDBScript. Have the following code: {code} DBScript.SQL.Clear; DBScript.SQL.Add('SCRIPT () '); DBScript.SQL.Add('BEGIN'); DBScript.SQL.Add('Declare Stmt STATEMENT;'); DBScript.SQL.Add('PREPARE Stmt FROM '); DBScript.SQL.Add('''INSERT INTO AJobTickets'); DBScript.SQL.Add('SELECT * FROM JobTickets'); DBScript.SQL.Add('WHERE JobStatus = ''CA'' or ((JobStatus = ''CO'') and (Paid = True) and (date_out < DATE ''2018-05-01''))'';'); DBScript.SQL.Add('EXECUTE Stmt;'); DBScript.SQL.Add('END'); {code} When executed it keeps falling over at line 7 column 20 complaining that it expected ; but instead found (CA). Thoughts/Suggestions? Regards & TIA, Ian |
Fri, Jun 1 2018 3:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
Thought 1: Since I can't spot anything obvious I suspect its down to the quotes. I'll have a test later on Suggestion 1: Type the script into EDBManager and see if its OK there - just Prepare should tell you. If it is cut'n'paste and add the extra single quotes Suggestion 2: If you're doing it this way so you can change the date use parameters Personal Preference 1: I'd use EXECUTE IMMEDIATE - I understand it better Personal Preference 2: I'd build the sql into a stringlist first and assign that to the sql property, or better use something like nlhStringBank (basically store a stringlist in the dfm) to just type it in Personal Preference 3: type the statement into a script component or use a stored procedure Roy Lambert CRIPT(IN ProjectID INTEGER, SiteID INTEGER, ContactID INTEGER, CompanyID INTEGER, IgnoreSite BOOLEAN, IgnoreCompany BOOLEAN) BEGIN DECLARE Cmnd VARCHAR; SET Cmnd = 'DELETE FROM Calls WHERE _fkProjects = '+ CAST(ProjectID AS VARCHAR) +' AND _fkContacts = ' + CAST(ContactID AS VARCHAR); IF NOT IgnoreCompany THEN SET Cmnd = Cmnd + ' AND _fkCompanies = '+ CAST(CompanyID AS VARCHAR); END IF; IF NOT IgnoreSite THEN SET Cmnd = Cmnd + ' AND _fkSites = '+CAST(SiteID AS VARCHAR); END IF; EXECUTE IMMEDIATE Cmnd; END |
Fri, Jun 1 2018 4:46 AM | Permanent Link |
Ian Branch | Hi Roy,
Thanks for looking. RE Suggestion 1. - Yes already tried in EDBMgr and works fine. RE Suggestion 2. - That was the plan. RE PP1. - Haven't looked at it yet. Will have a play. RE PP2. - OK, I'm game, where does nlhStringBank come from? RE PP3. - Just learning about this SCRIPT stuff. Regards, Ian |
Fri, Jun 1 2018 5:08 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
>RE Suggestion 1. - Yes already tried in EDBMgr and works fine. OK so must have been the cut'n'paste and add extra quotes that got you. >RE PP2. - OK, I'm game, where does nlhStringBank come from? Should be in with the rest of my components in the binaries. If not there's lots of similar stuff on Torry >RE PP3. - Just learning about this SCRIPT stuff. and SQL as well - oh you're gonna have fun Roy But just cos you're lazy .... {code} unit nlhMisc; {$D-} interface uses Windows, SysUtils, Classes; type TnlhStringBank = class(TComponent) private FItems: TStringList; function GetString(Index: integer): string; procedure SetString(Index: integer; const Value: string); procedure SetItems(Value: TStringList); function GetCount: integer; function GetAsText: string; procedure SetAsText(const Value: string); public property Strings[Index: Integer]: string read GetString write SetString; default; property AsText: string read GetAsText write SetAsText; constructor Create(AOwner: TComponent); override; destructor Destroy; override; published property Items: TStringList read FItems write SetItems; property Count: integer read GetCount; end; implementation constructor TnlhStringBank.Create(AOwner: TComponent); begin inherited; FItems := TStringList.Create; end; destructor TnlhStringBank.Destroy; begin FItems.Free; inherited; end; function TnlhStringBank.GetString(Index: integer): string; begin Result := FItems[Index]; end; procedure TnlhStringBank.SetString(Index: integer; const Value: string); begin FItems[Index] := Value; end; procedure TnlhStringBank.SetAsText(const Value: string); begin FItems.Text := Value; end; procedure TnlhStringBank.SetItems(Value: TStringList); begin FItems.Assign(Value); end; function TnlhStringBank.GetAsText: string; begin Result := FItems.Text; end; function TnlhStringBank.GetCount: integer; begin Result := FItems.Count; end; end. {code} |
Mon, Jun 4 2018 2:46 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Ian,
<< Have the following code: {code} DBScript.SQL.Clear; DBScript.SQL.Add('SCRIPT () '); DBScript.SQL.Add('BEGIN'); DBScript.SQL.Add('Declare Stmt STATEMENT;'); DBScript.SQL.Add('PREPARE Stmt FROM '); DBScript.SQL.Add('''INSERT INTO AJobTickets'); DBScript.SQL.Add('SELECT * FROM JobTickets'); DBScript.SQL.Add('WHERE JobStatus = ''CA'' or ((JobStatus = ''CO'') and (Paid = True) and (date_out < DATE ''2018-05-01''))'';'); DBScript.SQL.Add('EXECUTE Stmt;'); DBScript.SQL.Add('END'); {code} When executed it keeps falling over at line 7 column 20 complaining that it expected ; but instead found (CA). >> Whenever this happens, look at the actual SQL text being sent to EDB using the debugger (DBScript.SQL.Text). Most of the time the issue will be with the quotes, etc. Tim Young Elevate Software www.elevatesoft.com |
Tue, Jun 5 2018 5:46 AM | Permanent Link |
Adam Brett Orixa Systems | Ian
I _think_ you are not doubling up your single quotes enough. You have: DBScript.SQL.Add('WHERE JobStatus = ''CA'' or ((JobStatus = ''CO'') and (Paid = True) and (date_out < DATE I think this should actually be: DBScript.SQL.Add('WHERE JobStatus = ''''CA'''' or ((JobStatus = ''''CO'''') and (Paid = True) and (date_out < DATE The reason being that: 1. You are writing in Delphi so IT will "undouble" single quotes as it compiles. 2. Once the script is passed to the DBScript IT will ALSO "undouble" the single quote, so the '' before CA in your code is read as the END of the String. You must re-double it for the Script to read it as an internal single quote. -- Single quotes with scripts where there is a statement within the script are pretty hellish. Especially once you are trying to include single-quotes as an actual part of the "WHERE" !! To help with this, one of the things I always do when writing SQL in Delphi to pass to EDB is to add plenty of padding. This helps me with readability. You have written: DBScript.SQL.Add('SCRIPT () '); DBScript.SQL.Add('BEGIN'); DBScript.SQL.Add('Declare Stmt STATEMENT;'); I would tend to write: {code} var aScript: String begin aScript:= ' SCRIPT () ' + ' BEGIN ' + ' DECLARE Stmt STATEMENT; ' + //etc. DBScript.SQL.Text:= aScript; {code} Just having the main script in "open" space on the screen, and having the end-line single quotes separated from the main script makes things more readable. The primary issue is coping with single quotes and doubling single quotes within a string in order to pass them as single quotes in the script. The typical example is in your code: DBScript.SQL.Add('''INSERT INTO AJobTickets'); You already have 3 single quotes in a line. In my style of writing of this it would appear: ' ''INSERT INTO AJobTickets ' Mine is not massively clearer ... but a bit more readable. The real problem is that this doubling up of single quotes can get pretty massive. Now I usually place all my scripts in a Resources data-table. This means I can write them as "pure" SQL (with no doubling up of single quotes), it also means I can run all the scripts in Resources for testing, and check that they run. |
Tue, Jun 5 2018 5:24 PM | Permanent Link |
Ian Branch | Hi Adam,
Thanks for your input. I will re-check the script/code. I do like the way you redid the script to a string. You are correct, it does make it easier to read. Should have thought of that. My excuse is that I am still learning all this SQL/script stuff. Regards, Ian |
Wed, Jun 6 2018 2:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Adam
>Now I usually place all my scripts in a Resources data-table. This means I can write them as "pure" SQL (with no doubling up of single quotes), it also means I can run all the scripts in Resources for testing, and check that they run. As I posted before I use an nlhStringBank - same logic different tools. The other thing I often do is use QuotedStr to build the sqllines rather than trying to sort out the doubling myself. Roy |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |