SQL Message?!

bonkey

Mutant_Fruit wrote:

A well designed database and application will always use stored procedures.

Not necessarily. More importantly, it may not use stored procedures for everything.

I havn't ever come across a place where i can't use them,

A search across a large table (row-count-wise) with a large number of fields, any permutation or combination of which can be included or excluded from the search.

Short of getting your stored procedure to dynamically build an SQL string which you then execute, there is simply no efficient way of building a stored procedure to do this, adn thus it is the wrong approach.

If you get your stored procedure to dynamically build an SQL string which you then execute....you're once-again going to have to code against injection attacks....at which point, the use of prepared statements becomes an arguably preferable option.

I'll paste in an example of what i considar a "properly worded" SP tomorrow.

If you can show me an efficient way of solving the problem that I've described with a "properly worded" Stored Procedure, I'll be impressed.

As a general rule, anyone presenting a solution as the "best in all cases" is wrong. There is no panacea.

jc

Mutant_Fruit

You're right, i could never say a stored procedure can and should always be used. There is doubtlessly a situation where it would be impossible to use one, but those should be very rare.

This is what i'd considar a basic enough "properly worded" statement. Basically, i have an ASP.NET webform with about 6 "selection criteria". People can filter the information displayed based on what they select from 6 different dropdown lists.

Here i was told i should probably dynamicly build my SQL statement based on what filters were enabled, but i used a Stored Procedure instead. I've edit parts of it to remove real table names, so if it doesn't make complete sense, its cos i edited it badly

I think this is roughly what you're asking me to show, if not, give me a more detailed explanation of what you're asking, and i'll see if i can code a stored procedure to do it as "dynamically" as you want it.

CREATE PROCEDURE FilteredCalls
	(
	   @categorystart int,
	   @categoryend int,
	   @subcategorystart int,
	   @subcategoryend int,
	   @assignedstart int,
	   @assignedend int,
	   @startdate datetime,
	   @enddate datetime,
	   @statusstart int,
	   @statusend int,
	   @prioritystart int,
	   @priorityend int
	)


AS
select callnumber, categorytable.category, subcategorytable.subcategory, companyname, summary, notes,
userinfo.fullname as Assigned, dateopened, historyinfo.dateadded as datemodified, statustable.status as status, prioritytable.priority
from calls 
inner join
statustable
on
statustable.listvalue = calls.status
inner join
prioritytable
on
prioritytable.listvalue = calls.priority
inner join
categorytable
on
calls.category = categorytable.listvalue
left outer join
subcategorytable
on calls.subcategory = subcategorytable.listvalue
left outer join
userinfo
on calls.owner = userinfo.userid
left outer join
historyinfo
on calls.callnumber = historyinfo.fkey
where callnumber like 'CI%'
AND calls.category BETWEEN @categorystart AND @categoryend
AND calls.subcategory BETWEEN @subcategorystart AND @subcategoryend
AND calls.owner BETWEEN @assignedstart AND @assignedend
AND dateopened BETWEEN @startdate AND @enddate
AND calls.status BETWEEN @statusstart AND @statusend
AND calls.priority BETWEEN @prioritystart AND @priorityend
order by datemodified desc

From my C# code, if any of the dropdowns is set to "All" or "N/A", then the min value for that specific item is set to 0 (you can't get -1 ) and the max is set to the max in the table. Therefore, making that specific filter return ALL rows. It won't filter anything. If a dropdown (such as priority) is set to high, then both max and min are set to the same value (high=3). And you will only get "high" priority calls. Combine the other filters, and you can get any combination.

Therefore we have a very dynamic stored procedure able to pick and choose what rows get returned based on dynamicly chosen items from 6 different dropdowns.

This kind of logic can be applied in a lot of cases.


EDIT: Whats the difference between a prepared statement and a stored procedure?

bonkey

Mutant_Fruit wrote:

You're right, i could never say a stored procedure can and should always be used.

You replied to a statement where I was asking for a situation that Prepared Statements can't handle saying that a well-coded app will always use Stored Procedures.

Forgive me for if I misunderstand that to mean you were suggesting one should always use SPs in preference to PSs

Here i was told i should probably dynamicly build my SQL statement based on what filters were enabled, but i used a Stored Procedure instead.

/me Nods. Seen this type of thing before. Also seen it crash and burn horribly (performance-wise) when dealing with more than one large table in the joins, where you have unnecessary joins occurring when they're not needed.

Example - getting a list of all customers. I have the option to specify search criteria based on sales to those customers, but the returned information will not contain the sales info, just the customer info. If I don't search on sales there is nothing but a potentially massive performance loss asking the system to join all records in the sales table(s) that I don't want to filter against, nor retrieve information from. I'll simply force the join to occur, and then use a distinct (or somesuch) to remove the ensuing duplicated customer information. Do this on tables running into GBs, and you're screwed.

Your approach will generally work when joining "lookup" tables (i.e. for one record in your call record, only one record in the category table will match). But if you're talking about a one-to-many join (my sales example above) the performance impact of doing it that way can be unbelievable.

I used to be firmly in the Stored Procedure camp...particularly with MSSQL before Version 7.0. Since then, the performance benefits are - in the vast majority of cases - no longer there, and the security benefits are generally questionable, unless you're coding validation logic or somesuch into your stored procedures alongside the basic CRUD statements.

jc

bonkey

Mutant_Fruit wrote:

EDIT: Whats the difference between a prepared statement and a stored procedure?

A prepared statement is "ad-hoc" SQL built client-side, except where parameters are defined as just that - parameters.

The query is prepared in that the database server will compile its execution plan, set it up for parameters etc, and then typically re-use this over and over as you call repeatedly.

So, in kinda-code, you'd have something like :

[i]// Preparation[/i]
sqlStatement = " SELECT * from MyTable where someId = ?"
preparedStatement = dbConnection.PrepareStaetment(sqlStatement)
preparedStatement.AddParameter(
  new Parameter(someTypeInformationWillGoHere));
 
[i]// Usage[/i]
preparedStatement.Parameters[0].Value = 100;
resultSet1 = preparedStatement.GetResultSet();
 
preparedStatement.Parameters[0].Value = 999;
resultSet2 = preparedStatement.GetResultSet();

Obviously, in a real-world app, there are better ways to generate your SQL, but this shows you the basic idea.

You build like ad-hoc, and use like a stored procedure.

Couple with tools/technologies/components/widgets that do most of the building for you, and most of your work is done.

jc

Mutant_Fruit

Gotcha. Thats the exact same as "precompiled SQL statements" in SQLite. Its the half-way house between dynamic and stored procedures.

For functions that are called very frequently, a SP would be best simply because its compile once, use many. But for functions that would be used infrequently/change a lot, a prepared statement would probably be best.

In my example, those joins are all to lookup tables, and they always have to be done, regardless of how many rows are going to be returned. So my way works well in this situation.

As always, the order of your "where" statements makes a difference. You want to remove as many rows as possible, as soon as possible. The less rows available for later "filters", the faster they can go. So you always try and order your where statements where the first statement removes the most rows, second statement removes the second largest amount of rows etc.

You replied to a statement where I was asking for a situation that Prepared Statements can't handle saying that a well-coded app will always use Stored Procedures.

For some reason i was thinking "Prepared" = "Dynamic". I always refer to "prepared" as "precompiled" simply because my first database experience was SQlite, and SQlite calls em "precompiled" statements.

fatherdougalmag

Just like to say that, as someone learning re-learning SQL in my own time, it's through active discussions like this that you can learn bucket loads of stuff in 15 mins as opposed to the odd hours here and there that I can put in.

bonkey

Mutant_Fruit wrote:

For functions that are called very frequently, a SP would be best simply because its compile once, use many.

Most database systems (I believe) compile stored procedures once per session/connection, or more than that if explicitly instructed to do so and/or when the plan falls out of the cache.

Prepared Statements are handled identically - prepared once per session, and unless explicitly instructed to do so, or the plan falls out of cache, the prepared plan is re-used.

In my example, those joins are all to lookup tables, and they always have to be done, regardless of how many rows are going to be returned. So my way works well in this situation.

Absolutely...I was just pointing out (for other readers, if we still have any) where the approach might not work. And even in my examples, it might still work acceptably fine. A lot will depend on the table size, the usefulness of the indexes, how smart/good the optimiser is....and so on.

As always, the order of your "where" statements makes a difference.

As always? Maybe on some DB systems, but not on all.

With MSSQL (which I keep referring to because I know it the best), it makes no difference, nor does Join order. The optimiser will actually check which order makes most sense performance-wise.

For some reason i was thinking "Prepared" = "Dynamic". I always refer to "prepared" as "precompiled" simply because my first database experience was SQlite, and SQlite calls em "precompiled" statements.

Prepared is mostly a term I've picked up from VB/java/c#. It seems to be fairly common across the various data-access models. I've no idea if its a formal description or not....so precompiled is just as good (and perhaps more clear) a term.

jc

bonkey

As a side-note about precompilation....soemtimes it can bit you in the ass. There are certain situations where a query should be optimised differently depending on the value of the parameters.

Think of a table where a column mostly (99% of a million rows, say) has a single v value, but for the other 1% has unique values. You're filtering on this field. Should you table-scan (optimal for retrieving 99% of rows), or use an index (optimal for the other 10,000 rows)?

Ideally, you choose both.

You either have two SPs/PSs, each with compiled with optimisation hints to tell the system which way to go, and you decide in advance whether its the duplicated value or not....or you instruct your SP/PS to recompile on each execution, which will then take the actual parameters into account.

Now I'm off to do some work.

Mutant_Fruit

bonkey wrote:

As always? Maybe on some DB systems, but not on all.

Ya learn something new every day. I thought most (if not all) benefited from proper placement of where clauses. Also, i've heard some databases are a lot faster if you say

WHERE a = b AND b = c AND c = a;

as opposed to

WHERE a = b AND b = c;

Does the optimiser take that into account to?

LanceStorm

wow, its amazing, this one thread has taught me more than an entire year of college!!

---

ok, security is 100% not an issue, some validation rules are all there after, its a 3rd year project belive it or not!!

---

for the autogenerated order ID, presumably that has to be set to autonumber in access yeah??

---

Is it possible to have a search bar in with the database?? desirably I want to be able to enter a username and have it bring me up that users details in table?? anyone any pointers how id go about that??

LanceStorm

Mutant Fruit,

When I leave the order id field out of the code it gives me this:

"Number of query values and destination fields are not the same"

Mutant_Fruit

I want to be able to enter a username and have it bring me up that users details in table??

string query = "SELECT Details1, Details2, Details3 FROM Table_Details WHERE username =" TextBox_Username.Text;

Something like that. Dead simple to do.

string query = "SELECT Details1, Details2, Details3 FROM Table_Details WHERE username =" TextBox_Username.Text;

Yup.

Slightly off topic, but are you using an Access database? If so, maybe try ditching it and use a real mans database (i've used some programs before that had an Access database as the backend, and they are sooo slow).

Check out SQLite. It supports pretty much everything any SQL server supports (except for stored procedures... it just has precompiled statements). Its also a lot faster than access, and a lot smaller than access. You can either access it via a commandline interface (if you're lazy) or you could use a wrapper dll and read/write to the database directly from your program. I'm in the middle of a project at the moment, and SQLite is what i'm using as my database. Tis a great engine.

When I leave the order id field out of the code it gives me this:

Paste in your insert statement so i can take a look. Also, check out access's help on auto-increment primary keys.

LanceStorm

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"


Left out order id like ya said, but it gives an error that im leavin it out!!

---

Haha, a mans database!!
way too late to go changing database programs now im afraid, i'll have to suffer with access for now...!!

I'll try the string query in a minute!!

thanks again!!

bonkey

Mutant_Fruit wrote:

Ya learn something new every day. I thought most (if not all) benefited from proper placement of where clauses. Also, i've heard some databases are a lot faster if you say

WHERE a = b AND b = c AND c = a;

as opposed to

WHERE a = b AND b = c;

Does the optimiser take that into account to?

Good question

It rings a bell, but I'm not entirely convinced that what I'm saying here is fully correct. Here's how I'd understand the reasoning though....

If you think abotu comparing three values like this, ask why are you likely to do it? Odds are that what you're doing is linking fields across tables in at least one of these three, and then either linking a third table or comparing the linked field against a fixed value. The other combinations (all three in one table, two in one table and one "fixed" value, and so on) are all possible, but less likely.

So, I'll deal with the two cases I've described.

Option 1 : Two (joined) tables, and a filter on the joined value.

I'll name things A.a, B.b, and c, so its clear which are table values and which are filters.

A.a = B.b joins the tables.
B.b = c allows the filter to be done either on table B pre- or post- join. There's advantages to both, depending on the situation.

Yes?

Now, if we include A.a = c, this also allows table A to be filtered as well as table B or instead of table B before the join happens, as well as still makign it entirely possible to filter post-join.

It may give a performance boost, but whats more important is that it gives the optimiser all options without you having to second-guess which approach will be best.

Option 2 : Three (joined) tables, and no filter value

Here, its a similar logic - including all three options gives the optimiser the most flexibility in deciding which tables to join to which. It can join A to B, then the result to C, A to C and the result to B, B to C and the result to A, or the reverse of any of these (joining B to A being different to joining A to B in terms of which table you process seuqentially and which you look for join values in).

In general, I'd recommend that no-one takes any of this as gospel for the DB they're using. I've read a lot on optimising MSSQL across every version from 4.2. When I was at TechEd Europe last year, I was chatting to an MSSQL expert from whom I discovered that some of the optimisation techniques I was still using were not only obsolete since V 7.0 was released but were now decidedly non-optimal. So I don't even necessarily trust my own optimisation understanding.....I just use it as a starting point, adn then read up / google if it doesn't give me the performance I want.

I must say though...DB/Query optimisation is something I'd love to have the opportunity to do full-time. Not a DBA job....rather a DBO (DB Optimiser). Maybe some day...when I've another decade or two under my belt

jc

bonkey

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"


Left out order id like ya said, but it gives an error that im leavin it out!!

If you're not supplying values for each field (i.e. if OrderId is an Autobnumber in Access) then you shoul dhave :

Insert into myTable (thisField, thatField, theOtherField) Values (ValueForThisField, ValueForThatField, ValueForOtherField).

In other words...if you ain't supplying a value for all fields (in teh order they're defined), then you must supply a list of the fields you are supplying values for (in the order you supply them).

jc

LanceStorm

bonkey wrote:

If you're not supplying values for each field (i.e. if OrderId is an Autobnumber in Access) then you shoul dhave :

Insert into myTable (thisField, thatField, theOtherField) Values (ValueForThisField, ValueForThatField, ValueForOtherField).

In other words...if you ain't supplying a value for all fields (in teh order they're defined), then you must supply a list of the fields you are supplying values for (in the order you supply them).

jc

but if im asking the user to enter the value's, what to I set for(ValueForThisField, ValueForThatField, ValueForOtherField)??

rsynnott

bonkey wrote:

Prepared is mostly a term I've picked up from VB/java/c#. It seems to be fairly common across the various data-access models. I've no idea if its a formal description or not....so precompiled is just as good (and perhaps more clear) a term.

It's not necessarily accurate, tho. JDBC can do prepared statements for numerous DBs that don't support precompiled ones; there the only advantages of using prepared statements are portability and easy injection avoidance.

Mutant_Fruit

LanceStorm wrote:

but if im asking the user to enter the value's, what to I set for(ValueForThisField, ValueForThatField, ValueForOtherField)??

Like this:

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders(ThisField, ThatField, OtherField) VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

Or in your case:

If Request("submit") = "Insert" Then
sSQL = "INSERT INTO orders (uname, quantity, product) VALUES ('" & Request("uname") & "', '" & Request("quantity") & "', '" & Request("product") & "')"

Mutant_Fruit

rsynnott wrote:

It's not necessarily accurate, tho. JDBC can do prepared statements for numerous DBs that don't support precompiled ones; there the only advantages of using prepared statements are portability and easy injection avoidance.

I think you misunderstood my use of Precompiled.

We have:
Stored Procedures
Precompiled/Prepared Statements (same thing in my eyes)
Dynamic SQL (raw sql).

LanceStorm

it works....!!!!!!!

it actually works!!!!!!!!!!

thanks everyone that had an input into the gloriousness of it working!!!

I was 2 seconds away from radically changing the entire website to suit a product you could only ever order once!!

LanceStorm

Ok, now that excitement is over, next step!!

search box
->
Here's my code for the form im using to take in the username and direct it to the asp page userfound.asp
<form action="userfound.asp" method="post">
<input type="hidden" name="action" value="User Search" />
<table border="5">
<tr>
<td align="right"><B><font color = black>Enter User Name:</td>
<td><input type="text" name="login" /></td>
</tr>
<tr>
<td align="right"></TD>
<td><input type="submit" VALUE="Login" /></td>
</tr>
</table>
</form>
->
here's my asp for displaying the user that is entered into the searchbox
<%
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("db/order.mdb"))

string query = "SELECT order id, uname, quantity, product FROM orders WHERE uname = User Search.Text;"

Response.Write("<table border=2 cellpadding=2 cellspacing=3 style='font-family:arial; font-size:10pt;'>")
Response.Write("<tr bgcolor=blue style='color:white;'>")
Response.Write("<td>Order ID</td>")
Response.Write("<td>Username</td>")
Response.Write("<td>Quantity</td>")
Response.Write("<td>Product</td></tr>")

Do While NOT oRS.EOF
Response.Write("<tr><td>" & oRS("order id") & "</td>")
Response.Write("<td>" & oRS("uname") & "</td>")
Response.Write("<td>" & oRS("quantity") & "</td>")
Response.Write("<td>" & oRS("product") & "</td></tr>")
oRS.MoveNext
Loop

Response.Write("</table><br><br>")

oConn.Close
Set oRS = Nothing
Set oConn = Nothing
%>

**************
This is the code mutant fruit gave me for selecting a particular username from a textbox and displaying only its details
string query = "SELECT Details1, Details2, Details3 FROM Table_Details WHERE username =" TextBox_Username.Text;

this is what ive adapted it to, fairly sure ive the code wrong tho!?!
string query = "SELECT order id, uname, quantity, product FROM orders WHERE uname = User Search.Text;"
**************

im so unbeliveably greatful for all your help guys, the project consisted of : learn asp and make this site, they never actually taught us how to do any of it!?!

Mutant_Fruit

Without trying to be an ass (don't take this the wrong way), but how good are you at coding? If you can't tell me whats wrong with this, then you really need to go back to basics.

string query = "SELECT order id, uname, quantity, product FROM orders WHERE uname = User Search.Text;"

Remember, you're building a query string which selects the details based on a username which is typed into a textbox. You have the basic idea right... but made one serious blunder.
nobody tell him, he needs to figure this himself.

bonkey wrote:

I must say though...DB/Query optimisation is something I'd love to have the opportunity to do full-time. Not a DBA job....rather a DBO (DB Optimiser). Maybe some day...when I've another decade or two under my belt

Aye, query optimisation is an art in itself. Personally, i just write a query, order the WHERE statements to what i considar the best way to reduce rows, and leave it at that. The database i work with are generally quite small, definately no more than one or two hundred MB, so spending hours to eek about a 25% speed increase isn't actually that important, as it saves at most 1/4 of a second, which ain't that much. Also, its purely an internal database, and will never ever have a huge load.

LanceStorm

im rubbish at coding im afraid!!
ya see, ive had try try learn ASP from scratch, literally by piecing bits of code from websites together and playing around with it till it works... its been a hard way to learn but hell im amazed ive stared to get the hang of it!!

still rubbish at this coding malarky tho!!

Hmmm...
Where username = "" -> has to be set to the textbox right, but ive literally no idea how I do that, maybe i was supposed to declare the textbox as a string before hand??

LanceStorm

ok,

I have a page with a form to take in the uname(searchuser.asp) that links to the found user page(founduser.asp), so once the uname is typed in the form and they click search, it displays the details(founduser.asp)

trying to figure this out, can the founduser.asp page link to the form in searchuser.asp so it knows what username to display?? and if it can, I have no idea how to make it do it!!

jus tryin to get my head round this!!

Mutant_Fruit

Ok, you need to start with a few tutorials. A lot of what you're doing can be done using just one webpage, not several as you seem to be doing.

Check out www.asp.net and www.w3schools.com Then google for more info. That should (heopfully) give you a start on everything. You don't seem to know much about it, and we can't tell you absolutely everything. You need to learn yourself

LanceStorm

lol,

thanks for the sites!!
the w3schools.com site is really well done and has been a great help!!

still cant get the search or delete asp functions to work, cant figure out how to connect the search function to the value taken in by the textbox!!

but im sure i'll get eventually!!
thanks for all ye're help!!