Sql Passthru Commands Explained in VFP TextMerge

[stanlyn]

Hi,

	TEXT to m.lcSqlStr textmerge NOSHOW PRETEXT 15
	Use [<<gcSqlDbName>>]

	IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BookID]') AND name = N'IsDeleted')
		DROP INDEX [IsDeleted] ON [dbo].[BookID] WITH ( ONLINE = OFF )
		CREATE INDEX IsDeleted ON bookid (is_deleted)

	IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BookID]') AND name = N'SystemId')
		DROP INDEX [SystemId] ON [dbo].[BookID] WITH ( ONLINE = OFF )
		CREATE INDEX SystemId ON BookID (system_id)
	ENDTEXT

In the 1st "if exists" condition above what is the actual flow?

To me, I read it as if the condition is true then both the drop and "create index" commands will run. And if it is false, then neither drop or create commands runs. I copied this from ssms and had to take all the go commands out for it to work in vfp.

I don't remember exactly, but I believe it had a go after the drop command and then one after the "create index" command. If this is correct, is it ok to assume that the sql go command stops or implicitly ends the "if exists" statement?

So, to get the intended functionality that Sql's "create index script generator" creates for Sql Server, I would need to change it to this to use in Vfp?:

	IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[BookID]') AND name = N'IsDeleted')
	DROP INDEX [IsDeleted] ON [dbo].[BookID] WITH ( ONLINE = OFF )
end if

CREATE INDEX IsDeleted ON bookid (is_deleted)

 

[Olaf Doschke]

1. IF EXISTS will be true, if the query following has >0 records
This code drops an index, if it exists, but the CREATE INDEX is not part of the IF statement anymore. So the logic is to prevent an error caused by an already existing index by first dropping it, if it exists and then always create the index newly. As in many languages the IF here executes the next one command only. What is done on condition the IF expression is true is not determined by indentation, if more than one command should be part of the IF branch, a bracket around it via BEGIN...END would be needed and would form a statement block. So only the drop runs, if an index already exists, the CREATE runs always. Makes sense, doesn't it?

So, no, you don't change this, it's still T-SQL executed by SQL Server and not VFP, so it's not VFP IF syntax, which applies. To VFP this is merely a string. VFP doesn't parse this, check this, compile this and by any means doesn't execute this, SQLExec is sending commands and let the remote backend execute that, not execute itself.

https://msdn.microsoft.com/en-us/library/ms182717.aspx

Unless a statement block is used, the IF or ELSE condition can affect the performance of only one Transact-SQL statement.

Reading language reference helps, really.

2. GO is the "commit batch of commands" command. That's not part of the T-SQL language, but a command specifically known to SSMS only, to split the batch of commands into two (or more) batches of commands sent to SQL Server. The proper way to redo this is split up the commands at GO lines and do several SQLExec() with the split partial scripts. Some things can only be done as first command and need an extra execution. Not all GOs in generated code have that reasoning, most scripts end in GO as that ensures you then can continue with anything else, so you can most ofen simply disregard and delete a GO, but it also won't hurt to reproduce this behaviour and yes, when you send GO to SQL Server you get an error, which tells you it assumes GO is a name of a stored proc not defined, as with anything you send not being a T-SQL command. That's a big hint on it not being T-SQL. It's also described in books online:

https://msdn.microsoft.com/en-US/library/ms188037.aspx,

quote: "GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor."

Another thing is, since you SQLStringConnect or SQLConnect to a certain DB, you don't need a USE database line.

Bye, Olaf.

 

[stanlyn]

Hi Olaf,


Not if all you know is VFP as all lines would run until you hit an else or an end... I spent 2 hours searching on-line (google) for this answer. I was looking for sql "if", sql "if exists", sql "scripting indexes" and much more... and all results had a matching else or "end if" construct.

So, thanks to you, now I understand how sqls if structure works... Thanks...

Now, regarding the go... What would I substitute it with to reproduce its similar intended behavior in the t-sql statement as I can see possible uses for it. I'll read your references when I get back from customer...

Stanley

 

[Olaf Doschke]

GO" has no substitute, it just is a "cut here", you split the script into multiple scripts and then do multiple SQLEXEC with the partial scripts. That's what's meant with "to split the batch of commands into two (or more) batches of commands sent to SQL Server."

In the simplest case you take the "risk" the GOs are not necessary, simply remove them and stay with one single script. One example of a command needing a separate sqlexec is CREATE VIEW. If you want to define more than one view in a script , the second CREATE VIEW statement will give you Incorrect syntax near the keyword 'CREATE'.

Bye, Olaf.

PS: for excample instead of [tt]SQLEXEC(h, lcSQLScript[/tt]) you'd rather do:

ALINES(laScripts,lcSQLScript,1+4,CHR(13)+CHR(10)+"GO") 
For Each lcScript in laScripts
   SQLExec(h, lcScript)
Endfor