Posted by Erland Sommarskog on 09/30/08 12:00

Mike Husler (Michael.P.Husler@noaa.gov) writes:
> We have a SQL Server database that can accept data from several
> different projects throughout our lab. Is it better to (1) let each
> developer create their own prepared SQL statements for inserts or to (2)
> hide the schema from them and have them call stored procedures or views
> to get the data into the database?
>
> So for example, in the 1st case, the developer's code (e.g. PERL w/
> DBI/DBD) would prepare/execute:
>
> INSERT INTO table (col1,col2,col3) VALUES (1,2,3)
>
> and in the 2nd case they would prepare/execute:
> EXEC sp_insertdata, @val1 = 1, @val = 2, @val3 = 3
>
> .. and the stored procedure does the insert.
>
> The main goal here is to isolate the developer from knowing the database
> schema. Only the 'database team' knows the schema and then any changes
> are done in one place (i.e. the view/stored procedure) instead of one or
> many external applications.
>
> In the 2nd case, the schema is invisible to him/her but if a column was
> added to 'table', they would have to change their stored procedure call
> all the same.

Isolating the developer from the schema is a noble goal, but less
easy to implement.

But just because you add a column to a table, the developer may not
need to have to change his call: you could use default values in
either cases. After all, if the developer should add a value, he has
to get it from somewhere.

As long as we only talking insertion, the method that possibly gives
the developer the least to think of terms of SQL is sql_insert. Which
is not in DBI/DBD as far as I know, but which is in Win32::SqlServer,
which is a module for accessing SQL Server but nothing else from Perl.
With sql_insert you only pass a hash of values, and sql_insert will
build the SQL statement from the hash and the table definition (which
is cached).

Then again... I did not understand what you meant with "the cadence can
be sub-second", but if there are lots of data coming, the it is not
a very good idea to run single INSERT statements, with or without
stored procedures.

In case you are interested, Win32::SqlServer is here:
http://www.sommarskog.se/mssqlperl/index.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]