|
nbv4 posted:I am, I just would like one last line of defence against total query entry, just for piece of mind against anything unforeseen... I tried running through each query once with "LIMIT 0" attached, but that didn't work. I'll look into the transaction thing. kalleboo fucked around with this message at 12:53 on Feb 7, 2008 |
# ? Feb 7, 2008 12:51 |
|
|
# ? May 15, 2024 04:50 |
|
kalleboo posted:This is the whole point of transactions - to be able to roll back changes if anything goes wrong (well, along with making things atomic and the side effect of certain things being optimizable). With MySQL you need to be using InnoDB and a fairly recent version (5.0 IIRC) for support. I just spent the last hour looking up transactions, and have now got it working with my site. The only problem is that my webhost is using an old version of MySQL so it may not work there edit: basically i have this: code:
edit2: nevermind, it works nbv4 fucked around with this message at 13:49 on Feb 7, 2008 |
# ? Feb 7, 2008 13:20 |
|
Walked posted:Can someone help me understand: Regarding paging, for .NET 1.1 (what we use at my company) we have some code to return only the approrpiate records because from what I've been told .NET needs to call the stored procedure each time the page index is changed (thus if you have 1 million records and you're displaying 50, you have to get the entire million each time you change a page). I've also been told that this has been remedied in .NET 2.0, but I honestly don't know. Anyways, the basic idea of our paging solution is to pass in the page number as a parameter to the stored procedure and then use that page number parameter with a predefined page size and the ROW_NUM to determine which page to return. code:
The website http://dotnetjunkies.com/Tutorial/EA868776-D71E-448A-BC23-B64B871F967F.dcik is decent except for two modifications I would make to their code: 1) Don't use the Identity column, use the ROW_NUM. It is possible to have gaps in the Identity column after you delete records and then you will get pages that are of different sizes (i.e. page 2 will return records 11-20 2) The code for C# code behind, instead of doing all that stuff about enabling and disabling the next and previous buttons, set the virtualcount property (from the total number of returned records) and the current page index, and the datasource should take care of figuring out what buttons to display.
|
# ? Feb 7, 2008 13:47 |
|
chocojosh posted:Regarding paging, for .NET 1.1 (what we use at my company) we have some code to return only the approrpiate records because from what I've been told .NET needs to call the stored procedure each time the page index is changed (thus if you have 1 million records and you're displaying 50, you have to get the entire million each time you change a page). I've also been told that this has been remedied in .NET 2.0, but I honestly don't know. Thanks, thats a cool way for me to go about it. Anyone care to offer an explanation of the OVER clause of ROW_NUMBER for me? I'm just not getting what it "does". Is it just more or less a query in which ROW_NUMBER iterates over to "get" row numbers?
|
# ? Feb 7, 2008 14:51 |
|
I do know that in oracle, the OVER clause on any analytic function serves to specify the ordering (and optionally the partitioning) of the rows to perform the function on. So yes, you are asking for the row_num according to the ordering in your OVER() clause, which can be different than the ordering of your actual query. This is useful for situations where you need to determine the ranking of results according to one criteria, but are actually interested in returning them according to another. I don't know MSSQL and it might be different, but I suspect this much is the same.
|
# ? Feb 7, 2008 15:27 |
|
yaoi prophet posted:I do know that in oracle, the OVER clause on any analytic function serves to specify the ordering (and optionally the partitioning) of the rows to perform the function on. So yes, you are asking for the row_num according to the ordering in your OVER() clause, which can be different than the ordering of your actual query. This is useful for situations where you need to determine the ranking of results according to one criteria, but are actually interested in returning them according to another. Great, that pretty much explains what I needed to know. Next question! Anyone care to suggest a book on database design? "Database Design for Mere Mortals" seems rather good, but want to confirm first.
|
# ? Feb 7, 2008 15:30 |
|
Is ON DELETE CASCADE evil? For some reason at my company we don't use ON DELETE CASCADE. I've been told it's to prevent accidental loss of data. On the other hand, there is minor frustration when the database schema gets updated and any stored procedures that deletes the referenced table of the newly added foreign key needs to be modified. Opinions of experienced database designers/developers?
|
# ? Feb 7, 2008 16:19 |
|
chocojosh posted:Is ON DELETE CASCADE evil? This question is an easy way to start a pissing match between database developers, from my experience. In my opinion they are in the category of "things we can do if we really need to, but let's try to avoid it". This is because you can easily end up with a byzantine labyrinth of logic consisting of triggers and cascades that is very difficult to follow. Trying to play "guess how this record is getting deleted" is not fun. Of course, deleting records in general is considered something to avoid for us, so we naturally view automatic deletion with even more suspicion.
|
# ? Feb 7, 2008 17:14 |
|
Walked posted:Thanks, thats a cool way for me to go about it. In SQL Server 2005 using the OVER() clause is indicative of a Window function. I don't ever use them (because we are perpetually years behind state of the art) so I don't know anything about them. There is plenty on google though if you search for window functions. There is also a large section about them in a book "SQL Cookbook" http://www.oreilly.com/catalog/sqlckbk/ if you have access to that O'Reilly safari, or whatever their online library thing is.
|
# ? Feb 7, 2008 19:05 |
|
Walked posted:Can someone help me understand: Victor fucked around with this message at 19:40 on Feb 7, 2008 |
# ? Feb 7, 2008 19:12 |
|
code:
But anyone know why it's erroring out on the WHERE clause? Walked fucked around with this message at 03:45 on Feb 8, 2008 |
# ? Feb 8, 2008 03:36 |
|
Im going out on a limb here, but I think you have to name the result of your inner SELECT if you do something with it (like your WHERE):code:
|
# ? Feb 8, 2008 03:55 |
|
Walked posted:But anyone know why it's erroring out on the WHERE clause? code:
|
# ? Feb 8, 2008 04:12 |
|
Walked posted:Fixed problem number 1! code:
|
# ? Feb 8, 2008 22:41 |
|
Victor posted:Did you see the following comment in my code? Yeah, I missed it because I was pulling from 3 - 4 references at the time. The alias did it - thanks
|
# ? Feb 8, 2008 22:53 |
|
If there's anything my explanation didn't cover, let me know and I'll fix it. I wrote it just for you!
|
# ? Feb 8, 2008 22:59 |
|
I have a TEXT field that contains comma separated tags. Is it even possible to create a query that returns each unique tag used?
|
# ? Feb 11, 2008 15:02 |
|
nbv4 posted:I have a TEXT field that contains comma separated tags. Is it even possible to create a query that returns each unique tag used? If changing your schema to actually have a table of separate tags and setting up a many to many relationship is an option, then do so. So you basically want to SELECT DISTINCT across essentially the entire list of tags which can only currently be found by splitting a TEXT field on commas? I'd be surprised if there was a way of doing it in one query. You might be able to build a temp table and then do a SELECT DISTINCT on that, but that's the only way I can think of that you'd be able to do that.
|
# ? Feb 11, 2008 15:14 |
|
No Safe Word posted:If changing your schema to actually have a table of separate tags and setting up a many to many relationship is an option, then do so. using another table for tags seems like too much work for such a simple tagging system that my project requires. Basically each user owns a bunch of items, and the tags are used to organize and create statistics within that user's items. There will never be a full-table tag search. I'm pretty sure a non-normalized solution is the way to go. I guess if worse comes to worse, I could just do something like: SELECT DISTINCT tags FROM `planes` WHERE user_id='7' which would probably return 5 or 6 rows, normally. Then use PHP to separate out the fat and create the list that way...
|
# ? Feb 11, 2008 15:40 |
|
nbv4 posted:using another table for tags seems like too much work for such a simple tagging system that my project requires. Well, that's the way you do things in SQL.
|
# ? Feb 11, 2008 15:52 |
|
m5 posted:Well, that's the way you do things in SQL. Then how would it be done? The user types in their tags. PHP splits the string up at the commas. A recursive function goes through each tag, and checks to see if it's already in the database. If it's not, it adds it, then gets the auto_incrimented value and sticks it into the item's 'tags' row. Thats like 5 or 6 queries. Why do it that way when you can just stuff it all into a TEXT column and be done with it? I'm not writing an enterprise level project here where the database has to fulltext through millions of rows. Each tag exists within each user. None of these tags need to exist "globally".
|
# ? Feb 11, 2008 16:25 |
|
nbv4 posted:Then how would it be done? If the tags are really "structural", then they can be discrete columns along with other stuff about the entity. If there's an uncertain, possibly expanding population of tags and tag values, however, then using another table is the "right" way to do it. (Aside: why do you say that your tag-checking function is recursive? Are there tags within tags?) There are a couple of ways to do the SQL, and it's not 5 or 6 queries. Your tag table would have a primary key of its own (probably), and then a column for the main entity ID, plus the tag name and value columns. How you manage the updates would depend on the way you load and save the entity. One possibility is that you load up the entity, manage the tag set as a map, and then save it all back. In that case, your SQL would (in one trip to the database) delete all the existing tag rows and then insert all the updated tags. You're welcome to jam all your tags together into a string if you like. I've gotten used to always thinking in terms of scalability, because that's the world I know.
|
# ? Feb 11, 2008 16:36 |
|
m5 posted:If the tags are really "structural", then they can be discrete columns along with other stuff about the entity. If there's an uncertain, possibly expanding population of tags and tag values, however, then using another table is the "right" way to do it.
|
# ? Feb 11, 2008 17:12 |
|
I am trying to query a few tables: homes and photos (columns id, home_id, and order_id) Homes can have many photos and is joined by the column home.id = photo.home_id I would like to get a set of homes with only one photo based on whichever photo has the lowest order_id If I try: code:
Can anyone point me to a way that I can have one row per homes result, with a column for the photo linked to it with the lowest order_id field? Thanks
|
# ? Feb 12, 2008 22:56 |
|
code:
|
# ? Feb 12, 2008 23:03 |
|
I changed it to:code:
|
# ? Feb 12, 2008 23:14 |
|
code:
|
# ? Feb 12, 2008 23:17 |
|
Perfect. I still have a lot to learn on the joins, but I think I'm in love with you.
|
# ? Feb 12, 2008 23:19 |
|
With SQL2005 row_number() syntax:code:
|
# ? Feb 12, 2008 23:39 |
|
Does anyone know a good replacement for query analyzer? I'm working in a .NET/SQLServer shop now and find Query Analyzer abysmal to use. I used PL/SQL Developer at my last job as an Oracle dev and found it to be robust, easy to use and cheap. It made it easy to view tables/records/store procedures and had a proper IDE for creating/debugging PL/SQL stored procedures. Basically, it was a stripped down version of TOAD.
|
# ? Feb 13, 2008 01:34 |
|
SQL Server Management Studio? It's not awesome, but it has functional undo/redo, among other things.
|
# ? Feb 13, 2008 06:53 |
|
Grigori Rasputin posted:Does anyone know a good replacement for query analyzer? I'm working in a .NET/SQLServer shop now and find Query Analyzer abysmal to use. I used PL/SQL Developer at my last job as an Oracle dev and found it to be robust, easy to use and cheap. It made it easy to view tables/records/store procedures and had a proper IDE for creating/debugging PL/SQL stored procedures. Basically, it was a stripped down version of TOAD. You mentioned Query Analyzer which implies Sql Server 2000 and not 2005, so I'll say that that Sql Server Management Studio Express is free and works great with Sql 2000, with the only gotcha that I think some of the script generations may not be 100% compatible with sql 2000. (Not sure on that, though.)
|
# ? Feb 13, 2008 07:06 |
|
I think the key item here is that if you're a DBA these days, you pretty much also need a copy of Visual Studio to truly round out your toolbox. Management Studio is pretty good, but not all the way there. I don't believe you can debug stored procs without it, and even the BI studio includes a stripped down version. Anything with SQLCLR definitely would benefit from it.
|
# ? Feb 13, 2008 18:57 |
|
ray2k posted:the only gotcha that I think some of the script generations may not be 100% compatible with sql 2000. (Not sure on that, though.)
|
# ? Feb 13, 2008 19:07 |
|
jwnin posted:I think the key item here is that if you're a DBA these days, you pretty much also need a copy of Visual Studio to truly round out your toolbox. Management Studio is pretty good, but not all the way there. I don't believe you can debug stored procs without it, and even the BI studio includes a stripped down version. Anything with SQLCLR definitely would benefit from it. Actually, Management Studio is based on Visual Studio. And the BI tools are visual studio, they just install a limited number of project templates. quote:Unless of course you're using SSIS in which case good luck trying to figure out what juju needs to be done to get the parameterization to work right. And if you do figure that out let me know Well, I did manage to call a parameterized stored proc from a script task once. But that was probably not what you wanted to hear.
|
# ? Feb 13, 2008 19:39 |
|
wwb posted:Well, I did manage to call a parameterized stored proc from a script task once. But that was probably not what you wanted to hear.
|
# ? Feb 13, 2008 22:27 |
|
SQL noob here. I'm trying to print out a sales report from a daily sales table. It must print out the top selling stores of each month of 1998. Here is the daily sales table: code:
Here is how I tried to do it: code:
|
# ? Feb 14, 2008 17:10 |
|
Grigori Rasputin posted:Does anyone know a good replacement for query analyzer? I'm working in a .NET/SQLServer shop now and find Query Analyzer abysmal to use. I used PL/SQL Developer at my last job as an Oracle dev and found it to be robust, easy to use and cheap. It made it easy to view tables/records/store procedures and had a proper IDE for creating/debugging PL/SQL stored procedures. Basically, it was a stripped down version of TOAD. I just found this yesterday. It's got its quirks, but still seems to be pretty great. Support looks good too -- I emailed the dev over a couple issues I have with 2005 support and was told to expect a fixed build in a couple days. Pretty sure I'll be buying it (if I can't get my company to) once the evaluation is up.
|
# ? Feb 14, 2008 17:19 |
|
Fart Shark, define "top selling stores". If you need only one record, I showed how to do this above when helping Stephen. What RDBMS are you using?
|
# ? Feb 14, 2008 17:44 |
|
|
# ? May 15, 2024 04:50 |
|
I need to return a table that has the top selling stores of each month. Meaning 12 records, 1 per month with the top selling store of that month. I am using SQL Server 2005
|
# ? Feb 14, 2008 19:45 |