Re: Enforce value on select

[John Mistler]

Good idea.  liang le's answer almost got it, but I couldn't make it work
with string values quite right.  Here is my situation:

I am issuing a series of queries all-in-one like "SELECT nameColumn,
otherColumn FROM theDatabase WHERE rowID = 1;SELECT nameColumn, otherColumn
FROM theDatabase WHERE rowID = 2;SELECT nameColumn, otherColumn FROM
theDatabase WHERE rowID = 3;"

expecting a return of

aName   otherItem

aName   otherItem

aName   otherItem

but if one of those rowIDs does not exist, then I get

aName   otherItem

aName   otherItem

and my app has no value for the non-existing row.  I would like for the
query to return an indication that the row did not exist, like:

aName   otherItem

0 or '' or 'NULL' (no nameColumn entries will ever = 0, or '', or NULL)

aName   otherItem

Does that make more sense, and if so, is there a solution?

Thanks,

John

Liang Le -- Your query:

(SELECT IFNULL(a.nameColumn,'0') nameColumn, IFNULL(a.otherColumn,'0')
otherColumn FROM theTable a WHERE a.rowID = 5)
UNION
(SELECT IFNULL(b.nameColumn,'0') nameColumn, IFNULL(b.otherColumn,'0')
otherColumn FROM theTable b WHERE b.rowID = 5)
;

Worked when the row DID NOT exist (like I asked for).  However, when the row
DID exist, it was returning:

aName   otherItem
0   0

The zeros are troublesome.  It should look like:

aName   otherItem

Thanks!

on 10/10/04 10:46 PM, Michael Stassen at [EMAIL PROTECTED] wrote:

> Then how will you know the difference between a row with nameColumn = 0 (or
> '') and one that doesn't exist?
> 
> What you are asking for seems very strange.  You want the db to pretend
> there's a value for nonexistent rows.  If rowID 5 should have the value 0,
> then I wonder why there isn't a row with rowID=5 and value=0.  If it's just
> a matter of treating non-existent rows as having 0 value in your app, why
> don't you simply code that into your app?  In other words, I find it hard to
> provide a solution, because I don't understand what you want.  Perhaps if
> you explained it, someone could suggest how best to accomplish it.
> 
> Michael
> 
> John Mistler wrote:
> 
>> Thanks for the reply.  There is a slight difference in what I need from the
>> "IFNULL" function.  It will only return the specified value if the column is
>> null on a row that actually exists.  I am needing a function that will
>> return the specified value if the row does NOT exist.  Any other ideas?
>> 
>> SELECT nameColumn from theDatabase WHERE rowID = 5;
>> 
>> (when no row has ID "5")
>> 
>> result --> empty set (I want a value like '0' or something)
>> 
>> Thanks again!
>> 
>> -John
>> 
>> on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote:
>> 
>> 
>>> --- John Mistler <[EMAIL PROTECTED]>
>>> 
 Is there a way to force SOME value to be returned
 from a SELECT query when
 the result is empty set?  For instance:
 
 SELECT nameColumn from theDatabase WHERE rowID = 5;
 
 (when no row has ID "5")
 
 result --> empty set
 
 I would like for it to return some value, such as ''
 or 0 . . .
 
 Thanks,
 
 John
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 
>>> 
>>> http://lists.mysql.com/[EMAIL PROTECTED]
>>> 
 
>>> select ifnull(column,'0') from table
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Website site Database (Project)

[Benjamin Arai]

What do you mean? 


On Sun, 10 Oct 2004 23:07:59 -0700 (PDT), John <[EMAIL PROTECTED]> wrote:
> 
> 
> I wanted to know if this is a good place to post
> for a project I needed done, If not can someone direct
> me to a better place to post it.
> 
> Thanks
> 
> ___
> Do you Yahoo!?
> Declare Yourself - Register online to vote today!
> http://vote.yahoo.com
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
Benjamin Arai
http://www.cs.ucr.edu/~barai
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Website site Database (Project)

[John]

I wanted to know if this is a good place to post
for a project I needed done, If not can someone direct
me to a better place to post it.

Thanks



___
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Enforce value on select

[Michael Stassen]

Then how will you know the difference between a row with nameColumn = 0 (or 
'') and one that doesn't exist?

What you are asking for seems very strange.  You want the db to pretend 
there's a value for nonexistent rows.  If rowID 5 should have the value 0, 
then I wonder why there isn't a row with rowID=5 and value=0.  If it's just 
a matter of treating non-existent rows as having 0 value in your app, why 
don't you simply code that into your app?  In other words, I find it hard to 
provide a solution, because I don't understand what you want.  Perhaps if 
you explained it, someone could suggest how best to accomplish it.

Michael
John Mistler wrote:
Thanks for the reply.  There is a slight difference in what I need from the
"IFNULL" function.  It will only return the specified value if the column is
null on a row that actually exists.  I am needing a function that will
return the specified value if the row does NOT exist.  Any other ideas?
SELECT nameColumn from theDatabase WHERE rowID = 5;
 
(when no row has ID "5")
 
result --> empty set (I want a value like '0' or something)

Thanks again!
-John
on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote:

--- John Mistler <[EMAIL PROTECTED]>
Is there a way to force SOME value to be returned
from a SELECT query when
the result is empty set?  For instance:
SELECT nameColumn from theDatabase WHERE rowID = 5;
(when no row has ID "5")
result --> empty set
I would like for it to return some value, such as ''
or 0 . . .
Thanks,
John
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: 

http://lists.mysql.com/[EMAIL PROTECTED]

select ifnull(column,'0') from table

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ODBC Call failed - Query appears to be corrupt?

[Michael Stassen]

Subqueries are not supported until mysql 4.1.x.  You have 4.0.20.  You need 
to either upgrade mysql or rewrite your query as a join.  (Joins are often 
more efficient anyway.)  Try replacing

  WHERE NOT EXISTS (SELECT* FROM  featureenable
WHERE main_db.FeatureKey = featureenable.FeatureKey);
with
  LEFT JOIN featureenable ON main_db.FeatureKey = featureenable.FeatureKey
  WHERE featureenable.FeatureKey IS NULL;
See the manual for more 
.

Michael
Bartis, Robert M (Bob) wrote:
I have a query that I have put together. The query is an attempt to retrieve records from one table, main_db, whose keys are not present in another, featureenable. I am using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I traced the ODBC calls and see something very odd. 

It appears from the trace that the query being passed from an MS Access front-end to 
the ODBC connector is being corrupted I've extracted the lines of interest shown 
below. Please note the MS2 referenced in the 2nd line. These are not in the original 
query I passed in. Why is the query being passed to the ODBC connector changed? The 
full queries are also shown.

.EXISTS (SELECT* FROM  featureenable WHERE main_db.FeatureKey = 
featureenable.FeatureKey);
.EXISTS (SELECT `MS2`.`ID`  FROM `featureenable` `MS2` WHERE 
(`MS2`.`FeatureKey


Original Query
==
SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup], 
[main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey]
FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON 
[featureenable].[FeatureKey]=[main_db].[FeatureKey]
WHERE NOT EXISTS (SELECT* FROM  featureenable WHERE main_db.FeatureKey = 
featureenable.FeatureKey);

ODBC Call Trace

STRDB-v2.0b14   83c-218	ENTER SQLExecDirectW 
		HSTMT   08DC1C30
		WCHAR * 0x0F271F40 [  -3] "SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey`  FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID`  FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
		SDWORD-3

STRDB-v2.0b14   83c-218 EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)
HSTMT   08DC1C30
WCHAR * 0x0F271F40 [  -3] "SELECT DISTINCT 
`functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` 
,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey`  FROM 
`main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = 
`main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = 
`main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS 
(SELECT `MS2`.`ID`  FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = 
`main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD-3
DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-standard]You have 
an error in your SQL syntax.  Check the manual that corresponds to your MySQL server 
version for the right syntax to use near 'EXISTS (SELECT `MS2`.`ID`  FROM 
`featureenable` `MS2` WHERE (`M (1064)
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: <[EMAIL PROTECTED]>
Pgr: <[EMAIL PROTECTED]>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ODBC Call failed - Query appears to be corrupt?

[Bartis, Robert M (Bob)]

I have a query that I have put together. The query is an attempt to retrieve records 
from one table, main_db, whose keys are not present in another, featureenable. I am 
using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I 
traced the ODBC calls and see something very odd. 

It appears from the trace that the query being passed from an MS Access front-end to 
the ODBC connector is being corrupted I've extracted the lines of interest shown 
below. Please note the MS2 referenced in the 2nd line. These are not in the original 
query I passed in. Why is the query being passed to the ODBC connector changed? The 
full queries are also shown.



.EXISTS (SELECT* FROM  featureenable WHERE main_db.FeatureKey = 
featureenable.FeatureKey);


.EXISTS (SELECT `MS2`.`ID`  FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey





Original Query
==
SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup], 
[main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey]
FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND 
([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON 
[featureenable].[FeatureKey]=[main_db].[FeatureKey]
WHERE NOT EXISTS (SELECT* FROM  featureenable WHERE main_db.FeatureKey = 
featureenable.FeatureKey);



ODBC Call Trace


STRDB-v2.0b14   83c-218 ENTER SQLExecDirectW 
HSTMT   08DC1C30
WCHAR * 0x0F271F40 [  -3] "SELECT DISTINCT 
`functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` 
,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey`  FROM 
`main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE 
((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = 
`main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID`  FROM `featureenable` 
`MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD-3

STRDB-v2.0b14   83c-218 EXIT  SQLExecDirectW  with return code -1 (SQL_ERROR)
HSTMT   08DC1C30
WCHAR * 0x0F271F40 [  -3] "SELECT DISTINCT 
`functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` 
,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey`  FROM 
`main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE 
((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND 
(`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = 
`main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID`  FROM `featureenable` 
`MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0"
SDWORD-3

DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-standard]You have 
an error in your SQL syntax.  Check the manual that corresponds to your MySQL server 
version for the right syntax to use near 'EXISTS (SELECT `MS2`.`ID`  FROM 
`featureenable` `MS2` WHERE (`M (1064)

Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: <[EMAIL PROTECTED]>
Pgr: <[EMAIL PROTECTED]>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Enforce value on select

[John Mistler]

Thanks for the reply.  There is a slight difference in what I need from the
"IFNULL" function.  It will only return the specified value if the column is
null on a row that actually exists.  I am needing a function that will
return the specified value if the row does NOT exist.  Any other ideas?

SELECT nameColumn from theDatabase WHERE rowID = 5;
 
(when no row has ID "5")
 
result --> empty set (I want a value like '0' or something)

Thanks again!

-John

on 10/10/04 8:12 PM, liang lei at [EMAIL PROTECTED] wrote:

> --- John Mistler <[EMAIL PROTECTED]>
>> Is there a way to force SOME value to be returned
>> from a SELECT query when
>> the result is empty set?  For instance:
>> 
>> SELECT nameColumn from theDatabase WHERE rowID = 5;
>> 
>> (when no row has ID "5")
>> 
>> result --> empty set
>> 
>> I would like for it to return some value, such as ''
>> or 0 . . .
>> 
>> Thanks,
>> 
>> John
>> 
>> 
>> -- 
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: 
>> 
> http://lists.mysql.com/[EMAIL PROTECTED]
>> 
>> 
> 
> select ifnull(column,'0') from table
> 
> _
> Do You Yahoo!?
> 150??MP3
> http://music.yisou.com/
> ???
> http://image.yisou.com
> 1G??1000???
> http://cn.rd.yahoo.com/mail_cn/tag/1g/*http://cn.mail.yahoo.com/event/mail_1g/


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Will series of limited selects return entire table?

[Jim Kraai]

Jeff,

Yes, unless the contents of the table have changed during the
'paging' sequence.

1. The SQL language specification explicitly doesn't guarantee
a particular or reproducible order on rows returned for a select
unless an ORDER BY clause is specified.  How/whether order
remains the same depends on the underlying implementation, in
the case of MySQL, the underlying engines, whether they be MyISAM,
InnoDB, MaxDB, heap, etc.  MySQL engines will probably never
change the order that it returns things in unless a table change
occurs; a row is added/updated/deleted or the table is rebuilt/
checked/packed.

2. If a row is inserted in such a way that it would appear in a
page that has already been displayed then it would not appear in
a subsequent page display, but the last row displayed in the
previous 'page' would be displayed again.

3. If a row that had been previously displayed is deleted, then
the next select will be shy what would have been the first record
that was to be displayed, and that record will never be displayed.

Strategies to use for guaranteeing that all rows are displayed?

First of all--always use an ORDER BY clause when doing these.

1. If your table is guaranteed not to change during the paging
process, then you're good to go--all will be displayed.

2. If adds/deletes are possible during the process
  a. if you can guarantee that the paging sequence will be
completed, LOCK the tables prior to beginning and _don't_ forget
to UNLOCK them when done.
  b. If you can't guarantee the conditions for (a), then consider
selecting the rows into a temporary table and doing the paging on
that table instead of the original.

--jim

--
>
> Date: Sat, 09 Oct 2004 08:22:14 -0700
> From: Jeff Barr <[EMAIL PROTECTED]>
> Subject: Will series of limited selects return entire table?
>
> If I have a MyIsam table that is not subject to modification,
> is a series of select calls like this:
>
> select * from table limit 0,100;
> select * from table limit 100,100;
> select * from table limit 200,100;
> ...
>
> Guaranteed to return each row in the table exactly once (as long
> as I keep going until the end)?
>
> My limited testing seems to say that this is the case. However,
> the documentation is silent on this issue. I would prefer not
> to count on this if is not guaranteed.
>
> Jeff;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

SOLVED: Read-Only DB User

[Lee Zelyck]

Thanks James!
   The actual command that worked for me was as
follows:

mysql> GRANT SELECT ON db.* TO [EMAIL PROTECTED]
IDENTIFIED BY '2user3';
Query OK, 0 rows affected (0.09 sec)

I receieved errors while trying to include the
'PASSWORD' option, as that seemed to be wanting a the
password in hex format.

Anyway, thanks again James!
Lee


--- James Weisensee <[EMAIL PROTECTED]> wrote:

> Lee,
> On the MySQL side:
> 
> GRANT SELECT ON db.* TO user@'host' IDENTIFIED BY
> PASSWORD 'password';
> 
> This will give 'user' from 'host' SELECT privileges
> to
> all tables on 'db' (db.*).
> 
> http://dev.mysql.com/doc/mysql/en/GRANT.html
> 
> HTH,
> James






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Read-Only DB User

[James Weisensee]

Lee,
On the MySQL side:

GRANT SELECT ON db.* TO user@'host' IDENTIFIED BY
PASSWORD 'password';

This will give 'user' from 'host' SELECT privileges to
all tables on 'db' (db.*).

http://dev.mysql.com/doc/mysql/en/GRANT.html

HTH,
James


--- Lee Zelyck <[EMAIL PROTECTED]> wrote:

> Hi All,
>I'm sorry to access such a basic question, but I
> couldn't find a specific answer to it in the mysql
> manual pages.
> 
>The question is, how would someone create a basic
> read-only user for a single db?  I just intend for
> it
> to be used by a script to validate data in the db
> itself.
> 
>Anyway, if anyone can provide a lean and concise
> statement that will provide this, it would be very
> much appreciated.
> 
> Thanks!
> Lee
> 
> __
> Do You Yahoo!?
> Tired of spam?  Yahoo! Mail has the best spam
> protection around 
> http://mail.yahoo.com 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Read-Only DB User

[Lee Zelyck]

Hi All,
   I'm sorry to access such a basic question, but I
couldn't find a specific answer to it in the mysql
manual pages.

   The question is, how would someone create a basic
read-only user for a single db?  I just intend for it
to be used by a script to validate data in the db
itself.

   Anyway, if anyone can provide a lean and concise
statement that will provide this, it would be very
much appreciated.

Thanks!
Lee

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stuck in an upgrade to mysql 4.1 and other pains ...

[rik onckelinx]

Hi Michael,

Thx for your answer: About your feedback:

Current situation: I moved back to my working apache 2 / mysql 4.0/ php 4.3 
installation after trying to compile mysql 4.1 from source this afternoon. 
Which (after following the guidelines on mysql.com) returned in an error in 
the configure process (no temcap functions library found). I was able to 
compile php5 / apache 2 from source, but wouldn't take the risk so I switched 
back.

> As a side note, I'm not sure what you mean by "not possible".  Rewriting
> subqueries as joins is usuallly not that difficult, and joins are often
> faster.

With not "possible", I meant "not capable" ;-) But you says it right: "usually 
not that difficult". I do not consider myself as an expert, but usually I'm 
able tor rewrite my subqueries as joins, anyway not in this case. But this is 
actually not an issue. I still want to move forward to mysql 4.1.

> Hence this copy of php is linked against the mysql 4.0 client library,
> which does not support the improved authentication protocol added in mysql
> 4.1. One option would be to build your own php from source, linking against
> your newly installed mysql 4.1 client library.

Questions:
A] When I compile php from source, should I remove the old rpm's first that 
came with the installation of my suse 9.1 distro? Or can I leave it the way 
it is?
-b] Can I just compile php 5 or would that cause any new troubles? Or would it 
be better to use php 4 (for me it's either right now, but I can image that I 
want to use the new OO-tools from php 5).
C] When you say link php against mysql 4.1 client library; does I see it right 
that this needs to be done while building php? Or can I still do this 
afterwards? It should be something like (found this on builder.com)

$ ./configure --prefix=/usr/local/php5 
--with-apxs2=/usr/local/apache2/bin/apxs --with-libxml-dir=/usr/local/lib 
--with-zlib --with-zlib-dir=/usr/local/lib --with-mysql=/usr/local/mysql 
--with-mysqli=/usr/local/mysql/bin/mysql_config --with-gd --enable-soap 
--enable-sockets

D] Any other ideas of what I have take care off?

> Same error.  Mysql believes you did not specify a password in
> mysql_connect().

Idd. Eventhough I did ;)

> It appears so far that mysql 4.1 is running fine.  Your problems seem to be
> with php, your script, and failure to use passwords when connecting.

1/ To my believe, my script works fine as it is the basic way to connect to 
mysql. At least, I use that script since decades ;)
2/ MySQL 4.1 is running fine indeed, as far as I can see, because I was able 
to connect to phpmyadmin and create new db/users/tables with it.
3/ I hope that installing php from source would be a solution.

Best regs and thx,

Rik

Op maandag 11 oktober 2004 00:44, schreef Michael Stassen:
> rik wrote:
> > Hello all
> >
> > Sitting stuck in an upgrade to mysql 4.1, I thought this mailing list
> > would be a good idea to get help.
> >
> > I've a php4/mysql application developed on a windows xp machine. Right
> > now I want to migrate this app to my linux machine. Since the app uses
> > subqueries, I need to upgrade MySQL on my linux machine from 4.0 to 4.1.
> > Subqueries vs joins is not an issue, since it's (at least for me and some
> > other developers not possible).
>
> As a side note, I'm not sure what you mean by "not possible".  Rewriting
> subqueries as joins is usuallly not that difficult, and joins are often
> faster.
>
> > My machine is a very recent pc running SUSE 9.1 pro. MySQL was running
> > fine so far, with no problems at all. Since upgrading to 4.1 on XP was as
> > simple as installing the new 4.1 server and client, I tried to do it the
> > same way on Linux. Without success.
> >
> > 1]So I decided to reinstall suse from cd, without any mysql but with a
> > working apache2/php 4.3 configuration (tested and ok).
> >
> > 2]I've downloaded the mysql 4.1.5 rpm's from mysql.com
> >
> > MySQL-client-4.1.5-0.i386.rpm
> > MySQL-server-4.1.5-0.i386.rpm
> > MySQL-shared-4.1.5-0.i386.rpm
> > 3]Command line installation like rpm --install -v -h MySQL*.rpm
> >
> > 4]Installed phpmyadmin, and I was unable the start it getting the error
> > that php-mysql was missing. Installed php-mysql via Yast from the
> > suse-cd, but Yast wants allways to install the MySQL-shared-4.0 with it
> > (depen.).
>
> Hence this copy of php is linked against the mysql 4.0 client library,
> which does not support the improved authentication protocol added in mysql
> 4.1. One option would be to build your own php from source, linking against
> your newly installed mysql 4.1 client library.
>
> > 5]Once MySQL-shared-4.0 was installed, I was still not able to connect to
> > phpmyadmin getting the error " Access denied for user
> > 'tour'@'localhost' (using password: NO). That moment I was able to type
> > in the command line
> > shell> mysql
> >
> > 6]On http://dev.mysql.com/doc/mysql/en/Old_client.html I found this
> > solution: Reset the password to pre-4.1 style fo

Re: Enforce value on select

[Martin Gainty]

John
You will have to soemthing similar to Oracle Decode
Martin-
- Original Message -
From: "John Mistler" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Sunday, October 10, 2004 9:23 PM
Subject: Enforce value on select


> Is there a way to force SOME value to be returned from a SELECT query when
> the result is empty set?  For instance:
>
> SELECT nameColumn from theDatabase WHERE rowID = 5;
>
> (when no row has ID "5")
>
> result --> empty set
>
> I would like for it to return some value, such as '' or 0 . . .
>
> Thanks,
>
> John
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Date Type Probelm

[John Bonnett, R&D Australia]

In your query you have converted the Date column into a string by using
DATE_FORMAT so it is a string of bytes. If you want it to still be a
date then don't format it.

John Bonnett

-Original Message-
From: Thomas Trutt [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 7 October 2004 2:29 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Date Type Probelm


Hello all.. I'm running into a little bit of a problem.. I'm writing a 
program in VB.NET and every time i try to load query results into my 
dataset i keep getting:

/An Unhandled Exception of type 'System.InvalidOperationException' 
occurred in system.data.dll

Additional information: Inconvertable type mismatch between SourceColumn

'Tick Date' of Byte[] and the Datacolumn 'Tick Date' of DateTime.

/The Query I'm using is:

/SELECT DATE_FORMAT(date,'% %M %e %Y') as 'Tick Date', SUM( IF " 
(Ticktype='1',1,0)) as 'Technical', SUM( IF (Ticktype='2',1,0)) as 
'Reference', SUM( IF (Ticktype='3',1,0)) as 'Directional', SUM( IF 
(Ticktype='4',1,0)) as 'Search' FROM tickcount GROUP BY 
DATE_FORMAT(date,'% %M %e %Y')

/ I have tried changing the datatype in the dataset definition but the 
only one that works is unasignedbyte, and that returns " Byte [] Array" 
instead of the date that I'm formating..
I'm using MySQL version 3.23.37, and VB.NET v.1.2

Any help would be great,

Many thanks,

Tom T

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Strange join results

[Ville Mattila]

Hi there!
I have three tables:
1. Products
 - id
 - name
2. OutOrders (orders from customers to us)
 - id
 - productid
 - quantity
3. InOrders (our ourders to the traders)
 - id
 - productid
 - quantity
When I try to find out the current amount of products in our stock, and 
ordered quantities I use this query:

SELECT p.id, SUM(out.quantity) ordered_out, SUM(in.quantity) ordered_in 
FROM products p LEFT JOIN outorders out ON (out.productid = p.id) LEFT 
JOIN inorders (in.productid = p.id);

Even I have only one "OutOrder" for certain product, I get ordered_out 
value as 2. ordered_in value is anyway correct. If I remove all things 
related to "InOrders" I will get correct result to ordered_out.

Have I missed something? Isn't LEFT JOIN the correct query to join 
tables to the "main" table (in this case products)?

Thanks!
Ville
--
.
Ville Mattilagsm 040 849 7506
Pilotmedia  fax (03) 458 9080
Tapiolankatu 11   [EMAIL PROTECTED]
39500 Ikaalinen www.pilotmedia.fi
.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stuck in an upgrade to mysql 4.1 and other pains ...

[Michael Stassen]

rik wrote:
Hello all
Sitting stuck in an upgrade to mysql 4.1, I thought this mailing list would be 
a good idea to get help.

I've a php4/mysql application developed on a windows xp machine. Right now I 
want to migrate this app to my linux machine. Since the app uses subqueries, 
I need to upgrade MySQL on my linux machine from 4.0 to 4.1. Subqueries vs 
joins is not an issue, since it's (at least for me and some other developers 
not possible).
As a side note, I'm not sure what you mean by "not possible".  Rewriting 
subqueries as joins is usuallly not that difficult, and joins are often 
faster.

My machine is a very recent pc running SUSE 9.1 pro. MySQL was running fine so 
far, with no problems at all. Since upgrading to 4.1 on XP was as simple as 
installing the new 4.1 server and client, I tried to do it the same way on 
Linux. Without success. 

1]So I decided to reinstall suse from cd, without any mysql but with a working 
apache2/php 4.3 configuration (tested and ok). 

2]I've downloaded the mysql 4.1.5 rpm's from mysql.com
MySQL-client-4.1.5-0.i386.rpm
MySQL-server-4.1.5-0.i386.rpm
MySQL-shared-4.1.5-0.i386.rpm
3]Command line installation like rpm --install -v -h MySQL*.rpm
4]Installed phpmyadmin, and I was unable the start it getting the error that 
php-mysql was missing. Installed php-mysql via Yast from the suse-cd, but 
Yast wants allways to install the MySQL-shared-4.0 with it (depen.). 
Hence this copy of php is linked against the mysql 4.0 client library, which 
does not support the improved authentication protocol added in mysql 4.1. 
One option would be to build your own php from source, linking against your 
newly installed mysql 4.1 client library.

5]Once MySQL-shared-4.0 was installed, I was still not able to connect to 
phpmyadmin getting the error " Access denied for user 
'tour'@'localhost' (using password: NO). That moment I was able to type in 
the command line
shell> mysql

6]On http://dev.mysql.com/doc/mysql/en/Old_client.html I found this solution:
Reset the password to pre-4.1 style for each user that needs to use a pre-4.1 
client program. This can be done using the SET 	PASSWORD statement and the 
OLD_PASSWORD() function: 
		
		mysql> SET PASSWORD FOR
		-> 'some_user'@'some_host' = OLD_PASSWORD('newpwd');
which I did.
OK
7]From that moment on I was able to connect to phpmyadmin (using the proper 
password in the phpmyadmin config file) and do some database stuff.

8]But from the same moment, when I want to type in the commandline 
	
		linux> mysql
It returns this error in the command line

		linux:~ # mysql
		ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using 
password: NO)
Look at the error message.  It says, "using password: NO".  You need the 
password to connect.  Try

  linux> mysql -p
from the command line.  You'll be prompted for your password.
Since I was able to store my application data through phpmyadmin in a db, I've 
tried to run my application in my browser, but it returns the error:

		Warning: mysql_connect(): Access denied for user '...'@'localhost' (using 
password: NO) 
in /home/.../public_html/application_0.4/scripts/connections/connect.php on 
line 9
Same error.  Mysql believes you did not specify a password in mysql_connect().

Which means to my opinion that users don't have access (even new created mysql 
users and after changing the user values in the connection script from my 
app).

9] MySQL.com says how to reset password stuff and so on, but therefore I've to 
type in the command line >mysql, which returns an error (see 8).

My wish: running mysql 4.1 without problems.
It appears so far that mysql 4.1 is running fine.  Your problems seem to be 
with php, your script, and failure to use passwords when connecting.

My question: does anyone recognizes the above problems? Can anybody give me an 
insight on what I do wrong, how I can run it smoothly?). Would really 
appreciate help (and I believe with me many others with the same problem, 
since googling many hours didn't give me the right answer).

Thanks,
Rik
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

auth and ssh tunnel

[Carl Karsten]

I am trying to connect to MySql with an SSH tunnel that terminates on the same
box MySqld is running on.  I would expect that I do L3306:localhost:3306 and
grant access to [EMAIL PROTECTED]  This doesn't seem to work.  I got this to work on
a test box: L3306:my.sql.IP:3306 grant access to [EMAIL PROTECTED] but I don't
really want to expose the box like that.  I realize that a firewall could block
any real exposure, but I don't have that kind of authority over the box.

Is there a howto for this kind of setup?

Carl Karsten
http://www.personnelware.com/carl/resume.html


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Enforce value on select

[John Mistler]

Is there a way to force SOME value to be returned from a SELECT query when
the result is empty set?  For instance:

SELECT nameColumn from theDatabase WHERE rowID = 5;

(when no row has ID "5")

result --> empty set

I would like for it to return some value, such as '' or 0 . . .

Thanks,

John


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Finding records not in a set

[Bartis, Robert M (Bob)]

Thanks for the tip. I'm still facing an issue where I think I have the right syntax 
and I'm receiving an ODBC failure. Do you have any suggestions on how to go about 
understanding why the failure and hence how to correct it?

Bob

-Original Message-
From: Martin Gainty [mailto:[EMAIL PROTECTED]
Sent: Saturday, October 09, 2004 9:30 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Finding records not in a set


Keep in mind EXISTS/NOT EXISTS are for SQL (Parent) Heavy queries
Otherwise you should use IN/NOT IN

Martin Gainty
617-852-7822

Man1: In my next life I want to get paid for solving problems
Man2: You sound like a misguided capitalist!
Man1: But how do I pay my bills??
Man2: You can always beg on the street..problem solved


>From: "Bartis, Robert M (Bob)" <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>Subject: RE: Finding records not in a set
>Date: Sat, 9 Oct 2004 08:53:19 -0400 MIME-Version: 1.0
>Received: from lists.mysql.com ([213.136.52.31]) by mc7-f3.hotmail.com with 
>Microsoft SMTPSVC(5.0.2195.6824); Sat, 9 Oct 2004 05:54:52 -0700
>Received: (qmail 20728 invoked by uid 109); 9 Oct 2004 12:53:26 -
>Received: (qmail 20709 invoked from network); 9 Oct 2004 12:53:26 -
>Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 
>192.11.226.161 as permitted sender)
>X-Message-Info: JGTYoYF78jEAJ70xKNiMjuhlQYGFj9no
>Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm
>List-ID: 
>Precedence: bulk
>List-Help: 
>List-Unsubscribe: 
>
>List-Post: 
>List-Archive: http://lists.mysql.com/mysql/173887
>Delivered-To: mailing list [EMAIL PROTECTED]
>Message-ID: 
><[EMAIL PROTECTED]>
>X-Mailer: Internet Mail Service (5.5.2657.72)
>X-Virus-Checked: Checked
>Return-Path: [EMAIL PROTECTED]
>X-OriginalArrivalTime: 09 Oct 2004 12:54:52.0865 (UTC) 
>FILETIME=[2B57B310:01C4ADFF]
>
>I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I 
>get an ODBC Failed call when I run the following simplified query
>
>SELECT *
>FROM main_db
>WHERE NOT EXISTS (SELECT * FROM featureenable WHERE 
>featureenable.FeatureKey = main_db.FeatureKey);
>
>Bob
>
>-Original Message-
>From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED]
>Sent: Saturday, October 09, 2004 8:48 AM
>To: [EMAIL PROTECTED]
>Subject: Finding records not in a set
>
>
>I have a query, call qry_AssociatedFeatures, that finds all features 
>associated with a given plan
>
>SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, 
>main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, 
>featureenable.PlanName
>FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON 
>main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = 
>main_db.FeatureKey
>WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname]));
>
>So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, 
>or all the features not associated with a plan. Complication here is the 
>feature enable table can have the same feature key associated with multiple 
>plans. I assumed if I took the table containing the unique set of features 
>and query for those records whose feature key is not present in the 
>qry_AssociatedFeatures query I would get what I wanted
>
>SELECT DISTINCT main_db.Feature, main_db.FeatureKey
>FROM main_db, qry_AssociatedFeatures
>WHERE (((main_db.FeatureKey)<>[qry_AssociatedFeatures].[FeatureKey]));
>
>Unfortunately, I still see records in the qry_UnassociatedFeatures that are 
>also present in the qry_AssociatedFeatures. Any suggestions?
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>

_
Check out Election 2004 for up-to-date election news, plus voter tools and 
more! http://special.msn.com/msn/election2004.armx

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

MySQL and validation rule

[Jonathan Jesse]

As mentioned previously I am a MySQL newbie.  I have read most of the Paul
DuBois book and portions I have found relevant of the manual to help me out,
however I have not found an answer to this question, maybe it is not even
needed.

I have used MS Access a lot and one of the ways I use it is to track
hostname to username to ip address at work.  In the forms we have a
validation rule on the field ip address which allows only numbers in
XXX.XXX.XXX.XXX format.  Is there such a way to do this in MySQL or would
that be on the application that I would use to open/insert/etc ?

Thanks in advance,

Jonathan


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]