Parsing T-SQL

Question

Post reply

Parsing T-SQL

Viewing 13 posts - 16 through 27 (of 27 total)

You must be logged in to reply to this topic. Login to reply

doc_sewell SSC Eights! Points: 850 More actions · Answer 1

No, the company seeks code reuse. XML parsers can deal with: select * from t where tid= ?tid

Oracle's dbms_sql.parse: select * from t where tid = :tid

The question is can SQL Server parse a statement such as select * from t where tid = @tid

without having the variables declared and valued?

The application stores thousands of SQL statements daily in its application.xml file. Whenever a developer makes a code change (often daily) the new SQL is stored there. They currently use the above to evaluate the statements for xml and Oracle, now they want the same functionality for SQL Server. Without a SQL solution, they will have to manually parse through 3,500 statements.

Doc

doc_sewell SSC Eights! Points: 850 More actions · Answer 2

Lowell,

How would you check the return code after you exec the statement?

Doc

doc_sewell SSC Eights! Points: 850 More actions · Answer 3

SQL returns msg 137 must declare scalar variable. So the final and real issue is how do you get the parser to parse a statement and ignore the scalar variable?

Doc

doc_sewell SSC Eights! Points: 850 More actions · Answer 4

fmtonly does all but one thing: handle @variables. It checks the table names, column names, keyword spelling, everything but if there is an @ anywhere in the statement it errors out. Now if you replace the @variable with a 1, it works, no matter what the datatype that is being compared. So, how can you replace a whole word beginning with an @ in a string with a value of 1?

Doc

doc_sewell SSC Eights! Points: 850 More actions · Answer 5

So what I figured out is that if I replace the @ variables with the number 1, no matter what the column's datatype, the statement parses correctly. The "SET FMTONLY ON" works fine because it checks keyword spelling and the table and column names, that's all that we want. So what I have done so far is to write an OCCURS function (to get the number of @variable occurrences), an AT function (to get the starting position of each @). Then I proceed in a loop until I get to a space to give the entire word and use the REPLACE function.

This works.

Doc

ksmurale Newbie Points: 5 More actions · Answer 6

no need to parse the statement, use syscolumns and sysobjects table to check the column exist for the table

Sergiy SSC Guru Points: 110208 More actions · Answer 7

doc_sewell,

Did you ever consider a possibility of more than 1 user accessing your database at the same time?

Did you think that while 1st user validates the query 2nd one can change the schema by altering, creating or dropping objects and validation result aquired by 1st user would be irrelevant on execution time?

But if you're pretty sure it's not gonna happen and you'll never have 2 or more active users at the same time - why do you need SQL Server at all?

Replace your tables with files - it's easy to maintain, easy to check for existence, and main advantage - it's free!

_____________
Code for TallyGenerator

doc_sewell SSC Eights! Points: 850 More actions · Answer 8

I have it working fine now. This is an application being ported from an Oracle Forms application into .Net and SQL Server. My job is to port this app as fast as possible. Unfortunately, most of it is written in PL/SQL procs, functions, packages, and triggers. In this case, application DML (not DDL) is stored by the developers in an XML file. Also, users can create DML dynamically in the app as well. The procedure is to parse these statements and return any errors. I didn't write the app. As the DBA, I am just here to clean up the mess.

Doc

doc_sewell SSC Eights! Points: 850 More actions · Answer 9

Thanks, but the FMTONLY ON really does the trick.

Doc

Sergiy SSC Guru Points: 110208 More actions · Answer 10

doc_sewell (7/22/2008)
I have it working fine now. This is an application being ported from an Oracle Forms application into .Net and SQL Server. My job is to port this app as fast as possible. Unfortunately, most of it is written in PL/SQL procs, functions, packages, and triggers. In this case, application DML (not DDL) is stored by the developers in an XML file. Also, users can create DML dynamically in the app as well. The procedure is to parse these statements and return any errors. I didn't write the app. As the DBA, I am just here to clean up the mess.
Doc

doc_sewell, my point is - you don't really clean anything up with this approach.

Yes, it will work perfectly in DEV environment, where you're alone testing the code.

But in live system 2 or more users launching same script from XML will cause an error, no matter how hard you try to parse those sctripts prior to executing it.

On parsing time both will get green light, but seconds later they try to actually execute patsed script - and one will stand on the way of another.

Approach you've chosen just does not work. It's not C++ and not Java where you've parsed and compiled the code and you may be sure it will not fail.

It's database. Real multy-user environment. It's much more complex than you think.

Parsing done 2 statements ago is irrelevant to the statement you're trying to execute right now. There is a reason why some procedures got recompiled several times during execution.

Try to think about it, find that reason and you'll get my point.

Just wanna warn you - if you go on with this approach it's you who's gonna be blamed for the failure.

_____________
Code for TallyGenerator

MIBROWTITO Valued Member Points: 53 More actions · Answer 11

Hi

I'm trying to use those settings to test the validity of code objects like stored procedures, view, functions, etc... The idea is that I want to validate that all object's definitions by passing them thru the set noexec on.

however when I assemble the create statement inside the @sqlstring and run the execute I got the following error:

Msg 111, Level 15, State 1, Line 1

'CREATE VIEW' must be the first statement in a query batch.

It's not accepting the set noexec on as part of the batch.

Code:

declare @sqlstring nvarchar(max)

SELECT top 1 @sqlstring=sc.text

FROM syscomments sc

INNER JOIN sysobjects so

ON sc.id=so.id

INNER JOIN sysusers su

on so.uid=su.uid

inner join sys.schemas ss

on su.uid = ss.principal_id

WHERE so.xtype in ( 'V' )

set @sqlstring = 'set noexec on ' + @sqlstring

print @sqlstring

EXEC sp_executesql @sqlstring

Lowell SSC Guru Points: 323518 More actions · Answer 12

it's been a couple of years since this post was active; glad you found it.

since then, i put together a script to check for invalid objects; read this article, grab the script and see if it does what you are after:

note that this script is for SQL 2005/2008; you might need to change some of the underlying tables to make it SQL 2000 compatible.

Find Invalid SQL Server Objects

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

doc_sewell SSC Eights! Points: 850 More actions · Answer 13

No, you are missing the point. Apparently, this application does some sort of compile on statements saved to a table to validate them, albeit, before use. The question is moot.

Thanks,

Doc