|
pig-fukr posted:They have. In 2008 you get table-valued parameters that can be passed around, provided you're willing to strongly type them and read-only access will do what you need:
|
# ? Nov 7, 2007 22:03 |
|
|
# ? May 13, 2024 06:56 |
|
Victor posted:Sick! Is there some place on the net where all the features of 2008 (new over 2005/2000) are described, and described well? I've seen several overviews of what is coming in 2008 (like intellisense, yay!), but I haven't yet seen the above. I just glanced at 2008 Books Online and it does not seem conducive to learning about the new features. Most of the stuff I've found that was actually useful for learning how to REALLY use features have been by googling the specific feature you want to work with. There just doesn't seem to be a good central repository at the moment. This isn't a bad starting point to get a little overview. If you click on the main category icons it will launch into presentations about what's new in each deal. You can also click on each individual item to get more information. This is what's available for table-valued parameters. The main CTP page can be found Here if you don't already frequent it. I think table-valued parameters and the addition of a spatial engine are going to be the biggest draws for my purposes. The other stuff is nice, but those are items I could use a daily basis. pig-fukr fucked around with this message at 22:38 on Nov 7, 2007 |
# ? Nov 7, 2007 22:23 |
|
chocojosh posted:replace(varchar_variable, '''', '''''') You would need to check for ';' as well, since they might try to submit a compound statement. ie "Select * from sys;Drop X;"
|
# ? Nov 8, 2007 00:06 |
|
chocojosh posted:replace(varchar_variable, '''', '''''') [0] assuming that anything that doesn't go through the replace function above is strongly typed into something other than a string, e.g. int, float, date, etc. [1] that process the input from initial text input -> when the database query optimizer gets its hands on the query Xae posted:You would need to check for ';' as well, since they might try to submit a compound statement. ie "Select * from sys;Drop X;"
|
# ? Nov 8, 2007 00:41 |
|
This is a bit of a different SQL question, but I thought I'd ask it here. Our database stores information about our customers. In general, there are very few times where we need to do queries across the entire customer base - what happens at one customer has absolutely no relation to the other ones, other than our people who are assigned to that customer. As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size. In general I do not feel that this is a good idea, if only because at some point you start to reach SQL's native limitations- number of databases, SP reuse in caching, and even just raw performance for backup/restore/indexing operations. Beyond that, when you do eventually have some level of cross-customer querying/reporting to do, it will not be very performant to query this, even if you go with a data waarehouse solution. Can anyone provide me with some more ammo as to why we would not want to go down this path?
|
# ? Nov 8, 2007 17:29 |
|
From a SQL injection standpoint, I would just use an enum to enumerate the table options and pass that parameter to the method you are calling this from. You can't inject squat in SomeEnum.ToString().
|
# ? Nov 8, 2007 18:12 |
|
jwnin posted:Can anyone provide me with some more ammo as to why we would not want to go down this path? Did the devteam also volunteer to manage, troubleshoot, and update those tens of thousands of databases? It's a horrible idea and whoever suggested it should be torn into little bitsy pieces and buried alive.
|
# ? Nov 8, 2007 20:06 |
|
Here's an interesting question. I have a database that houses customers and users, and a RADIUS server uses the database for authentication. We also keep logs of data usage for accounting purposes, and I have an ASP.NET application that generates reports that the customers can view. The customer selects which users will be included in the report with two listboxes (selected/unselected). Some customers have thousands of users. This means that the SQL generated for the reports becomes pretty hairy if they select lots of units. Something along the lines of this... code:
|
# ? Nov 8, 2007 20:35 |
|
Hairy == slow? Ugly?
|
# ? Nov 8, 2007 21:06 |
|
Victor posted:Hairy == slow? Ugly? Slow, ugly, and I think I'll be hitting a limit soon as far as characters allowed in a SQL statement. I believe it tops out at 8000 characters, correct?
|
# ? Nov 8, 2007 21:20 |
|
I don't think there is some solid limit -- the 8000 number just comes from the maximum storage length of char, varchar, binary, varbinary, in SQL 2000 and earlier (2005 has [n]varchar(max), and varbinary(max)). Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option. If so, I guess a temporary table would be the best approach, albeit ugly.
|
# ? Nov 8, 2007 21:52 |
|
You're pretty much in the same situation as Dakha was on page 1. If you're going to need to scale it up, a temp table is the way to go. I'm pretty sure 8000 characters isn't the limit for a query. That's the max size of the usual text variable types.
|
# ? Nov 8, 2007 21:53 |
|
Victor posted:Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option. This is the main problem. I've told them repeatedly that common sense dictates that no one in their right mind would select 6879 individual entries out of 12543 to generate a report, and that it should be "either select up to XXX users, or all users". I can't win with these clowns though. I'll go back and look over Dakha's post and the resulting solution to see what I can come up with. I'll also try to push the "X or all" argument on them again to see if I can get them to agree with me (not likely).
|
# ? Nov 8, 2007 22:18 |
|
Victor posted:I don't think there is some solid limit -- the 8000 number just comes from the maximum storage length of char, varchar, binary, varbinary, in SQL 2000 and earlier (2005 has [n]varchar(max), and varbinary(max)). Selecting that many IDs from a GUI is a bit questionable, so if it is possible, I would just put a limit on that (say, to a 100 entries), and also have a "select all" option, in which case you wouldn't use in. However, people can be retarded and require that users be allowed to select hundreds of entries, so this might not be an option. If so, I guess a temporary table would be the best approach, albeit ugly. SQL Server seems to have issues with the size of individual "IN" clauses, but in our experience it works OK if the lists are split up into separate IN clauses that are connected with OR. In our application, it isn't a case of idiot customers, and selecting thousands of things to operate on makes perfect sense.
|
# ? Nov 8, 2007 22:37 |
|
jwnin posted:As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size. If what you're saying is that for any given customers there's a foozle table and a thingy table, and each customer may have 50 foozles and 1000 thingies, then one possibility is to look into clustering (if your DBMS supports it; MS SQL Server does). If you keep your customer ID on each table, and cluster on that (it does not have to be the primary key), then as long as you make sure to include customer ID when you do queries (incl your joins) then it's almost like having separate tables for each customer. It'll slow your inserts down a little, but not much, and your searches will be much faster.
|
# ? Nov 8, 2007 22:41 |
|
m5 posted:In our application, it isn't a case of idiot customers, and selecting thousands of things to operate on makes perfect sense.
|
# ? Nov 9, 2007 01:09 |
|
Victor posted:I'm assuming they aren't clicking thousands of items? My "idiot" comment was predicated upon that happening. Well it wouldn't surprise me, but they certainly don't have to do that. Actually we're kind-of dumb about how we do this. We provide "queue" views with filters and stuff, and things can be multi-selected or drag-selected or whatever. The easiest thing to do is to use the filters to get what you want and then click "select all". If we were smart, we'd then couple the filtering onto the running of the operation direction. (Actually most operations, for various reasons, have to go one-at-a-time anyway.)
|
# ? Nov 9, 2007 01:12 |
|
jwnin posted:As a result, our devevelopment team thinks that the best thing to do is to split each customer out into its own database. The catch is that there are around 20,000 customers per year, and each customer database would be about 100 MB in size. The more disparate databases you have, the more work you'll have to do when it comes to setup, backup and restore, and ensuring schema changes are propagated to each database. And it's utter hell to do any sort of report that requires data from multiple databases. Depending on the complexity, a cross-database report would have to extract the necessary info from each database, insert it into a temporary database , and then run the report. That's very slow. I speak from experience: our company had a database built around our product, and then management surprised us by announcing a new product. We didn't have time to rejig the database to support multiple products, so we simply duplicated the database schema and kept them separate, hoping that some day we'd be able to merge the two. It's caused us a load of grief and time and I wish we'd kept it all in one.
|
# ? Nov 9, 2007 02:19 |
Is there any reason to use PDO for my abstraction layer if I'm using mysql and will always use mysql for this app?
|
|
# ? Nov 9, 2007 04:17 |
|
jwnin posted:This is a bit of a different SQL question, but I thought I'd ask it here. Just because the data does not all need to be accessed at the same time doesn't mean that they should be in separate databases. They are logically the same entity type so they should logically be grouped together. From personal experience I have worked on systems where 15 years before someone said "Just clone the DB and put it in a different schema, we will work something out later", and 15 years later the solution was to pay through the rear end for an outsider to come in and unfuck their mess. Xae fucked around with this message at 06:24 on Nov 9, 2007 |
# ? Nov 9, 2007 06:20 |
|
I'm using Oracle. What's the best way to do something similar to WHERE name IN (a set of values), except instead of checking for equality, we use the LIKE keyword? It would end up being functionally the same as WHERE name LIKE val1 OR name LIKE val2 OR name LIKE val3, except the number of right hand values can change. Currently, what I'm doing is pre-preparing the SQL by iterating over the list of possible names (which are not retrieved from the DB) and appending "OR name LIKE ?" each time.
|
# ? Nov 9, 2007 07:50 |
|
Argue posted:I'm using Oracle. What's the best way to do something similar to WHERE name IN (a set of values), except instead of checking for equality, we use the LIKE keyword? It would end up being functionally the same as WHERE name LIKE val1 OR name LIKE val2 OR name LIKE val3, except the number of right hand values can change. Currently, what I'm doing is pre-preparing the SQL by iterating over the list of possible names (which are not retrieved from the DB) and appending "OR name LIKE ?" each time. code:
|
# ? Nov 9, 2007 08:38 |
|
minato posted:Perhaps you can build a more general Perl-compatible regex, and that'll only use 1 statement? E.g.: Ah, great. That sounds cleaner than mine; the sets I'm expecting aren't all that large so it shouldn't waste too much time. Thanks.
|
# ? Nov 9, 2007 08:56 |
|
jwnin posted:(...) Are the database running on your server for your clients (IE they are using your server all the time) or is it just a backup you want to have access to ? Well, at first, updating thoses databases is gonna be a BITCH. "Hey joe, can you fix that stored procedure in the clients databases ? Sure boss, let me run that query in 1097 database, I'll be right back !" The risk of mistales is big too, because one or a few clients DB could end up different from the rest because of all that updating. comparing all theses database is also gonna be a bith, even with great tools. Also, you absolutely WON'T be able to query all clients at once, even if your boss thinks he's never gonna need it, he WILL. "Hey let's find out if somebody had that bug where the transactions were written all reversed..." Caching is gonna be ugly, if all clients use stored procedure X, they all gonna end up with a cached copy of the plan for that SP, even if it's exactly the same SP. That will mean fewer cached plans since you'll have so much identical plans cached (1000 copies of storedproc X's plan will be flushed when they try to run storedproc Y 1000 times too.
|
# ? Nov 9, 2007 12:55 |
|
OK, so you have databases which exist within a mysql installation. Within each database, you have tables. In each table you have fields. Is all that correct? For a website project, should I have ALL of it in one database, each user in their own table, and then user details as a field in the tables, or do I have all this messed up? I'm having trouble wrapping my brain around how data is represented in mysql. Most beginner tutorials focus on specific commands (which I can just learn just fine by reading code examples) and have nothing on the kind of stuff I'm having trouble with. Basically I'm wanting to make a site that has a ton of users, and each user has a ton of data that is stored in the database (with no data shared between any other users). I'm got phpmyadmin installed, and when I create a new table, it wants me to give a name for the table, and I don't know if I should put "bob", "user_name", "users", or what-the-gently caress. nbv4 fucked around with this message at 10:07 on Nov 12, 2007 |
# ? Nov 12, 2007 10:04 |
|
Imagine that you want to enter the user information into an Excel spreadsheet, like so:code:
So if you want to look up the information for user "bob", then you might use the SQL: code:
minato fucked around with this message at 10:17 on Nov 12, 2007 |
# ? Nov 12, 2007 10:12 |
|
minato posted:Edit: To elaborate, a table consists of a list of 0-many rows. Each row represents all the information about some "thing". A row consists of various fields that describe that single thing. So examples of tables would be "users", "projects", "products", etc. Each row in those tables describes a single user, project or product. The fields depend on the thing you're trying to describe. A user might have an ID, name, DOB, etc. A product might have an ID, name, quantity available, etc. OK I think I'm starting to get it now. One more question. Is there a guide somewhere that helps you determine which data type is the best for certain kinds of data? For instance, whats the best type to use to store a users email address? What if you have a value that only has a small number of values? Such as, for example, car manufacturer. Lets say there are only 5 possible manufacturers. Would it be best to store that data as either "1,2,3,4,5", then have each number correlate to a manufacturer, or would it be best to just store it as a string? Or what about setting up 5 different boolean rows for each manufacturer? Would it matter which way you did it?
|
# ? Nov 12, 2007 11:19 |
|
nbv4 posted:One more question. Is there a guide somewhere that helps you determine which data type is the best for certain kinds of data? For instance, whats the best type to use to store a users email address? What if you have a value that only has a small number of values? Such as, for example, car manufacturer. Lets say there are only 5 possible manufacturers. Would it be best to store that data as either "1,2,3,4,5", then have each number correlate to a manufacturer, or would it be best to just store it as a string? Or what about setting up 5 different boolean rows for each manufacturer? Would it matter which way you did it? To store a car manufacturer, there are lots of ways you could do it, and there are pros and cons to each way. If you chose to use a string and store it directly (e.g. "Chrysler"), then it's easy to just browse the car rows and see which car corresponds with which manufacturer. However most people will use integers because it's more space efficient, faster for the database to process, is less prone to spelling/case errors, and is less effort to update if the name ever changes (say, due to a typo). Most people would set it up like this: code:
|
# ? Nov 12, 2007 11:34 |
|
Bringing the discussion back to SQL Server 2005 for a quick moment. There is one add-on that would help me save HOURS of work. I'd love it if I have some text highlighted, that one of my right click options would be to "go to definition" for a stored proc, user defined function (and even table, although I'd prefer just a select *) For example, right now I want to see a stored procedure called up_extractfirstvalue because I am not sure how it is being used in my stored procedure. If I could simply highlight up_extractfirstvalue, right click, and select "Go To definition" instead of finding it in our list of thousands of stored procs, it would be so helpful!
|
# ? Nov 12, 2007 16:19 |
|
chocojosh posted:Bringing the discussion back to SQL Server 2005 for a quick moment. In SQL Server management studio, just map CTRL-F1 to "sp_helptext". Go to Tools > Options > Environment > Keyboard. Then, all you have to do is higlight a storedproc / table / function, and press CTRL-F1. Edit:
|
# ? Nov 12, 2007 16:31 |
|
Alex007 posted:In SQL Server management studio, just map CTRL-F1 to "sp_helptext". Great tip! Would be better if it opened a new window and allow me to edit the query also (although I didn't ask for that initially, I know).
|
# ? Nov 12, 2007 16:58 |
|
chocojosh posted:Great tip! I agree, that would be even cooler. I just end up copy-pasting what I need into a new window and modify from there. On thing that [b]SUCKS[b] with that, when doing that for a stored proc, each line is padded with some spaces (usually 3). It doesn't do anything wrong, but it's frustrating to press END and end up 3 spaces after the last character of that line. And if you do alter the stored proc from that script, the spaces are saved with the script and stay forever (until osmeone removes them).
|
# ? Nov 12, 2007 19:04 |
|
code:
code:
|
# ? Nov 14, 2007 07:06 |
|
Not closing the </form>?
|
# ? Nov 14, 2007 07:32 |
|
minato posted:Not closing the </form>? that was what it was, I feel stupid. Now I've got another problem that is stumping me. Here is my query: $result = mysql_query("SELECT * FROM pilots WHERE email=$email and password=" . MD5($password); then below that, I have: $row = mysql_fetch_row($result); print $row[0]; which should print out something, but it's giving me this error: Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /var/www/logbook/login-script.php on line 20 makes me believe the problem is with my query string. I copied the string exactly as how it comes from php: SELECT * FROM users WHERE email=nbvfour@gmail.com and password=a4f9767401ed74edc2321b7b1bc7790e into phpmyadmin, and it says it's an invalid query string. I'm thinking I'm supoosed to escape the '@' but I can't seem to find anything on google.
|
# ? Nov 19, 2007 05:52 |
|
You need to put the parameters into quotes. Try something like this:php:<? $sql = sprintf( "SELECT * FROM pilots WHERE email='%s' AND password='%s'", mysql_real_escape_string($email), mysql_real_escape_string(md5($password)) ); $result = mysql_query($sql); ?>
|
# ? Nov 19, 2007 05:57 |
Let's say I've got a simple users table likecode:
code:
code:
SELECT users.name, messages.to, messages.message FROM messages, users WHERE messages.author = users.id I'm running MYSQL5.
|
|
# ? Nov 21, 2007 11:12 |
|
My SQL is a little rusty but here's a shot at itcode:
|
# ? Nov 21, 2007 11:42 |
Bonus posted:My SQL is a little rusty but here's a shot at it What does the INNER do? What happens if you take it out?
|
|
# ? Nov 21, 2007 12:00 |
|
|
# ? May 13, 2024 06:56 |
|
fletcher posted:What does the INNER do? What happens if you take it out? It JOINs tables for a SELECT, you don'T want to take it out, that'S your solution. It's like your "WHERE a.id = b.id" clause, that's a JOIN too, but that's the old way to join tables.
|
# ? Nov 21, 2007 12:59 |