473,320 Members | 1,978 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What is best practice for storing SQL code for an app

I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string
so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

Thanks
Mike
Nov 20 '05 #1
17 7981

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...
I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.


I think you have the best practice.

It used to be considered best practice to break queries over multiple lines
with string concatenation and line continuation. It was thought that this
made queries more "readable". This was crap. And the only excuse for it
was that there was a limit to the length of a line of VB code, so you
couldn't store the SQL on one line anyway. This also encouraged the bad
habit of binding parameters into your query through string concatenation.

In VB.NET lines can be as long as you want, and using parameter markers in
your queries is much easier. So SQL queries should be CONST's and written
all on one line. Like this

CONST sqlGetCustomer as string = "select * from customer where id = ?"

This seperates the SQL from the VB, and makes it easy to cut and paste the
SQL out and work on it in a database tool, then paste it back in.

It's also best practice to minimize the complexity of SQL stored in your
application.
Complicated joins and procedural logic (for databases that support that),
should be pushed down into the database as much as possible. For instance
if you have a query that joins 8 tables, create an Access QueryDef in the
database, and then do a simple select from that in your VB code. This
limits the amount of mixing of different languages in your source code.

Interleaving different languages is bad form, and makes both of the
languages hard to read and maintain. This is one big reason "old" ASP
sucked: one file would contain a tangled mess of VBScript, HTML, JavaScript,
and possibly SQL. And this is also why you shouldn't break your SQL queries
over multiple lines with string concatenation and line continuation.
David
Nov 20 '05 #2
Hi David

It has always been my practice to split SQL by keyword. For example,

SELECT Column1, Column2, ColumnN
FROM MyTable
WHERE ColumnX = SomeValue
ORDER BY Column2

Personally, I find it easier to read and maintain this way, and it has
nothing to do with limits on line length. Only if the string exceeds the
screen width would I continue on a new line (not with a continuation line
though), just to avoid having to scroll left and right in order to view the
whole string.

Charles
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:u3**************@TK2MSFTNGP11.phx.gbl...

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...
I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string
so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.


I think you have the best practice.

It used to be considered best practice to break queries over multiple

lines with string concatenation and line continuation. It was thought that this
made queries more "readable". This was crap. And the only excuse for it
was that there was a limit to the length of a line of VB code, so you
couldn't store the SQL on one line anyway. This also encouraged the bad
habit of binding parameters into your query through string concatenation.

In VB.NET lines can be as long as you want, and using parameter markers in
your queries is much easier. So SQL queries should be CONST's and written
all on one line. Like this

CONST sqlGetCustomer as string = "select * from customer where id = ?"

This seperates the SQL from the VB, and makes it easy to cut and paste the
SQL out and work on it in a database tool, then paste it back in.

It's also best practice to minimize the complexity of SQL stored in your
application.
Complicated joins and procedural logic (for databases that support that),
should be pushed down into the database as much as possible. For instance
if you have a query that joins 8 tables, create an Access QueryDef in the
database, and then do a simple select from that in your VB code. This
limits the amount of mixing of different languages in your source code.

Interleaving different languages is bad form, and makes both of the
languages hard to read and maintain. This is one big reason "old" ASP
sucked: one file would contain a tangled mess of VBScript, HTML, JavaScript, and possibly SQL. And this is also why you shouldn't break your SQL queries over multiple lines with string concatenation and line continuation.
David

Nov 20 '05 #3
Hi David,

I second Charle's method - split for readability.

Regards,
Fergus
Nov 20 '05 #4

"Charles Law" <bl**@thingummy.com> wrote in message
news:uq**************@TK2MSFTNGP11.phx.gbl...
Hi David

It has always been my practice to split SQL by keyword. For example,

SELECT Column1, Column2, ColumnN
FROM MyTable
WHERE ColumnX = SomeValue
ORDER BY Column2


I do this in SQLServer but in VB this looks like

sqlQ = "SELECT Column1, Column2, ColumnN " & _
"FROM MyTable " & _
"WHERE ColumnX = SomeValue " & _
"ORDER BY Column2 "

Which I think is a real hastle to maintain.

Also I think everyone can agree that keeping embedded SQL as short and
simple as possible is the most important practice. And the shorter and
simpler it is, the less it matters how it is stored.

David
Nov 20 '05 #5
Cor
Hi Fergus,
The first time I saw something of SQL I started hating it, but I see in this
newsgroup you are very good in it.
A lot of the things you write about this and Ado.net {not the ones I
recognise now as something else :-)} I put apart for reavaluating.
Cor
Nov 20 '05 #6
Hi David,

Mine would be slightly different:

sqlQ = " SELECT Column1, Column2, ColumnN" _
& " FROM MyTable" _
& " WHERE ColumnX = SomeValue" _
& " ORDER BY Column2"

It's just a little bit neater on the right-hand side and ensures that I
never omit the spaces.

Regards,
Fergus
Nov 20 '05 #7
Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const,
hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in
the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...
I have an app that retrieves data from an Access database. At the moment I
have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this string so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

Thanks
Mike

Nov 20 '05 #8
I believe that the jist of it would be everyone is implying that the Const
version is ok.

I would take it just one minor step further, and people can scream at me for
this if they want to...
Place your sql code in a string table in a seperate resource DLL. This has
a couple of possible benefits in my mind...
If the data tables change for some reason, you can edit and redistribute the
resource DLL without having to recompile the entire EXE or other assemblies.
Also, if your program eventually supports many databases (not taking into
account the differences in actually database access programming), you can
drop new SQL syntax in via the DLL and not have to create seperate program
versions. (for example, Oracle and SQL Server have some slight differences,
Access and SQL functions are slightly different, etc).

Go ahead everyone, sock it to me.

"Mike Stephens" <no************@nosuchdomain.com> wrote in message
news:OG**************@TK2MSFTNGP09.phx.gbl...
Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const, hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in
the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.

<user> wrote in message news:OY**************@TK2MSFTNGP09.phx.gbl...
I have an app that retrieves data from an Access database. At the moment I have the SQL string as a Const in my app. I understand this is not best
practice. I don't want the user to have access to read or modify this

string
so I don't want to store it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.

Thanks
Mike


Nov 20 '05 #9

"Mike Stephens" <no************@nosuchdomain.com> wrote in message
news:OG**************@TK2MSFTNGP09.phx.gbl...
Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const, hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in
the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.


Not an ini file. That just makes things more complicated.

Either in your application (a const is fine), or in the database using a
view (access queryDef) or stored procedure.

The SQL statements in your application are the interface into your database
layer. They are source code and it is proper for changes in the interface
to require you to modify and recompile your component.

This is another reason complicated SQL should be pushed down into your
database. If your application is bound directly to the physical tables then
any schema change will require you to change and recompile your application.
In such a case, your application is "tightly bound" to your schema.

If your application is bound to views and stored procedures, however, many
changes can occur at the database level without requiring a change to the
application code. Then your app is "loosely bound" to your schema.

David
Nov 20 '05 #10
<user> schrieb:
I have an app that retrieves data from an Access
database. At the moment I have the SQL string as
a Const in my app. I understand this is not best
practice. I don't want the user to have access to
read or modify this string so I don't want to store
it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.


People in the ADO.NET newsgroup will tell you what the best practice is:

<news://msnews.microsoft.com/microsoft.public.dotnet.framework.adonet>

Web interface:

<http://msdn.microsoft.com/newsgroups...ups/loadframes
..asp?icp=msdn&slcid=us&newsgroup=microsoft.public .dotnet.framework.adonet>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>
Nov 20 '05 #11
Hi Richard,

Sock my approval to you ? Lol.

I was going to mention in my earlier post, that a separate resource is an
option for truly separating the SQL from the code. Ypu gone and listed all the
good reasons for doing so. :-)

Now - here's a really wacky solution. :-D

Put the SQL on your web server as a Web Service. Then, not only can you
change it as-and-when without even visiting your clients, you can also monitor
their use of the program.

Regards,
Fergus
Nov 20 '05 #12

"Herfried K. Wagner [MVP]" <hi*******@m.activevb.de> wrote in message
news:ec*************@TK2MSFTNGP09.phx.gbl...
<user> schrieb:
I have an app that retrieves data from an Access
database. At the moment I have the SQL string as
a Const in my app. I understand this is not best
practice. I don't want the user to have access to
read or modify this string so I don't want to store
it in an INI / Text file or in registery. Can
someone please tell me the best practice for this.


People in the ADO.NET newsgroup will tell you what the best practice is:


No.

This is a question of VB best practice. It belongs here.
For instance in C# you can embed SQL in source code like this

string sqlQ = @"
SELECT
SOME_FIELD,
SOME_OTHER_FIELD
FROMSOME_TABLE
WHERESOME_FIELD=6
"

If you could do that in VB, it would change the best practices.

David
Nov 20 '05 #13
Cor
Fergus,
First I thought he is flaming (a new word I have learned yesterday)
This is brilliant, I did never thought about that?
Cor
Nov 20 '05 #14
Oh Cor, I never 'flame', I use these newsgroups too much and have too much
respect for the people who offer their advice to do anything like that.
(Um, it may not seem like it, but I am being sincere about it).

"Cor" <no*@non.com> wrote in message
news:3f***********************@reader20.wxs.nl...
Fergus,
First I thought he is flaming (a new word I have learned yesterday)
This is brilliant, I did never thought about that?
Cor

Nov 20 '05 #15
David,

Your arguments about 'pushing down' the sql into the database are 100%
correct, however, eventually, you still have the question come up of 'where
to put the original SQL statements'.

For instance, in MS Access, under certain data corruption conditions,
Queries are completely lost and must be rebuilt in the database. Where are
those query definition-definitions stored? Another program, an INI file,
where?

Also, what about SQL stored procedures? They must be run from somewhere,
normally during product installation, but still they are run. This one does
not have as much of a point as the Access problems.

In my response before, by placing the SQL code in a DLL, you are somewhat
simulating the 'querydef' idea in Access and/or views in SQL Server,
because, in some cases, without changing your application, you can change
the database structure and the sql resource library and still have the
program run properly.

For performance reasons, you can take these SQL statements and create views,
procedures, and querydefs.
"David Browne" <davidbaxterbrowne no potted me**@hotmail.com> wrote in
message news:uZ**************@TK2MSFTNGP10.phx.gbl...

"Mike Stephens" <no************@nosuchdomain.com> wrote in message
news:OG**************@TK2MSFTNGP09.phx.gbl...
Slightly off topic. My question is, where should I store the SQL string?

What is best practice for storing the string? Should I store it as a Const,
hardcoded in my app. Should I store it in an INI / Text file? There is
potential the SQL string could change over time, and if it is hardcoded in the EXE then then I need to replace the EXE everytime. Likewise if it is
stored in a DLL.


Not an ini file. That just makes things more complicated.

Either in your application (a const is fine), or in the database using a
view (access queryDef) or stored procedure.

The SQL statements in your application are the interface into your

database layer. They are source code and it is proper for changes in the interface
to require you to modify and recompile your component.

This is another reason complicated SQL should be pushed down into your
database. If your application is bound directly to the physical tables then any schema change will require you to change and recompile your application. In such a case, your application is "tightly bound" to your schema.

If your application is bound to views and stored procedures, however, many
changes can occur at the database level without requiring a change to the
application code. Then your app is "loosely bound" to your schema.

David

Nov 20 '05 #16
Cor
Richard,
Maybe I didn't use the wrong word, but I have seen it so often in a thread
from a long discussion last night.
You should have seen that awful discussion to understand what I wrote to
Fergus.
Fergus is very active in this newsgroup and when I see his mails he
somethimes think he likes to prickle to get it sharp.
When you look in this newsgroup there are a lot of people who are very fond
with the help from Fergus.
So I thought this was a prickle one.
And it was absolutly not ment for you or either negative.
I hope you will aspect this if you thought it was in anyway some negative.
I am like Fergus, not to breaking but building.
Cor
Nov 20 '05 #17
Cor
Richard,
Now I see it, the he is not you in my post, that is Fergus.
Cor
Nov 20 '05 #18

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Alfonso Morra | last post by:
I have written the following code, to test the concept of storing objects in a vector. I encounter two run time errors: 1). myClass gets destructed when pushed onto the vector 2). Prog throws a...
7
by: Mike | last post by:
I want to remove all commas from a numerical form field. I know that Replace(",", "") will to the job, but Replace(',', '') seems more appropriot but does not work. Any efficient suggestions...
1
by: AMeador | last post by:
I am doing a project where we need to read a write data to a database (SQL Server 2K in this case). I have seen the use of data binding to link a property of a control to a field in a record set...
1
by: serge | last post by:
Right now the database I am working with is storing time in an Integer data type and is storing the time value in seconds. The application does not allow entering seconds. It accepts minutes and...
10
by: Ren | last post by:
Hi All, I'm still rather new at vb.net and would like to know the proper way to access private varibables in a class. Do I access the variable directly or do I use the public property? ...
10
by: tshad | last post by:
I am trying to find the best procedure for storing keys used for encryption. This would also be a question for the connection string to the database. At the moment, this is kept in the web.info...
2
by: kbutterly | last post by:
All, I have a menu which contains Category as the master and Product as the child. When I click on a Category in the menu, I want one formView control, fvpc, to show, and then when I click on...
1
by: Jeff | last post by:
Hey asp.net 2.0 at work we are about to start on a new project. Creating a website. My mananger has created the database. The database has a table holding user information (not that standard...
1
by: oldyork90 | last post by:
I have two sets of text strings. They are related to each other and are not proprietary. X group contains about 2000 short, one to three word, strings. Y contains about 50. These two groups...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.