Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Stored Queries syntax problem

    I'm switching most of my sql queries from within the ASP page to stored queries (after reading M@rco's great articles: http://www.15seconds.com/issue/020919.htm). I've been doing some tutorials that worked great (very simple ones though). I'm facing a problem with that one:
    Code:
    ' like it was in the asp page (perfectly working):
    SELECT name FROM members WHERE id=" & memberID & " and sessionID=" & sessionID & " and visitorID=" & visitorID
    
    ' syntax of the stored query that doesn't work:
    SELECT (name) 
    FROM [members] 
    WHERE id=" & memberID & " AND sessionID=" & sessionID & " AND visitorID=" & visitorID & ";
    as you can see, the second one is more carefully written and that's all my work is about now. I've got things doing what I want them to do but my code is very dirty since I was writting my script and learning at the same time. Now I'm cleaning it all. Do you see what is going wrong in that query? If you fel like giving me advices in a more general way, feel free to do it!

    <edit>
    here is the tutorial I've been doing (just in case...): http://www.stardeveloper.com/article...1050101&page=1
    </edit>
    Last edited by jeskel; 11-15-2003 at 07:26 PM.

  • #2
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Are you sure that the & "; at the end, shouldn't just be ; ?

  • #3
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I tried it but it did not make any difference.... unfortunatly...

  • #4
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Shouldn't the variables in the where-claues be parameters ? So enclosed in brackets.

    By the way, you need to realise that if you use stored querys, that you wount be able to upsize as easy to another db as when you use embedded sql. The speed-improvement is also a bit a strange argument. You shoud only use MsAccess for single user or almost-single user (max 20 users) applications so there wount be much 'stress' on your db. And for me, the speddimprovement will never compensate for the loss of db-flexability.

    Just my opinion.

  • #5
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you are very right raf... I wouldn't use it for a big project actually... But since no one knows how a site can grow, I won't use it unless I'm sure that it's gonna be a site with only a few connections (which is not the case for my actual project that could face something like 30 connections at the same time). Thanx for pointing it out raf

  • #6
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Actually, you can call stored queries in Access in the same way that you call SPs in MSSQL (i.e. using parameter objects, etc), so I fail to see how it make migration harder. I would say that although not ideal, it is a step in the right direction towards SPs, and if your code already uses SQs it will make migration to real SPs a doddle.

    Anyway, now to answer bouchel's question...

    bouchel, I think you are getting confused between what is the VBScript and what is the SQL statement...

    I believe your stored query SQL statement should be this:
    Code:
    SELECT name 
    FROM members
    WHERE id=@memberID AND sessionID=@sessionID AND visitorID=@visitorID
    Just so you know, although in MSSQL you can indeed excute a whole bunch of SQL statements sequentially by indicating the end of each one with a semicolon, Access only executes the first, so there's no point stringing multiple statements together, and hence the semicolon is redundant.
    Last edited by M@rco; 11-17-2003 at 09:04 PM.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #7
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanx for the explanation M@rco... But I'm getting confused on using it or not at this point of the thread... I mean, if I have to move to MSSQL db, I won't have to change my code right? It will just remain the same right? All I would have to do is: when I'm designing the MSSQL db and moving the data to it, I'll have to write in stored procs that have the same names as the stored queries I'm calling from my ASP pages and everything should work the same... is that it?
    Last edited by jeskel; 11-17-2003 at 10:37 AM.

  • #8
    raf
    raf is offline
    Master Coder
    Join Date
    Jul 2002
    Posts
    6,589
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It's strange how some people never actually read a post or respond to the message.

    "you wount be able to upsize as easy to another db as when you use embedded sql" and "the speddimprovement will never compensate for the loss of db-flexability." gets translated into "Actually, you can call stored queries in Access in the same way that you call SPs in MSSQL (i.e. using parameter objects, etc), so I fail to see how it make migration harder."

    So what are you saying? Because you can save them as db-objects you .... Yeah what ?
    I don't see how you could contradict that embedded sql leaves you more flexability to upsize to another db later on. So maybe that's why you don't actually contradict it, and point to the one upsizing path with the 'smallest' rework? If you upsize to MsSQL with embeddep SQL, no changes will be needed (except for the connectionfile, but you need to take those 5 seconds rework anyway). If you would like to switch to mySQL, idem dito (unless you uses the JET-wildcards in LIKE clauses or you used TOP() and a few other Jet-goodies). Same goes for Oracle, DB2, PostGreSQL, ...
    Saving querys as db-object (whether they are SQ's or SP's) ties you more to that db-format. It's as simple as that. And if you want to stick with Jet-db's, then only use SP's from the start on.

    It's nice to point people to 'forgotten' or 'neglected' possibilitys to improve their apps, but i realy think that in this case, it's good that noone bothers to mention them inside tutorials. If you plan on sticking with Jet-db's, then you should use SP's and not fumble around with SQ's.

    To me, it's important to always lay out the advantages and disadvantages of a method, so that newer coders can make an informed decission. And in this case, i realy don't see how the speedimprovement would matter that much. I certainly wount compensate for the rework that could be necessary later on. So i think it would be only fair to stress that disadvantage. Perhaps even more the performance-advantage.


    There are so many, much more usefull things to learn, that probably will have a bigger impact on your app's performance and stability + that certainly will proove themselvs more usefull later on. SQ's certainly wouldn't make my "Top 100 SQL trics and tips" list. I think it more belongs to the "Top 100 Geek-issues"

    Besides, the bracket needs to go + about the semicolon --> if i remember correctly, the MsAccess querywindow also adds that to the generated statements. I never quite understood that because it makes no difference if you include it or not...

  • #9
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Originally posted by raf
    It's strange how some people never actually read a post or respond to the message.

    "you wount be able to upsize as easy to another db as when you use embedded sql" and "the speddimprovement will never compensate for the loss of db-flexability." gets translated into "Actually, you can call stored queries in Access in the same way that you call SPs in MSSQL (i.e. using parameter objects, etc), so I fail to see how it make migration harder."

    So what are you saying? Because you can save them as db-objects you .... Yeah what ?
    I don't see how you could contradict that embedded sql leaves you more flexability to upsize to another db later on. So maybe that's why you don't actually contradict it, and point to the one upsizing path with the 'smallest' rework? If you upsize to MsSQL with embeddep SQL, no changes will be needed (except for the connectionfile, but you need to take those 5 seconds rework anyway). If you would like to switch to mySQL, idem dito (unless you uses the JET-wildcards in LIKE clauses or you used TOP() and a few other Jet-goodies). Same goes for Oracle, DB2, PostGreSQL, ...
    Saving querys as db-object (whether they are SQ's or SP's) ties you more to that db-format. It's as simple as that. And if you want to stick with Jet-db's, then only use SP's from the start on.
    I wasn't merely going for the "least rework" approach, if that was the case then clearly inline SQL *would* be better. However, using SQs provides significant performance and business logic encapsulation advantages, and any such SQs would be childsplay to recreate as SPs anyway. I find your last sentence (above) regarding JET DBs rather contradictory, since JET (i.e. MS Access .MDB) doesn't support SPs, only SQs. MSSQL is *not* JET. Perhaps that was a typo?

    Originally posted by raf
    It's nice to point people to 'forgotten' or 'neglected' possibilitys to improve their apps, but i realy think that in this case, it's good that noone bothers to mention them inside tutorials. If you plan on sticking with Jet-db's, then you should use SP's and not fumble around with SQ's.

    To me, it's important to always lay out the advantages and disadvantages of a method, so that newer coders can make an informed decission. And in this case, i realy don't see how the speedimprovement would matter that much. I certainly wount compensate for the rework that could be necessary later on. So i think it would be only fair to stress that disadvantage. Perhaps even more the performance-advantage.
    No argument from me there, but it is my opinion that the improvement in security (with regards to SQL injection), the (variable but certainly significant) performance gains that using SQs brings, the forward compatibility with SPs, AND the improvements in code readibility & simplified development all add up to SQs remaining the preferable approach when developing using an Access backend.

    Originally posted by raf
    There are so many, much more usefull things to learn, that probably will have a bigger impact on your app's performance and stability + that certainly will proove themselvs more usefull later on. SQ's certainly wouldn't make my "Top 100 SQL trics and tips" list. I think it more belongs to the "Top 100 Geek-issues"
    My top 100 SQL "tricks and tips" probably wouldn't cover anything Access-related at all, but that's because no-one should be expecting to do any serious SQL using Access in the first place - it was never designed for it, and still isn't up to the job. However, since bouchel IS using Access, and until such a time as he is able to move up to MSSQL (or some other database server environment), I think that SQs are a useful weapon to add to his development armoury.

    Originally posted by raf
    Besides, the bracket needs to go
    Thanks for pointing out the error - I originally included bouchel's brackets, then decided to remove them, but in my haste forgot the closing braket. I have corrected it above - cheers!

    Originally posted by raf
    about the semicolon --> if i remember correctly, the MsAccess querywindow also adds that to the generated statements. I never quite understood that because it makes no difference if you include it or not...
    Exactly.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #10
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I say try to get a copy of SQL Server, Oracle, or something that can take advantage of Stored Procedures (does MySQL? Haven't used that yet... ).

    Some people tend to overuse sp's however; like anything else they have their advantages and (believe it or not) disadvantages (of course, this depends on their implementation, how well the application/database design was planned, etc.).

    That having been said, they are extremely useful, and can really save time and server resources when used appropriately.

    I haven't been using them that long myself to be honest, but a few nice little tricks I learned in Microsoft's 2073 class have come in really handy and saved me hours of work, and some VERY nifty reports that use views, stored procedures, and some other neat little tricks. For one application alone the stuff I learned in that class probably saved me 8 hours work at the very least.

    On the other hand I have to maintain an application that is over 5 years old (or darn close), and has grown to be pretty unmanageable. It uses stored procedures all over the place (not to mention several different stored procedures that *should* have been just one).

    Calls to these procedures are made all over the place as well (instead of being a single include). Although that's just mostly bad application design and NOT the fault of stored procedures themselves (I'm pretty sure much of the mess was created because of the original developer being rushed as well, and though he had some SQL knowledge, his web knowledge left a lot to be desired) , it can give you some idea of what to avoid.

    Oh... and m@rco, before you say "Why don't you rewrite it?", that's exactly what I plan on doing, and also the kind of thing I've been pushing for in my company (since in the long run it will be easier to just rewrite it to make things as dynamic as possible than to maintain a monster) - along with better planning and less haste. But it will probably be 6 months to a year before I get a chance to do anything along those lines as I have too much other stuff to write and maintain.

    By the way I've updated my functions after some quick testing and have closed all opened objects. I will be posting the updated version this week.

    I think with that flaw gone you will agree they can be very useful - either on their own or combined WITH sp's, if one was to go that route they'd be pretty well protected against sql injection attacks; just enough to start worrying about other security issues - and believe me that's just the start.

    P.S., I am *always* open to constructive criticism - and always looking for ways to improve not only my knowledge but help others as well.
    Last edited by whammy; 11-18-2003 at 02:16 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)

  • #11
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have to say that raf pointed out something that I unfortunatly misconsidered... I obviously won't use Access for the rest of my life so I think it's much better for me to have the sql statements inline. But I still find interesting to know about SQ so I'm going to work on it anyways, I'm sure it will be helpful one day or the other, if not for me, maybe for someone else Actually, this thread had the benefit to make me choose to move from Access to another db ASAP (I'll follow your advice Whammy). I will wait maybe a few days, redesigning my db, get rid of redundant data and make it as optimized as possible considering my level of knowledge. Then I'll get another one for sure.

    Speaking about Access.... M@rco: reading the critic of this db you make in your answer to raf I can feel a contradiction between your words here and the sentences of your article in which that aspect of the problem doesn't appear. I'm fully aware that your article was not about "is using Access a good idea or not" but about Stored Queries themselves and their speed improvement. You obviously consider that people reading your article choosed to use Access in purpose knowing every aspect of the good and bad sides of this db. I don't think that you can expect that from all your readers. If I follow what you say in that thread: Request.QueryString in a SELECT statement? :
    I myself have been "ASP Guru of the year" two years' running at Sitepointforums, so I am aware that others follow my advice and use my code, and so I do my best to ensure that my code is as good as possible (best practice, efficient, well-structured, neat presentation), and fix errors/bugs that slip through the net (in code that I have posted) ASAP, and I do the same here as a matter of course.
    I would say that including the critic you make here in CF in your article would have been a good idea. Just one line, a few words... I'm just trying to point out that I was rather surprised to read your thread here because I basically choose to use SQ after reading things from you. Don't misunderstand me, I'm not dissing
    your article (definitly not at all) and I definitly know that your article had the Access-users angle so there is no contradiction in it (if we consider it in itself, cut from what you've posted here). But including the *not that good* side of it in one or two sentences would have been reflecting in a more realstic way the limits of that practice for people like me who are not programmers but still enjoy learning new things (coding in that case). As you point out, you are influent... because you are really good... ahhhh responsabilities Anyways, I'll still learn about it because I find it an intersting topic to know about.
    Originally posted by whammy

    By the way I've updated my functions after some quick testing and have closed all opened objects. I will be posting the updated version this week.

    I think with that flaw gone you will agree they can be very useful - either on their own or combined WITH sp's, if one was to go that route they'd be pretty well protected against sql injection attacks; just enough to start worrying about other security issues - and believe me that's just the start.
    could you post something or PM me when that will be posted? Thanx a lot Whammy for your helpfull code
    Last edited by jeskel; 11-18-2003 at 11:33 AM.

  • #12
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    bouchel, thanks for your comments. I assume that you a referring to this?
    Originally posted by M@rco
    My top 100 SQL "tricks and tips" probably wouldn't cover anything Access-related at all, but that's because no-one should be expecting to do any serious SQL using Access in the first place - it was never designed for it, and still isn't up to the job.
    As you suggest yourself, the article was not intended to present the reader with any information regarding whether or not Access should be used as a DB backend - the 15Seconds site is targeted at intermediate to advanced-level ASP developers, and therefore a certain level of knowledge and experience with the relevant technologies is assumed, such as the basic caveats relating to using Access. Such topics have been written about time and time again all over the web, and the advice to *not* use Access for anything other than learning or a very low hit-rate site has been shouted from the (proverbial) rooftops countless times.

    My article therefore focused on helping developers who *have* chosen to use Access to improve the performance and thus the scalability of the database.

    As you may have noticed from the way I post on this forum and SPF, I don't like regurgitating advice which I have already answered many times before (and is easily found via a search), or is already well covered elsewhere on the web. In both cases, I try to provide relevant links.
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #13
    Regular Coder
    Join Date
    Aug 2003
    Posts
    565
    Thanks
    0
    Thanked 0 Times in 0 Posts
    bouchel, thanks for your comments.
    you're welcome... I really hope I didn't sound sarcastic or something like that. I defintly wasn't disrespecting your work.
    As you suggest yourself, the article was not intended to present the reader with any information regarding whether or not Access should be used as a DB backend - the 15Seconds site is targeted at intermediate to advanced-level ASP developers, and therefore a certain level of knowledge and experience with the relevant technologies is assumed, such as the basic caveats relating to using Access. Such topics have been written about time and time again all over the web, and the advice to *not* use Access for anything other than learning or a very low hit-rate site has been shouted from the (proverbial) rooftops countless times.
    I didn't know that 15seconds was kind of elitist. But I think it is a good thing. I just didn't read a lot of things from this site and didn't know their politic. I wasn't aware of this. Now I'll have it in mind when reading things there.
    My article therefore focused on helping developers who *have* chosen to use Access to improve the performance and thus the scalability of the database.
    So people intermediate and advanced still choose to use Access even when knowing that they probably should not? A client must be behind that choice
    As you may have noticed from the way I post on this forum and SPF, I don't like regurgitating advice which I have already answered many times before (and is easily found via a search), or is already well covered elsewhere on the web. In both cases, I try to provide relevant links.
    I totally respect that way of answering and actually I fully understand you. I'm also like that in other fields like music where I have a much better level than my ASP level

    See you soon M@rco. respect.
    Last edited by jeskel; 11-18-2003 at 09:58 PM.

  • #14
    Regular Coder
    Join Date
    Oct 2003
    Location
    London, UK
    Posts
    411
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks...
    Marcus Tucker / www / blog
    Web Analyst Programmer / Voted SPF "ASP Guru"

  • #15
    Senior Coder
    Join Date
    Jun 2002
    Location
    41 8' 52" N -95 53' 31" W
    Posts
    3,660
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ok... just for your perusal... I present my new and improved function library.

    I thought about naming every regular expression "objRegEx", but when testing, discovered that functions that were dependent upon one another failed because they used the same object names (what a surprise lol). So I guess I'll stick with the original naming scheme for now until I figure out the best way to approach that problem.
    Attached Files Attached Files
    Last edited by whammy; 11-19-2003 at 02:25 AM.
    Former ASP Forum Moderator - I'm back!

    If you can teach yourself how to learn, you can learn anything. ;)


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •