SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Runtime Code


Runtime Code

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: Administrators
Points: 64543 Visits: 19118
Comments posted to this topic are about the item Runtime Code

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Gary Varga
Gary Varga
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16743 Visits: 6534
It is an extremely interesting thought.

I bet the level of testing is much lower especially when considering that this area of research is undoubtedly less mature and therefore we will have less input into professional development.

When I have done similar work (i.e. generated code) I have not been as rigorous as I otherwise would have been as I have been less confident in ways to provide the same level of coverage.

I, for one, am certainly interested in any experience (successful or less so) in this area. Also not necessarily limited to SQL related features (SSIS, ETL etc.) as often cross pollination of ideas works wonders.

Gaz

-- Stop your grinnin' and drop your linen...they're everywhere!!!
joe.eager
joe.eager
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 467
I agree this is an interesting discussion topic.
Peter Ryan-300561
Peter Ryan-300561
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 113
Hi,

I am doing this using stored procedures interacting with SSIS 2008 R2. My testing is to run against the entire data set.

The code I am generating is a select statement executed in a MySQL database which creates an export file for loading via a SSIS file import task. The original five different MySQL stored procedures gave five different files and had five separate, but very similar, SSIS packages to load them into the same data destination. (A Data Warehouse.)

By writing a stored procedure to write the required MySQL select statement I was able to have only one SSIS package to load the data. This could then be encapsulated by a "Looper" package, which would call the new SSIS package. It uses expressions extensively to shape sources and destinations for the package.

Outside of ensuring that the package fails safely, and can be run against the entire initial data set, the code generation is based on fixed tables, not the data itself, so there is a finite number of possibilities.

There are still cases I have not expected and I've learned a great deal about the SSIS task inconsistencies. (The Sort data flow task defaults to case sensitive, but the partial option of the Lookup Transformation tast seems to be case in-sensitive.)

I'm sure that I don't always use SSIS correctly, I tend to used stored procedures to transform large data sets, but I'm reading and learning to do better.

Thanks,
Peter
Toby Harman
Toby Harman
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 668
Ultimately we will see more and more of this from the likes of LINQ and other ORM tools.

Admittedly these aren't particularly complex SQL most of the time, and we can mitigate some of this by enforcing data access throuhg stored procedures, but they're still generating SQL.
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7867 Visits: 3290
I had a lot of fun building software to auto-generate CRUD stored procedures for reference data. It takes a surprising amount of code to generate relatively simple code.

The problem was mainly in understanding the different use cases that apply when auto-generation comes into affect.

I got caught out by the length of the VARCHAR parameters/fields because a length of -1 means VARCHAR(MAX).

I also had some fun with the primary key fields and description fields because in the DB they follow the naming convention <tablename>ID and <tableName>Description where as in the stored procs they present as ID, Description. You have to remember to allow for a table where someone has already called a column ID or Description and also how you detect which column to call Description if you multiple VARCHAR fields.

Generally if you can autogenerate the code then you can autogenerate a fair number of the tests which will flush out these quite effectively.

Generally autogenerating code is taking tried and tested code and turning it into a template.

LinkedIn Profile

Newbie on www.simple-talk.com
joe.eager
joe.eager
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 467
Toby Harman (11/13/2013)
Ultimately we will see more and more of this from the likes of LINQ and other ORM tools.

Admittedly these aren't particularly complex SQL most of the time, and we can mitigate some of this by enforcing data access throuhg stored procedures, but they're still generating SQL.


I prefer forcing them through stored procedures as some of the SQL I've seen generated by ORM's be pretty horrible.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search