SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

noonches posted:

If you're using php, you can call mysql_insert_id() after the insert.

You rock, that will make my life much simpler. I would never have got that, I kept reading "insert" as a verb. Why didn't they call it "mysql_get_the_autoincrement_value_from_that_last_insert_i_did()"?

# ? Jan 25, 2008 01:08

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 10:19

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Do you guys usually name your tables with the singular or plural form of the object?

# ? Jan 25, 2008 01:08

Tad Naff: Jul 8, 2004; _{I told you you'd be sorry buying an emoticon, but no, you were hung over. Well look at you now. It's not catching on at all!}

fletcher posted:

Do you guys usually name your tables with the singular or plural form of the object?

Historically I've done both, and mixtures of, which led to big unnecessary headaches. Now whenever I do a new project I'm more disciplined and only use the singular. Just pick one and be consistent -- though the plural doesn't give you any more information.

Unrelated, but here's a pro-tip from my own recent experience: don't use "field" as a column name. For some reason phpmyadmin lets you do that, but you won't be able to import the structure elsewhere later on.

# ? Jan 25, 2008 01:19

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Thanks Felonious Drunk.

Another question about using PHP/PDO: If my $dbh->prepare() statement fails, how do I get the information about the error? I'm having a hard time finding this with google.

edit: bah found it 2 seconds after I post this, of course.

It's:

code:

$stmt = $dbh->prepare("SELECT id FROM table");
if (!stmt)
 print_r($dbh->errorInfo());

edit2: Another PDO question now that I have figured out the error. I want to iterate over every row of a table and update information in another table, like this:

code:

$stmt = $dbh->query("SELECT id, thisfield FROM table1");
while ($row = $stmt->fetch()) {
	$substmt = $dbh->prepare("UPDATE table2 SET thatid = :id WHERE this = :that");
}

but I run into this error:

quote:

Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

The table is massive, I think fetchAll would be a bad idea. I'd like my code to be more portable so I want to avoid the mysql specific way. What do I do?

fletcher fucked around with this message at 02:13 on Jan 25, 2008

# ? Jan 25, 2008 01:45

USSR Lives On: Jun 10, 2003; asdf

Rumsfoord posted:

SELECT Count(attributes.item_id) AS item_attribute_type_count, attributes.tag_type, attributes.item_id
FROM attributes
GROUP BY attributes.tag_type, attributes.item_id

is that what you mean?

The problem with doing this is that if you join on the items table (which you need to do in order to get the rest of the info about that item), this will return one row for each type of attribute, so each item will be returned several times.

# ? Jan 25, 2008 04:17

brae: Feb 2, 2006

USSR Lives On posted:

The problem with doing this is that if you join on the items table (which you need to do in order to get the rest of the info about that item), this will return one row for each type of attribute, so each item will be returned several times.

code:

mysql> select * from items;
+---------+-----------+
| item_id | item_name |
+---------+-----------+
|       1 | foo       | 
|       2 | bar       | 
+---------+-----------+
2 rows in set (0.00 sec)

mysql> select * from attributes;                                
+---------+----------+----------+
| item_id | tag_name | tag_type |
+---------+----------+----------+
|       1 | red      | color    | 
|       1 | green    | color    | 
|       1 | heavy    | weight   | 
+---------+----------+----------+
3 rows in set (0.00 sec)

mysql> select a.item_id, i.item_name, a.tag_type, count(a.tag_type) as type_count 
          from attributes a inner join items i on a.item_id=i.item_id 
          group by a.item_id, a.tag_type;
+---------+-----------+----------+------------+
| item_id | item_name | tag_type | type_count |
+---------+-----------+----------+------------+
|       1 | foo       | color    |          2 | 
|       1 | foo       | weight   |          1 | 
+---------+-----------+----------+------------+
2 rows in set (0.00 sec)

Isn't that what you want - item id whatever has however-many tags of type whatever?

brae fucked around with this message at 05:50 on Jan 25, 2008

# ? Jan 25, 2008 05:41

Walked: Apr 14, 2003

This thread has been an amazing reference just flipping through so far.

However, while I feel like I have a solid foundation on SQL databases and queries and whatnot, I still find myself a bit fuzzy on "sound" database design principles (even basics of design really), and JOINs are still confusing me.

Can anyone suggest a reference or book or whatnot that covers these topics pretty much ground up?

edit: And is there any way that anyone knows how to run SQL statements in MSSQL one-by-one, without using their little query editor and having to hit F5 each time? CLI style basically.

# ? Jan 25, 2008 14:55

m5: Oct 1, 2001

Walked posted:

edit: And is there any way that anyone knows how to run SQL statements in MSSQL one-by-one, without using their little query editor and having to hit F5 each time? CLI style basically.

We have a Java CLI tool we wrote. It's probably very easy to build something like that in almost any language with facilities for talking to the database, and it's an instructive little project.

# ? Jan 25, 2008 15:21

Walked: Apr 14, 2003

m5 posted:

We have a Java CLI tool we wrote. It's probably very easy to build something like that in almost any language with facilities for talking to the database, and it's an instructive little project.

I suppose I can roll my own. Not too terribly much effort, but not high on my list

Worthwhile thought though, thanks.

Walked fucked around with this message at 15:37 on Jan 25, 2008

# ? Jan 25, 2008 15:32

wwb: Aug 17, 2004

Yes, there is a command line sql client. See osql.exe for Sql 2000 [I think] and sqlcmd.exe for Sql 2005.

# ? Jan 25, 2008 16:19

Walked: Apr 14, 2003

wwb posted:

Yes, there is a command line sql client. See osql.exe for Sql 2000 [I think] and sqlcmd.exe for Sql 2005.

Awesome! Thanks.

# ? Jan 25, 2008 16:35

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I thought of a radical idea on the train today. Brace yourselves.

Do you template your SQL?

Every time I see SQL intertwined with code (Perl in this case), I can't help but think... God. That is so ugly. Something is wrong... But I'm not sure what.

Then it dawned on me on the train... The concern can be seperated out with a templating engine. SQL, like HTML, is hardly ever useful in a 100% static form. Requests and desires are generated on the fly. HTML is easily handled by something like Mason (templating for Perl). So, shouldn't SQL be easily handled as well in a Mason context?

The idea seems far too radical, at least for me. In all four of my SQL-aware jobs, not one of them seperate the SQL out into different files. I feel like I'm really breaking ground with this one.*

What do you guys do, do you seperate out your SQL like you seperate out your HTML? Or does it just get jammed right into the code?

_{* Not really, I'm just being hyperbolic.}

# ? Jan 25, 2008 17:17

brae: Feb 2, 2006

We abstract as much of the data-management logic we can into stored procedures and functions. The middle tier knows stuff like "create this new model" or "find this model with these parameters", which map to stored procedures. It's the same benefit as abstracting functionality in any codebase, we can change the underlying schema or try new things for optimization and the calling code doesn't have to change.

I agree mixing web code/application code in with raw SQL is bad but I'd rather just put it all in the database as stored procedures rather than try to maintain a bunch of templates.

# ? Jan 25, 2008 17:24

npe: Oct 15, 2004

Triple Tech posted:

Do you template your SQL?

Every time I see SQL intertwined with code (Perl in this case), I can't help but think... God. That is so ugly. Something is wrong... But I'm not sure what.

Then it dawned on me on the train... The concern can be seperated out with a templating engine. SQL, like HTML, is hardly ever useful in a 100% static form. Requests and desires are generated on the fly. HTML is easily handled by something like Mason (templating for Perl). So, shouldn't SQL be easily handled as well in a Mason context?

The idea seems far too radical, at least for me. In all four of my SQL-aware jobs, not one of them seperate the SQL out into different files. I feel like I'm really breaking ground with this one.*

What do you guys do, do you seperate out your SQL like you seperate out your HTML? Or does it just get jammed right into the code?

_{* Not really, I'm just being hyperbolic.}

This is exactly what we do, but it's a home-grown system. The idea is that I can write a query with bind variables, and put it in a sql template file. It looks something like

code:


 [lookupSomeValue]

  SELECT blah 
    FROM table 
   WHERE column = :val

Then I can call $db->lookupSomeValue(-val => $whatever) directly and it manages preparing and caching the handle and everything. This works great because we have full time Oracle developers working here who like to be able to hunt through our queries and tweak their plans, and this way they can do so by just looking at pretty much straight sql.

For things that can't be done with bind vars (like in lists and subqueries), we also have more typical templating... such as

code:


 [dynamicQuery]

  SELECT blah
    FROM table
   WHERE column IN ({list})

Then I call $db->dynamicQuery(-list => join ',', @values) or whatever. The newest version even lets you nest query templates inside other templates, so you can nest one query as a subquery in another template, and then "push" multiple instances together when you call the method.

Overall it's been extremely successful for us. The result is we don't end up with raw SQL mixed into any perl code, ever. It's pretty nice.

# ? Jan 25, 2008 17:38

m5: Oct 1, 2001

Funny. I had the same thought the other day. Our app is all Java, so something like StringTemplate (the ANTLR thing) or a simple home-grown one that used OGNL or something would make a lot of things nice.

# ? Jan 25, 2008 18:29

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

Nobody knows the answer to my question?

# ? Jan 25, 2008 18:42

kalleboo: Jan 13, 2001; Hj�lp

fletcher posted:

edit2: Another PDO question now that I have figured out the error. I want to iterate over every row of a table and update information in another table, like this:

Nobody knows the answer to my question?

Most databases don't let you run queries at the same time, mysql is the exception. Either store the queries you want to run in an array and run them after you've closed the query, or open a second database connection.

# ? Jan 25, 2008 20:17

jwnin: Aug 3, 2003

I have a sql 2005 object permissions issue that I can't quite figure out.

One of our approaches to database security is to deny select/update/delete access to all tables, and then grant select/execute on views & stored procs.

This has worked well for us to date, except for a new server installation we've just done. In that case, when we execute sp_foo, it tells us that select permission is denied on tbl_foo (referenced by sp_foo). The exact object path is:

Windows Integrated login x assigned to user Y in the database.
User Y is granted to role Z in the database.
Role Z is granted select/execute on sprocs & views, and denied delete/insert/select/update/references on tables.

We've compared a known good server with this bad server, and they're both the same (9.0.3054), both on 64-bit sql server 2005.

# ? Jan 25, 2008 23:42

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

kalleboo posted:

Most databases don't let you run queries at the same time, mysql is the exception. Either store the queries you want to run in an array and run them after you've closed the query, or open a second database connection.

The problem is that I need the data from the first query in order to execute the second query. I upped the allowed memory size in php to 256MB and it let me do a fetchAll(), I was just trying to avoid doing that though.

# ? Jan 25, 2008 23:50

kalleboo: Jan 13, 2001; Hj�lp

fletcher posted:

The problem is that I need the data from the first query in order to execute the second query. I upped the allowed memory size in php to 256MB and it let me do a fetchAll(), I was just trying to avoid doing that though.

You can store the queries you want to run using the data from the first query in an array, and when the query-building is one, run them after you've closed the query. If there are too many queries to do that, you can open a second database connection to do the insertions. I've used both these methods in C# when I had to work with MSSQL.

# ? Jan 25, 2008 23:55

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

kalleboo posted:

You can store the queries you want to run using the data from the first query in an array, and when the query-building is one, run them after you've closed the query. If there are too many queries to do that, you can open a second database connection to do the insertions. I've used both these methods in C# when I had to work with MSSQL.

Ahhh I didn't think about storing the second queries, that makes sense. I will try this!

# ? Jan 26, 2008 00:09

brae: Feb 2, 2006

fletcher posted:

The table is massive, I think fetchAll would be a bad idea. I'd like my code to be more portable so I want to avoid the mysql specific way. What do I do?

Is the row you want to update in the second table (with the value from the first table) selected by a different column in the first table? I mean a variation on:

code:

UPDATE table2, table1 SET table2.thatid = table1.id WHERE table2.this=table1.thisfield;

# ? Jan 26, 2008 00:12

the onion wizard: Apr 14, 2004

This question popped into my head while reading another thread, but I figured it was more suited here. Also, I'm using Hibernate/HQL but I suspect the issue is pretty much the same regardless.

Let's say we've got two classes/tables, A and B, is there any way to select between the two in a query using parameter substitution rather than string concatenation?

# ? Jan 26, 2008 09:34

m5: Oct 1, 2001

triplekungfu posted:

This question popped into my head while reading another thread, but I figured it was more suited here. Also, I'm using Hibernate/HQL but I suspect the issue is pretty much the same regardless.

Let's say we've got two classes/tables, A and B, is there any way to select between the two in a query using parameter substitution rather than string concatenation?

Huh? I mean, the answer is probably "yes", but I don't know what "select between the two" means. Are you talking about a join?

# ? Jan 26, 2008 14:49

the onion wizard: Apr 14, 2004

m5 posted:

Huh? I mean, the answer is probably "yes", but I don't know what "select between the two" means. Are you talking about a join?

Sorry, I suspected I wasn't clear enough. I meant selecting from one table or another.

Currently we have something resembling the following:

code:

getReferenceCodes(String tableName) {

  //stuff...
  String query = "from " + tableName;

  hibernateTemplate.find(query);
}

What I'd like to know, is can I get away with something like this:

code:

getReferenceCodes(String tableName) {

  //stuff...
  String query = "from ?";

  hibernateTemplate.find(query, tableName);
}

It's not a huge issue, but all the string construction is loving ugly and it irks me.

Edit: This probably still doesn't make any sense; I'll try again after I've had some sleep. :saddowns:

the onion wizard fucked around with this message at 17:23 on Jan 26, 2008

# ? Jan 26, 2008 16:46

npe: Oct 15, 2004

You can't do that with parameterized queries on any RDBMS I've ever used. You will have to prepare a new query with the new table name if you want to change it. Now, whether the abstraction you are using has this built in or not, I don't know, but under the hood it would certainly be preparing a new query for you, as opposed to just binding in parameters.

# ? Jan 26, 2008 18:42

m5: Oct 1, 2001

... and that brings us back to the idea of using a templating system for plugging stuff into SQL statements.

# ? Jan 26, 2008 20:57

the onion wizard: Apr 14, 2004

Ok, I suspected that was the case. I just wasn't sure if I had missed something obvious. Thanks for the help.

# ? Jan 27, 2008 05:27

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

brae posted:

Is the row you want to update in the second table (with the value from the first table) selected by a different column in the first table? I mean a variation on:
code:
UPDATE table2, table1 SET table2.thatid = table1.id WHERE table2.this=table1.thisfield;

I didn't know you could do that! thank you, it was exactly what I needed

# ? Jan 27, 2008 09:20

Quantum Jiggles: Jul 22, 2007

I've been trying to locate good books and sites that go in depth on RDBMS performance issues.

Specifically, I have a Java system that uses ResultSet from the JDBC and PreparedStatement for every single query and I think this is causing real performance issues on huge (20G) databases where the code is doing hundreds of sequential queries. I suspect that cursor performance on PostgreSQL is really bad and should switch to using limit/offset, but I need more ideas to test.

So far I found this broad outline,

http://www.datadirect.com/developer/jdbc/topics/perfoptjdbc/index.ssp#anchor2

Anyone have other resources?

# ? Jan 27, 2008 17:15

Xae: Jan 19, 2005

quote:

Specifically, I have a Java system that uses ResultSet from the JDBC and PreparedStatement for every single query and I think this is causing real performance issues on huge (20G) databases where the code is doing hundreds of sequential queries. I suspect that cursor performance on PostgreSQL is really bad and should switch to using limit/offset, but I need more ideas to test.

That is your problem. Figuring out what a query wants, especially for a small query, will take longer than retrieving the data.

You need to figure out exactly what queries are being run against the database. There is no RDBMS on the planet that will perform great with bad code running against it.

The majority of tuning is done at an application/design level. It isn't done to database settings, or even individual queries. Your system has to be designed to perform well, and throwing hundreds of queries at a database is designing to kill the DB.

# ? Jan 28, 2008 04:29

Quantum Jiggles: Jul 22, 2007

Xae posted:

You need to figure out exactly what queries are being run against the database. There is no RDBMS on the planet that will perform great with bad code running against it.

Yeah, this is what I fear. The application is in fact doing this globally as a common pattern. I'll probably have to rip out the custom ORM layer and replace it with Hibernate or something that will allow intelligent queries, then rewrite all the inefficient methods. :sigh:

Quantum Jiggles fucked around with this message at 06:02 on Jan 28, 2008

# ? Jan 28, 2008 06:00

m5: Oct 1, 2001

Quantum Jiggles posted:

Yeah, this is what I fear. The application is in fact doing this globally as a common pattern. I'll probably have to rip out the custom ORM layer and replace it with Hibernate or something that will allow intelligent queries, then rewrite all the inefficient methods.

I don't know what your application does, but one architectural thing that can be a problem is for the app to use the ORM layer as a means of producing views of large aggregations of objects (like, tables). Doing a query per row to produce a tabular "inventory" view is never going to be as good as producing the view with a single query.

# ? Jan 28, 2008 14:06

SuckerPunched: Dec 20, 2006

Ok, so I'm primarily a LAMP developer, but for a side project I'm working on the codebase is PHP on a Win2k3 box, with MSSQL 2005 as a database. I'm a little out of my element, and I just can't seem to find on Google what I am expecting for this question:

In MySQL, to escape data before insertion, there's the lovely mysql_real_escape_string function - I'm looking for the MSSQL equivalent. Everything I've seen online simply says to replace the 's with ''s, but this can't be the it. I mean, I guess it could be but that just seems like a very minimal amount of work to prevent against injection attacks.

Am I missing something, or is that really all that needs to be done? I read through the thread but could have missed if this has already been answered, so sorry if this is a repeat.

# ? Jan 28, 2008 14:42

Xae: Jan 19, 2005

Is there a reason why the SQL was being generated instead of hand written? I know I'm a huge DB nerd, but I always like to right the SQL myself.

# ? Jan 28, 2008 14:43

No Safe Word: Feb 26, 2005

SuckerPunched posted:

Ok, so I'm primarily a LAMP developer, but for a side project I'm working on the codebase is PHP on a Win2k3 box, with MSSQL 2005 as a database. I'm a little out of my element, and I just can't seem to find on Google what I am expecting for this question:

In MySQL, to escape data before insertion, there's the lovely mysql_real_escape_string function - I'm looking for the MSSQL equivalent. Everything I've seen online simply says to replace the 's with ''s, but this can't be the it. I mean, I guess it could be but that just seems like a very minimal amount of work to prevent against injection attacks.

Am I missing something, or is that really all that needs to be done? I read through the thread but could have missed if this has already been answered, so sorry if this is a repeat.

Use parameterized queries instead. That will handle the escaping for you. Escaping functions are dicey and error-prone at best (not the functions themselves, but using them).

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 :v:

# ? Jan 28, 2008 17:36

SuckerPunched: Dec 20, 2006

I've never really dealt with parameterized queries in PHP, and Google is telling me that it's only available through the MySQLI classes - which don't really help me with MSSQL. I haven't dealt with MSSQL in years and even then it was in Classic ASP so I'm a bit lost

# ? Jan 28, 2008 18:05

Golbez: Oct 9, 2002; 1 2 3!
If you want to take a shot at me get in line, line
1 2 3!
Baby, I've had all my shots and I'm fine

So I've given up on FULLTEXT in MySQL, it's not robust enough for my needs. I have a PHP site using a database of (at present) 13,000 entries, each with 5 text fields that need searching. The stupidity of FULLTEXT failing to find things like "testrange" and "test ranges" for the search of "test range", yet can find "test; range" has to stop, among other needed features.

I started looking (and implemented in a test environment) Lucene, via the Zend Search Lucene part of the Zend Framework, but indexing is quite slow. My plan is to use the Lucene engine to search its index for the terms, then plug those IDs into a MySQL query to get the rest of the information. But I wanted to ask about if anyone knows much about the other search engines, the main ones appear to be Sphinx and Xapian. Xapian in particular looks good, with stemming and such. Oh, and part of going to a separate indexer like this is that I will be able to index the (much more than 13,000) PDFs and DOCs on the site, files that are unsearchable in my system at present.

Any comments on Xapian or the others? Or am I going about this totally the wrong way?

# ? Jan 28, 2008 20:46

Fly: Nov 3, 2002; moral compass

m5 posted:

I don't know what your application does, but one architectural thing that can be a problem is for the app to use the ORM layer as a means of producing views of large aggregations of objects (like, tables). Doing a query per row to produce a tabular "inventory" view is never going to be as good as producing the view with a single query.

Does anyone here know whether Hibernate Query Language (and the JPA's QL implementations) supports the generation of dynamic multi-table queries with the correct JOINing of secondary tables?

# ? Jan 28, 2008 21:41

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 10:19

fletcher: Jun 27, 2003; ken park is my favorite movie; Cybernetic Crumb

SuckerPunched posted:

I've never really dealt with parameterized queries in PHP, and Google is telling me that it's only available through the MySQLI classes - which don't really help me with MSSQL. I haven't dealt with MSSQL in years and even then it was in Classic ASP so I'm a bit lost

You can use prepared statements in PHP with PDO, which has a MSSQL driver for it. I've only used it for MySQL but I assume it's used the same way.

# ? Jan 28, 2008 22:27

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »