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 »

solarjetman: Jan 27, 2001; Fun Shoe

One other advantage of sequence-driven integer synthetic keys is that you know for sure the order in which rows are inserted. This is not always important, but it is occasionally very important.

# ? Apr 27, 2010 04:33

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 17:40

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

I'm looking for a database solution and I was wondering what your thoughts are...

I work for a company that does product testing, and each time our product is tested, large amounts of data is produced.

By large, I mean the equivalent to 10,000 - 100,000 rows of data per test.

(We are testing planes and recording information based on every instant, so there is a ton of data).

So, here are the specs of this database:
1) It will have an enormous number of rows for some tables, lets just say an indefinite number.

2) for our database, only about 3-5 users, so not a lot of demand on it*

3) Almost every other day, certain tables will be dumped and downloaded.. so the speed of doing queries should be reasonably fast.

4) It needs to work with Matlab

What system do you suggest? Oracle, MS SQL Server 2005, MySQL good enough, etc? Comedy Access 2007 option?

*The reason for only 3 users is because our database is locked down and thus only certain data can be transmitted by manually dumping. The open version of this database will be Oracle 10g, but that is a separate server. The open version will have lots of demand, 1000 of users using it constantly, so that has to be a superhero level server.

What database do you think will work for us here? If Oracle, what kind of server or would a software solution work? (We currently have very powerful, but generic server computers in our area). What kind of price are we looking at?

(I called up Oracle and they claimed they have no pure software version of their database and wanted upward of $30k... I could have sworn there is a software version of Oracle..)

# ? Apr 27, 2010 04:36

Vanadium Dame: May 29, 2002; HELLO I WOULD LIKE TO TALK TO YOU ABOUT MY VERY STRONG OPINIONS

cannibustacap posted:

I'm looking for a database solution and I was wondering what your thoughts are...

I work for a company that does product testing, and each time our product is tested, large amounts of data is produced.

By large, I mean the equivalent to 10,000 - 100,000 rows of data per test.

(We are testing planes and recording information based on every instant, so there is a ton of data).

So, here are the specs of this database:
1) It will have an enormous number of rows for some tables, lets just say an indefinite number.

2) for our database, only about 3-5 users, so not a lot of demand on it*

3) Almost every other day, certain tables will be dumped and downloaded.. so the speed of doing queries should be reasonably fast.

4) It needs to work with Matlab

What system do you suggest? Oracle, MS SQL Server 2005, MySQL good enough, etc? Comedy Access 2007 option?

*The reason for only 3 users is because our database is locked down and thus only certain data can be transmitted by manually dumping. The open version of this database will be Oracle 10g, but that is a separate server. The open version will have lots of demand, 1000 of users using it constantly, so that has to be a superhero level server.

What database do you think will work for us here? If Oracle, what kind of server or would a software solution work? (We currently have very powerful, but generic server computers in our area). What kind of price are we looking at?

(I called up Oracle and they claimed they have no pure software version of their database and wanted upward of $30k... I could have sworn there is a software version of Oracle..)

Oracle is (imo) really the only viable DB instance implementation to use for large enterprise solutions. It doesn't sound like you need that scale of things (10-100k rows is not a Lot Of Data) and Oracle is loving Expensive so I'd go for postgres or mysql for your app. YMMV.

No loving clue on the Matlab thing.

# ? Apr 27, 2010 04:44

solarjetman: Jan 27, 2001; Fun Shoe

It seems like you need a lot of raw power but not a lot of complexity or special features.

I would lean towards a free database solution rather than Oracle; take the money hats you'd spend on Oracle licensing and spend it on speedy hardware with a fast disk array.

So that gets us down to postgresql vs. mysql; I know nothing about MS SQL Server, other than it's probably cheaper than Oracle because everything is cheaper than Oracle. What kind of queries will you be using to dump your data? If you will need to join large numbers of rows from multiple tables together, go with postgresql, because MySQL does not yet support hash joins as far as I know. But if all you're going to do is grab a day's worth of data from each table individually and dump it to the 1000-user Oracle database, MySQL can probably do just fine.

As for Matlab, if you just need to input a bunch of data to it, I would hope that it can work with simple csv files which any db product can dump; in fact it would be pretty appalling if it couldn't.

# ? Apr 27, 2010 05:26

MoNsTeR: Jun 29, 2002

Markoff Chaney posted:

The other thing it boils down to is if you have to add a synthetic PK *later* instead of just adding it *now* it's going to be a horrible long running script to do so instead of a quick little bit of DDL out the gate.

This is huge. We have a couple of multi-million-row tables that lack synthetic keys and it just kills me on a daily basis. At one point we applied a synthetic key to a core table that originally lacked one, and the number of man hours of development and testing it took was just criminal.

Also while I was in college (and working for campus IT) my school switched away from using SSNs as student identifiers to a synthetic ID number. It took about 2 years, during which time the DBAs did almost nothing else.

# ? Apr 27, 2010 17:04

No Safe Word: Feb 26, 2005

MoNsTeR posted:

Also while I was in college (and working for campus IT) my school switched away from using SSNs as student identifiers to a synthetic ID number. It took about 2 years, during which time the DBAs did almost nothing else.

Also, a corollary lesson: don't ever use SSNs for unique identifiers. They're not unique and not everyone has one.

# ? Apr 27, 2010 17:08

I like turtles: Aug 6, 2009

I've looked through about 20 pages of the thread and didn't see, I apologize if this has been covered.

Are there any apps where you can automatically generate at least a basic PHP web interface to an existing SQL database you have rights to? Any for OS X?

I'm working on an internal tool for work, and it doesn't need to be pretty, we just need to be able to easily control how information is dumped to screen. Being able to edit and save to the DB would be valuable as well.

Edit:
Ok, different question, and this is hopefully really simple.
How do I verify that a record I am inserting is not identical to another record in the table, except for the unique auto-incrementing ID?

I like turtles fucked around with this message at 03:15 on Apr 28, 2010

# ? Apr 27, 2010 23:49

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I like turtles posted:

Ok, different question, and this is hopefully really simple.
How do I verify that a record I am inserting is not identical to another record in the table, except for the unique auto-incrementing ID?

This depends somewhat on

- what you want to happen if the record is indeed identical
- what the table schema is.

Best case scenario:

- if the record is identical, you want to not insert a new record at all
- all of the columns in the table are NOT NULL
- the number of columns in the table is not excessive

In this case, you just add a UNIQUE KEY on all of the columns bar the ID column.

Why are those three things important:

- If there's a UNIQUE KEY and you try to insert a duplicate row, the insert will not happen. (You will get an error message back, unless you use INSERT IGNORE (may be MySQL only).)
- If there are nullable columns in the table, the RDBMS will permit rows that are duplicates except for placement of NULL values
- If there are a lot of columns in the table, a UNIQUE KEY on them might be unwieldy for your DBMS to manage.

What you could do if your design does not satisfy those three things:

- Check in application logic that the record you are inserting does not match another in the table

# ? Apr 28, 2010 13:18

I like turtles: Aug 6, 2009

Hammerite posted:

This depends somewhat on

- what you want to happen if the record is indeed identical
- what the table schema is.

Best case scenario:

- if the record is identical, you want to not insert a new record at all
- all of the columns in the table are NOT NULL
- the number of columns in the table is not excessive

In this case, you just add a UNIQUE KEY on all of the columns bar the ID column.

Why are those three things important:

- If there's a UNIQUE KEY and you try to insert a duplicate row, the insert will not happen. (You will get an error message back, unless you use INSERT IGNORE (may be MySQL only).)
- If there are nullable columns in the table, the RDBMS will permit rows that are duplicates except for placement of NULL values
- If there are a lot of columns in the table, a UNIQUE KEY on them might be unwieldy for your DBMS to manage.

What you could do if your design does not satisfy those three things:

- Check in application logic that the record you are inserting does not match another in the table

I ended up doing the last option, as unique keys on a per column basis wouldn't pull off what I needed, all of the following are valid, but unless there's a way to have a unique index cover multiple columns I guess check-at-insert is probably easiest:

code:

  ID    Col A   Col B   Col C
  1       X       Y       Z
  2       X       Y       Q
  3       V       Z       Z
  4       V       Y       Q

# ? Apr 28, 2010 16:44

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I like turtles posted:

I ended up doing the last option, as unique keys on a per column basis wouldn't pull off what I needed, all of the following are valid, but unless there's a way to have a unique index cover multiple columns I guess check-at-insert is probably easiest:

Having a unique key over 3 columns is no problem, my thing about not having too many columns was just to note that if there are, like, 50 columns, it probably wouldn't make sense to try and put on a unique index. In fact, checking I see that MySQL does not permit an index to be more than 16 columns. For a given storage engine there might also be a limit on the byte length of an index.

You could add a unique index on your table using (assuming MySQL)

ALTER TABLE `TableName` ADD UNIQUE INDEX `TableName_uk` (`Col A`, `Col B`, `Col C`)

# ? Apr 28, 2010 16:53

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

I think you misunderstood me - when I said you could "put a unique index on all the columns", what I meant was a siongle unique index, rather than individual ones for each column.

# ? Apr 28, 2010 16:56

RoadCrewWorker: Nov 19, 2007; camels aren't so great

Depending on your table design, normalization and functional dependencies shouldn't the primary key already be sufficient to set the uniqueness of records?

Then again if you don't have the mindset of multi-column indices or have an excel-style 'database' this probably doesn't mean much to you.

# ? Apr 28, 2010 20:18

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

RoadCrewWorker posted:

Depending on your table design, normalization and functional dependencies shouldn't the primary key already be sufficient to set the uniqueness of records?

Then again if you don't have the mindset of multi-column indices or have an excel-style 'database' this probably doesn't mean much to you.

We literally just had this discussion at the end of last page/start of this one.

# ? Apr 29, 2010 00:38

Chumpington: Jan 17, 2010; by Ozma

Stupid question here but TOAD is throwing errors at me where i don't think it should :saddowns:

First one is "Identifier is too long" and the 2nd is "Invalid identifier"

The piece of code it complains about for the first time is this bit

code:

create table Qualification
(
);

The second bit is this one

code:

create table Person
(
);

Chumpington fucked around with this message at 10:28 on Apr 29, 2010

# ? Apr 29, 2010 10:01

Victor: Jun 18, 2004

You need at least one column in a create table statement?

# ? Apr 29, 2010 11:00

Chumpington: Jan 17, 2010; by Ozma

Victor posted:

You need at least one column in a create table statement?

Oh sorry. Yes there was stuff in there,it was just the names it was complaining about though.

edit: here is both bits

code:

create table Qualification
(
Abbreviation varchar2(10)
constraint Qualification_Abbreviation_Null	not null,
Full_Name varchar2(100)
constraint Qualification_Full_Name_Null	not null,
Type varchar2(100)
constraint Qualification_Type_Null	not null,
constraint Type_Type_Invalid,
			check (Type in ('Degree','Diploma','Certificate' )),
--
			constraint Qualification_PK primary key (Abbreviation),

);


create table Paper
(
	Paper_Code		char(7),
		constraint Paper_Paper_Code_Null	not null,
	Title 			varchar2(50),
		constraint Paper_Title_Null	not null,
	Description	   varchar2(500),
		constraint Paper_Description_Null		not null,
	Points			number(2),
		constraint Paper_Points_Null		not null,
	Period			char(2),
		constraint Paper_Period_Null		not null,
		check (Type in ('S1','S2','SS','FY' )),
		--
	constraint Paper_Code_PK primary key (Paper_Code),
);

# ? Apr 29, 2010 11:05

Victor: Jun 18, 2004

Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition?

Victor fucked around with this message at 11:19 on Apr 29, 2010

# ? Apr 29, 2010 11:17

Chumpington: Jan 17, 2010; by Ozma

Victor posted:

Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition?

No I think that was a mistake on my part. I'll try that when I get there tomorrow..Cheers

# ? Apr 29, 2010 11:21

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

Victor posted:

Have you tried quoting the names with backticks? Also, your second CREATE TABLE has too many commas. I forget, does MySQL allow trailing commas after the last column/constraint definition?

I'm not certain about CREATE TABLE statements specifically, but I would guess not, since there are other situations in which it doesn't allow trailing commas after the last item in a list.

# ? Apr 29, 2010 12:02

Victor: Jun 18, 2004

Does anyone make a tool that checks MySQL for syntax errors and provides, ya know, good error messages? The MySQL 5.x errors when I use the CLI tool are just horrific! I can't believe the foolishness that is "comment out most of the code and see if it accepts, now comment out a little less". That'd probably be my biggest reason not to use MySQL; I want to spend my time writing code, not troubleshooting things a computer should be able to do, trivially. (nerrrrd rage)

# ? Apr 29, 2010 13:43

Wonderbread 2000: Oct 27, 2008

I'm trying to debug this, and maybe I'm dumb but I don't see anything obviously wrong with it.

code:

DECLARE @InsertedRows TABLE (PhotoID uniqueidentifier)
                            INSERT INTO TankPhoto (
				[TankInvID]
                                ,[TankInspID]
                                ,[PhotoName]
                                ,[Comments]
                                ,[PhotoDate],[Photo]) OUTPUT INSERTED.PhotoID INTO @InsertedRows 
                            VALUES (
				@tankInvID
                                ,@tankInspID
                                ,@photoName
                                ,@comments
                                ,@photoDate,@photoBytes)
                            SELECT TOP(1) [PhotoID] FROM @InsertedRows, @tankInvID uniqueidentifier
                            ,@tankInspID uniqueidentifier,@photoName nvarchar(15),@comments nvarchar(4000)
                            ,@photoDate datetime,@photoBytes image
                            , @tankInvID = '52AA40D4-ED02-44D3-8175-32301DD3F559', @tankInspID = NULL
                            , @photoName = 'kitten-sink.jpg', @comments = NULL, @photoDate = 'Apr 30 2010 12:00:00:000AM'
                            , @photoBytes = <snip>

Database is throwing the error "Line 7: Incorrect syntax near 'OUTPUT'".

# ? Apr 30, 2010 16:32

Victor: Jun 18, 2004

Does it work without the OUTPUT clause?

# ? Apr 30, 2010 16:56

Wonderbread 2000: Oct 27, 2008

I haven't tried it - other parts of the code need that clause, the whole point of the insert statement being this complicated is to retrieve the guid for the new entry. I'm thinking of just simplifying the whole mess by generating the guid in the front-end code instead.

# ? Apr 30, 2010 18:42

Victor: Jun 18, 2004

You should be able to run a syntax check on it without executing it.

# ? Apr 30, 2010 18:44

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

SQLite question: I have a ~100MB some_data.db3 and I want to use ATTACH DATABASE to hook up with a 2GB db3 file to do some cross-database joins. Does the ATTACH hurt the performance of queries that only use tables in some_data.db3?

# ? Apr 30, 2010 23:47

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

I need to get up to speed on programming for SQL Server 2008 with regards to SQL basics, triggers, stored procedures, and functions (UDFs). My current database knowledge extends to basic select/insert/update/delete queries and basic joins, from writing hobby web projects in MySQL.

Can anyone recommend any good or well known books on the topic which I could study? Has anyone read Microsoft SQL Server 2008 T-SQL Fundamentals and what did you think?

# ? May 4, 2010 04:28

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

I want to duplicate a production server onto an internal development server (both running MSSQL 2005). However, the production environment is way too big and not really necessary. Is there any way that I can :
- Drop just the table structure of the production server onto the dev server AND
- Somehow do something like '* TOP 1000' per table so I have some but not all data from the production environment?

EDIT-ignore, found a better way

Scaramouche fucked around with this message at 21:27 on May 4, 2010

# ? May 4, 2010 21:15

Malfeasible: Sep 10, 2005; The sworn enemy of Florin

Oracle question:

Is there a way to automatically set linesize, pagesize, or serveroutput, or does that have to be manually done after logging in?

I wish I could create a trigger to fire after login to do that sort of mundane stuff, or somehow set custom default values.

# ? May 5, 2010 06:29

var1ety: Jul 26, 2004

Malfeasible posted:

Oracle question:

Is there a way to automatically set linesize, pagesize, or serveroutput, or does that have to be manually done after logging in?

I wish I could create a trigger to fire after login to do that sort of mundane stuff, or somehow set custom default values.

You can create a login.sql (or glogin.sql) file that will be automatically parsed by SQL*Plus.

http://www.orafaq.com/wiki/SQL*Plus_FAQ#Can_one_run_commands_when_SQL.2APlus_starts_up.3F

# ? May 5, 2010 13:25

MikeRabsitch: Aug 23, 2004; Show us what you got, what you got

cannibustacap posted:

I'm looking for a database solution and I was wondering what your thoughts are...

I work for a company that does product testing, and each time our product is tested, large amounts of data is produced.

By large, I mean the equivalent to 10,000 - 100,000 rows of data per test.

(We are testing planes and recording information based on every instant, so there is a ton of data).

So, here are the specs of this database:
1) It will have an enormous number of rows for some tables, lets just say an indefinite number.

2) for our database, only about 3-5 users, so not a lot of demand on it*

3) Almost every other day, certain tables will be dumped and downloaded.. so the speed of doing queries should be reasonably fast.

4) It needs to work with Matlab

What system do you suggest? Oracle, MS SQL Server 2005, MySQL good enough, etc? Comedy Access 2007 option?

*The reason for only 3 users is because our database is locked down and thus only certain data can be transmitted by manually dumping. The open version of this database will be Oracle 10g, but that is a separate server. The open version will have lots of demand, 1000 of users using it constantly, so that has to be a superhero level server.

What database do you think will work for us here? If Oracle, what kind of server or would a software solution work? (We currently have very powerful, but generic server computers in our area). What kind of price are we looking at?

(I called up Oracle and they claimed they have no pure software version of their database and wanted upward of $30k... I could have sworn there is a software version of Oracle..)

We're in the process of testing a postgres variant called Greenplum, which is incredibly faster than our Oracle setup and much, much cheaper. That said, everything is cheaper than Oracle. Obviously it's going to cost a bit of hours if we were to convert all our Oracle stuff to Greenplum, but it might be worth looking into for your situation. Scripts that take hours to run in our Oracle setup takes minutes on just one node of Greenplum. And to make it faster, all you have to do is add more nodes. It's not as forgiving as Oracle is, it doesn't like correlated subqueries and there's some weird distribution key fields but it's much easier to start with it than it is to convert to it.

# ? May 5, 2010 22:34

Scaramouche: Mar 26, 2001; SPACE FACE! SPACE FACE!

Had a more operational question. We've got two MSSQL 2005 databases in a transactional replication relationship, one publishes (let's say S1) and one subscribes (let's say S2). We're noticing a big chunk of trunc info that's taking up disk space, however we know from experience we can't just run truncuate on the publisher and expect the subscriber to just accept the changes gracefully. Is there a way to truncuate replicated databases, or do we have to break replication, truncuate both, and reconnect?

# ? May 7, 2010 18:20

Victor: Jun 18, 2004

Factor Mystic posted:

I need to get up to speed on programming for SQL Server 2008 with regards to SQL basics, triggers, stored procedures, and functions (UDFs). My current database knowledge extends to basic select/insert/update/delete queries and basic joins, from writing hobby web projects in MySQL.

Can anyone recommend any good or well known books on the topic which I could study? Has anyone read Microsoft SQL Server 2008 T-SQL Fundamentals and what did you think?

How computer-sciencey are you? The Caltech databases course uses Databse System Concepts, which is alright. I really liked Database in Depth, which explains some of the theoretical underpinnings of relational databases that will expand your brain. However, both of these are more academic than you may prefer.

# ? May 8, 2010 01:15

Flatlander: May 1, 2007; "For heaven's sake, what's happened to you?" "I think,"he replies, "I was 'up'." They pat him on his sides and comfort him. Delusions always ran in his family.

Using sql only how could I extract just the birthdate from a serialized array using substring or index of?

a:16:{i:0;s:10:"1985-12-10";i:1;i:1;i:2;i:1;i:3;i:1;i:4;i:0;i:5;i:1;
i:6;i:1;i:7;i:1;i:8;i:1;i:9;i:0;i:10;i:1;i:11;i:0;i:12;i:0;i:13;i:0;i:14;i:0;i:15;i:0;}

select birthdate from profile_users

I want to manipulate the birthday field until I just get that date.
I tried:
SELECT substring( birthdate, 17, 17 ) AS birthdate
FROM profile_people but that still leaves me with 1955-08-13";i:1;s when I want 1955-08-13

Edit nvm: where type ="stupid" is correct!
SELECT substring( birthdate, 17, 10 ) AS birthdate does the trick.

Flatlander fucked around with this message at 22:19 on May 10, 2010

# ? May 10, 2010 22:07

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Flatlander posted:

Using sql only how could I extract just the birthdate from a serialized array using substring or index of?

a:16:{i:0;s:10:"1985-12-10";i:1;i:1;i:2;i:1;i:3;i:1;i:4;i:0;i:5;i:1;
i:6;i:1;i:7;i:1;i:8;i:1;i:9;i:0;i:10;i:1;i:11;i:0;i:12;i:0;i:13;i:0;i:14;i:0;i:15;i:0;}

select birthdate from profile_users

I want to manipulate the birthday field until I just get that date.
I tried:
SELECT substring( birthdate, 17, 17 ) AS birthdate
FROM profile_people but that still leaves me with 1955-08-13";i:1;s when I want 1955-08-13

well that likely just a small mental error on your part.

Substring works by ( yourstring, starting index, length) so

code:

SELECT substring( birthdate, 17, 10 ) AS birthdate 
FROM profile_people

Should work?

# ? May 10, 2010 22:20

Flatlander: May 1, 2007; "For heaven's sake, what's happened to you?" "I think,"he replies, "I was 'up'." They pat him on his sides and comfort him. Delusions always ran in his family.

Yes, thx, that works fine and I made it a bit better:
select substring(birthdate, locate('"', birthdate) + 1, 10) from profile_people :toot:

# ? May 10, 2010 22:37

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

How come when I use the MySQL "export" tool, it screws up unicode characters?

I'm using the MySQL Administrator tool to back up my database. When I look through the backup, I see rows like

code:

 (2,20,250,'Usines Sidérurgiques',1,NULL),

where it should instead say Usines Sid�rurgiques - it's screwing up the accented character.

The column in which the data is stored is VARCHAR(750) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL. I've checked this by looking at the CREATE TABLE statement. Furthermore, I feel confident that the text isn't stored mis-encoded, because I have a PHP script that displays it correctly (output straight from the database), and the PHP script has charset=utf-8 specified in its content-type meta tag (and Firefox correctly identifies it as UTF-8 encoded).

EDIT: Never mind. I did some testing with a dummy table and found that the "restore" functionality in MySQL Admin doesn't even appear to work (it claims to have successfully restored data, but doesn't actually do anything). The export/import functionality in PHPMyAdmin exhibits the same peculiar character set issue in the dumped file, but it seems to arrive correctly in the database upon doing a restore, so perhaps it doesn't matter. I'm just going to write MySQL Administrator off as being bloody useless and continue to use PHPMyAdmin for this stuff.

Hammerite fucked around with this message at 22:57 on May 13, 2010

# ? May 13, 2010 22:03

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Hammerite posted:

How come when I use the MySQL "export" tool, it screws up unicode characters?

I'm using the MySQL Administrator tool to back up my database. When I look through the backup, I see rows like
code:
 (2,20,250,'Usines Sidérurgiques',1,NULL),
where it should instead say Usines Sid�rurgiques - it's screwing up the accented character.

The column in which the data is stored is VARCHAR(750) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL. I've checked this by looking at the CREATE TABLE statement. Furthermore, I feel confident that the text isn't stored mis-encoded, because I have a PHP script that displays it correctly (output straight from the database), and the PHP script has charset=utf-8 specified in its content-type meta tag (and Firefox correctly identifies it as UTF-8 encoded).

EDIT: Never mind. I did some testing with a dummy table and found that the "restore" functionality in MySQL Admin doesn't even appear to work (it claims to have successfully restored data, but doesn't actually do anything). The export/import functionality in PHPMyAdmin exhibits the same peculiar character set issue in the dumped file, but it seems to arrive correctly in the database upon doing a restore, so perhaps it doesn't matter. I'm just going to write MySQL Administrator off as being bloody useless and continue to use PHPMyAdmin for this stuff.

Your connection via MySQL Admin probably wasn't using a UTF8 connection. Yes Mysql is very stupid for things like this where the connection language will override whats in the tables for exports.

# ? May 14, 2010 01:35

Stobbit: Mar 9, 2006

This is gonna be the stupidest question ever, but I feel like the biggest retard right now ...

I have a many-to-many relationship between two tables, Pizza and Topping. Obviously a Pizza can have many Toppings and a Topping can be on many Pizzas.

I have a bridging table, PizzaToppings, which lists which Pizzas have which toppings.

The relationship looks like this:

code:


Pizza         PizzaToppings
-----         -------------        Topping
ID<---------->Pizza                -------
Name          Topping<------------>ID
                                   Name

I have a query which I'm using to populate a WPF listbox of which toppings are on the current pizza.

It looks like this:

code:

SELECT PizzaToppings.Pizza AS Pizza, PizzaToppings.Topping AS ToppingID, Topping.Name AS ToppingName
FROM PizzaToppings, Topping
WHERE PizzaToppings.Topping=Topping.ID;

and that gives me each Pizza's ID number with the ID number and Name of the Topping for that pizza.

Now, I want another listbox whose query will list each Pizza's ID number as well as the ID number and Name of every topping which is NOT on the Pizza (the idea being that I can put arrow buttons between the two listboxes allowing the user to add and remove toppings from a pizza).

Do excuse my complete idiocy but how would I achieve this? It doesn't seem to be as simple as saying "NOT" in the WHERE clause. I feel like such an idiot ...

Stobbit fucked around with this message at 07:36 on May 17, 2010

# ? May 16, 2010 17:02

Hammerite: Mar 9, 2007; And you don't remember what I said here, either, but it was pompous and stupid.; Jade Ear Joe

code:

SELECT
    Pizza.ID AS Pizza,
    Topping.ID AS ToppingID,
    Topping.Name AS ToppingName
FROM
    Pizza
    JOIN Topping ON Topping.ID NOT IN (
        SELECT
            Topping
        FROM
            PizzaToppings
        WHERE
            PizzaToppings.Pizza = Pizza.ID
    ) AS MySubquery

Not sure how well this would work or if it does, whether there's actually a better way. This is a problem I haven't seen before.

# ? May 16, 2010 17:46

Adbot: ADBOT LOVES YOU

# ? May 26, 2024 17:40

Sprawl: Nov 21, 2005; I'm a huge retarded sperglord who can't spell, but Starfleet Dental would still take me and I love them for it!

Stobbit posted:

This is gonna be the stupidest question ever, but I feel like the biggest retard right now ...

I have a many-to-many relationship between two tables, Pizza and Topping. Obviously a Pizza can have many Toppings and a Topping can be on many Pizzas.

I have a bridging table, PizzaToppings, which lists which Pizzas have which toppings.

The relationship looks like this:
code:
Pizza         PizzaToppings
-----         -------------        Topping
ID<---------->Pizza                -------
Name          Topping<------------>ID
                                   Name
I have a query which I'm using to populate a WPF listbox of which toppings are on the current pizza.

It looks like this:
code:
SELECT PizzaToppings.Pizza AS Pizza, PizzaToppings.Topping AS ToppingID, Toppings.Topping AS ToppingName
FROM PizzaToppings, Toppings
WHERE PizzaToppings.Topping=Toppings.ID;
and that gives me each Pizza's ID number with the ID number and Name of the Topping for that pizza.

Now, I want another listbox whose query will list each Pizza's ID number as well as the ID number and Name of every topping which is NOT on the Pizza (the idea being that I can put arrow buttons between the two listboxes allowing the user to add and remove toppings from a pizza).

Do excuse my complete idiocy but how would I achieve this? It doesn't seem to be as simple as saying "NOT" in the WHERE clause. I feel like such an idiot ...

That really sounds something i would do application side rather then at the db level.

# ? May 16, 2010 19:38

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

«‹›303 »