473,387 Members | 1,592 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,387 software developers and data experts.

Removing embedded SQL from database application

I inherited an existing database application about 6 months ago. I've
finished converting it to use SQL Server 2000 as the backend (MS Access
2002 frontend w/ Userforms+VBA code pointing to the backend).

In its current state the application makes use of a ton of embedded SQL
statements and I'm thinking about the best way to clean this up. It
seems like the best way would probably to encapsulate each of the SQL
statements into its own stored proc and then have the MS Access
application call the sp. However, this would result in a very large
number of stored procedures.

Anyone have any links or suggestions regarding best practices on
removing embedded SQL scattered throughout a DB app? Thanks.
Apr 24 '06 #1
1 1333
Beowulf (be*****************@hotmail.com) writes:
I inherited an existing database application about 6 months ago. I've
finished converting it to use SQL Server 2000 as the backend (MS Access
2002 frontend w/ Userforms+VBA code pointing to the backend).

In its current state the application makes use of a ton of embedded SQL
statements and I'm thinking about the best way to clean this up. It
seems like the best way would probably to encapsulate each of the SQL
statements into its own stored proc and then have the MS Access
application call the sp. However, this would result in a very large
number of stored procedures.

Anyone have any links or suggestions regarding best practices on
removing embedded SQL scattered throughout a DB app? Thanks.


Certainly sounds like a daunting task. An alternative is review all
embedded SQL and make sure that no SQL statements interpolate values,
but all queries are parameterised. Furthermore, make sure that tables
are prefixed with dbo.

As for why, read these two sections:
http://www.sommarskog.se/dynamic_sql.html#SQL_injection
http://www.sommarskog.se/dynamic_sql.html#queryplans
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 24 '06 #2

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

Similar topics

0
by: quadric | last post by:
Hi, I have an application that has an embedded/extended Python interpreter. I need to add database capabilities and for reasons to lengthy to explain in this email, also require an embedded...
4
by: jrefactors | last post by:
I want to distinguish between static SQL, dynamic SQL, and embedded SQL, but couldn't find too much useful resources in the web. For example, if we put SQL statements (SELECT, INSERT, UPDATE,...
26
by: jini us | last post by:
Hi, I am starting a new project where I intend to use embedded database server in my win32 application. I intend to use VC++ microsoft studio 6.0 as my development environment. The...
11
by: Mark Yudkin | last post by:
The documentation is unclear (at least to me) on the permissibility of accessing DB2 (8.1.5) concurrently on and from Windows 2000 / XP / 2003, with separate transactions scope, from separate...
59
by: Jeff Bowden | last post by:
For ease of configuration and other reasons, I would like for my single-user GUI app to be able to use postgresql in-process as a library accessing a database created in the users home directory. ...
0
by: Ann Harrison | last post by:
Tom, Actually, I've never had a corrupted firebird database. There were two bugs in an older version of InterBase that produced income for me. The first was a failure to realize that a file had...
3
by: Richard Connamacher | last post by:
I have two questions about PostgreSQL: Does anybody know if it can be used as an embedded database engine within an application, so the application doesn't have to connect to an external server?...
25
by: VictorReinhart | last post by:
Hi, I am intersted in trying to reduce the cost of C# development, by reducing the number of lines of code. In my opinion, as a business developer, the biggest opportunity to reduce the number of...
0
by: YellowFin Announcements | last post by:
Whitepaper: "Yellowfin Reporting" enables Embedded Business Intelligence -------------------------------------------------------------------------------- Embedded reports are a standard...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...

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.