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

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

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

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}

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

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.

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

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