RE: Install on WIN2k
Stefan, I have checked these things. To re-state the problem. I already have mysql up and running on several servers with apps accessing them from my web site. This is just a new install but it has been so long that I have forgotten what to do about this localhost situation. Thanks. -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 23, 2003 2:27 AM To: Norris, Joseph; Mysql_List (E-mail) Subject: Re: Install on WIN2k Norris, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Not to be impolite, but I'd say you should simply start the MySQL server :) Check system control administration services if there is a service called Mysql, and if it's running. If not, install the service (I'd suggest to use winmysqladmin.exe for this) and start it. If you're having problems like the MySQL server starts but crashes immediately after starting, you can try this in a DOS window: c:\mysql\bin mysqld --standalone --console This will give you output in the DOS window so you can see why it keeps crashing. And, of course, you can use any other server than mysqld, e.g. mysqld-max-nt.exe, to try the same. mysqld-max... has InnoDB tables switched on by default, and there is a good chance something's wrong in your setup and InnoDB can't create its tablespace etc. You will see all of this in the DOS window output, though. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 1:55 AM Subject: Install on WIN2k Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - 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 - 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: Install on WIN2k
Thanks Stefan - I found the problem in my my.ini file. Fixed and on. Thanks for the help. -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 23, 2003 3:15 PM To: Norris, Joseph; Mysql_List (E-mail) Subject: Re: Install on WIN2k Joseph, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) Okay, if MySQL is up and alive, you should check on which port it is listening. For instance, you could (in a DOS window) do: mysqld --print-defaults to find out if there are some strange configuration settings. If the port is the problem, you could try to connect somehow like this: mysql -h localhost -p 3307 HTH, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: 'Stefan Hinz, iConnect (Berlin)' [EMAIL PROTECTED]; Norris, Joseph [EMAIL PROTECTED]; Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 5:20 PM Subject: RE: Install on WIN2k Stefan, I have checked these things. To re-state the problem. I already have mysql up and running on several servers with apps accessing them from my web site. This is just a new install but it has been so long that I have forgotten what to do about this localhost situation. Thanks. -Original Message- From: Stefan Hinz, iConnect (Berlin) [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 23, 2003 2:27 AM To: Norris, Joseph; Mysql_List (E-mail) Subject: Re: Install on WIN2k Norris, ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Not to be impolite, but I'd say you should simply start the MySQL server :) Check system control administration services if there is a service called Mysql, and if it's running. If not, install the service (I'd suggest to use winmysqladmin.exe for this) and start it. If you're having problems like the MySQL server starts but crashes immediately after starting, you can try this in a DOS window: c:\mysql\bin mysqld --standalone --console This will give you output in the DOS window so you can see why it keeps crashing. And, of course, you can use any other server than mysqld, e.g. mysqld-max-nt.exe, to try the same. mysqld-max... has InnoDB tables switched on by default, and there is a good chance something's wrong in your setup and InnoDB can't create its tablespace etc. You will see all of this in the DOS window output, though. Regards, -- Stefan Hinz [EMAIL PROTECTED] Geschäftsführer / CEO iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Tel: +49 30 7970948-0 Fax: +49 30 7970948-3 - Original Message - From: Norris, Joseph [EMAIL PROTECTED] To: Mysql_List (E-mail) [EMAIL PROTECTED] Sent: Thursday, January 23, 2003 1:55 AM Subject: Install on WIN2k Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - 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 - 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
FW: question about update sql
-Original Message- From: Norris, Joseph Sent: Wednesday, January 22, 2003 10:56 AM To: Mysql_List (E-mail) Subject: question about update sql Hello, I have the following in my SQL: update call_center set call_center.who_for_id = activeemployees.hr_emp_no where call_center.who_for_id = activeemployees.id; This is something I think that I should be able to do, but I get an error. Is there a way to do what I am trying to do here? Thanks to all. - 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
Install on WIN2k
Hello, I have been running mysql on windows 2000 for about 8 months. Now I have to install it on a new win2k box. I get everything installed and go to get into mysql and I am getting this error: ERROR 2003: Can't connect to MySQL server on 'localhost' (10061) I remember this well but I don't remember what I did to fix it. Any and all help appreciated. - 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
Can't lock file (errno: 13) - don't understand this one on win32
Group, Here is the problem. I have my mysql databases set up on one windoze 2000 box on d drive with the main folder on C drive using junction to create a symbolic link ( Unix so much easier! ) so as to make the sys admin happier. I have a perl script on another box that accesses this database via the network and I am getting the following message: DBD::mysql::st execure failed: Can't lock file (errno: 13) at (gives the location in the script where error is happening. Not sure how to trace this down. Any and all help appreciated. - 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: Programmatic MySQL backup in Perl
Jeff, Here is a piece of my backup script. Its been modified from Unix world to windoze world ( because at this point I am working in that environment - seeking to return to Unix if anyone has a gig ) =pod =head1 Script: mysql_backup.pl =head2 =over 4 =item 1 This script builds a db dump file to s:\mysql_dump The dump file is named for the date In addition this script backs up all script files in cgi-bin to s drive for nightly backup =back =cut use HTTP::Date; my ($time, $date_string, $cmd, $s); $date_string = time2str($time);# Format as GMT ASCII time $date_string =~ s/\s/\_/g; $date_string =~ s/[^a-zA-Z0-9]//g; $cmd = build_cmd('my_mysqldb'); do_cmd($cmd); exit(0); sub build_cmd{ my ($name) = @_; my $s = $date_string . $name . '.dmp'; my $cmd = qq!mysqldump -c --add-drop-table -uroot -psirron $name s:\\mysql_backup\\!; $cmd .= $s; return($cmd); } sub do_cmd{ my ($cmd) = @_; if (!(system ($cmd) == 0)){ warn could not do system $cmd - $!; } } -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 8:16 AM To: [EMAIL PROTECTED] Subject: Programmatic MySQL backup in Perl Hi, is there an easy way to programmatically backup MySQL database tables using Perl? My Perl code will be running on a machine remote to the MySQL server. Ta, Jeff - 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
FW: WoW!!!! SQL is something!
I had a problem that required a proper sort of the following: IT66 ITD38 IT68 ITD39 ITD40 IT69 ITD55 ITD56 IT72 ITD57 ITD58 ITD59 ITD60 IT73 ITD73 ITD67 IT74 ITD69 ITD70 IT78 IT79 IT83 ITD71 IT81 ITD75 ITD76 IT84 ITD96 ITD97 ITD98 ITD99 ITD100 ITD101 ITD102 ITD103 ITD104 ITD105 Because of the different lengths, this was a nightmare for order by and so it led me to my first experiments with case and I go the following: select req_number, case when (length(req_number)=4) then concat('00', req_number) when (length(req_number)=5) then concat('0', req_number) when (length(req_number)=6) then req_number END as sort_req from req order by sort_req; Now there might be other ways to handle this and I would love to hear about them but I thought that this taught me something about sql that I had not understood until now. Just would like to give a little back. Thanks to all. - 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: WoW!!!! SQL is something!
Thanks - this is even better - I will give it a try. -Original Message- From: Cal Evans [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 05, 2002 6:19 AM To: Norris, Joseph; Mysql_List (E-mail) Subject: RE: WoW SQL is something! select req_number, right(concat('00',trim(req_number)),6) as sort_req, case from req order by sort_req; Haven't tried it but it should work. =C= * * Cal Evans * The Virtual CIO * http://www.calevans.com * -Original Message- From: Norris, Joseph [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:08 PM To: Mysql_List (E-mail) Subject: FW: WoW SQL is something! I had a problem that required a proper sort of the following: IT66 ITD38 IT68 ITD39 ITD40 IT69 ITD55 ITD56 IT72 ITD57 ITD58 ITD59 ITD60 IT73 ITD73 ITD67 IT74 ITD69 ITD70 IT78 IT79 IT83 ITD71 IT81 ITD75 ITD76 IT84 ITD96 ITD97 ITD98 ITD99 ITD100 ITD101 ITD102 ITD103 ITD104 ITD105 Because of the different lengths, this was a nightmare for order by and so it led me to my first experiments with case and I go the following: select req_number, case when (length(req_number)=4) then concat('00', req_number) when (length(req_number)=5) then concat('0', req_number) when (length(req_number)=6) then req_number END as sort_req from req order by sort_req; Now there might be other ways to handle this and I would love to hear about them but I thought that this taught me something about sql that I had not understood until now. Just would like to give a little back. Thanks to all. - 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: WoW!!!! SQL is something!
Colin, According to the docs this function wants a string and when I use the field name it says unknown column. Thanks anyway. -Original Message- From: Colin Kettenacker [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 04, 2002 5:32 PM To: Mysql_List (E-mail) Subject: Re: WoW SQL is something! I had a problem that required a proper sort of the following: IT66 ITD38 . . SNIP . . ITD100 ITD101 ITD102 ITD103 ITD104 ITD105 Because of the different lengths, this was a nightmare for order by and so it led me to my first experiments with case and I go the following: select req_number, case when (length(req_number)=4) then concat('00', req_number) when (length(req_number)=5) then concat('0', req_number) when (length(req_number)=6) then req_number END as sort_req from req order by sort_req; Now there might be other ways to handle this and I would love to hear about them but I thought that this taught me something about sql that I had not understood until now. Just would like to give a little back. Thanks to all. Well I'm fairly new to MySQL so I am not sure if this'll work. If it does it will save a few cycles of logic. SELECT LPAD(req_number,6,'0'); Don't know if you need to coerce the numeric data into a string first though. ck - 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
question about inner and left join
Hello to all I have the following sql: select phones.*, ops.plid, ops.box, ops.mac, concat(phones.prefix1,phones.suffix1) as complete_phone from phones inner join ops on phones.suffix1 = ops.phone I do want to get the items from table ops for display of relationship of data to table phones but I also want to get ( in this instance ) all phones that do not have phones.suffix1 = ops.phone. In other words I want to get everything in table phones, obtaining the data from ops if there is a suffix present in ops but in addition I want the phone information if I find no values in ops. (Fun huh!!! :-) - 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: Perl DBI or C++ API Help!
QD answer in PERL! my ($file); $file = 'database_names'; $cmd = qq!show databases;!; open O, cmd or die could open cmd - $!; print O $cmd\n; close O; system (mysql -uusername -ppassword cmd $file) == 0 or die could not do system - $!; open I, $file or die could not open $file - $!; while (I){ print $_; } close I; -Original Message- From: Bernd Prager [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 2:33 PM To: Sarah Killcoyne; [EMAIL PROTECTED] Subject: Re: Perl DBI or C++ API Help! - Original Message - From: Sarah Killcoyne [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 03, 2002 11:14 AM I need to be able to connect to mysql through a perl or C++ script without knowing the name of the database to connect to. ,,, I'm not an expert om the C++ API. But if the host is a UNIX system you can do a RSH command in C++ as well as in Perl. mysql -u username 'show databases;' Maybe there's a better solution, but in worst case this would do 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 - 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
WoW!!!! SQL is something!
I had a problem that required a proper sort of the following: IT66 ITD38 IT68 ITD39 ITD40 IT69 ITD55 ITD56 IT72 ITD57 ITD58 ITD59 ITD60 IT73 ITD73 ITD67 IT74 ITD69 ITD70 IT78 IT79 IT83 ITD71 IT81 ITD75 ITD76 IT84 ITD96 ITD97 ITD98 ITD99 ITD100 ITD101 ITD102 ITD103 ITD104 ITD105 Because of the different lengths, this was a nightmare for order by and so it led me to my first experiments with case and I go the following: select req_number, case when (length(req_number)=4) then concat('00', req_number) when (length(req_number)=5) then concat('0', req_number) when (length(req_number)=6) then req_number END as sort_req from req order by sort_req; Now there might be other ways to handle this and I would love to hear about them but I thought that this taught me something about sql that I had not understood until now. Just would like to give a little back. Thanks to all. - 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
problem with mysql on windows
Group, We have been running along with little or no problems with mysql installed on windows 2000. Just recently I have been noticing a creation - great qty of creation of temp files in my script directories. I have all of my scripts on C drive with mysql on my D drive using junction to make a logical link from c to d for all mysql files in data directory. The temp files contain nothing, but still the question - why are they being created when they were not being created before. Any and all ideas appreciated. - 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
Looking for Mysql look-alike for OpenVMS
Sorry if this not on the exact mysql subject. I have an upcoming project that requires use of a SQL database system. I wanted to use mysql but mysql is not available for OpenVMS (thread issue as I understand it). Does anyone know of something out there that would be on the same order as mysql for OpenVMS? Thanks. - 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: Insert Error
Why do you use the single quotes around your field names. I have never used them when I build my schemas. That way you don't have to worry about spaces. HTH -Original Message- From: Insanely Great [mailto:insanely420;PunkAss.com] Sent: Thursday, October 24, 2002 8:59 AM To: MySQL List Subject: Insert Error Greetings... I have a table in database ch whose create statement is as follows - CREATE TABLE `chn1` ( `id` int(10) NOT NULL auto_increment, `chn` varchar(10) NOT NULL default 'Pagal', `name ` varchar(50) default '', `chn3` text, PRIMARY KEY (`id`) TYPE=ISAM ; Now when I giving a insert statement like this - insert into chn.chn1 ( id, chn, name , chn3 ) values ( '10', 'Pagal', 'Chagal', 'I am a bad boy' ) MySQL is giving me an error : Error Code : 1054 Unknown column 'name' in 'field list' As you see there exists a column name field ? I checked out and there was a space after name in the column defination. If I remove the space it works ? How come a space causes an error because I am giving space after the column field also ? Can anybody shade a light on it. Thanks in advance. Rgds Insane - 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
query slow on join and order by
Group, Here is my query: explain select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac My describe of the phones table and the ops table: Phones: ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | id | int(11) | | MUL | NULL| auto_increment | | first_name | varchar(255) | YES | | NULL|| | last_name | varchar(255) | YES | | NULL|| | identifier | varchar(255) | YES | | NULL|| | prefix1| char(3) | YES | | NULL|| | suffix1| varchar(10) | YES | MUL | NULL|| | prefix2| char(3) | YES | | NULL|| | suffix2| varchar(4) | YES | | NULL|| | location | varchar(255) | YES | | NULL|| | mod_date | varchar(30) | YES | | NULL|| | vacant | char(1) | YES | | NULL|| | centrex| char(1) | YES | | NULL|| | voice_mail | varchar(255) | YES | | NULL|| | jack | varchar(10) | YES | | NULL|| | division | varchar(255) | YES | | NULL|| | unit | varchar(255) | YES | | NULL|| | hr_emp_no | varchar(10) | YES | | NULL|| | no_print | char(1) | YES | | NULL|| ++--+--+-+-++ Ops: ++-+--+-+-++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-++ | id | int(11) | | MUL | NULL| auto_increment | | phone | varchar(10) | YES | MUL | NULL|| | plid | varchar(25) | YES | | NULL|| | vacant | char(1) | YES | | NULL|| | box| varchar(30) | YES | | NULL|| | loc| varchar(30) | YES | | NULL|| | jack | varchar(30) | YES | | NULL|| | mac| varchar(20) | YES | | NULL|| | phone_type | varchar(30) | YES | | NULL|| ++-+--+-+-++ And here is my explain of the query: table typepossible_keys key key_len ref rowsExtra ops ALL phone NULLNULLNULL345 where used; Using temporary; Using filesort phones ALL suffix1 NULLNULLNULL809 where used I am very sure that my bottle-neck has to do with the creation of a temporary file for sorting. What can I do to optimize this. Thanks to all. - 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
Problem with query - join two tables - order by goes crazy
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. Thanks. - 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
Query going crazy
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. - 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
query going crazy.
Group, I have the following query: select phones.*, ops.plid, ops.box, ops.mac from phones, ops where (ops.box = 'Mcds') or (ops.box = 'Mn3300') and (phones.suffix1 = ops.phone) order by ops.mac When I change the order by to a field in the phones table - sorts just fine and produces results but in this case I am sorting by one of the fields in the other table (ops). massive bunch of disk activity and mysqladmin shows that the process is writing stuff to disk then sorting then producing the results. Am I doing this query incorrectly? BTW - I am Win32 mysql version. - 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: Three Tables. Please help
In which tables of the three that you mention are the fields: part number, part description, location, part cost, sell price and on hand quantity What is the structure of each or the tables. -Original Message- From: William Martell [mailto:[EMAIL PROTECTED]] Sent: Friday, October 04, 2002 8:34 AM To: MySQL Main List Subject: Fw: Three Tables. Please help Pass Filter: sql,query Hello All, I have three tables in my database t1, t2, and t3. Each table has records that are the same or similar. I am trying to SELECT part number, part description, location, part cost, sell price and on hand quantity from these three tables. Each part number is the same in all three tables. The other fields are different. I would like to display each tables data sorted by item number. I am having trouble with my select statement. Thank you for your time and assistance. Sincerely, William Martell - 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: Win32::ODBC Question (Oracle)
This email leads to a question I have about SQL. I have been accustomed to Mysql with all of its nifty little extensions - like describe. Now I have a project with MSSQL (not because I wanted it - politics). Does any one now how to describe a table structure with standard SQL. Even more important - in Mysql there is the show tables; command which allows you to see the structure of the whole DB. Is there a way to do this in standard SQL? Thanks. -Original Message- From: Peter van der Goes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 8:39 AM To: Thomas R Wyant_III; [EMAIL PROTECTED] Subject: Re: Win32::ODBC Question (Oracle) DESCRIBE is not a SQL command, it's a SQL+ command IIRC. The Oracle ODBC drivers don't know anything about DESCRIBE, because it's not a SQL command. (Warning - based on recollections of two years ago when I actually *worked* for a living) - Original Message - From: Thomas R Wyant_III [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 25, 2002 8:45 AM Subject: Re: Win32::ODBC Question (Oracle) [EMAIL PROTECTED] wrote: I'm trying to get Information about a spezific table in an Oracle with the command 'describe table_name; ' This command works within SQL-PLus and other Tools but I get the error message: Ora-00900: invalid SQL statement when I use it within a perl-script using a Win32::ODBC connection. Does someone know a solution , maybe to use a DBD, DBI module or a different statement to get information about the table definition? You don't say what information you want about the table. If you want to know what columns are in the table, and what data types are in the columns, you perform a select against the table, and then pull the data out of the selection results. If you don't want any data at this point, the usual dodge is to specify a where clause that is never satisfied. In fact, the usual dodge is select * from your_table where 1 = 0 I don't know of any better way under DBI, but you need to know less about your data types if you use DBI, because it supports placeholders. So instead of generating the entire text of a query, and worrying about whether values need to be quoted, and if so how to escape any embedded quotes, you just put a question mark in the query, and provide the value when the query is executed. Tom Wyant This communication is for use by the intended recipient and contains information that may be privileged, confidential or copyrighted under applicable law. If you are not the intended recipient, you are hereby formally notified that any use, copying or distribution of this e-mail, in whole or in part, is strictly prohibited. Please notify the sender by return e-mail and delete this e-mail from your system. Unless explicitly and conspicuously designated as E-Contract Intended, this e-mail does not constitute a contract offer, a contract amendment, or an acceptance of a contract offer. This e-mail does not constitute a consent to the use of sender's contact information for direct marketing purposes or for transfers of data to third parties. Francais Deutsch Italiano Espanol Portuges Japanese Chinese Korean http://www.DuPont.com/corp/email_disclaimer.html ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs - 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: Win32::ODBC Question (Oracle)
Ian's help worked great. I have one question. How do I specify the DB? I mean in the query analyzer I can select a db from the pulldown box - is there a way to specify the db in the statements below? Thanks. -Original Message- From: Ian Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 8:50 AM To: 'Norris, Joseph'; 'Peter van der Goes'; 'Thomas R Wyant_III'; [EMAIL PROTECTED]; 'Mysql_List (E-mail)' Subject: RE: Win32::ODBC Question (Oracle) Hi, Use the following: SELECT * FROM sysobjects WHERE type='U' This will show you all of the user tables And then select * from syscolumns where ID = (select ID from sysobjects where name = 'your_tablename' and type = 'U' This should get you started! HTH Ian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Norris, Joseph Sent: 25 September 2002 16:47 To: 'Peter van der Goes'; Thomas R Wyant_III; [EMAIL PROTECTED]; Mysql_List (E-mail) Subject: RE: Win32::ODBC Question (Oracle) This email leads to a question I have about SQL. I have been accustomed to Mysql with all of its nifty little extensions - like describe. Now I have a project with MSSQL (not because I wanted it - politics). Does any one now how to describe a table structure with standard SQL. Even more important - in Mysql there is the show tables; command which allows you to see the structure of the whole DB. Is there a way to do this in standard SQL? Thanks. -Original Message- From: Peter van der Goes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 8:39 AM To: Thomas R Wyant_III; [EMAIL PROTECTED] Subject: Re: Win32::ODBC Question (Oracle) DESCRIBE is not a SQL command, it's a SQL+ command IIRC. The Oracle ODBC drivers don't know anything about DESCRIBE, because it's not a SQL command. (Warning - based on recollections of two years ago when I actually *worked* for a living) - Original Message - From: Thomas R Wyant_III [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 25, 2002 8:45 AM Subject: Re: Win32::ODBC Question (Oracle) [EMAIL PROTECTED] wrote: I'm trying to get Information about a spezific table in an Oracle with the command 'describe table_name; ' This command works within SQL-PLus and other Tools but I get the error message: Ora-00900: invalid SQL statement when I use it within a perl-script using a Win32::ODBC connection. Does someone know a solution , maybe to use a DBD, DBI module or a different statement to get information about the table definition? You don't say what information you want about the table. If you want to know what columns are in the table, and what data types are in the columns, you perform a select against the table, and then pull the data out of the selection results. If you don't want any data at this point, the usual dodge is to specify a where clause that is never satisfied. In fact, the usual dodge is select * from your_table where 1 = 0 I don't know of any better way under DBI, but you need to know less about your data types if you use DBI, because it supports placeholders. So instead of generating the entire text of a query, and worrying about whether values need to be quoted, and if so how to escape any embedded quotes, you just put a question mark in the query, and provide the value when the query is executed. Tom Wyant This communication is for use by the intended recipient and contains information that may be privileged, confidential or copyrighted under applicable law. If you are not the intended recipient, you are hereby formally notified that any use, copying or distribution of this e-mail, in whole or in part, is strictly prohibited. Please notify the sender by return e-mail and delete this e-mail from your system. Unless explicitly and conspicuously designated as E-Contract Intended, this e-mail does not constitute a contract offer, a contract amendment, or an acceptance of a contract offer. This e-mail does not constitute a consent to the use of sender's contact information for direct marketing purposes or for transfers of data to third parties. Francais Deutsch Italiano Espanol Portuges Japanese Chinese Korean http://www.DuPont.com/corp/email_disclaimer.html ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list
RE: Win32::ODBC Question (Oracle)
Figured it out. just use the use name_of_db and you can switch to another DB. thanks. -Original Message- From: Norris, Joseph Sent: Wednesday, September 25, 2002 9:02 AM To: 'Ian Robertson'; Norris, Joseph; 'Peter van der Goes'; 'Thomas R Wyant_III'; [EMAIL PROTECTED]; 'Mysql_List (E-mail)' Subject: RE: Win32::ODBC Question (Oracle) Ian's help worked great. I have one question. How do I specify the DB? I mean in the query analyzer I can select a db from the pulldown box - is there a way to specify the db in the statements below? Thanks. -Original Message- From: Ian Robertson [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 8:50 AM To: 'Norris, Joseph'; 'Peter van der Goes'; 'Thomas R Wyant_III'; [EMAIL PROTECTED]; 'Mysql_List (E-mail)' Subject: RE: Win32::ODBC Question (Oracle) Hi, Use the following: SELECT * FROM sysobjects WHERE type='U' This will show you all of the user tables And then select * from syscolumns where ID = (select ID from sysobjects where name = 'your_tablename' and type = 'U' This should get you started! HTH Ian. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Norris, Joseph Sent: 25 September 2002 16:47 To: 'Peter van der Goes'; Thomas R Wyant_III; [EMAIL PROTECTED]; Mysql_List (E-mail) Subject: RE: Win32::ODBC Question (Oracle) This email leads to a question I have about SQL. I have been accustomed to Mysql with all of its nifty little extensions - like describe. Now I have a project with MSSQL (not because I wanted it - politics). Does any one now how to describe a table structure with standard SQL. Even more important - in Mysql there is the show tables; command which allows you to see the structure of the whole DB. Is there a way to do this in standard SQL? Thanks. -Original Message- From: Peter van der Goes [mailto:[EMAIL PROTECTED]] Sent: Wednesday, September 25, 2002 8:39 AM To: Thomas R Wyant_III; [EMAIL PROTECTED] Subject: Re: Win32::ODBC Question (Oracle) DESCRIBE is not a SQL command, it's a SQL+ command IIRC. The Oracle ODBC drivers don't know anything about DESCRIBE, because it's not a SQL command. (Warning - based on recollections of two years ago when I actually *worked* for a living) - Original Message - From: Thomas R Wyant_III [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 25, 2002 8:45 AM Subject: Re: Win32::ODBC Question (Oracle) [EMAIL PROTECTED] wrote: I'm trying to get Information about a spezific table in an Oracle with the command 'describe table_name; ' This command works within SQL-PLus and other Tools but I get the error message: Ora-00900: invalid SQL statement when I use it within a perl-script using a Win32::ODBC connection. Does someone know a solution , maybe to use a DBD, DBI module or a different statement to get information about the table definition? You don't say what information you want about the table. If you want to know what columns are in the table, and what data types are in the columns, you perform a select against the table, and then pull the data out of the selection results. If you don't want any data at this point, the usual dodge is to specify a where clause that is never satisfied. In fact, the usual dodge is select * from your_table where 1 = 0 I don't know of any better way under DBI, but you need to know less about your data types if you use DBI, because it supports placeholders. So instead of generating the entire text of a query, and worrying about whether values need to be quoted, and if so how to escape any embedded quotes, you just put a question mark in the query, and provide the value when the query is executed. Tom Wyant This communication is for use by the intended recipient and contains information that may be privileged, confidential or copyrighted under applicable law. If you are not the intended recipient, you are hereby formally notified that any use, copying or distribution of this e-mail, in whole or in part, is strictly prohibited. Please notify the sender by return e-mail and delete this e-mail from your system. Unless explicitly and conspicuously designated as E-Contract Intended, this e-mail does not constitute a contract offer, a contract amendment, or an acceptance of a contract offer. This e-mail does not constitute a consent to the use of sender's contact information for direct marketing purposes or for transfers of data to third parties. Francais Deutsch Italiano Espanol Portuges Japanese Chinese Korean http://www.DuPont.com/corp/email_disclaimer.html ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs ___ Perl-Win32-Users mailing list [EMAIL PROTECTED] To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
FW: questions about IF(expr1,expr2,expr3)
-Original Message- From: Norris, Joseph Sent: Thursday, September 12, 2002 12:39 PM To: Mysql_List (E-mail) Subject: questions about IF(expr1,expr2,expr3) Hello, Can any one give me some examples of how they have used this function in a practical way? I would like to get a better understanding of how it can be used in a select. Thanks. - 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: and or in query
I would write this in this way: select * from table where (field = x and ($field = y or field = z)) or (field = x and (field = a or field = b or field = c)); assuming that you want a record with x in field having either y or z in field or a or b o c in field HATH -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:49 PM To: [EMAIL PROTECTED] Subject: and or in query Hi I would like to issue a logical query as follow: select * from table where field1 = x and ((field2 = y or field2 = z) or (field3 = a or field3 = b or field3 = c)); But this syntax is not returning what I expect. Plz help me with syntax - 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
question about replication - a very simple one - I know they all say that - but this is - sql, query
(filter bypass words - Mysql, sql, query) Group, I have just got into a situation in which I need to explore replication and I know nothing about it. Can any one direct me to specific documentation on this and maybe a simple tutorial for the greener of us? Thanks. - 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
Not sure which list to use - this is a real strange one with ODBC
Groups, I have this snippet of code: $dbh = DBI-connect( dbi:ODBC:${db_name}, $user, $pass) or DoError(Unable to connect: $DBI::errstr); #$dbh-debug(2); $fsql = qq!select empl_no_a, user_ln, user_fn, user_mi, aka, longname_a, longname_b, longname_c, useaka from activeempsinfo!; $db_action = $dbh-prepare($fsql); $db_action-execute or DoError(could not do $fsql - $DBI::errstr ); The resource is set up for odbc as a system dsn it is setting on another drive and I have a batch file running the script that contains the code above which does the following: @echo off net use h: /delete net use h: \\mcdsssql\hrdata Then runs my script. OK - here is the problem: the execute above dies with the following error: panic: dbd_error on bad handle type at update_from_hr.pl line 48. However when I allow the debug statment above - it spews all of its debug infor and the script finishes successfully and updates what it is supposed to. ANY IDEAS?? - very perplexing. Thanks. - 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: Not sure which list to use - this is a real strange one with ODBC
Agreed except in this case what I have to read is a FoxPro database - (yuck!) -Original Message- From: Gerald Clark [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 21, 2002 11:28 AM To: Norris, Joseph Cc: Mysql_List (E-mail); Perl Database (E-mail); Perl Web (E-mail); Perl Win32 Users (E-mail) Subject: Re: Not sure which list to use - this is a real strange one with ODBC Why use odbc when native drivers are available? Norris, Joseph wrote: Groups, I have this snippet of code: $dbh = DBI-connect( dbi:ODBC:${db_name}, $user, $pass) or DoError(Unable to connect: $DBI::errstr); #$dbh-debug(2); $fsql = qq!select empl_no_a, user_ln, user_fn, user_mi, aka, longname_a, longname_b, longname_c, useaka from activeempsinfo!; $db_action = $dbh-prepare($fsql); $db_action-execute or DoError(could not do $fsql - $DBI::errstr ); The resource is set up for odbc as a system dsn it is setting on another drive and I have a batch file running the script that contains the code above which does the following: @echo off net use h: /delete net use h: \\mcdsssql\hrdata Then runs my script. OK - here is the problem: the execute above dies with the following error: panic: dbd_error on bad handle type at update_from_hr.pl line 48. However when I allow the debug statment above - it spews all of its debug infor and the script finishes successfully and updates what it is supposed to. ANY IDEAS?? - very perplexing. Thanks. - 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
Need recommendations Advanced SQL book
Group, I have to move my work from Mysql to Postgres ( political decision ) - Need any and all recommendations for books on advanced SQL. Mysql is a holds a lot less to the standard than postgres - or so it seems and I need to look more at standard SQL for some of the things that I am doing. Thanks to all. - 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: Need recommendations Advanced SQL book
We are in Agreement on this point Gerald. I would prefer Mysql to Postgresql. The problem is that the shop where I work had a major Oracle system - full of triggers and functions (Oracle $$$ - Yuck!). So the decision was made to move to Postgresql. The systems that I developed have been based upon Mysql - but management decision has been to move everything to one DB. So that's where I am at. My current problem has been in select and grouping - two problems. On mysql I could do something like the following from my perl script: Problem 1) my $select = qq! select tablea.id, tableb.id sum(tablea.qty1), sum(tablea.qty2)... group by tablea.id And then print $return_rec-{'sum(tablea.qty1)'}\n; Postgresql returns nothing with this type of statement. Doing the sql manually at the prompt produces tablea.id | tableb.id | sum | sum | with the data in the rows. So I try print $return_rec-{'sum'}\n and I get the first sum. How do I get to the second? Problem 2) Related to problem 1 is the group by expression - I get an error from postgresql about needing tableb.id in the group or aggregate part of the sql statment. Adding this to group by phrase works in some sql statments - however in others trying to group by the additional fields the postgresql requires produces different results. These are my main two issues which has led to my search for books. I am thinking ( could be wrong and will accept any correction ) that postgresql holds closer to the SQL standard. My rational for this thinking is based upon things like not being able to do sum on a varchar field - postgres croaks by mysql says if there is number there I will sum it. Thanks again. -Original Message- From: Gerald Clark [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 14, 2002 9:14 AM To: Norris, Joseph Cc: Mysql_List (E-mail) Subject: Re: Need recommendations Advanced SQL book Let us know how your support issues are addressed when you have your first problem. Mine were never even acknowledged on the postgresql group. That was the reason I went with MySQL. I have never regretted it. Norris, Joseph wrote: Group, I have to move my work from Mysql to Postgres ( political decision ) - Need any and all recommendations for books on advanced SQL. Mysql is a holds a lot less to the standard than postgres - or so it seems and I need to look more at standard SQL for some of the things that I am doing. Thanks to all. - 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: question about varchars
Hello, What you say below is of great interest to me. Currently I have been writing front end code to handle the size issue but I would like to be open to other possibilities. So my question about your answer below is: Just how does the developer obtain the error condition from Mysql when an insert or update sends data that is too long to a field with a restricted size? Thanks -Original Message- From: Ralf Narozny [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 1:59 AM To: Desmond Lee Cc: [EMAIL PROTECTED] Subject: Re: question about varchars Hello! Desmond Lee wrote: Hello there Mysql documentation says the following: values are stored using only as many characters as are needed, plus one byte to record the length. Values are not padded; instead, trailing spaces are removed when values are stored. So, then would there be anything wrong with making all my varchar's up to 255. That is defining most of my fields to be varchar(255) even when i know they may only go 100? There's nothing wrong with it for storage reasons only, but many database developers use lengths to restrict the size of the entries. For example storing the zipcodes from more than one country might need at max 15 characters, so why allowing 255, preventing MySQL to raise an error, if the ZIP is 100 chars long. Greetings Ralf -- Ralf Narozny SPLENDID Internet GmbH Co KG Skandinaviendamm 212, 24109 Kiel, Germany fon: +49 431 660 97 0, fax: +49 431 660 97 20 mailto:[EMAIL PROTECTED], http://www.splendid.de - 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: Data Entry for a Newbie
Hello! two ways: Fun way: Get Perl and write some cgi using html/javascript front end Another way (maybe not as fun - but gets the job done): Enter data in Excel spreadsheet and export to comma delimited file. Then go and read the docs on mysqlimport command. Cheers! -Original Message- From: William Bradley [mailto:[EMAIL PROTECTED]] Sent: Thursday, July 25, 2002 8:13 AM To: Mysql Subject: Data Entry for a Newbie At the moment I have Mysql installed and understand command line entry of data, or entry via a text file. Either way, it is difficult, especially if you have been used to data entry screens. Is there a utility somewhere that would allow me to do this? Failing that, is it possible to write a html file to communicate with the server on my own computer? Any help is appreciated, Bill. - 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
help in re-thinking a way to do the following:
Group, Given the following code: $sql = qq! select * from table where suffix1 ''!; $db_action = $DB-prepare($sql); $db_action-execute or die could not do $sql - $DBI::errstr; $rows = $db_action-rows; $rec = $db_action-fetchall_arrayref; $db_action-finish; for ($i=0;$i$rows;$i++) { $id = $rec-[$i][0]; $first_name = $rec-[$i][1]; $last_name = $rec-[$i][2]; $identifier = $rec-[$i][3]; $prefix1 = $rec-[$i][4]; $suffix1 = $rec-[$i][5]; $prefix2 = $rec-[$i][6]; $suffix2 = $rec-[$i][7]; $site = $rec-[$i][8]; $location = $rec-[$i][9]; $phone_loc_id = $rec-[$i][10]; I would like to find alternate ways to do this that would take into account changes to the table. For example let's say that the table changes (for example lets say that site field in the above example is removed). What I am interested in, is how are others in the group are handling this type of thing. I have heard something about tying column names - not sure what this is. Any and all suggestions will be most appreciated. Thanks. - 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
HELP!!! major situation with mysql on windows 2000
Group, I have a system running on windoze 2000 with apache and perl that was running just great until I mades some changes to the sql and a join of two tables. Now I get a bunch of disk activity and my pages take forever to load I shut down the browser and still the activity - memory usage goes through the roof with ossilation between perl and mysql in task manager. I see that there are several servers that can be run. Should I be running one of other servers? I am currently running mysqld-nt but there is mysqld-max-nt, mysqld-max Not sure how to troubleshoot this. Thanks. - 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: HELP!!! major situation with mysql on windows 2000
Thanks to all - another coder showed me the error of my ways :) I had a join without a where clause - a big no-no. I had to restart the server and repair my code and now I am back to normal. Thanks to all. -Original Message- From: Benjamin Pflugmann [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 18, 2002 11:26 AM To: Norris, Joseph Cc: Mysql_List (E-mail) Subject: Re: HELP!!! major situation with mysql on windows 2000 Hi. On Tue 2002-06-18 at 10:28:41 -0700, [EMAIL PROTECTED] wrote: Group, I have a system running on windoze 2000 with apache and perl that was running just great until I mades some changes to the sql and a join of two tables. Now I get a bunch of disk activity and my pages take forever to load I shut down the browser and still the activity - memory usage goes through the roof with ossilation between perl and mysql in task manager. The MySQL server will only notice that you stopped the client, when it is ready to send some results back. Depending on the query this may take a while. You should use your preferred admin tool (mysqladmin if nothing else), and as database root look at the processlist and kill the query in question. I see that there are several servers that can be run. Should I be running one of other servers? No. As long as you have to ask, you have no need for the other servers. ;-) I am currently running mysqld-nt but there is mysqld-max-nt, mysqld-max mysqld-max* has some additional features compiled in by default (e.g. InnoDB table type). This has nothing to do with the problem you observed. The *-nt version are for the server version of Windows, including Windows 2000, so you are using the correct binary. Bye, Benjamin. -- [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: deleteing data from a row
Justin, delete from table_name where field = 'search criteria'; example: delete from properties where some_field_in_properties = 'search criteria' -Original Message- From: justin cunningham [mailto:[EMAIL PROTECTED]] Sent: Thursday, June 13, 2002 12:46 PM To: 'mysql' Subject: deleteing data from a row How do I delete data from a table? I tried the man pages for delete and replace but there not working. I have a table with 8 rows. The table is called properties. The row is called bulletpoints. i tried deleting and replacing the fuged data and sql says ok bla but when I select bulletopints from properties I see the same fuged data I'm trying to eliminate. Please help. justin - 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
question about differences with mysql on windows and unix
Group, On my unix box when I use mysql I can spawn vi and edit the sql statement or save it out for reuse. This feature does not appear to be present on the windows version - or did I miss something? Thanks. - 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
question about perl connection to mysql on another machine
group, Here is the situation. I have to connect to Mysql running on another windows 2000 box. I have never done this before. Do I set this up in the tables as another host and use the -h parm in command shell and the host parm in my perl scripts? or is there another way to do it? Any help would be appreciated - I am of unix background trying to swim in the windows pool. Thanks. - 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
What is going on? Performance question
I am running mysql and apache on windows 2000. Everything is running smoothly and then all of the sudden my perl scripts that have been running just find are loading very very slowly. I am not sure if it is mysql, perl, apache or what. These are the same scripts that have been running just fine until today. Are there any steps I can take to figure this out? Thanks. ( know unix - a little knowledge of windows). - 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
Very new to windows version - question about host and server
Group, I have been using (very happily) mysql on unix boxes. I am in a new job and there is not a unix box in sight!!! ouch! This is a windows 2000 shop. Here is the situation and I am looking for any and all suggestions: 1) I have mysql, apache, perl, etc running on my own box and I have just installed mysql on the main server ( also windows 2000). 2) How do I access my new mysql databases on the server? 3) Does it have to do with the -h parm? Any and all help would be appreciated. If I have not been clear enough - I will try to add more. Thanks. - 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