Re: Two Primary Keys
Hi, try this and you will see exactly how autoincrement behaves in MyISAM tables when it is part of primary key. 1) declare table like this: CREATE TABLE `test_tbl` ( `field1` int(10) unsigned NOT NULL default '0', `field2` int(10) unsigned NOT NULL auto_increment, `field3` char(10) NOT NULL default '', PRIMARY KEY (`field1`,`field2`) ) ENGINE=MyISAM; 2) then insert some values INSERT INTO test_tbl (field1, field3) VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); 3) see what's in the table SELECT * FROM test_tbl ORDER BY field1; result is: 1, 1, 'test1' 1, 2, 'test3' 2, 1, 'test2' 2, 2, 'test4' field2 is unique only in context of field1. Hth, Dusan Victor Subervi napsal(a): 2010/6/29 João Cândido de Souza Neto j...@consultorweb.cnt.br As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Complex conditional statement during select
Try this: SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) = '20080101' AND SUBSTRING(myDate,1,10) = '20080131', 1, 0)) AS `January` FROM theTable GROUP BY theOther Problem was in parentheses Dusan Jay Blanchard napsal(a): SELECT this, that, theOther, SUM(IF(SUBSTRING(myDate,1,10) = '20080101' AND SUBSTRING(myDate,1,10) = '20080131'), 1, 0) AS `January` FROM theTable GROUP BY theOther Throws this error... ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')), 1, 0) AS `January` Can I even do something like this during the SELECT. I tried a BETWEEN and while it did not throw errors it did not give back the expected data...I just got 0 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help to query with timestamp in C++
Hi Kandy,
this could be the query you are looking for. It should return record
with the closest timestamp to your required time:
(SELECT TIMEDIFF('20080815091907', timestamp_column) AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column DESC LIMIT 1
)
UNION
(SELECT TIMEDIFF(timestamp_column, '20080815091907') AS diff, t.* FROM
table1 t
WHERE timestamp_column = '20080815091907'
ORDER BY timestamp_column LIMIT 1
)
ORDER BY diff LIMIT 1
HTH,
Dusan
Kandy Wong napsal(a):
Hi,
I need to write a C++ program in a Linux environment to query with a
timestamp.
The user will only provide with an approximate time so I'd like to know
how can I write a program or a query to return the closest data.
The followings are the timestamp in the MySQL database:
| 2008-08-05 03:56:09 | 1217933769 |
| 2008-08-05 03:56:19 | 1217933779 |
| 2008-08-05 03:56:29 | 1217933789 |
| 2008-08-05 03:59:39 | 1217933979 |
| 2008-08-05 03:59:49 | 1217933989 |
| 2008-08-05 03:59:59 | 1217933999 |
| 2008-08-05 04:02:39 | 1217934159 |
| 2008-08-05 04:02:49 | 1217934169 |
| 2008-08-05 04:02:59 | 1217934179 |
For example, '2008-08-05 04:01:39' is the time provided by the user which
does not exist in the database. So, how can I return the closest data? I
know I can make use of 'LIKE' but this will return more than one data.
What is the best method to get the closest one?
And what is the good connector (C++ to MySQL) to use?
Any suggestion?
Thank you.
Kandy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost Connection each hour :(
Marco, can you post values of wait_timeout and interactive_timeout variables. You can get them from SHOW VARIABLES output. If they are set to 3600 (1 hour in secs), set them to something bigger like one week and then you will see if it helps or not. You can read more about those timeouts also in manual. HTH, Dusan MAS! napsal(a): Can anyone help me to understand why my site (php 4.4.2 / ubuntu dapper) loose all connections to mysql server when /etc/cron/hourly starts? (there are no process to start hourly, the directory is empty) I have heavvy web/apache2 traffic on my site and that is not so nice for my users.. thank you in advance regards marco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost Connection each hour :(
MAS! napsal(a): can you post values of wait_timeout and interactive_timeout variables. You can get them from SHOW VARIABLES output. If they are set to 3600 (1 hour in secs), set them to something bigger like one week and then you will see if it helps or not. You can read more about +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | table_lock_wait_timeout | 50| | wait_timeout | 28800 | +--+---+ :( may be I have to increase the net_% variables? I don't think that slave_net_timeout can cause you those problems but you can increase its value and you will see if it it helps or not. But read about those timeouts anyway. Also try to look at: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data infile and character set
Are you sure your file is coded in utf8? Character set of your file must be same as charset of your database. Dusan Caleb Racey napsal(a): Does anyone know how to get the load data infile command to load utf8 data? I have setup a database as utf8 with a collation of utf8_general_ci, the mysqld server is started with --character-set-server=utf8. Server variables say character_set_database = utf8. I use the sql below LOAD DATA INFILE 'filename' REPLACE INTO TABLE db.table CHARACTER SET utf8 FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' Yet when i try to load a file with an accented value in it e.g. Agustín the value gets truncated to Agust anyone got any solutions to this? Regards Cal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 problem in index
Marten Lehmann napsal(a): Hello, I have a table like this: CREATE TABLE `places` ( `name` varchar(256) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (`name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | Then I want to insert two values: pjöngjang.com and pjongjang.com But on the second record I get this message: DBD::mysql::st execute failed: Duplicate entry 'pjongjang' for key 1 Whats wrong with it? The character set and collation set is defined as utf8, so mysql should see a difference between ö and o. Regards Marten It's not problem of charset but collation. Try to change collation to utf8_bin if it's possible. ö and o are of course stored as two different chars in utf8 but collation gives them weight for correct ordering and probably in utf8_unicode_ci collation they have same weight. HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Edward Kay napsal(a): I would like to import data from a utf8-coded comma seperated file. I created my database with DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci and I started my mysql-client with the --default-character-set=utf8 option. Nevertheless, when I input primary key fields, which differ only in one umlaut character (e.g. achten and ächten) I get the following error message: ERROR 1062 (23000): Duplicate entry 'ächten' for key 1 (Same thing happens when I try to manually INSERT the row.) When I display my variable settings with SHOW variables LIKE 'c%'; I receive the following result: +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | utf8_general_ci| | collation_database | utf8_general_ci| | collation_server | latin1_swedish_ci | | completion_type | 0 | | concurrent_insert| 1 | | connect_timeout | 5 | +--++ 14 rows in set (0.02 sec) From this I conclude it is the server setting, which causes the trouble here. When I manipulate the settings manually from the client (with SET character_set_server=utf8; SET collation_server=utf8_general_ci;) the values do change, but not the behaviour. But this can be expected, since the server is already up and running with the wrong settings. Does anybody know how I restart my mysql-server with the correct character and collation settings, if this is the cause for my problem, or if there might be any other reason for it. My mysql version is 5.0.26-12, running on a Suse Linux 10.2. Best regards, H. Try using the SET NAMES 'utf8' statement [1] to tell MySQL that your client is sending data in UTF-8. I believe that as your server is latin1, it will assume this is the character set used by the command line client. [1] http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html Edward From my experience SET NAMES doesn't work, but character set of the database must be same as file's character set and this condition is OK. For sure I used script: USE database_with_correct_charset; LOAD DATA ...; And this worked fine for files with cp1250 and also with keybcs2 (I had two databases, of course) HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Harald Vajkonny napsal(a): Ananda Kumar schrieb: strange. did u exit and reconnect and did the select? Yes, I tried it once more. I have to put the USE command before I change session settings to latin to make it work without error (otherwise I still get the duplicate message). But even after exiting I get the national characters displayed as two (or more) bytes. Try to convert file to latin1, if it's possible, create database with latin1 charset, create table with required structure (you can set utf8 charset to string fields ) and then load data. What client do you use to check whether data are OK? I can recommend MySQL Query Browser where I don't have to care about setting charset and collation parameters. HTH, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INTO doesn't work correctly with utf8
Harald Vajkonny napsal(a): Dušan Pavlica schrieb: Try to convert file to latin1, if it's possible, create database with latin1 charset, create table with required structure (you can set utf8 charset to string fields ) and then load data. I can not convert the file into latin1, because it is multilingual (i.e. European, Japanese, Korean etc.). What client do you use to check whether data are OK? I can recommend MySQL Query Browser where I don't have to care about setting charset and collation parameters. I checked the file in MySQL Query Browser too, but even there I get it wrong, when I imported it with a latin session. I get it displayed right, when I import it with utf8, but then I have the message with duplicate keys and get only half of the data. What's the charset and collation of your primary field in the table? Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump problem with large innodb tables...
Try to look for Lost connection error in MySQL manual and it can give your some hints like http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Dusan Hartleigh Burton napsal(a): Hi All, I have a database which is currently at ~10GB in it's test phase. It is containing uncompressed audio and is expected to reach 1.5TB in no time at all. I am just running some backup tests and I have been having lots of problems creating an accurate backup. I have tried both MySQL Administrator mysqldump, both applications drop out on the same table, the table `trackdata` which contains ~9GB worth of data. There is no single row any larger than 50MB, most average around 40MB. Windows 2000 Server, MySQL v5.0.37-community-nt and all tables are InnoDB. If anyone can help me out with this problem the assistance is greatly appreciated. I have scoured google and various other sources and not found much information that has been useful to me. I hope I have enough info below... if more is required let me know. mysqldump example P:\mysqldump -u username -p mraentertainment mraentertainment.sql --opt --verbose --max_allowed_packet=500M --hex-blob --single_transaction --net_buffer_length=100M Enter password: ** -- Connecting to localhost... -- Retrieving table structure for table albums... -- Sending SELECT query... ... -- Retrieving rows... -- Retrieving table structure for table trackdata... -- Sending SELECT query... -- Retrieving rows... mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `trackdata` at row: 1 my.ini configuration file [client] port=3306 [mysql] default-character-set=latin1 [mysqld] log-bin=itd002-bin server-id=1 port=3306 wait_timeout=86400 max_allowed_packet=100M basedir=C:/Program Files/MySQL/MySQL Server 5.0/ datadir=C:/Program Files/MySQL/MySQL Server 5.0/Data/ default-character-set=latin1 default-storage-engine=INNODB sql-mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION max_connections=100 query_cache_size=0 table_cache=256 tmp_table_size=77M thread_cache_size=8 #*** MyISAM Specific options myisam_max_sort_file_size=100G myisam_max_extra_sort_file_size=100G myisam_sort_buffer_size=154M key_buffer_size=130M read_buffer_size=64K read_rnd_buffer_size=256K sort_buffer_size=256K #skip-innodb innodb_additional_mem_pool_size=6M innodb_flush_log_at_trx_commit=1 innodb_log_buffer_size=3M innodb_buffer_pool_size=252M innodb_log_file_size=126M innodb_thread_concurrency=8 Regards, Hartleigh Burton Resident Geek MRA Entertainment Pty Ltd 5 Dividend St | Mansfield | QLD 4122 | Australia Phone: (07) 3457 5041 Fax: (07) 3349 8806 Mobile: 0421 646 978 www.mraentertainment.com Internal Virus Database was built: Never Checked by MAC OSX... we don't get viruses! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select first letters
I'm not sure, but I think that greek characters are sorted after English chars so try this: SELECT DISTINCT LEFT(title,1) FROM odigos_details WHERE LEFT(title,1) 'z' ORDER BY title HTH, Dusan nikos napsal(a): Hello list. I want to select discinct the first letters of titles in a UTF8 table but only the greek ones. There are both english and greek charakter titles. How can I exclude the english from selection? My table is: CREATE TABLE `odigos_details` ( `id` int(11) NOT NULL auto_increment, `cat` tinyint(3) default NULL, `territory` tinyint(3) default NULL, `title` varchar(150) default NULL, `story` text, `link` varchar(100) default NULL, `address` varchar(150) default NULL, `tel` varchar(50) default NULL, `fax` varchar(11) default NULL, `photo` varchar(100) default NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 and I run: SELECT DISTINCT LEFT(title,1) FROM odigos_details ORDER BY title witch is working fine. Thank you. Nikos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Borland C++ Builder 2006 DLL Woes
I had to buy dbExpress libraries for MySQL from third party. Dusan [EMAIL PROTECTED] napsal(a): We just purchased The Borland Developer Studio 2006 IDE and are having significant problems using dbExpress objects to communicate with MySQL servers (both 4 and 5). Curiously, we can perform inserts but not selects, even though identical code in C++ Builder 6 worked just fine. The DLL in C++ Builder 2006 is dbxmys30.dll. Anybody having similar issues? Thanks, David David P. Giragosian, Psy.D. Database and Software Developer MD Anderson Cancer Center Houston, TX 713-792-7898 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a select query
select * from table1 order by field1 limit 10,1 Dusan balaraju mandala napsal(a): Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: running sum with a @variable
try to put parenthesis around @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , (@runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar)) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; HTH, Dusan C.R.Vegelin napsal(a): I want to calc a running sum with @variables. Using the command line client, I enter: SET @row := 0, @runsum := 0; followed by: SELECT @row := @row+1 AS `Row`, mycountries.Name AS `Country` , ROUND(SUM(db.Jan+db.Feb+db.Mar)) AS `Q1` , @runsum := @runsum + SUM(db.Jan+db.Feb+db.Mar) AS `RunSum` FROM data2006 AS db LEFT JOIN mycountries ON db.Country = mycountries.ID WHERE ... GROUP BY db.Country; Results are: Row Country Q1RunSum 1 Germany 9090 2 France 6060 3 Norway 2424 etc. I expect the RunSum for Germany 60, France 150, Norway 174 etc. Whay am I doing wrong ? Any help is appreciated ! Regards, Cor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET @var and insert that as cunting var into table with insertselect
Barry napsal(a): Dušan Pavlica schrieb: Barry napsal(a): Hello Everyone! I am having a big problem with counting inserting rows. This is my Query: SELECT MAX(id) INTO @maxid FROM table1; // @maxid is now 44 INSERT INTO table2 (orderid, someothervars) SELECT @maxid +1, blahvar FROM table3; try select @maxid:=max(id)-1 from table1; insert into table2 (orderid, someothervars) SELECT @maxid:[EMAIL PROTECTED] +1, blahvar FROM table3; dusan Thanks dusan, thanks filip that works perfect :) Can you tell me where i can find that := function on the mysql dev site? Search function don't allow := and i didn't found it. That would be very nice :) My best regards Barry Barry, it's not function, it's assignment operator. Try to search 'user-defined variables' in manual. It's chapter 9.3 in version I have Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SET @var and insert that as cunting var into table with insert select
Barry napsal(a): Hello Everyone! I am having a big problem with counting inserting rows. This is my Query: SELECT MAX(id) INTO @maxid FROM table1; // @maxid is now 44 INSERT INTO table2 (orderid, someothervars) SELECT @maxid +1, blahvar FROM table3; try select @maxid:=max(id)-1 from table1; insert into table2 (orderid, someothervars) SELECT @maxid:[EMAIL PROTECTED] +1, blahvar FROM table3; dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CONCAT(int_col, string_col) and charset and collation problems
I know about CONVERT but I wanted to check character set of 'tt' column and this result use in CONVERT. Something like CONVERT(id USING CHARSET(Name)) which doesn't work. I just wonder why CONCAT(1, ',', Name) works OK even if CHARSET(1) is binary string and CONCAT(id, ',', Name) doesn't Thanks, Dusan ViSolve DB Team napsal(a): Hi, It is that, what you think a binary is ,...is indeed a binary. As per the manual, If a string input or function result is a binary string, the string has no character set or collation. so the resultant 'binary' is expected. if u want the resultant as: mysql select charset(concat(tt,CONVERT(id USING latin1))) from test; Thanks ViSolve DB Team - Original Message - From: Dušan Pavlica [EMAIL PROTECTED] To: list mysql mysql@lists.mysql.com Sent: Wednesday, January 31, 2007 5:31 PM Subject: CONCAT(int_col, string_col) and charset and collation problems Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- 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]
CONCAT(int_col, string_col) and charset and collation problems
Hi, I'm using MySQL 4.1.15, WinXP and my problem is that SELECT CHARSET(CONCAT(int_column, string_column)) FROM mytable; always returns charset 'binary' and I need resulting charset to be same as a charset of a string_column because I don't want to look for charset of a column whenever I have to call CONCAT function. Do you have any tips how to achieve it? In my opinion, results of concatenating string and numeric columns should always have charset of string column(s) and not binary charset. Thanks in advance for any response Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Extracting data from MySQL into Access
Shortly, if you have MySQL ODBC driver installed, you can create ODBC data source and connect to this data source from Access, Excel, Open Office, Dusan Dewald Troskie napsal(a): Hi, I know this is a weird request, but I need a tool / tutorial on extracting data from InnoDB tables in MySQL 5.x and putting the data into Microsoft Access. COuld anybody advise me where to do this? Thanks, Dewald Troskie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need any that can translate this
Temporary tables are stored in memory so they are as quick as joins. Dusan Ran napsal(a): Not necessarily to use temp tables,they are expensicve. Temp tables introduce extra disk IOs which slows down compare to using explicit JOINs which is done in memory. Here is techniques on how to rewrite a subquery with JOINs: http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html Ran On 1/11/07, Peter Bradley [EMAIL PROTECTED] wrote: Ysgrifennodd Tomás Abad Fernández: I dont know why thease don't work at MySql 3.21 , anyone can help me to make this compatible with mysql3? $query = SELECT * FROM facturas WHERE pedido IN (SELECT orders_id FROM orders WHERE user_id='.$user.') ORDER BY factura_id; Thanks for all. If I remember correctly, 3.21 didn't do sub-queries. You had to use temp tables. Someone else will correct me if I'm wrong. Peter -- 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: MySQL 5.0.27: character problem
Eric, I think that you don't have to write a conversion program because MySQL have built-in pretty good character set conversions. All you need to do is to tell MySQL which character set uses your file with your SQL commands. Create your file with one of the character sets MySQL understands and put as a first command to execute SET NAMES 'character_set_of_your_file' You can check MySQL's character sets by executing command SHOW CHARACTER SET. HTH, Dusan Eric Lilja napsal(a): Thanks for the reply, Mr Schwartz. I will see if I can find some clue on how to write such a program myself. - Eric Jerry Schwartz skrev: I have run into this as well. Windows uses CP-1522 (if I remember correctly), which is not exactly equivalent to UTF-8. I presume it is also not exactly equivalent to the character set you're using for MySQL. I wound up writing a program to convert the one character set to the other. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Eric Lilja Sent: Wednesday, December 13, 2006 2:03 PM To: mysql@lists.mysql.com Subject: MySQL 5.0.27: character problem Hello, I'm using MySQL version 5.0.27 under Windows XP professional. I have a text file with some SQL commands (I create a few tables and insert some rows into them). I noticed that all columns where I tried to insert a swedish character, that character got corrupted. But it works if I type the same explicitly in mysql monitor. What do I need to do so I can use command files and still have proper handling of swedish characters? - Eric -- 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: Convert hex to decimal?
Frederic Wenzel napsal(a):
Hey,
I have a table with a SMALLINT column that -- trough a mistake --
contains values like 57, 13 etc. which in fact are 0x57 and 0x13 (i.e.
HEX numbers).
How would I convert
13 (false decimal) to 0x13 (Hex) and from there to 19 (decimal)?
I tried my luck with UNHEX and CAST but I only got 0 or NULL back
respectively.
Thanks in advance
Fred
conv('column_name', 16, 10)
Dusan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deleting, skip the first n records
If you don't know the cutoff_date you can simply find it by SELECT creation FROM tablename ORDER BY creation DESC LIMIT n, 1 which gives you the creation of the n-th newest record and then you can use Dan's solution DELETE FROM tablename WHERE creation cutoff_date HTH, Dusan Dan Julson napsal(a): Brian, My bad in steering you into the offset direction. You are right. Offset cannot be used within a delete statement. However, if I am reading your email correctly, you could specify a cutoff date and use that in the Delete statement like this: DELETE FROM tablename WHERE creation cutoff_date -Dan My bad, the = was my own typo just in the email, it's not in my actual query. I've tried using offset (delete...limit 50,99) and it returns an error, and it is not documented (search that page you referenced for offset). On Oct 4, 2006, at 9:04 AM, Rob Desbois wrote: DELETE does support the offset (http://dev.mysql.com/doc/refman/5.0/ en/delete.html) the problem is you have an erroneous equals character: You wrote: DELETE FROM tablename ORDER BY creation DESC LIMIT=n You need: DELETE FROM tablename ORDER BY creation DESC LIMIT offset, count HTH, --Rob The offset is what I was thinking of - that would be the simplest - but as far as I can tell, delete doesn't support the offset. It's not documented, and it gives me an error when I try it. I was hoping to avoid two queries but it sounds like that's what I might have to do. On Oct 4, 2006, at 8:37 AM, Dan Julson wrote: You can add an offset in the Limit statement. Look at the Select Syntax in the docs. There is an even simpler solution to this problem. Use your creation field within a Where clause instead of using Order by and Limit. -Dan I'm trying to delete all but the newest n records. DELETE FROM tablename ORDER BY creation DESC LIMIT=n This does the opposite of what I want. Is there some way to tell it to start the delete after n and delete all the remaining records? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- Original Message -- FROM: Brian Dunning [EMAIL PROTECTED] TO:mysql@lists.mysql.com DATE: Wed, 4 Oct 2006 08:49:48 -0700 SUBJECT: Re: Deleting, skip the first n records The offset is what I was thinking of - that would be the simplest - but as far as I can tell, delete doesn't support the offset. It's not documented, and it gives me an error when I try it. I was hoping to avoid two queries but it sounds like that's what I might have to do. On Oct 4, 2006, at 8:37 AM, Dan Julson wrote: You can add an offset in the Limit statement. Look at the Select Syntax in the docs. There is an even simpler solution to this problem. Use your creation field within a Where clause instead of using Order by and Limit. -Dan I'm trying to delete all but the newest n records. DELETE FROM tablename ORDER BY creation DESC LIMIT=n This does the opposite of what I want. Is there some way to tell it to start the delete after n and delete all the remaining records? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Converting string hex column to integer
Hello, I have column of type char(2) containing hex numbers (e.g. 0A, FF, ...) and I cannot find correct function which could convert those hex numbers to integers so I can perform futher calculations. I experimented with HEX(), CAST(), CONVERT() but I wasn't succesfull. Thanks in advance, Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Converting string hex column to integer
Wolfram Kraus napsal(a):
On 28.06.2006 13:54, Dušan Pavlica wrote:
Hello,
I have column of type char(2) containing hex numbers (e.g. 0A, FF,
...) and I cannot find correct function which could convert those hex
numbers to integers so I can perform futher calculations. I experimented
with HEX(), CAST(), CONVERT() but I wasn't succesfull.
Thanks in advance,
Dusan
Use conv:
mysql select conv('11', 16, 10);
++
| conv('11', 16, 10) |
++
| 17 |
++
1 row in set (0.01 sec)
mysql select conv('0a', 16, 10) + conv('a0', 16, 10);
+-+
| conv('0a', 16, 10) + conv('a0', 16, 10) |
+-+
| 170 |
+-+
See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
HTH,
Wolfram
Thanks a lot. This is the function I was looking for.
Dusan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Illegal mix of collations error
Hello,
could someone help me to explain and resolve this error? Or maybe it is
a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY (`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING (vnitrnicislo);
returns error: Illegal mix of collations (latin2_general_ci,IMPLICIT)
and (latin2_czech_cs,IMPLICIT) for operation '='
I don't know where latin2_general_ci collation comes from.
OS WinXP, MySQL 4.1.15-nt-log
Thanks in advance
Dusan Pavlica
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Illegal mix of collations error
flyerheaven - Barry Krein napsal(a):
Dušan Pavlica schrieb:
Barry napsal(a):
Dušan Pavlica schrieb:
Hello,
could someone help me to explain and resolve this error? Or maybe
it is a bug.
DROP TABLE IF EXISTS `test`.`karty`;
CREATE TABLE `test`.`karty` (
`ICO` char(12) collate latin2_czech_cs NOT NULL default '',
`CisloProvozu` char(6) collate latin2_czech_cs NOT NULL default '',
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default '',
`TypKarty` smallint(6) NOT NULL default '0',
`Vyhotoveni` smallint(6) NOT NULL default '0',
`Jmeno` char(20) collate latin2_czech_cs NOT NULL default '',
`CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
PRIMARY KEY
(`ICO`,`CisloProvozu`,`CisPrac`,`TypKarty`,`Vyhotoveni`),
UNIQUE KEY `VnitrniCislo` (`VnitrniCislo`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
DROP TABLE IF EXISTS `test`.`karty_imp_tmp`;
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
`CisPrac` char(10) collate latin2_czech_cs NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs;
INSERT INTO test.karty
values('','01','01',10,1,'test',0,'R0','9001AB12D3E','');
INSERT INTO test.karty_imp_tmp
values('test','9001AB12D3E','01');
SELECT k.*, kt.* FROM karty k JOIN karty_imp_tmp kt USING
(vnitrnicislo);
returns error: Illegal mix of collations
(latin2_general_ci,IMPLICIT) and (latin2_czech_cs,IMPLICIT) for
operation '='
I don't know where latin2_general_ci collation comes from.
Well one table or the column has this collation.
Check your structures.
= `VnitrniCislo` char(15) character set latin2 default NULL,
shouldn it be
= `VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
Barry
I have seen it too and checked both structures in MySQL Table Editor
and both columns had exactly same definitions. And both tables have
same character set and collation
Dusan
Nah look at your structrue:
CisloProgramu` smallint(6) NOT NULL default '0',
`Embasing` char(7) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) character set latin2 default NULL,
`Stanice` char(3) collate latin2_czech_cs NOT NULL default '',
Here its just character set latin2
CREATE TABLE `test`.`karty_imp_tmp` (
`PracJmeno` char(30) collate latin2_czech_cs NOT NULL default '',
`VnitrniCislo` char(15) collate latin2_czech_cs default NULL,
And here is it collate latin2_czech_cs
That's causing the error.
Barry
You are right. If I create tables where both columns VnitrniCislo have
exactly same definitions, it doesn't throw the error.
Bug must be in the MySQL Table Editor which shows in column details
wrong collations
Thanks, Barry.
Dusan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I keep character_set_client value after MyODBC auto reconnect?
Hi, did you try SET CHARACTER SET utf8 as Initial Statement under Connection Options of your MyODBC DSN? HTH, Dusan - Original Message - From: 古雷 [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Monday, January 09, 2006 10:19 AM Subject: How can I keep character_set_client value after MyODBC auto reconnect? Hello: I found that MyODBC use mysql_ping to check connection and reconnect. After reconnect by using mysql_ping character_set_client, character_set_connection and character_set_results go back to latin1. But I need them to be utf8. What can I do with MyODBC? Regards, gu lei 祝 事业有成，家庭和睦，身体健康，一切吉祥 古雷 --- 中企动力科技集团 技术事业发展部___技术架构部 \__企业IP通讯部 电话：010 58022278-302 地址：北京亦庄经济技术开发区北工大软件园 （地盛北街1号）A区3号楼 邮编：100176 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete query problem
Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; This query works only for master records which have at least one detail record: DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10 Thanks in advance Dusan Pavlica
Re: Delete query problem
Tomas, thanks that was the solution I couldn't find out. I had to change only ON clause to master_tbl.ID=detail_tbl.Master_ID . Thanks also to Felix, I know I could use referential integrity but I knew there is a way how to do it without it and I was trying to find that way. Dusan. - Original Message - From: Tomas Rasek [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, December 21, 2005 11:12 AM Subject: Re: Delete query problem What about DELETE master_tbl,detail_tbl FROM master_tbl LEFT JOIN detail_tbl ON master_tbl.ID=detail_tbl.ID WHERE . T.R. Dušan Pavlica napsal(a): Hello, I have master-detail tables and I would like to delete in one statement master record and all detail records but not every master record has details. MySQL versions 4.1.10 and higher. Could someone help me, please, to create such a query? Example: CREATE TABLE `master_tbl` ( `ID` int(10) unsigned NOT NULL auto_increment, `Desc` varchar(45) NOT NULL default '', `Data` varchar(45) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; CREATE TABLE `detail_tbl` ( `Master_ID` int(10) unsigned NOT NULL default '0', `ID` int(10) unsigned NOT NULL default '0', `Desc` varchar(45) NOT NULL default '', PRIMARY KEY (`Master_ID`) ) ENGINE=InnoDB; This query works only for master records which have at least one detail record: DELETE master_tbl, detail_tbl FROM master_tbl, detail_tbl WHERE detail_tbl.Master_ID = master_tbl.ID AND master_tbl.ID = 10 Thanks in advance Dusan Pavlica -- 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: Help with query
Shawn, - Original Message - From: [EMAIL PROTECTED] To: Dušan Pavlica Cc: Michael Stassen ; list mysql Sent: Wednesday, October 12, 2005 4:45 PM Subject: Re: Help with query Dušan Pavlica [EMAIL PROTECTED] wrote on 10/12/2005 10:00:53 AM: Thanks, Michal, for your help. Your query works as I need. I tried to use same syntax as in MS Access, but results are for some reason different for this query. I'm working on application which should be able to connect to MySQL or to MSAccess (users' choice) and I didn't want to write querries for each DB system separately. Now I see that I will have to. Dusan - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dušan Pavlica [EMAIL PROTECTED] Cc: list mysql mysql@lists.mysql.com Sent: Wednesday, October 12, 2005 2:54 PM Subject: Re: Help with query Dušan Pavlica wrote: Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? snip Kind regards, Dusan Pavlica snip In any case, does this do what you want? SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM products_prices pp JOIN products p ON p.ID = pp.Product_ID LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1 WHERE StartDate Now() ORDER BY p.ID, pp.StartDate DESC; Michael I don't think you will need to change anything. MS Access should be able to work with Michael's query just fine. Just because the Query Builder in Access (I despise the SQL that comes out of that tool) always nests its JOINs doesn't mean that MS Access can't use un-nested joins. Give it a shot, you may be surprised. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Unfortunately, I'm afraid that MS Access is not able to work with un-nested joins. I tried Michael's solution also with different changes but Access keeps complaining about syntax error and on MSDN help and in one book about Access I have I saw that they use only syntax with nested joins. I don't know if it's matter but I use ADO for accessing MS Access database. Dusan
Help with query
Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? I have three tables: table products table products_codes where some products could have assigned another additional codes table products_prices I want to get all rows from product_prices listed with product Description and Code for particular CodeType And here is the create script, sample data and the query: CREATE TABLE `products` ( `ID` int(10) unsigned NOT NULL auto_increment, `Description` varchar(50) NOT NULL default '', `Units` varchar(10) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; INSERT INTO products VALUES(NULL, Product 1, lt); INSERT INTO products VALUES(NULL, Product 2, lt); INSERT INTO products VALUES(NULL, Product 3, lt); CREATE TABLE `products_codes` ( `Product_ID` int(10) unsigned NOT NULL default '0', `Code` varchar(50) NOT NULL default '', `CodeType` tinyint NOT NULL default '', PRIMARY KEY (`Product_ID`,`CodeType`) ) ENGINE=InnoDB; INSERT INTO products_codes VALUES(1, ABC, 1); INSERT INTO products_codes VALUES(2, XYZ, 1); CREATE TABLE `products_prices` ( `Product_ID` int(10) unsigned NOT NULL default '0', `StartDate` datetime NOT NULL default '-00-00 00:00:00', `Price` double NOT NULL default '0', PRIMARY KEY (`Product_ID`,`StartDate`) ) ENGINE=InnoDB; INSERT INTO products_prices VALUES(1, '2005090100', 20); INSERT INTO products_prices VALUES(1, '2005100100', 25); INSERT INTO products_prices VALUES(1, '2005110100', 30); INSERT INTO products_prices VALUES(2, '2005100100', 15); INSERT INTO products_prices VALUES(3, '2005100100', 10); SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM products_prices pp INNER JOIN (products p LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1) ON p.ID = pp.Product_ID where StartDate Now() ORDER BY p.ID, pp.StartDate desc Here is the result of the query: Description,ID,Product_ID,Code,StartDate Product 1,1,3,NULL,2005-10-01 00:00:00 Product 1,1,1,ABC,2005-10-01 00:00:00 Product 1,1,2,NULL,2005-10-01 00:00:00 Product 1,1,1,ABC,2005-09-01 00:00:00 Product 2,2,2,XYZ,2005-10-01 00:00:00 Product 2,2,3,NULL,2005-10-01 00:00:00 Product 2,2,1,NULL,2005-10-01 00:00:00 Product 2,2,1,NULL,2005-09-01 00:00:00 Product 3,3,2,NULL,2005-10-01 00:00:00 Product 3,3,3,NULL,2005-10-01 00:00:00 Product 3,3,1,NULL,2005-10-01 00:00:00 Product 3,3,1,NULL,2005-09-01 00:00:00 I don't know why this query returns also rows where p.ID != pp.ProductID And another thing. If I remove from selected columns pp.Product_ID query returns error: Column 'Product_ID' in field list is ambiguous (ErrorNr. 1052). Why?? MySQL 4.1.14, WinXP Thanks a lot in advance for any help Kind regards, Dusan Pavlica
Re: Help with query
Thanks, Michal, for your help. Your query works as I need. I tried to use same syntax as in MS Access, but results are for some reason different for this query. I'm working on application which should be able to connect to MySQL or to MSAccess (users' choice) and I didn't want to write querries for each DB system separately. Now I see that I will have to. Dusan - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Dušan Pavlica [EMAIL PROTECTED] Cc: list mysql mysql@lists.mysql.com Sent: Wednesday, October 12, 2005 2:54 PM Subject: Re: Help with query Dušan Pavlica wrote: Hello, could someone help me please to construct correct query or tell me what I'm doing wrong? I have three tables: table products table products_codes where some products could have assigned another additional codes table products_prices I want to get all rows from product_prices listed with product Description and Code for particular CodeType And here is the create script, sample data and the query: CREATE TABLE `products` ( `ID` int(10) unsigned NOT NULL auto_increment, `Description` varchar(50) NOT NULL default '', `Units` varchar(10) NOT NULL default '', PRIMARY KEY (`ID`) ) ENGINE=InnoDB; INSERT INTO products VALUES(NULL, Product 1, lt); INSERT INTO products VALUES(NULL, Product 2, lt); INSERT INTO products VALUES(NULL, Product 3, lt); CREATE TABLE `products_codes` ( `Product_ID` int(10) unsigned NOT NULL default '0', `Code` varchar(50) NOT NULL default '', `CodeType` tinyint NOT NULL default '', PRIMARY KEY (`Product_ID`,`CodeType`) ) ENGINE=InnoDB; INSERT INTO products_codes VALUES(1, ABC, 1); INSERT INTO products_codes VALUES(2, XYZ, 1); CREATE TABLE `products_prices` ( `Product_ID` int(10) unsigned NOT NULL default '0', `StartDate` datetime NOT NULL default '-00-00 00:00:00', `Price` double NOT NULL default '0', PRIMARY KEY (`Product_ID`,`StartDate`) ) ENGINE=InnoDB; INSERT INTO products_prices VALUES(1, '2005090100', 20); INSERT INTO products_prices VALUES(1, '2005100100', 25); INSERT INTO products_prices VALUES(1, '2005110100', 30); INSERT INTO products_prices VALUES(2, '2005100100', 15); INSERT INTO products_prices VALUES(3, '2005100100', 10); SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM products_prices pp INNER JOIN (products p LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1) ON p.ID = pp.Product_ID where StartDate Now() ORDER BY p.ID, pp.StartDate desc Here is the result of the query: Description,ID,Product_ID,Code,StartDate Product 1,1,3,NULL,2005-10-01 00:00:00 Product 1,1,1,ABC,2005-10-01 00:00:00 Product 1,1,2,NULL,2005-10-01 00:00:00 Product 1,1,1,ABC,2005-09-01 00:00:00 Product 2,2,2,XYZ,2005-10-01 00:00:00 Product 2,2,3,NULL,2005-10-01 00:00:00 Product 2,2,1,NULL,2005-10-01 00:00:00 Product 2,2,1,NULL,2005-09-01 00:00:00 Product 3,3,2,NULL,2005-10-01 00:00:00 Product 3,3,3,NULL,2005-10-01 00:00:00 Product 3,3,1,NULL,2005-10-01 00:00:00 Product 3,3,1,NULL,2005-09-01 00:00:00 I don't know why this query returns also rows where p.ID != pp.ProductID And another thing. If I remove from selected columns pp.Product_ID query returns error: Column 'Product_ID' in field list is ambiguous (ErrorNr. 1052). Why?? MySQL 4.1.14, WinXP Thanks a lot in advance for any help Kind regards, Dusan Pavlica I haven't really tried to figure out what mysql is doing with your query, but perhaps these lines from the manual http://dev.mysql.com/doc/mysql/en/join.html are relevant: In versions of MySQL prior to 5.0.1, parentheses in table_references were just omitted and all join operations were grouped to the left. In general, parentheses can be ignored in join expressions containing only inner join operations. As of 5.0.1, nested joins are allowed (see Section 7.2.10, “How MySQL Optimizes Nested Joins”). In any case, does this do what you want? SELECT p.Description, p.ID, pp.Product_ID, pc.Code, pp.StartDate FROM products_prices pp JOIN products p ON p.ID = pp.Product_ID LEFT JOIN products_codes pc ON p.ID = pc.Product_ID AND pc.CodeType = 1 WHERE StartDate Now() ORDER BY p.ID, pp.StartDate DESC; Michael -- 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: Problem with datetime values and MyODBC driver
Thanks Dwi for the tip, but unfortunately this solution is possible only in applications which connect only to MySQL and I am already using it in those applications but now I need to connect to MySQL or to MS Access (or to other DB engine) with one query using only different connection string (and different ODBC drivers). And thus I must set query parameters to their correct data types and ODBC driver should do the conversions. And that is what is not working. I will probably fill a bug report and will see. Dusan - Original Message - From: Dwi Putra L To: Dušan Pavlica Sent: Friday, August 05, 2005 6:34 AM Subject: Re: Problem with datetime values and MyODBC driver Dušan Pavlica wrote: Hello, I'm working in C++ Builder 6.0 and I'm trying to create application which could connect through ADO components to different databases using ODBC drivers. If I create TADOQuery object which has datetime parametr and I fill this parametr with valid datetime value then I can see in MYSQL's query log that only date portion is used. But if I use same query object to connect to MS Access then data in MS Access are OK. Win XP, MyODBC 3.51.11, MySQL 4.1.9 Does anybody have any idea how to solve this problem? Thanks in advance Dusan Pavlica I use delphi 7 and I have same problem with you MySQL save and search datetime data as string. My solution is to change datetime data into string with format : 2005-08-05 (year-month-day) -- var str_year, str_month, str_day, str_date : string; year, month, day : word; DecodeDate(Now,year,month,day); str_year := inttostr(year); str_month := inttostr(month); if Length(str_month) 2 then str_month := '0' + str_month; str_day := inttostr(day); if Length(str_day) 2 then str_day := '0' + str_day; str_date := str_year + '-' + str_month + '-' + str_day; --- The same thing when I want to conduct a query, which using date as parameters. I change certain date into string. _dwi.
Problem with datetime values and MyODBC driver
Hello, I'm working in C++ Builder 6.0 and I'm trying to create application which could connect through ADO components to different databases using ODBC drivers. If I create TADOQuery object which has datetime parametr and I fill this parametr with valid datetime value then I can see in MYSQL's query log that only date portion is used. But if I use same query object to connect to MS Access then data in MS Access are OK. Win XP, MyODBC 3.51.11, MySQL 4.1.9 Does anybody have any idea how to solve this problem? Thanks in advance Dusan Pavlica
Re: [PARTIALLY SOLVED] Illegal mix of collations for operation IN
I did more investigation and found out that
1) with MySQL 4.1.10-nt both queries executed from Query Browser work fine
(no error) - I haven't chance to test it with my application through ODBC
connection
2) when I set initial statement in MyODBC SET CHARACTER SET 'cp1250'
instead of SET NAMES 'cp1250' for connection to MySQL 4.1.9-nt-log queries
from my application work fine and that is what I need
mysql show create table files \G
*** 1. row ***
Table: files
Create Table: CREATE TABLE `files` (
`FileName` char(100) collate latin2_czech_cs NOT NULL default '',
`CommandType` enum('NONE','ON_SUCCESS','ON_NOTRANSMIT') collate
latin2_czech_cs NOT NULL default 'NONE',
`Command` char(128) collate latin2_czech_cs default NULL,
PRIMARY KEY (`FileName`,`CommandType`)
) ENGINE=MyISAM DEFAULT CHARSET=latin2 COLLATE=latin2_czech_cs
1 row in set (0.01 sec)
mysql show variables like '%char%';
+--+-+
| Variable_name| Value
|
+--+-+
| character_set_client | cp1250
|
| character_set_connection | latin2
|
| character_set_database | latin2
|
| character_set_results| cp1250
|
| character_set_server | latin2
|
| character_set_system | utf8
|
| character_sets_dir | D:\Program Files\MySQL\MySQL Server
4.1\share\charsets/ |
+--+-+
7 rows in set (0.00 sec)
mysql show variables like '%col%';
+--+---+
| Variable_name| Value |
+--+---+
| collation_connection | cp1250_general_ci |
| collation_database | latin2_czech_cs |
| collation_server | latin2_general_ci |
| protocol_version | 10|
+--+---+
4 rows in set (0.00 sec)
I am still wondering why
SELECT * FROM files WHERE filename = 'file1.txt' OR filename = 'file2.txt'
was working and
SELECT * FROM files WHERE filename IN('file1.txt', 'file2.txt')
wasn't
Dusan
- Original Message -
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, June 02, 2005 2:13 PM
Subject: Re: Illegal mix of collations for operation IN
Hello.
Usually debugging of such kind of problems starts with examination of
the output of:
show variables like '%char%';
show variables like '%colla%';
Send the output of:
show create table your_table;
Hello,
I have problem which I don't understand.
if I send command (from Query Browser or from C++ Builder application)
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN'
I thought that IN is somehow by optimizer translated to ORs
Could someone explain me why first query is OK and second not? Please.
I'm using WinXP SP2 and MySQL 4.1.9-nt-log
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they
didn't helped me
much.
Thanks in advance
Dusan Pavlica
Du$an Pavlica [EMAIL PROTECTED] wrote:
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Illegal mix of collations for operation IN
Hello,
I have problem which I don't understand.
if I send command (from Query Browser or from C++ Builder application)
SELECT FileName FROM Files WHERE FileName = 'FILE1.TXT' OR FileName =
'FILE2.TXT'
query executes without any problem but command
SELECT FileName FROM Files WHERE FileName IN ('FILE1.TXT', 'FILE2.TXT')
throws error illegal mix of collations for operation 'IN'
I thought that IN is somehow by optimizer translated to ORs
Could someone explain me why first query is OK and second not? Please.
I'm using WinXP SP2 and MySQL 4.1.9-nt-log
databases and tables use CHARSET=latin2 COLLATE=latin2_czech_cs
I already read some articles about the topic in MySQL forum but they didn't
helped me much.
Thanks in advance
Dusan Pavlica
