Re: help regarding mysql
2003 8 2 15:11:
Hi frnd.
I am also using mysql and facing problems regarding access to
the
db using odbc from visual basic.
i have made a table under mysql default db and
used ms ado to connect to the db.
what will be the server name in such a case(that will be used to
connect to the db) using ado.
well when i go to the gui i see that on my system's name
ipaddress i have 2 databases registered.
mysql and
test
i have made a table under mysql.
and used ado to get to the db using server name = system
name(OTAP)
ado_pre.Open DRIVER={MySQL ODBC for 3.51
Driver};UID=root;PWD=root;SERVER=OTAP;
it says root dosent have permissions to open using
[EMAIL PROTECTED]
password(YES)
Please help in this regard.
what should be the server name or what must be the method to
access the db.
Thanking you.
Alok.
___
Download the hottest happening ringtones here!
OR SMS: Top tone to 7333
Click here now:
http://sms.rediff.com/cgi-bin/ringtone/ringhome.pl
--
---
Valentin Nils
Internet Technology
E-Mail: [EMAIL PROTECTED]
URL: http://www.knowd.co.jp
Personal URL: http://www.knowd.co.jp/staff/nils
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query Cache slow downs?
Hi everyone, I've been looking everywhere for an answer to this, so please excuse me if it should have been obvious. I have a very big MySQL server, under load, serving in the 1500 QPS range. Under times of high concurrenncy (many threads connected issuing queries), I start to see a lot of the threads with NULL as their state (rather then Sending data, sorting, etc). These threads seem to be blocked for some reason, as they eventually do get executed. Am I running into a locking issue? MySQL seems like it -should- scale to 12 CPU's... so I must be missing something. Up until this point, it's just grown and grown along with us (using InnoDB table system), so I'm hoping to continue. :) Thanks in advance, Dylan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropping primary key/unique key
Hi Mysql fans ;-); I have a problem understanding why MySQL is deleting a unique key instead of a primary key. from Documentation: DROP PRIMARY KEY drops the primary index. If no such index exists, it drops the first UNIQUE index in the table. When I do it then I get this: mysql desc uksample4; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | UNI | 0 | | | name | char(30) | YES | | NULL| | | tel | char(20) | | PRI | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) mysql alter table uksample4 drop primary key ; Query OK, 0 rows affected (0.24 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql desc uksample4; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | id| int(11) | | | 0 | | | name | char(30) | YES | | NULL| | | tel | char(20) | | PRI | | | +---+--+--+-+-+---+ 3 rows in set (0.00 sec) It deletes the unique key (id) instead of he primary key (tel). Did I do something wrong ? MySQL 4.0.14 Best regards -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR: Lost Connection
Hi List, Does anyone knows why this error message is displaying when I ran a cshell script which is using mysql and mysqlimport client commands? I encountered this after I upgrade from 4.0 gamma to 4.0.13. Error: Lost connection to MySQL server during query, when using table: SUMMARY Thanks. -- Richard Bornay ST Assembly Test Services Test Product Engineering Test Data Management Group 6824-1367
Re: ERROR: Lost Connection ### RESOLVED
Hi Lists, Just like to share what I found out in resolving my own problem. Actually, my script is using the mysqlimport client in loading a file to mysql and did not noticed that I used the option '-r'(replace) when I first created the script. When I upgrade to 4.0.13, I created an index to the SUMMARY table to prevent record duplication. I just removed the -r option then no more error displayed and my script runs smoothly.. Hope this will be a useful tip. Thanks -- Richard Bornay ST Assembly Test Services Test Product Engineering Test Data Management Group 6824-1367 BORNAY Richard/Engr/STATS/ST [EMAIL PROTECTED] Domain02/08/2003 03:39 PM To: [EMAIL PROTECTED] cc: (bcc: BORNAY Richard/Engr/STATS/ST Group) Subject: ERROR: Lost Connection Hi List, Does anyone knows why this error message is displaying when I ran a cshell script which is using mysql and mysqlimport client commands? I encountered this after I upgrade from 4.0 gamma to 4.0.13. Error: Lost connection to MySQL server during query, when using table: SUMMARY Thanks. -- Richard Bornay ST Assembly Test Services Test Product Engineering Test Data Management Group 6824-1367
Re: Changing error message
Hi! mysqld thinks some character pairs like \b and \s are special characters. You should replace basedir=C:\mysql\bin language=c:\mysql\share\english\ by basedir=C:/mysql/bin language=c:/mysql/share/english/ mysqld will internally convert those slashes to backslashes on Windows. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Mysql [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Saturday, August 02, 2003 4:12 AM Subject: Changing error message now when using the C:\mysql\bin\mysqld --standalone the error message changes to: Can't find messagefile 'c:mysql hare\english\errmsg.sys' NO that is not typed incorrectly! mysql is has no idea where is wants to find that file. I have downloaded the file twice now, with same result below is a copy of C:\windows\my.ini # This will be passed to all mysql clients [client] #password=my_password port=3306 #socket=MySQL # The MySQL server [mysqld] basedir=C:\mysql\bin datadir=C:\mysql\data language=c:\mysql\share\english\ port=3306 #socket=MySQL skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 # Try number of CPU's*2 for thread_concurrency # set-variable = thread_concurrency=8 log-bin server-id = 1 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash safe-updates [isamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=128M set-variable = sort_buffer=128M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout -- 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: calender table - time column?
guess this was too much information for you all! Or too complicated :) Well
this is what we came up with, I would like to know your opinions if you have
any? I can see two flaws in the design for the Time/Date so I really would be
interested in any of your views :)
CREATE TABLE booking (
id varchar(4) NOT NULL default '',
month char(3) NOT NULL default '',
date varchar(4) NOT NULL default '',
day char(3) NOT NULL default '',
year varchar(4) NOT NULL default '',
one varchar(50) NOT NULL default '',
two varchar(50) NOT NULL default '',
three varchar(50) NOT NULL default '',
four varchar(50) NOT NULL default '',
five varchar(50) NOT NULL default '',
six varchar(50) NOT NULL default '',
seven varchar(50) NOT NULL default '',
eight varchar(50) NOT NULL default '',
nine varchar(50) NOT NULL default '',
ten varchar(50) NOT NULL default '',
eleven varchar(50) NOT NULL default '',
twelve varchar(50) NOT NULL default '',
thirteen varchar(50) NOT NULL default '',
fourteen varchar(50) NOT NULL default '',
fithteen varchar(50) NOT NULL default '',
sixteen varchar(50) NOT NULL default '',
seventeen varchar(50) NOT NULL default '',
eighteen varchar(50) NOT NULL default '',
nineteen varchar(50) NOT NULL default '',
twenty varchar(50) NOT NULL default '',
twentone varchar(50) NOT NULL default '',
twentytwo varchar(50) NOT NULL default '',
twentythree varchar(50) NOT NULL default '',
twentyfour varchar(50) NOT NULL default '',
places char(2) NOT NULL default '24'
) TYPE=MyISAM;
#
#
# Table structure for table `flights`
#
CREATE TABLE flights (
day char(3) NOT NULL default '',
flightone varchar(5) NOT NULL default '',
flighttwo varchar(5) NOT NULL default '',
flightthree varchar(5) NOT NULL default '',
flightfour varchar(5) NOT NULL default ''
) TYPE=MyISAM;
#
#
# Table structure for table `guests`
#
CREATE TABLE guests (
surname varchar(50) NOT NULL default '',
firstname varchar(50) NOT NULL default '',
email varchar(50) NOT NULL default '',
phone varchar(50) NOT NULL default '',
date varchar(15) NOT NULL default '',
places char(2) NOT NULL default '',
flight varchar(5) NOT NULL default '',
ref varchar(50) NOT NULL default ''
) TYPE=MyISAM;
INSERT INTO flights VALUES ('Mon', '06:00', '12:00', '15:30', '18:00');
INSERT INTO flights VALUES ('Tue', '01:00', '02:00', '11:00', '23:00');
INSERT INTO flights VALUES ('Wed', '00:00', '14:00', '', '');
INSERT INTO flights VALUES ('Thu', '08:23', '09:16', '17:21', '22:09');
INSERT INTO flights VALUES ('Fri', '03:55', '', '', '');
INSERT INTO flights VALUES ('Sat', '07:22', '13:45', '', '');
INSERT INTO flights VALUES ('Sun', '05:33', '09:40', '23:12', '');
$query = select id,day from booking where month='$month' and date='$day' and
year='$year';
$result = @mysql_db_query ($database, $query);
if ($result){
$dbid = mysql_result ($result, 0, id);
echo $dbid;
echo 'br';
for ($i = $dbid; $i $dbid+5; $i++){
$query2 = select places from booking where id = '$i';
$result2 = @mysql_db_query ($database, $query2);
$dbplaces = mysql_result ($result2, 0, places);
if ($place = $dbplaces){
if ($i==$dbid+4){
$dbday = mysql_result ($result, 0, day);
header(Location:
continue.php?day=$dbdayid=$dbidplaces=$place);
}
}else{
header(Location: booking.php?advice=sorry);
break;
}
}
}
Thanks
Andrew
-Original Message-
From: Andrew [mailto:[EMAIL PROTECTED]
Sent: 01 August 2003 17:26
To: Keith C. Ivey; [EMAIL PROTECTED]
Subject: RE: calender table - time column?
Thanks Keith here it is :)
The booking system will take the format of a form. Customer or travel agent can
select a date of travel. They will also input how many places are needed.
The system will then check that the selected dates are available. At this point
there are two possible responses.
1)
Places are not available: System will bring user back to 1st page and ask them
to choose another date.
2)
Places are available.
System will look at the date of the holiday and check to see if that day is a
Mon, Tue, Wed, etc. Depending on the result a list of possible flights
and costs
will be displayed (pulled from the data base)
User will choose their flight.
At this point the system will move the user to a payment area. Where
all details
are filled out and will be sent for payment and also e-mail sent to Admin with
booking details. The booking system will also now be updated so that
no one else
can book those dates (up to 24 persons rotating).
To deal with the travel agent commission a reference input field for the travel
agent to fill in on the e-mail/payment form. With a description similar to;
If you have a reference code for this
Re: mysql_info not very informative?
Woah! 10.3! Some is using the developer preview of Panther! Actually, MySQL doesn't normally give overrun cut-off information (best I know). Use MySQL 4.0.x on 10.2.6, and MySQL has always performed that way. Regards, A$ On Friday, August 1, 2003, at 05:37 PM, Ray Kiddy wrote: I am trying to insert data using the C API. Particulars: OS: Mac OS X 10.3 (7A179) MySQL: MySQL 4.1.0-alpha table type: tried both MyISAM and InnoDB I have a table: mysql describe testtable; ++-+---+--+-+-+---+ | Field | Type| Collation | Null | Key | Default | Extra | ++-+---+--+-+-+---+ | _PK| int(11) | binary| | PRI | 0 | | | first | char(3) | latin1_swedish_ci | YES | | NULL| | | second | char(3) | latin1_swedish_ci | YES | | NULL| | | third | char(3) | latin1_swedish_ci | YES | | NULL| | ++-+---+--+-+-+---+ 4 rows in set (0.00 sec) Note that the first, second, and third columns have 3 characters of space. When I do: printf(insert: %s\n, insert); int result = mysql_query(one, insert); printf(info: %s\n, mysql_info(one)); I get: insert: INSERT INTO testtable (_PK,first,second,third) VALUES (1,'AAAXXX','BBB','CCC') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (2,'DDD','EEE','FFF') info: (null) insert: INSERT INTO testtable (_PK,first,second,third) VALUES (3,'GGG','HHHXXX','III') info: (null) Note that the value in the first insert, 'AAAXXX', is too long to fit. As is 'HHHXXX' in the third insert. And indeed, I see: mysql select * from testtable; +-+---++---+ | _PK | first | second | third | +-+---++---+ | 1 | AAA | BBB| CCC | | 2 | DDD | EEE| FFF | | 3 | GGG | HHH| III | +-+---++---+ 3 rows in set (0.00 sec) So, why is mysql_info not giving me any information about the data loss that is going on here? Is that not information that might be of interest? Is there some other call I have to make that will prepare for the mysql_info call? The doc does not seem to indicate this, but one never knows. thanx - ray -- 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: C API AND MYSQL
Dave,
The documentation has several examples. Here's a function we use. Are
you having a particular difficulty?
Regards,
Andy.
snip
MYSQL *aDb = NULL;
int xConnect()
{
#define MYSQL_HOST 192.168.103.112 // or host name
#define MYSQL_DBfoodb // database name
#define MYSQL_USERIDmyuser// DB user name
#define MYSQL_PASSWORD mypass// password
aDb = mysql_init(NULL);
if (!mysql_real_connect(aDb,MYSQL_HOST,MYSQL_USERID,
MYSQL_PASSWORD,MYSQL_DB,0,NULL,0)) {
die(701 Unable to connect\r\n);
}
return -1;
}
/snip
dave wrote:
anyone have a good sample script of connecting mysql from C API? thanks
--
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: ERROR while compiling:
Prem, I had this problem and adding the library 'z' (whatever that is) helped. Try putting -lz at the end of your compile line. Regards, Andy. Prem Soman wrote: i am using Linux 6.2(Zoot) and MySql 2.23.36and found the following error while compiling the C program using mysql APIcc -o test prog1.c -L /usr/lib/mysql -I /usr/include/mysql -lmysqlclientThe following Error occured :/usr/lib/mysql/libmysqlclient.a(my_compress.o): In function `my_uncompress':my_compress.o(.text+0x97): undefined reference to `uncompress'/usr/lib/mysql/libmysqlclient.a(my_compress.o): In function `my_compress_alloc':my_compress.o(.text+0x12b): undefined reference to `compress'collect2: ld returned 1 exit statusplease help me in understanding the problem. - Want to chat instantly with your online friends? Get the FREE Yahoo!Messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Apache-mpm
System rh9 Mysql 4 Php 4.2.3 I compiled apache with mpm=worker and have lot of problem with mysql too many connections If I install apache in traditional way there is no problem So what's the problem? From http://httpd.apache.org/docs-2.0/mod/worker.html By using threads to serve requests, it is able to serve a large number of requests with less system resources than a process-based server. I wanted to achieve better performance but the effects was totally diffrent. Do you have any experience with taht problem. Maybe I did something wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
permission issue because of dynamic IP
Hello, I've got 2 sites. 1) server : mysqld 4.0.14 standard from mysql.com on linux 2) client : windows 2000 whith mysqlcc 0.9.2 beta and myodbc 3.51.6 Both have dynamic IPs. The server listens on e.g. server.dyndns.org with ssh port open. In reality it's IP is something like dialin-a-b-c-d.some-isp.net. The client is e.g. client.dyndns.org with putty as ssh-client. In reality it's IP is again something like x-y-z.another-isp.com. Client does ssh into server and ssh-forwards server's mysql to 127.0.0.1:3306. That works. If I try to connect with MySQLcc from client -- server. MySQL denies access for: dialin-a-b-c-d.some-isp.net ! That's the server's real IP not the origine of the request, client. Client gets access when I either put 'dialin-a-b-c-d.some-isp.net' into the privileges or '%'. The address is dynamic so it had to be updated on every ip-up event. And '%' is no good either since it would weaken mysql's security. 1) Why gets such a remote request transcribed to a request of the server's external address ? 2) How can I have mysql check for client.dyndns.org ? Thanks Andreas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Adv. Mysql query
Hi there. I have a little problem with a sql-query I'm trying to get together. Well I got it to work halfways. I have a table with items, which are composed of 1-6 other items, which is saved in the same table. You might call it a recursive query, I need to get the names of the components that makes up the item I search for. It looks about like this: (3 CompX removed for readability) ID Name Comp1 Comp2 Comp3 1Item10 0 0 2Item20 0 0 3Item30 0 0 4Item40 0 0 5Item51 3 4 Item5 is a composition of item 1,3 and 4. It would be no problem if I only wanted to get the ID of the Included components but I want the names. I tried using: SELECT i.Name, c1.Name, c2.Name, c3.Name FROM Items AS i, Items AS c1, Items AS c2, Items AS c3 WHERE i.ID=5 AND i.Comp1=c1.ID AND i.Comp2=c2.ID AND i.Comp3=c3.ID; This gives me the right result, but if an Item only consists of 2 components then I don't get anything Which is understandable. And I can only imagine what kinds of resources it will take if the Items table Gets really large. Is there a better way to do this? I want to keep the number of queries to a minimum. I have read some on Union, joins and subqueries but I can't think of a way to make it work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing error message
Heikki, Thanks so much! That did the trick - mysqld is up and humming. SJohnson Heikki Tuuri wrote: Hi! mysqld thinks some character pairs like \b and \s are special characters. You should replace basedir=C:\mysql\bin language=c:\mysql\share\english\ by basedir=C:/mysql/bin language=c:/mysql/share/english/ mysqld will internally convert those slashes to backslashes on Windows. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: calender table - time column?
* Andrew guess this was too much information for you all! Or too complicated :) ...or maybe too vague? ;) Well this is what we came up with, I would like to know your opinions if you have any? ok, but be warned, I would have done this _totally_ different... :) I can see two flaws in the design for the Time/Date so I really would be interested in any of your views :) CREATE TABLE booking ( id varchar(4) NOT NULL default '', month char(3) NOT NULL default '', date varchar(4) NOT NULL default '', day char(3) NOT NULL default '', year varchar(4) NOT NULL default '', one varchar(50) NOT NULL default '', two varchar(50) NOT NULL default '', three varchar(50) NOT NULL default '', four varchar(50) NOT NULL default '', five varchar(50) NOT NULL default '', six varchar(50) NOT NULL default '', seven varchar(50) NOT NULL default '', eight varchar(50) NOT NULL default '', nine varchar(50) NOT NULL default '', ten varchar(50) NOT NULL default '', eleven varchar(50) NOT NULL default '', twelve varchar(50) NOT NULL default '', thirteen varchar(50) NOT NULL default '', fourteen varchar(50) NOT NULL default '', fithteen varchar(50) NOT NULL default '', sixteen varchar(50) NOT NULL default '', seventeen varchar(50) NOT NULL default '', eighteen varchar(50) NOT NULL default '', nineteen varchar(50) NOT NULL default '', twenty varchar(50) NOT NULL default '', twentone varchar(50) NOT NULL default '', twentytwo varchar(50) NOT NULL default '', twentythree varchar(50) NOT NULL default '', twentyfour varchar(50) NOT NULL default '', places char(2) NOT NULL default '24' ) TYPE=MyISAM; # You seem to use string column types for everything? You should use the 'correct' column types whenever possible, it will make the database faster and lighter. URL: http://www.mysql.com/doc/en/Column_types.html Also, you should define indexes. In this early stage of your project all needed index are not yet known, but you should at least define primary keys. URL: http://www.mysql.com/doc/en/MySQL_indexes.html URL: http://www.mysql.com/doc/en/CREATE_INDEX.html month, date, day and year can/should be stored in a single column. The above table should imo have been split into three tables: CREATE TABLE booking ( b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, booking DATETIME); CREATE TABLE person ( p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); This person table is probably the same thing as your 'guests' table below. If so, add the needed columns from that table to the 'person' table, and drop the 'guests' table (or the other way around, if you prefer). CREATE TABLE booking_person ( b_id INT UNSIGNED NOT NULL, p_id INT UNSIGNED NOT NULL, booking_time TIMESTAMP, PRIMARY KEY (b_id,p_id), UNIQUE KEY (p_id,b_id)); The 'booking_time' column is just a suggestion, because it is maintained 'for free': the TIMESTAMP column type is 'magic' and is automatically set to the current time when the record is created or changed. It could be usefull to know _when_ a booking has been made, right? Read about the TIMESTAMP datatype here: URL: http://www.mysql.com/doc/en/DATETIME.html To find how many persons are booked on a flight: SELECT COUNT(*) AS booking_count FROM booking_person NATURAL JOIN booking WHERE booking.booking = '2003-08-10 15:30' NATURAL JOIN can be used in this example because there is only the 'b_id' column name that is common between the two tables. Read about NATURAL JOIN and other joins here: URL: http://www.mysql.com/doc/en/JOIN.html # # Table structure for table `flights` # CREATE TABLE flights ( day char(3) NOT NULL default '', flightone varchar(5) NOT NULL default '', flighttwo varchar(5) NOT NULL default '', flightthree varchar(5) NOT NULL default '', flightfour varchar(5) NOT NULL default '' ) TYPE=MyISAM; # This design will break when there are five or more flights any one day. Also, there is no way to define exceptions: what if the next monday is a national holliday, and some of the flights won't be flying? I guess your design reflects the normal situation: most mondays there are four flights, at 06:00, 12:00, 15:30 and 18:00. You would not want to put a record in the flights table for _every_ monday, but it would be nice to be able to define exceptions, wouldn't it? Furthermore, weekdays seems to be a relevant attribute in this system, usually it is only an output attribute, created by the server during query execution, using DATE_FORMAT(date_column,'%a') or similar. Read about DATE_FORMAT() and a heap of other date time related functions here: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html Consider this design: CREATE TABLE flights ( f_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
Allowing remote login
I have been using MySQL for awhile, and for the first time someone from outside our portable /16 needs access to some tables. We have given them the server IP address, the dB name, the port 3306, the root username, and the password, but they still can't get access. Of course, no firewalls or other things in the way. Is it true that MySQL defaults to local access only, and to enable remote access you must do something unusual with grants? If so, how would I do this? (warning, we use the win version, but this question is not a windows-only thing so it is topical for this list:-) Thanks in advance for reading a post like this with such a dumb question! Mary Grace -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: calender table - time column?
Hi Roger this was very useful indeed thank you. I would really like to know any other views on this design and implications for the future development of such a system in terms of flexibility Andrew -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: 02 August 2003 16:49 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: calender table - time column? * Andrew guess this was too much information for you all! Or too complicated :) ...or maybe too vague? ;) Well this is what we came up with, I would like to know your opinions if you have any? ok, but be warned, I would have done this _totally_ different... :) I can see two flaws in the design for the Time/Date so I really would be interested in any of your views :) CREATE TABLE booking ( id varchar(4) NOT NULL default '', month char(3) NOT NULL default '', date varchar(4) NOT NULL default '', day char(3) NOT NULL default '', year varchar(4) NOT NULL default '', one varchar(50) NOT NULL default '', two varchar(50) NOT NULL default '', three varchar(50) NOT NULL default '', four varchar(50) NOT NULL default '', five varchar(50) NOT NULL default '', six varchar(50) NOT NULL default '', seven varchar(50) NOT NULL default '', eight varchar(50) NOT NULL default '', nine varchar(50) NOT NULL default '', ten varchar(50) NOT NULL default '', eleven varchar(50) NOT NULL default '', twelve varchar(50) NOT NULL default '', thirteen varchar(50) NOT NULL default '', fourteen varchar(50) NOT NULL default '', fithteen varchar(50) NOT NULL default '', sixteen varchar(50) NOT NULL default '', seventeen varchar(50) NOT NULL default '', eighteen varchar(50) NOT NULL default '', nineteen varchar(50) NOT NULL default '', twenty varchar(50) NOT NULL default '', twentone varchar(50) NOT NULL default '', twentytwo varchar(50) NOT NULL default '', twentythree varchar(50) NOT NULL default '', twentyfour varchar(50) NOT NULL default '', places char(2) NOT NULL default '24' ) TYPE=MyISAM; # You seem to use string column types for everything? You should use the 'correct' column types whenever possible, it will make the database faster and lighter. URL: http://www.mysql.com/doc/en/Column_types.html Also, you should define indexes. In this early stage of your project all needed index are not yet known, but you should at least define primary keys. URL: http://www.mysql.com/doc/en/MySQL_indexes.html URL: http://www.mysql.com/doc/en/CREATE_INDEX.html month, date, day and year can/should be stored in a single column. The above table should imo have been split into three tables: CREATE TABLE booking ( b_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, booking DATETIME); CREATE TABLE person ( p_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); This person table is probably the same thing as your 'guests' table below. If so, add the needed columns from that table to the 'person' table, and drop the 'guests' table (or the other way around, if you prefer). CREATE TABLE booking_person ( b_id INT UNSIGNED NOT NULL, p_id INT UNSIGNED NOT NULL, booking_time TIMESTAMP, PRIMARY KEY (b_id,p_id), UNIQUE KEY (p_id,b_id)); The 'booking_time' column is just a suggestion, because it is maintained 'for free': the TIMESTAMP column type is 'magic' and is automatically set to the current time when the record is created or changed. It could be usefull to know _when_ a booking has been made, right? Read about the TIMESTAMP datatype here: URL: http://www.mysql.com/doc/en/DATETIME.html To find how many persons are booked on a flight: SELECT COUNT(*) AS booking_count FROM booking_person NATURAL JOIN booking WHERE booking.booking = '2003-08-10 15:30' NATURAL JOIN can be used in this example because there is only the 'b_id' column name that is common between the two tables. Read about NATURAL JOIN and other joins here: URL: http://www.mysql.com/doc/en/JOIN.html # # Table structure for table `flights` # CREATE TABLE flights ( day char(3) NOT NULL default '', flightone varchar(5) NOT NULL default '', flighttwo varchar(5) NOT NULL default '', flightthree varchar(5) NOT NULL default '', flightfour varchar(5) NOT NULL default '' ) TYPE=MyISAM; # This design will break when there are five or more flights any one day. Also, there is no way to define exceptions: what if the next monday is a national holliday, and some of the flights won't be flying? I guess your design reflects the normal situation: most mondays there are four flights, at 06:00, 12:00, 15:30 and 18:00. You would not want to put a record in the flights table for _every_ monday, but it would be nice to be able to define exceptions, wouldn't it? Furthermore, weekdays seems to be a relevant attribute in this system,
Re: Allowing remote login
Hi There, Though it's in the manual, you may want to do something like this: GRANT ALL ON database.* TO [EMAIL PROTECTED] IDENTIFIED BY 'password' Of course, you'll want to replace: - ALL with access privelages that are much safer (assuming you don't completely trust the remote user). - database with the database that you are actually giving remote access to - user with the username you want to assign - domain with the IP address the remote user will be connecting from (or their host and domain name) - password with the password they'll be using. I'd point you at this link for more info: http://www.mysql.com/doc/en/GRANT.html Take care, Dylan On Saturday, August 2, 2003, at 11:55 AM, Mary Grace wrote: I have been using MySQL for awhile, and for the first time someone from outside our portable /16 needs access to some tables. We have given them the server IP address, the dB name, the port 3306, the root username, and the password, but they still can't get access. Of course, no firewalls or other things in the way. Is it true that MySQL defaults to local access only, and to enable remote access you must do something unusual with grants? If so, how would I do this? (warning, we use the win version, but this question is not a windows-only thing so it is topical for this list:-) Thanks in advance for reading a post like this with such a dumb question! Mary Grace -- 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: Allowing remote login
Mary,
I guess, depending on your perspective, that is the case. MySQL
identifies users by their user name and their originating host. For
example: [EMAIL PROTECTED] is different than [EMAIL PROTECTED]. Its
likely you have 2-entries in your mysql.user table for root: localhost
and [workstation].[domain] (where workstation is the workstation's name
and domain is the name of the domain that its in - it might be suffixed
with .com or .net).
As a side note, you should never share your root account. At best, give
them all privileges for the database(s) they need.
Try something like this:
GRANT ALL TO DB_NAME.* ON [EMAIL PROTECTED];
UPDATE mysql.user SET password=PASSWORD('temp') WHERE user =
'temp_user');
FLUSH PRIVILEGES;
Then see if they can access that.
Regards,
A$
On Saturday, August 2, 2003, at 11:55 AM, Mary Grace wrote:
I have been using MySQL for awhile, and for the first time someone
from outside our portable /16 needs access to some tables. We have
given them the server IP address, the dB name, the port 3306, the root
username, and the password, but they still can't get access. Of
course, no firewalls or other things in the way.
Is it true that MySQL defaults to local access only, and to enable
remote access you must do something unusual with grants? If so, how
would I do this? (warning, we use the win version, but this question
is not a windows-only thing so it is topical for this list:-)
Thanks in advance for reading a post like this with such a dumb
question!
Mary Grace
--
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: standardized naming system ?
Jim Smith wrote: Wasn't my question. well ... it was mine but I'll share it with you ;) Why do you need to label a table as a table? That was my thought, too. There is some merit in using type prefixes in VB because of its loose typing, but it makes no sense in a database context where the types of objects are quite distinct. If everything that has collums stored in is a table like in MySQL now, it really doesn't make sense. On the other hand there are DBMS which have such things as stored queries or views that akt as tables but aren't. There it makes sense to discriminate those objects. I'd really liked to have views by the way ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
NON web based client server applications
hi all, i am a new-bie, should i even think of using mySQL in the following scenario ? i have a situation in mind where two or three ( sometimes concurrently ) people will be making invoices, on three computers in different locations on lan. what front end tools should i think of using for dataentry clients ? how can i print invoices from the database, on a specific printer only, when somebody ( with a username and pswd ) gives a command from anywhere on the Lan / intranet. kb ** Message from InterScan E-Mail VirusWall NT ** ** WARNING! Attached file noname.htm contains: VBS_REDLOF.A virus It has been deleted. * End of message *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_connections doesn't change
Hi, I'm trying to change the value of max_connections variable but mysqld doesn't accept that. I use mysql 3.23.54 and my config file /etc/my.cnf looks like: [mysql.server] ... ... set-variable max_connections=200 After a newstart of mysqld the value of max_connections is unchanged and equals 100. PLS help Regards Jan Josefowicz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help in querying two tables
SELECT [field list] FROM archivetable,currenttable WHERE archivetable.username=currenttable.username notes: can use join,left,right,select inside select aka subselect check the manual for detail Regards, -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo Fred van Engen wrote: Hi, On Fri, Aug 01, 2003 at 11:55:36PM +0800, Jaime Teng wrote: Now, I have two of these tables (archivetable,currenttable). My problem is how do I perform a single query such that I get results from these two tables: mysql select * from archivetable,currenttable; +++-+-+ | sessionid | username | logon | logoff | +++-+-+ | 03 | dangco77 | 1996-09-25 20:51:59 | 1996-09-25 21:07:00 | | 06 | mccarthy | 1996-09-26 06:15:35 | 1996-09-26 06:20:00 | | 07 | sigmaph| 1996-09-26 06:25:48 | 1996-09-26 06:28:00 | | 09 | sigmaph| 1996-09-26 08:31:53 | 1996-09-26 08:51:00 | | 1000265891 | okame | 2003-08-01 13:38:24 | 2003-08-01 13:43:42 | | 1000265893 | kbs| 2003-08-01 13:38:30 | 2003-08-01 13:38:48 | | 1000265897 | bdo-albaro | 2003-08-01 13:38:54 | 2003-08-01 14:07:06 | +++-+-+ of course that last query isnt correct but thats the result I want. You can use a UNION to do this, but you need MySQL 4.x. It won't work in 3.23.x or before. Look in the manual for details. any suggestion? I read about using JOIN but I have no idea how to\ make it work for my need. Joins are used for combining records from multiple tables, which is not what you seem to want to do. Regards, Fred. Regards, -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using query file from console
try the shortest. mysql yoursource.sql ;) -- Aftab Jahan Subedar Software Engineer Subedar Technologies Subedar Baag Bibir Bagicha #1 81/1-A North Jatrbari Dhaka 1204 Bangladesh sms://+447765341890 sms://+880171859159 http://www.ceobangladesh.com http://www.DhakaStockExchangeGame.com http://www.geocities.com/jahan.geo Fred van Engen wrote: Hi, On Fri, Aug 01, 2003 at 04:55:09PM +0200, Lorenzo Rossi wrote: I need to run a query written in a file from inside mysql console, not shell prompt. Anyone can help me? mysql help MySQL commands: Note that all text commands must be first on line and end with ';' help(\h)Display this help. ? (\?)Synonym for `help'. clear (\c)Clear command. connect (\r)Reconnect to the server. Optional arguments are db and host. edit(\e)Edit command with $EDITOR. ego (\G)Send command to mysql server, display result vertically. exit(\q)Exit mysql. Same as quit. go (\g)Send command to mysql server. nopager (\n)Disable pager, print to stdout. notee (\t)Don't write into outfile. pager (\P)Set PAGER [to_pager]. Print the query results via PAGER. print (\p)Print current command. quit(\q)Quit mysql. rehash (\#)Rebuild completion hash. source (\.)Execute a SQL script file. Takes a file name as an argument. status (\s)Get status information from the server. tee (\T)Set outfile [to_outfile]. Append everything into given outfile. use (\u)Use another database. Takes database name as argument. Connection id: 171359 (Can be used with mysqladmin kill) mysql So it looks like you could try the 'source' command. Copying the query through your clipboard is another option. Regards, Fred. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Date to Days Query
Hello, I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memiddaysregdate expdate -- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the days are accurate according to the expdate, and change the days so they are correct ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update from 3.23 to 4.0
Hi!
I updated my MySQL from 3.23 upto 4.0 and now began the problems!
All Users in my mysql-db are with this privileges:
INSERT INTO `user` (`Host`, `User`, `password`, `Select_priv`,
`Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`,
`Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`,
`Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`,
`Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`,
`Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`,
`Repl_client_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`,
`x509_subject`, `max_questions`, `max_updates`, `max_connections`)
VALUES
('localhost', 'SOME_USER', 'SOME_ENCRYPTED_PASSWORD', 'N', 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', '', '', '',
'', 0, 0, 0),
And Show_db_priv is definitiv to N.
But when I login as a user I see ALL other databases.
I only want that the users do not see the other databases.
Must I edit something else, I read the online-help,
but it seems that I made everything right. :(
Did I made something wrong or is it a bug?
Viele Gruesse,
Peter.
--
_
Peter Padberg Pyrmonter Str. 42
Webagentur web4.hm 31789 Hameln
Geschaeftsfuehrung Germany
Telefon: +49-5151 60 99 70-0[EMAIL PROTECTED]
Telefax: +49-5151 60 99 70-1http://web4.hm
_
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Date to Days Query
Mike, Is the field days meant to be the number of days until expdate? (This is implied by the number 14 you gave). If so, you shouldn't be storing the days value in the database at all (the reason being it causes exactly the problem you seem to have). Instead you should store only expdate and then calculate the number of days to expiry in a query like this: select (to_days(expdate) - to_days(curdate()) as days_to_expire from xxx...; If you get a negative answer then the record has already past it's expiry date. If I've misunderstood then sorry, please try again. Regards, Andy. Mike Blezien wrote: Hello, I need to update one of our mysql tables, which has about 60,000 entires and correct the amount of days remain on each data record. An example of one of the data entires is: memiddaysregdate expdate -- 625290 | 5 | 2003-07-15 | 2003-08-16 now the days should be 14 and not 5 days. I have been trying to update the table with a single SQL query but haven't come up with a way to do this.. I'm sure it's something simple but I can't seem to come up with it. What is the best way to accomplish this in a single query to update the entire table so all the days are accurate according to the expdate, and change the days so they are correct ?? thx's -- MikemickaloBlezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Web Hosting http://www.justlightening.net Tel: 1(985)902-8484 MSN: [EMAIL PROTECTED] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- 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: Adv. Mysql query
Mattias, It may not be such a good idea to store the 6 items in 1 record. An alternative structure is one table with Item ID and Name and another with Item Id and Component Id, both of which point back to item id in the first table. This avoids having to know how many components there are and allows you to add a seventh component without changing the data structure. See Jim Smith's excellent answer to a previous, similar question (I've added it below). If you want to fix what you've got then an OUTER join will help (see docs) but sure as eggs is eggs someone will add a seventh component and then you will have to fix every table and query and program, so if you're still in design phase - then IMHO it's time to rethink the structure. Best regards, Andy jim smith This is a classic problem known as a Bill of Materials explosion and unfortunately relational databases don't handle it very well. Storage is easy(ish). Fundamentally you have a recursive many to many relationship between components, resolved as Component: Component_Link id ---|---assembly_id name|---subcomponent_id That is 2 foreign keys back to the same master table, if the diagram isn't clear. In OO terms, both item and kit are subclasses of component. There are may ways to implement that in a relation database, but the simplest is to store them as a single table with a type field. Retrieval is harder. To get the contents of an assembly (kit), select * from component as assembly, component as subcomponent, component_link where assembly.name=? and component_link.assembly_id=assembly_id and subcomponent.id= componentLink.subcomponent_id BUT, this only goes down to one level which may be enough for most purposes, but for stock monitoring (e.g. I've sold 10 of kit ZZA102, what effect does that have on my item stocks), you need to do it recursively. With mysql ( and most other DBMS) the only alternative is to do the recursion in a program - ie get all first level children foreach get next level foreach get next level etc Oracle has an excellent CONNECT BY extension to standard sql which does this brilliantly, and I believe mysql AB are planning to imlpement it sometime. /jim smith Mattias Larsson wrote: Hi there. I have a little problem with a sql-query I'm trying to get together. Well I got it to work halfways. I have a table with items, which are composed of 1-6 other items, which is saved in the same table. You might call it a recursive query, I need to get the names of the components that makes up the item I search for. It looks about like this: (3 CompX removed for readability) ID Name Comp1 Comp2 Comp3 1Item10 0 0 2Item20 0 0 3Item30 0 0 4Item40 0 0 5Item51 3 4 Item5 is a composition of item 1,3 and 4. It would be no problem if I only wanted to get the ID of the Included components but I want the names. I tried using: SELECT i.Name, c1.Name, c2.Name, c3.Name FROM Items AS i, Items AS c1, Items AS c2, Items AS c3 WHERE i.ID=5 AND i.Comp1=c1.ID AND i.Comp2=c2.ID AND i.Comp3=c3.ID; This gives me the right result, but if an Item only consists of 2 components then I don't get anything Which is understandable. And I can only imagine what kinds of resources it will take if the Items table Gets really large. Is there a better way to do this? I want to keep the number of queries to a minimum. I have read some on Union, joins and subqueries but I can't think of a way to make it work. -- 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: Update from 3.23 to 4.0
In the last episode (Aug 03), web4.hm - Peter Padberg said:
Hi!
I updated my MySQL from 3.23 upto 4.0 and now began the problems!
All Users in my mysql-db are with this privileges:
INSERT INTO `user` (`Host`, `User`, `password`, `Select_priv`,
`Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`,
`Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`,
`Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`,
`Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`,
`Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`,
`Repl_client_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`,
`x509_subject`, `max_questions`, `max_updates`, `max_connections`)
VALUES
('localhost', 'SOME_USER', 'SOME_ENCRYPTED_PASSWORD', 'N', 'N', 'N', 'N', 'N', 'N',
'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'Y', 'Y', 'N', 'N', 'N', '', '',
'', '', 0, 0, 0),
And Show_db_priv is definitiv to N.
But when I login as a user I see ALL other databases.
I only want that the users do not see the other databases. Must I
edit something else, I read the online-help, but it seems that I made
everything right. :(
Did I made something wrong or is it a bug?
From http://www.mysql.com/doc/en/GRANT.html :
`SHOW DATABASES' `SHOW DATABASES' shows all databases.
You have that set (the Show_db_priv field). Revoke that privilege from
your users if you only want them to see databases they have access to.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NON web based client server applications
KB, 1) There's an interesting virus alert attached to your message. 2) what front end tools should i think of using for dataentry clients IMHO, the answer is 'the web'. Is there a good reason why you don't want to do that? I realise that JSP is an obvious choice and perhaps like me you think it sucks, but it's the best of a bad lot. PHP and perl are other options. You won't need more hardware than your mysql server if you run something like apache tomcat for just 4 users. The web/intranet brings all the advantages of not having to install and update software (except on the server). For smaller project we actually use visual basic 6 with a JSP 'gateway' that passes messages back and forth between web clients and the VB program. So we get all the advantages of VB (well known, easy to use, great debugger, easy to print!) but the down sides are: We need a windows OS to run the VB (but it need not be a 'server'), VB can be slower than raw JSP and you need to keep an eye on the VB program cos if it crashes the whole shooting match goes down. I'd be interested in the other answers. Regards, Andy. Java: A sledgehammer to crack a nut? No, more like a sledgehammer to paddle a canoe. KB wrote: hi all, i am a new-bie, should i even think of using mySQL in the following scenario ? i have a situation in mind where two or three ( sometimes concurrently ) people will be making invoices, on three computers in different locations on lan. what front end tools should i think of using for dataentry clients ? how can i print invoices from the database, on a specific printer only, when somebody ( with a username and pswd ) gives a command from anywhere on the Lan / intranet. kb Name: InterScan_SafeStamp.txt InterScan_SafeStamp.txtType: Plain Text (text/plain) Encoding: 7bit -- 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: NON web based client server applications
Well, unless you like a bucket of spaghetti with your code. Avoid an MS solution like VB at all costs. If you want a non web-based solution. I would use Java RMI on Linux. The MOST flexible and the MOST robust solution. And, stay way from the Win platforms for your server box. As the previous poster stated. When an MS solution goes down. It takes everyone with it. Also, MS solutions costs mucho deniro. The FREE Java solutions are too numerous to list http://www.sourceforge.net. If you do decide to use a Java solution. There are a myriad of web sites with lots of code samples and examples to get you going. -- Kevin J Citron Sr. Object Imagineer Optimized Objects, Inc. EL Paso, Texas 79930 (915) 565-5777 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Nested SELECT statements problem
Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Nested SELECT statements problem
Dan Nelson wrote: In the last episode (Aug 02), Pascal Dlisle said: I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur = ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. I doubt think it would work under Oracle either, since you quoted your subselect. Unless book.IDLivre is a varchar field with one of the records containing the string SELECT book.IDLivre ... = ecr.IDLivre, of course. Also make sure you're using MySQL 4.1.0, since that's the first version that supports subselects. If you're running something older, take a look at http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html which shows you how to rewrite most (not all) subqueries as joins. How about if you try this: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM auteur aut INNER JOIN (livreEcritPar ecr INNER JOIN livre book ON ecr.IDLivre = book.IDLivre) ON aut.IDAuteur = ecr.IDAuteur WHERE book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail)
RE: Nested SELECT statements problem
One problem is that you have quoted your sub-query, which makes it to return a constant string. Another problem I saw in your code is that you used the same aliases for tables in the query and in the sub-query. In such case, the SQL parser would take all of them to refer to the same table, probably the ones in the query. My suggestion would be to use different aliases in the query and sub-query for the same table. That way, in each of your where-clause, the SQL parser will know exactly which table reference you want. Also, be sure that your data is good so that your sub-query indeed returns some records to be matched; or otherwise the query will not return anything. Hope this helps. Lin -Original Message- From: Pascal Délisle [mailto:[EMAIL PROTECTED] Sent: Saturday, August 02, 2003 11:46 PM To: [EMAIL PROTECTED] Subject: Nested SELECT statements problem Hi! I try to figure out how to use a nested SELECT statement after the IN predicate. For example, when I try this code, it doesn't return anything although it should: SELECT book.IDLivre, aut.Prenom, aut.Nom FROM livre book, livreEcritPar ecr, auteur aut WHERE ecr.IDLivre = book.IDLivre AND aut.IDAuteur ecr.IDAuteur AND book.IDLivre IN (SELECT book.IDLivre FROM livre book, livreEcritPar ecr, auteur aut WHERE aut.Prenom like '%$firstName%' AND aut.Nom like '%$name%' AND ecr.IDAuteur = aut.IDAuteur AND book.IDLivre = ecr.IDLivre); So, my question is the following: How should I change syntax in order to make this bunch of code work? I mean, under Oracle SQL, this syntax would be legal and work perfectly, so I'm confused how to solve my problem. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Root
Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Root
FT wrote: Hi everyone I am quiet new to DB and MySql I must have made a big mistake by changing the root @ localhost password via phpMyAdmin and now it is impossible to connect from neither the mysql prompt nor phpMyAdmin... Does anyone know how to recover this problem...I did try to reinstall MySql after uninstalling it and rebooted the server but no luck so far... Thanks for the hand Fabrice Try re-installing (even w/o uninstalling) using the --force option. -- Peter K. Aganyo Eternal Designs Inc., +1(617)344-8023 (fax voicemail) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
