best practice on hard-coding db name? |
|
Author |
Message |
theriddler
|
Posted: Thu Jan 15 17:18:43 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
I've been working with a client who has hard-coded the database name
throughout the code, both client-side inline code (c#/asp.net) code and
plsql. In other words, "select blah from MYAPP.dbo.tablename..." instead
of "select blah from dbo.tablename". There is only one db in use.
This has created problems on 2-3 occasions when they wanted to run a
parallel system for test/demos, since the hard-coded name forces them to
the "MYAPP" database even if they connect string specifies "OTHERAPP" as
dbname.
My question is, what is the accepted best practice for whether to
include the dbname in object refs? I have been pushing for eliminating
"MYAPP." but getting a lot of resistance, I guess I need to charge more
before anyone will listen to me. Also, are there any acceptable
workarounds to get a parallel system running without eliminating the
dbnames, like aliasing or something?
SQL Server13
|
|
|
|
|
TIMA
|
Posted: Thu Jan 15 17:18:43 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
use a global variable for the dbname, and then just alter that in the one
spot,
sDBNAME= "mydatabase"
select blah from " & sDBNAME & ".dbo.tablename
> I've been working with a client who has hard-coded the database name
> throughout the code, both client-side inline code (c#/asp.net) code and
> plsql. In other words, "select blah from MYAPP.dbo.tablename..." instead
> of "select blah from dbo.tablename". There is only one db in use.
>
> This has created problems on 2-3 occasions when they wanted to run a
> parallel system for test/demos, since the hard-coded name forces them to
> the "MYAPP" database even if they connect string specifies "OTHERAPP" as
> dbname.
>
> My question is, what is the accepted best practice for whether to
> include the dbname in object refs? I have been pushing for eliminating
> "MYAPP." but getting a lot of resistance, I guess I need to charge more
> before anyone will listen to me. Also, are there any acceptable
> workarounds to get a parallel system running without eliminating the
> dbnames, like aliasing or something?
|
|
|
|
|
Grant
|
Posted: Thu Jan 15 20:36:14 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
Jeff,
We have a similar situation in our shop, but it revolves around the need for
securing our passwords for the production databases from our development
staff. Instead of hardcoding the database, user name and password and other
values associated with the connection string in the ASP itself, the
developers go out and decrypt a connection string from a file housed on the
server. The connection string has the server name, the user name, password,
and the default database housed within it. Thus all the developer needs to
do is go out to the file, parse it, and keep that information during the
instance invoked by the user. It works very well and gives us the side
benefit of being able to change databases and servers when necessary without
having to redesign the application. Hope this helps.
BTW, IMO it is not good coding practice to resovle your database name in the
code itself. There is a slight performance increase by doing this, but it
is not worth the pain if you have to go through and redesign your
application because you need to move to another box or database. The only
time I specify the naming of a database is in a VIEW when I need to jump to
another database on the same server and pick up information for my
application. This at least gives you some level of abstraction.
Grant
> I've been working with a client who has hard-coded the database name
> throughout the code, both client-side inline code (c#/asp.net) code and
> plsql. In other words, "select blah from MYAPP.dbo.tablename..." instead
> of "select blah from dbo.tablename". There is only one db in use.
>
> This has created problems on 2-3 occasions when they wanted to run a
> parallel system for test/demos, since the hard-coded name forces them to
> the "MYAPP" database even if they connect string specifies "OTHERAPP" as
> dbname.
>
> My question is, what is the accepted best practice for whether to
> include the dbname in object refs? I have been pushing for eliminating
> "MYAPP." but getting a lot of resistance, I guess I need to charge more
> before anyone will listen to me. Also, are there any acceptable
> workarounds to get a parallel system running without eliminating the
> dbnames, like aliasing or something?
|
|
|
|
|
James
|
Posted: Thu Jan 15 21:04:52 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
Grant,
> We have a similar situation in our shop, but it revolves around the need
for
> securing our passwords for the production databases from our development
> staff. Instead of hardcoding the database, user name and password and
other
> values associated with the connection string in the ASP itself, the
> developers go out and decrypt a connection string from a file housed on
the
> server.
I suppose this crack staff can't just look at the decrypted result?
Not good.
James Hokes
|
|
|
|
|
Grant
|
Posted: Thu Jan 15 21:24:25 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
No, because that don't get rights to the production environment. We have
two environments: development and production. The development IIS system
they have free reign on all folders, but they can't promote their own work
into production. Thus they can never see the file containing production
connection string, they just know the location within the production
environment (it's in a restricted location). Also, they do not the know the
decrypt key for the connection string.
> Grant,
>
> > We have a similar situation in our shop, but it revolves around the need
> for
> > securing our passwords for the production databases from our development
> > staff. Instead of hardcoding the database, user name and password and
> other
> > values associated with the connection string in the ASP itself, the
> > developers go out and decrypt a connection string from a file housed on
> the
> > server.
>
> I suppose this crack staff can't just look at the decrypted result?
> Not good.
>
> James Hokes
>
>
|
|
|
|
|
Jeff
|
Posted: Fri Jan 16 13:55:02 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
Thanks, Grant and others...
I wasn't aware of a performance gain from hard-coding the db name. Does
is only apply when the statement is parsed? Since we use mostly stored
procs, that shouldn't be a big difference if so.
Also, I'd still be willing to hear of any hacks for getting the code to
work with a differently-named database, in the event this has to be
shoehorned in. I was thinking of something like a db synonym for a
single login user that overrides the actual db name...not for production
work, of course.
|
|
|
|
|
James
|
Posted: Fri Jan 16 19:58:46 CST 2004 |
Top |
SQL Server Developer >> best practice on hard-coding db name?
Jeff,
Since it's a "shoehorn" job, you could always go old-school and us a DSN.
James Hokes
> Thanks, Grant and others...
>
> I wasn't aware of a performance gain from hard-coding the db name. Does
> is only apply when the statement is parsed? Since we use mostly stored
> procs, that shouldn't be a big difference if so.
>
> Also, I'd still be willing to hear of any hacks for getting the code to
> work with a differently-named database, in the event this has to be
> shoehorned in. I was thinking of something like a db synonym for a
> single login user that overrides the actual db name...not for production
> work, of course.
>
|
|
|
|
|
|
|