Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Runtime Code Expand / Collapse
Author
Message
Posted Tuesday, November 12, 2013 8:55 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 4:29 PM
Points: 32,819, Visits: 14,965
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
Post #1513698
Posted Wednesday, November 13, 2013 2:08 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 4,882, Visits: 2,272
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!!!
Post #1513738
Posted Wednesday, November 13, 2013 6:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 41, Visits: 425
I agree this is an interesting discussion topic.


Post #1513861
Posted Wednesday, November 13, 2013 12:05 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 10:51 AM
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
Post #1514024
Posted Wednesday, November 13, 2013 7:33 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 7:46 PM
Points: 381, Visits: 535
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.
Post #1514133
Posted Thursday, November 14, 2013 4:26 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:22 AM
Points: 2,867, Visits: 1,709
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
Post #1514529
Posted Friday, November 15, 2013 6:00 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Yesterday @ 1:59 PM
Points: 41, Visits: 425
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.
Post #1514693
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse