Re: IDE

2009-06-23 Thread Janek Bogucki 
Have you tried SQL Developer? It works great with Oracle and also
supports MySQL,

http://www.oracle.com/technology/products/database/sql_developer/files/what_is_sqldev.html

Cheers,
-Janek

On Tue, 2009-06-09 at 19:40 +0300, Mosaed zamil wrote:
 Hello all,
 I used Database Workbench on trial bases. It is nice. I plan to purchase an
 IDE. Is is it the best around. Your feed back is appreciated.
 yours
 mosaed


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Date Time

2009-05-22 Thread Janek Bogucki 
Hi John,

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-types.html includes
some information about acceptable literal forms for dates and times.

'Thu May 21 03:15:28 + 2009' is not an acceptable literal form but
this is how to parse it APART from the time zone component. I could not
see from the documentation how to specify the time zone component so the
format below IGNORES the time zone.

mysql create table t(d datetime);

mysql insert into t(d) values(str_to_date('Thu May 21 03:15:28 + 2009', 
'%a %b %e %H:%i:%s + %Y'));

mysql select * from t;
+-+
| d   |
+-+
| 2009-05-21 03:15:28 |
+-+
1 row in set (0.01 sec)

On Thu, 2009-05-21 at 15:19 -0600, John Meyer wrote:
 Is Thu May 21 03:15:28 + 2009 a valid date/time string?
 

-- 
Best Regards,
-Janek Bogucki, CMDEV 5.0.
StudyLink. Helping People Realise Their Potential.
http://studylink.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Stored Procedure Data Types

2009-05-22 Thread Janek Bogucki 
Hi,

Use a temporary table to store the ids and join to it for the final
update? That will at least avoid an error when the cursor selects zero
records.

Cheers,
-Janek

On Wed, 2009-05-20 at 16:05 -0400, W. Scott Hayes wrote:
 Hello,
 
 I would like to do a select on a table to get back the IDs of some of
 the records.
 
 Then take those IDs and do a single update using a WHERE clause like
 (recordID IN (2,44,21))
 
 My question is:
 
 Can I build a string using a cursor that has all of the IDs and then
 issue an update using the string as part of the WHERE clause?
 
 Are there functions that facilitate this better?  I'm wondering if
 there is some sort of column function that will grab the IDs from the
 initial select.
 
 Below is my code.  Thanks for any advice.
 
 DELIMITER $$
 
 DROP PROCEDURE IF EXISTS sp_getNextQueueBlock$$
 
 CREATE PROCEDURE sp_getNextQueueBlock()
 BEGIN
 DECLARE l_LinkQueueID INTEGER;
 DECLARE no_more_queue_items INT DEFAULT 0;
 DECLARE l_updateString VARCHAR(2000) DEFAULT '';
 DECLARE queue_csr CURSOR FOR
   SELECT LinkQueueID FROM linkqueue WHERE Completed  0 LIMIT 200;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_queue_items=1;
 
 START Transaction;
 OPEN queue_csr;
 queue_loop:LOOP
   FETCH queue_csr INTO l_LinkQueueID;
   IF no_more_queue_items=1 THEN
   LEAVE queue_loop;
   END IF;
   SET l_updateString=CONCAT(l_updateString,', ',l_LinkQueueID);
 END LOOP queue_loop;
 
 IF LENGTH(l_updateString)  2 THEN
   SET l_updateString=SUBSTRING(l_updateString,3,LENGTH(l_updateString)-2);
 END IF;
 
 UPDATE linkqueue SET Completed = 0 WHERE (LinkQueueID IN (l_updateString));
 commit;
 END$$
 
 DELIMITER ;
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: load data into temporary table

2009-05-19 Thread Janek Bogucki 
Hi,

mysql create temporary table t(i int);

mysql \! echo 1  /tmp/data.txt

mysql load data infile '/tmp/data.txt' into table t;
Query OK, 1 row affected (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

mysql select * from t;
+--+
| i|
+--+
|1 |
+--+
1 row in set (0.00 sec)

Best Regards,
-Janek, CMDEV 5.0.
StudyLink. Helping People Realise Their Potential.
http://studylink.com


On Tue, 2009-05-19 at 02:57 +0100, Alex K wrote:
 Hello,
 
 Would anyone know how to load data infile into a temporary table?
 
 Thank you,
 
 Alex
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: If condition in select query / insert /update

2009-05-18 Thread Janek Bogucki 
Hi,

mysql create table t(i int);

mysql insert into t values(1),(2),(3);

mysql select i, if(i = 1, 'low', 'high') from t order by i;
+--+---+
| i| if(i = 1, 'low', 'high') |
+--+---+
|1 | low   |
|2 | high  |
|3 | high  |
+--+---+
3 rows in set (0.06 sec)

Take a look at the documentation for IF(),
  http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

All the best,
-Janek, CMDEV 5.0.
StudyLink. Helping People Realise Their Potential.
http://studylink.com

On Mon, 2009-05-18 at 09:55 +0530, bharani kumar wrote:
 Hi all ,
 
 Can u give one example query ,
 
 Which contain the IF condition ,
 
 Because here before am not used the IF and all ,
 
 
 Thanks
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Inserting a default null date

2009-05-15 Thread Janek Bogucki 
Hi Octavian,

One approach is to use a trigger,

mysql set sql_mode = '';

mysql create table temp_date(d date default null);

mysql create trigger temp_date_bi before insert on temp_date for each row set 
new.d = if(new.d = '-00-00', null, new.d);

mysql insert into temp_date(d) values('2009-13-99');

mysql select * from temp_date;
+--+
| d|
+--+
| NULL |
+--+
1 row in set (0.01 sec)

mysql insert into temp_date(d) values('2009-11-19');

mysql select * from temp_date;
++
| d  |
++
| NULL   |
| 2009-11-19 |
++
2 rows in set (0.07 sec)

Cheers,
-Janek
CMDEV 5.0

On Fri, 2009-05-15 at 11:32 +0300, Octavian Rasnita wrote:
 Hi,
 
 I have a table with a column like:
 
 date date default null,
 
 If I enter an empty string in it, the default null value is added (as it 
 should).
 
 But if I enter an invalid date by mistake, the date -00-00 date date is 
 entered instead of the default null, and this is not good.
 
 Can I do something to force MySQL to insert a null date if the entered date 
 is an invalid one?
 
 Thank you.
 
 --
 Octavian
 
 
 
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: Unix compress equivalent

2009-05-11 Thread Janek Bogucki 
Hi Olaf,

If you only need to compress the column *while* loading it from the csv file 
then use load data infile with a user variable to do the compression at load 
time,.

mysql create table t(uncompressed varchar(4000), compressed varbinary(1000));
Query OK, 0 rows affected (0.07 sec)

mysql \! echo -e 'abcde\nfghijk\n'  /tmp/test.csv

mysql load data infile '/tmp/test.csv' into table t (@raw) set uncompressed = 
@raw, compressed = compress(@raw);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

mysql select uncompressed, @a:=hex(compress(uncompressed)), 
@b:=hex(compressed), @a = @b from t;
+--+--+--+-+
| uncompressed | @a:=hex(compress(uncompressed))  | @b:=hex(compressed) 
 | @a = @b |
+--+--+--+-+
| abcde| 0500789C4B4C4A4E49050005C801F0   | 
0500789C4B4C4A4E49050005C801F0   |   1 |
| fghijk   | 0600789C4B4BCFC8CCCA060008870274 | 
0600789C4B4BCFC8CCCA060008870274 |   1 |
|  |  | 
 |   1 |
+--+--+--+-+
3 rows in set (0.04 sec)


Cheers,
-Janek

On Fri, 2009-05-08 at 12:29 -0400, Olaf Stein wrote:
 Hi all
 
 What is the equivalent in unix (more specifically python) to the compress()
 function.
 
 I am trying to make csv file for use with load data infile and am wondering
 how to compress the strings that I would usually compress with compress() in
 a regular sql statement. The field I am writing this into is longblob and I
 need the compressed version here to be identical to what compress() would do
 
 Thanks
 olaf
 
 - Confidentiality Notice:
 The following mail message, including any attachments, is for the
 sole use of the intended recipient(s) and may contain confidential
 and privileged information. The recipient is responsible to
 maintain the confidentiality of this information and to use the
 information only for authorized purposes. If you are not the
 intended recipient (or authorized to receive information for the
 intended recipient), you are hereby notified that any review, use,
 disclosure, distribution, copying, printing, or action taken in
 reliance on the contents of this e-mail is strictly prohibited. If
 you have received this communication in error, please notify us
 immediately by reply e-mail and destroy all copies of the original
 message. Thank you.
 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Oracle , what else ?

2009-04-28 Thread Janek Bogucki 
On Thu, 2009-04-23 at 17:58 +0100, Gabriel - IP Guys wrote:
  The real question is whether they will let MySQL
  wither
  and die by not providing updates for it?
 
 Well, MySQL is open source, right? And the source is available? I'm
 sure
 a team of devs will come to the rescue. As for MySQL, as a company,
 they
 don't make even close to the potential money they can. People do not
 really go to MySQL for support, which is the model RedHat uses. For
 MySQL, it's different, because the MySQL userbase by their very
 nature,
 solve problems for a living. They have the attitude of how can I fix
 things? How do I make things work the way I want? This has a serious
 adverse effect on MySQL as a company, because the number one revenue
 stream for any company whos main 'product' or 'service' is open source
 based, is the support contract. 
 

The code is available under the GPL but the documentation is not.
Without adequate documentation a project becomes less accessible and
less used.

-Janek


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

RE: Oracle , what else ?

2009-04-28 Thread Janek Bogucki 

On Thu, 2009-04-23 at 17:58 +0100, Gabriel - IP Guys wrote:
  The real question is whether they will let MySQL
  wither
  and die by not providing updates for it?
 
 Well, MySQL is open source, right? And the source is available? I'm
 sure
 a team of devs will come to the rescue. As for MySQL, as a company,
 they
 don't make even close to the potential money they can. People do not
 really go to MySQL for support, which is the model RedHat uses. For
 MySQL, it's different, because the MySQL userbase by their very
 nature,
 solve problems for a living. They have the attitude of how can I fix
 things? How do I make things work the way I want? This has a serious
 adverse effect on MySQL as a company, because the number one revenue
 stream for any company whos main 'product' or 'service' is open source
 based, is the support contract. 
 

The code is available under the GPL but the documentation is not.
Without adequate documentation a project becomes less accessible and
less used.

-Janek


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

mysql_upgrade not running mysql_check: Access denied for user 'root'@'localhost'

2007-04-26 Thread Janek Bogucki 
Hi,

When I try mysql_upgrade I get a connection problem,

$ mysql_upgrade -p
Enter password:
/usr/bin/mysqlcheck: Got error: 1045: Access denied for user 
'root'@'localhost' (using password: YES) when trying to connect
Error executing '/usr/bin/mysqlcheck --check-upgrade --all-databases 
--auto-repair --user=root'

but I am able to run mysql_check directly without a problem,

$ mysqlcheck --check-upgrade --all-databases --auto-repair -u root -p
Enter password:
main.provider  OK
main.request_dispatch  OK
mysql.columns_priv OK
mysql.db   OK
mysql.func OK
mysql.help_categoryOK
mysql.help_keyword OK
mysql.help_relationOK
mysql.help_topic   OK
mysql.host OK
mysql.proc OK
mysql.procs_priv   OK
mysql.tables_priv  OK
mysql.time_zoneOK
mysql.time_zone_leap_secondOK
mysql.time_zone_name   OK
mysql.time_zone_transition OK
mysql.time_zone_transition_typeOK
mysql.user OK

Does anyone know why this would be?

This is my version information:

$ mysqladmin version
mysqladmin  Ver 8.41 Distrib 5.0.32, for pc-linux-gnu on i486
Copyright (C) 2000 MySQL AB  MySQL Finland AB  TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license

Server version  5.0.32-Debian_7etch1-log
Protocol version10
Connection  Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock

Cheers,
-Janek

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

Duplication in sles9 for my.cnf

2007-04-16 Thread Janek Bogucki 
Hi,

I have noticed in the man page for mysql from
MySQL-client-community-5.0.37-0.sles9.i586.rpm there is a repetition in
the my.cnf files read by mysql. I am wondering if this is at all
significant.

This is what man mysql shows for the
MySQL-client-community-5.0.37-0.sles9.i586.rpm install,

mysql  Ver 14.12 Distrib 5.0.37, for pc-linux-gnu (i686) using
readline 5.0

Default options are read from the following files in the given
order:
/etc/my.cnf ~/.my.cnf /etc/my.cnf

So the order is
  
  /etc/my.cnf
  ~/.my.cnf
  /etc/my.cnf

On Debian Sarge with 4.1.11, the order is more sensible,

mysql  Ver 14.7 Distrib 4.1.11, for pc-linux-gnu (i386)

Default options are read from the following files in the given
order:
/etc/mysql/my.cnf /var/lib/mysql/my.cnf ~/.my.cnf

The order on Sarge, 4.1.11 is

  /etc/mysql/my.cnf
  /var/lib/mysql/my.cnf
  ~/.my.cnf

Cheers,
-Janek




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

Re: Synchronizing a remote database with a local database

2007-03-16 Thread Janek Bogucki 
On Fri, 2007-03-16 at 18:43 +0900, Dave M G wrote:
 MySQL Users,
 
 I have a local website development environment where I have a master 
 MySQL database.
 
 I have several web sites which use the exact same database structure.
 
 The structure of the master database doesn't change very often, but it 
 does sometimes. When that happens, I want to be able to synchronize all 
 the web sites to match it.
 
 Currently, I'm creating a bash sell script so that I can update all my 
 web sites in one go. I've got it so that it will upload all the newest 
 PHP and other web files.
 
 For MySQL, so far I've figured out that I can create a .sql file with 
 the latest database structure with this command:
 
 mysqldump -u root -ppassword articlass_db --no-data 
 --result-file=backup_db.sql
 
 But I'm now stuck on how to use that .sql file to upload the new 
 structure to each web site's MySQL server. Can this be done?
 
 And can it be done in a non-destructive way. I mean, the web sites may 
 include data that I don't want to lose. So I don't want the new data 
 structure to wipe out any existing structure. I just want to compare the 
 master database structure with the one on the web site, and if there are 
 new tables or columns, then add them.
 
 Is this possible without third party commercial software?
 
 Thank you for any advice.
 
 -- 
 Dave M G
 Ubuntu 6.10 Edgy Eft
 Kernel 2.6.20-5-generic
 Pentium D Dual Core Processor
 PHP 5, MySQL 5, Apache 2
 

The developers of http://dbdeploy.com/ tell me it supports MySQL despite
that not being clear on the site.

Cheers,
-Janek


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