Re: MySQL Feature

[Zak Greant]

On Thu, Feb 13, 2003 at 02:10:18PM -0500, Luc Foisy wrote:
> BUT
> 
> SELECT Data, CASE WHEN 0 != 0 THEN 'Something' END FROM table
> 
> will return two columns, "Data" and "CASE WHEN 0 != 0 THEN 'Something' END". It 
>would be better if I could do..
> 
> SELECT Data, CASE WHEN 0 != 0 THEN 'Something' AS 'A Column' END FROM table
> 
> and the result set would only contain one column, "Data"
> 
> 
> I am sure people could find a use for it...

  I don't think this would work the way you like. I am pretty tired
  right now, but I can't think of another query that could return a
  variable length list of fields per row. :)

-- 
 Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate |  http://zak.fooassociates.com

MySQL Tip: Allow duplicate values in columns, but force rows to be unique
  mysql> CREATE TABLE lock (t0 TINYINT NOT NULL, t1 TINYINT NOT NULL,
   UNIQUE combination (t0, t1));

EFF: Protecting Freedoms on the New Frontier (http://eff.org)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL Feature

[Luc Foisy]

> -Original Message-
> From: Zak Greant [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, February 13, 2003 10:38 AM
> To: [EMAIL PROTECTED]
> Subject: Re: MySQL Feature
> 
> 
> On Thu, Feb 13, 2003 at 09:57:06AM -0500, Luc Foisy wrote:
> > 
> > I don't think it is possible at the moment, and pretty sure 
> its not standard SQL but it would still be neat to have
> > 
> > SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 
> 'aColumn' ) FROM aTable
> > 
> > So in this example, if the expression was true, include 
> that column in the result set, otherwise don't
> > Sure this can be handled outside MySQL and just change the 
> query on condition, just thought it would be nice
> 
> You can use a WHERE clause to get an effect like this.
> 
>   Something like:
> 
>   SELECT CASE expression
> WHEN 'value'
> THEN column
> ...
> ELSE 'default result'
> END
> FROM Table;

BUT

SELECT Data, CASE WHEN 0 != 0 THEN 'Something' END FROM table

will return two columns, "Data" and "CASE WHEN 0 != 0 THEN 'Something' END". It would 
be better if I could do..

SELECT Data, CASE WHEN 0 != 0 THEN 'Something' AS 'A Column' END FROM table

and the result set would only contain one column, "Data"


I am sure people could find a use for it...

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: MySQL Feature

[Stefan Hinz]

Luc, Zak,

>> SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 'aColumn' ) FROM aTable

> You can use a WHERE clause to get an effect like this.
>   SELECT CASE expression
> WHEN 'value'
> THEN column
> ...
> ELSE 'default result'
> END
> FROM Table;

Or even IF() ;-)

mysql> SELECT IF(c='foo',c,'N/A') FROM t;
+-+
| IF(c='foo',c,'N/A') |
+-+
| N/A |
| foo |
| N/A |
| N/A |

Regards,
--
  Stefan Hinz <[EMAIL PROTECTED]>
  iConnect GmbH 
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL Feature

[Zak Greant]

On Thu, Feb 13, 2003 at 09:57:06AM -0500, Luc Foisy wrote:
> 
> I don't think it is possible at the moment, and pretty sure its not standard SQL but 
>it would still be neat to have
> 
> SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 'aColumn' ) FROM aTable
> 
> So in this example, if the expression was true, include that column in the result 
>set, otherwise don't
> Sure this can be handled outside MySQL and just change the query on condition, just 
>thought it would be nice

You can use a WHERE clause to get an effect like this.

  Something like:

  SELECT CASE expression
WHEN 'value'
THEN column
...
ELSE 'default result'
END
FROM Table;

Cheers!
-- 
 Zak Greant <[EMAIL PROTECTED]> | MySQL Advocate |  http://zak.fooassociates.com

MySQL Tip: Load the contents of a text file into a field
  mysql> INSERT table (field) VALUES (LOAD_FILE("/path/to/file"));
  See http://mysql.com/doc/en/String_functions.html for more details

Email signature rotated by Signify v1.10 

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySQL Feature

[Luc Foisy]

I don't think it is possible at the moment, and pretty sure its not standard SQL but 
it would still be neat to have

SELECT aField, COLUMN_IF( some_expression, 'afield2' AS 'aColumn' ) FROM aTable

So in this example, if the expression was true, include that column in the result set, 
otherwise don't
Sure this can be handled outside MySQL and just change the query on condition, just 
thought it would be nice

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySql Feature

[Stefan Hinz, iConnect \(Berlin\)]

Dear Benjamin, dear Michael,

> > > 1. I need to know if these features are supported or not by MySql ver
> > 4.05a.
> > > - Referencial integrity constraint

This is supported with InnoDB tables. If you _always_ need referential
integrity, simply set InnoDB tables as the default table type in my.cnf /
my.ini like this:

[mysqld]
default-table-type=innodb

> > > - Stored Procedure

General framework for this will be implemented in 4.1. Release date for
4.1-alpha: very soon (about the time when 4.0.6 will be "gamma"). Releases
are in the order "alpha - beta - gamma". Alpha will be tested as much and as
good as commercial "final" releases, whereas Gamma has a user basis which is
big enough to declare this one as Stable.

> > > - View
> > > - Trigger

Both planned for 5.0. Release date for 5.0-alpha will be summer / autumn
2003.

> I can't wait for these features too! But since some of the features are
> slated for v5.0 - v6.0, I  guess we won't be seeing them for a couple of
years.

You can already see (and use) all foreign key restraints, thus referential
integrity. You will have to wait for stored procedures until spring / autumn
2003 (alpha / gamma) and for the rest until autumn 2003 / spring 2004. So
we're not talking about years, but about months.

HTH!
--
  Stefan Hinz <[EMAIL PROTECTED]>
  CEO / Geschäftsleitung iConnect GmbH <http://iConnect.de>
  Heesestr. 6, 12169 Berlin (Germany)
  Telefon: +49 30 7970948-0  Fax: +49 30 7970948-3


- Original Message -
From: "Michael She" <[EMAIL PROTECTED]>
To: "Benjamin Pflugmann" <[EMAIL PROTECTED]>
Cc: "Johan" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Saturday, December 07, 2002 8:22 PM
Subject: Re: MySql Feature


> At 04:22 PM 12/7/2002 +0100, Benjamin Pflugmann wrote:
> >Hello.
> >
> >On Sat 2002-12-07 at 07:47:18 +0700, [EMAIL PROTECTED] wrote:
> > > Hello friends, newbie here.
> > > 1. I need to know if these features are supported or not by MySql ver
> > 4.05a.
> > > - Referencial integrity constraint
> > > - Stored Procedure
> > > - View
> > > - Trigger
>
>
> I can't wait for these features too! But since some of the features are
> slated for v5.0 - v6.0, I  guess we won't be seeing them for a couple of
years.
> --
> Michael She  : [EMAIL PROTECTED]
> Mobile   : (519) 589-7309
> WWW Homepage : http://www.binaryio.com/
>
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php   (the manual)
>http://lists.mysql.com/   (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySql Feature

[Michael She]

At 04:22 PM 12/7/2002 +0100, Benjamin Pflugmann wrote:

Hello.

On Sat 2002-12-07 at 07:47:18 +0700, [EMAIL PROTECTED] wrote:
> Hello friends, newbie here.
> 1. I need to know if these features are supported or not by MySql ver 
4.05a.
> - Referencial integrity constraint
> - Stored Procedure
> - View
> - Trigger


I can't wait for these features too! But since some of the features are 
slated for v5.0 - v6.0, I  guess we won't be seeing them for a couple of years.
--
Michael She  : [EMAIL PROTECTED]
Mobile   : (519) 589-7309
WWW Homepage : http://www.binaryio.com/



-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySql Feature

[Benjamin Pflugmann]

Hello.

On Sat 2002-12-07 at 07:47:18 +0700, [EMAIL PROTECTED] wrote:
> Hello friends, newbie here.
> 1. I need to know if these features are supported or not by MySql ver 4.05a.
> - Referencial integrity constraint
> - Stored Procedure
> - View
> - Trigger

Please see

  http://www.mysql.com/doc/en/MySQL-PostgreSQL_features.html (at the bottom)
  http://www.mysql.com/doc/en/Differences_from_ANSI.html

(both can be easily found by searching for the features you listed)

> 2. How to use SSL ?

  http://www.mysql.com/doc/en/Secure_connections.html

> 3. How to add a user that can login from any host ?

  http://www.mysql.com/doc/en/Adding_users.html
  (first example, second GRANT; second ex. third GRANT)

> 4. What are the differences between standard and max version ?

  http://www.mysql.com/products/index.html
  http://www.mysql.com/doc/en/mysqld-max.html (more technical)

> 5. I am using Mandrake Linux, how to set directory of mysql file
>(the database not the binary) to a different disk ?

Either create a link beforehand or change data_dir in /etc/my.cnf to
the place you want (don't forget to move the database, too).

HTH,

Benjamin.


PS: Yes, that was the kinder version of RTFM!

-- 
[EMAIL PROTECTED]

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MySql Feature

[Johan]

Hello friends, newbie here.
1. I need to know if these features are supported or not by MySql ver 4.05a.
- Referencial integrity constraint
- Stored Procedure
- View
- Trigger

2. How to use SSL ?
3. How to add a user that can login from any host ?
4. What are the differences between standard and max version ?
5. I am using Mandrake Linux, how to set directory of mysql file (the database not the 
binary) to a different disk ?

 Thanks for your enlightment



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re[2]: MYSQL Feature :)

[Peter Zaitsev]

Hello Sinisa,

Thursday, March 01, 2001, 3:42:46 PM, you wrote:

SM>  > I'm not really shure if this is a bug or correct behavior, but still
SM>  > it leads to toe problems in this case:
SM>  > 
SM>  > insert into g02layers_stat_hits set counter=1, type=1, page_group=1;
SM>  > ERROR 1062: Duplicate entry '4294967295' for key 1
SM>  > 
SM>  > This is quite well result, but if we use replace in this case (which
SM>  > works quite OK then the table has not maximum number of rows:
SM>  > 
SM>  > mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
SM>  > Query OK, 2 rows affected (0.33 sec)
SM>  > 
SM>  > and in this case the old row with id=4294967295 is destroyed.
SM>  > 
SM>  > There for this works quite nice then the table does not have last_id
SM>  > used:
SM>  > 
SM>  > mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
SM>  > Query OK, 1 row affected (0.02 sec)
SM>  > 
SM>  > in this case the new ID is generated and everything is just fine :)
SM>  > 


SM> Hi!

SM> Yes, the above is well known behaviour. 

SM> What do you suggest, how should we change that ??

I think in this case, as you allow a replace with an auto increment
value (which is in case EQ to insert as no such value may exist), it
should return a dublicate key error in this case as it was unable to
insert new record to a database, which was expected for it to do.




-- 
Best regards,
 Petermailto:[EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MYSQL Feature :)

[Sinisa Milivojevic]

Peter Zaitsev writes:
 > Hello mysql,
 > 
 >   Mysql 3.23.32 running on Linux.
 > 
 > I've got a table with auto_increment command stuck, well this is no
 > problem there is a record in this table with MAX_UINT value.
 > 
 > I'm not really shure if this is a bug or correct behavior, but still
 > it leads to toe problems in this case:
 > 
 > insert into g02layers_stat_hits set counter=1, type=1, page_group=1;
 > ERROR 1062: Duplicate entry '4294967295' for key 1
 > 
 > This is quite well result, but if we use replace in this case (which
 > works quite OK then the table has not maximum number of rows:
 > 
 > mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
 > Query OK, 2 rows affected (0.33 sec)
 > 
 > and in this case the old row with id=4294967295 is destroyed.
 > 
 > There for this works quite nice then the table does not have last_id
 > used:
 > 
 > mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
 > Query OK, 1 row affected (0.02 sec)
 > 
 > in this case the new ID is generated and everything is just fine :)
 > 
 > 
 > 
 > 
 > 
 > -- 
 > Best regards,
 >  Peter  mailto:[EMAIL PROTECTED]
 > 
 > 
 > 


Hi!

Yes, the above is well known behaviour. 

What do you suggest, how should we change that ??


Regards,

Sinisa

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

MYSQL Feature :)

[Peter Zaitsev]

Hello mysql,

  Mysql 3.23.32 running on Linux.

I've got a table with auto_increment command stuck, well this is no
problem there is a record in this table with MAX_UINT value.

I'm not really shure if this is a bug or correct behavior, but still
it leads to toe problems in this case:

insert into g02layers_stat_hits set counter=1, type=1, page_group=1;
ERROR 1062: Duplicate entry '4294967295' for key 1

This is quite well result, but if we use replace in this case (which
works quite OK then the table has not maximum number of rows:

mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
Query OK, 2 rows affected (0.33 sec)

and in this case the old row with id=4294967295 is destroyed.

There for this works quite nice then the table does not have last_id
used:

mysql> replace into g02layers_stat_hits set counter=1, type=1, page_group=1;
Query OK, 1 row affected (0.02 sec)

in this case the new ID is generated and everything is just fine :)





-- 
Best regards,
 Peter  mailto:[EMAIL PROTECTED]



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php