BETWEEN, IN, , .... not using index with floats
Hi, im working with google maps and im and trying to do this, but i cant
make a good query of it.
I want to select elements between a given latitude and longitude from this
table:
*CREATE TABLE `images` (*
*`id_img` bigint(20) unsigned NOT NULL auto_increment,**
**`filename` char(50) NOT NULL,**
**`extension` enum('jpg','jpeg','gif','png') NOT NULL,**
**`lat` float(10,6) NOT NULL,**
**`lng` float(10,6) NOT NULL,*
*PRIMARY KEY (`id_img`),**
**KEY `lat` (`lat`,`lng`)
**) ENGINE=InnoDB DEFAULT CHARSET=utf8*
**
**
**
im trying with this query and some similars but all of them scans all the
table, and i dont know why
SELECT *
FROM `images`
WHERE lat BETWEEN 29.993002 AND 49.410973
AND lng BETWEEN -40.209960 AND 32.871093
id select_type tabletypepossible_keys key
key_len ref rows Extra
1SIMPLEimagesALL lat NULL
NULL NULL 108 Using where
thanks in advance
Re: fetch floats/doubles in native binary representation
Andras Pal wrote: Hi, I've a large table with lots of floats (or doubles) that i want to query using C API. By default, in C, the rows are returned as a type of char **, therefore floating numbers are returned as ascii strings. Is it possible to fetch somehow directly these numbers in their original binary representation? I know it is architecture-dependent, but if we use localhost, it won't cause any problem if the result is casted directy to (float) and/or (double). For our problem, it would be much more efficent to do the queries this way than the server converts the number to ascii, the client converts the number againt to binary (on the same machine), and we do what we want to do after it (which is a simple multiplication, not as time consuming as two conversions...). Hi Andras! Why not have the MySQL server do the computation, if it is a simple multiplication? Can you post the code you are using so we might suggest an alternative solution? Cheers, -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
fetch floats/doubles in native binary representation
Hi, I've a large table with lots of floats (or doubles) that i want to query using C API. By default, in C, the rows are returned as a type of char **, therefore floating numbers are returned as ascii strings. Is it possible to fetch somehow directly these numbers in their original binary representation? I know it is architecture-dependent, but if we use localhost, it won't cause any problem if the result is casted directy to (float) and/or (double). For our problem, it would be much more efficent to do the queries this way than the server converts the number to ascii, the client converts the number againt to binary (on the same machine), and we do what we want to do after it (which is a simple multiplication, not as time consuming as two conversions...). Andras -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rounding floats
Kenneth Hylton wrote something about SQL or QUERYs: I NEVER use float or double to store values like you appear to be doing. I always used DECIMAL so that they are stored as strings and you do not have problems associated with what you see here. I then have complete control over what is stored and the precision associated with it. FWIW, all of our financial database software stores values in either pennies or tenths of a cent, not dollars, to avoid decimals altogether. -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rounding floats
From: Michael T. Babcock [EMAIL PROTECTED] Kenneth Hylton wrote something about SQL or QUERYs: I NEVER use float or double to store values like you appear to be doing... FWIW, all of our financial database software stores values in either pennies or tenths of a cent, not dollars, to avoid decimals altogether. FLOAT + money = lawsuit :-) -- SQL SQL SQL SQL SQL SQL SQL SQL : Jan Steinman -- nature Transography(TM): http://www.Bytesmiths.com : Bytesmiths -- artists' services: http://www.Bytesmiths.com/Services - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rounding floats
Oh, for BCD floating point. My first computer with a language (SWTP 6800) had BCD math. It had something like 6 byte mantissa and a 1 byte exponent. That would give 11 digits with e +-99 Maybe we should have BCD data types. Jan Steinman wrote: From: Michael T. Babcock [EMAIL PROTECTED] Kenneth Hylton wrote something about SQL or QUERYs: I NEVER use float or double to store values like you appear to be doing... FWIW, all of our financial database software stores values in either pennies or tenths of a cent, not dollars, to avoid decimals altogether. FLOAT + money = lawsuit :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Rounding floats
Another alternative for storage of currency values is to use the DECIMAL type, which allows you to explicitly specify scale and precision. You're still constrained to a maximum precision that matches that of DOUBLE, and you get the inefficiency of storing values as strings but you don't have to worry about rounding errors. (unless you're recording truly enormous sums of money...) -JF -Original Message- From: gerald_clark [mailto:gerald_clark;suppliersystems.com] Sent: Wednesday, October 23, 2002 10:10 AM To: Jan Steinman Cc: [EMAIL PROTECTED] Subject: Re: Rounding floats Oh, for BCD floating point. My first computer with a language (SWTP 6800) had BCD math. It had something like 6 byte mantissa and a 1 byte exponent. That would give 11 digits with e +-99 Maybe we should have BCD data types. Jan Steinman wrote: From: Michael T. Babcock [EMAIL PROTECTED] Kenneth Hylton wrote something about SQL or QUERYs: I NEVER use float or double to store values like you appear to be doing... FWIW, all of our financial database software stores values in either pennies or tenths of a cent, not dollars, to avoid decimals altogether. FLOAT + money = lawsuit :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql arbitrarily rounds large floats
Description: Mysql arbitrarily rounds large floats How-To-Repeat: create table temp ( id int unsigned not null auto_increment primary key, amount float(8,2) ); insert into temp(amount) values(244409.29); insert into temp(amount) values(09.29); insert into temp(amount) values(777409.29); insert into temp(amount) values(999409.29); select * from temp; ++---+ | id | amount| ++---+ | 1 | 244509.30 | | 2 | 444509.28 | | 3 | 777509.31 | | 4 | 999509.31 | ++---+ 4 rows in set (0.00 sec) Fix: Not Known Submitter-Id: submitter ID Originator:Lonny Byrd Organization: Bake Crafters organization of PR author (multiple lines) MySQL support: none Synopsis: Mysql arbitrarily rounds large floats Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) AND mysql 3.23.53 Environment: machine, os, target, libraries (multiple lines) System: FreeBSD secure.bakecrafters.com 4.4-RELEASE FreeBSD 4.4-RELEASE #8: Mon Apr 1 09:00:21 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SERVERBERT i386 Also tried on System: Linux squiggle.kianta.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown running mysql Ver 11.18 Distrib 3.23.53, for pc-linux-gnu (i686) and got identical results Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='c++' CXXFLAGS='-O -pipe -f elide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1205382 Sep 18 2001 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jan 23 2002 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 572876 Sep 18 2001 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --w ithout-debug --without-readline --without-bench --with-mit-threads=no --with -lib wrap --with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/l ocal i386--freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql arbitrarily rounds large floats
You have a bigger problem than that. All your 409s changed to 509s. Lonny Byrd wrote: Description: Mysql arbitrarily rounds large floats How-To-Repeat: create table temp ( id int unsigned not null auto_increment primary key, amount float(8,2) ); insert into temp(amount) values(244409.29); insert into temp(amount) values(09.29); insert into temp(amount) values(777409.29); insert into temp(amount) values(999409.29); select * from temp; ++---+ | id | amount| ++---+ | 1 | 244509.30 | | 2 | 444509.28 | | 3 | 777509.31 | | 4 | 999509.31 | ++---+ 4 rows in set (0.00 sec) Fix: Not Known Submitter-Id: submitter ID Originator:Lonny Byrd Organization: Bake Crafters organization of PR author (multiple lines) MySQL support: none Synopsis: Mysql arbitrarily rounds large floats Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) AND mysql 3.23.53 Environment: machine, os, target, libraries (multiple lines) System: FreeBSD secure.bakecrafters.com 4.4-RELEASE FreeBSD 4.4-RELEASE #8: Mon Apr 1 09:00:21 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SERVERBERT i386 Also tried on System: Linux squiggle.kianta.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown running mysql Ver 11.18 Distrib 3.23.53, for pc-linux-gnu (i686) and got identical results Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='c++' CXXFLAGS='-O -pipe -f elide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1205382 Sep 18 2001 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jan 23 2002 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 572876 Sep 18 2001 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --w ithout-debug --without-readline --without-bench --with-mit-threads=no --with -lib wrap --with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/l ocal i386--freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql arbitrarily rounds large floats
I didn't see anyone else reply, so I will give you my thoughts. I thought one of the list gurus would give you a much better answer than I could so I waited. Assuming that with two decimal places, you are doing something monetary. But this applies whenever you want fixed decimal places, too. I NEVER use float or double to store values like you appear to be doing. I always used DECIMAL so that they are stored as strings and you do not have problems associated with what you see here. I then have complete control over what is stored and the precision associated with it. I then pull the decimals out and either work with them CAREFULLY as double items (or you could use variant, I suppose) or extended math functions to add, etc. strings of infinite precision. Then, convert back to strings and store as DECIMAL items in MySQL. That my be overkill, but, I don't get bit doing it that way. I usually use DECIMAL(16,6) to store my monetary stuff. Also note that although MySQL will allow you to do something like DECIMAL column name = DECIMAL column name + 0.15 if you are beyond the precision of a double, it will truncate the result incorrectly and oddly. I played with it when I first started using MySQL and it appears that internally MySQL does it's math as doubles when working with DECIMAL items. If you stay within the range of a double, it appears that using DECIMAL data types works OK. Also, I think that you have a misunderstanding of how you are defining your float. Doesn't the first 8 mean bytes to store the float in NOT decimal places? IF you defined it as a DECIMAL type of item, then (8,2) would yield a maximum of 99.99 like it appears that you want. I don't think that is true with float items. Check the web doc to make sure. Have Fun! Ken Hylton Programmer Analyst IV LEC Systems Programming Billing Concepts, Inc. 7411 John Smith Drive San Antonio, Texas 78229-4898 (210) 949-7261 -Original Message- From: Lonny Byrd [mailto:lonny;bakecrafters.com] Sent: Tuesday, October 22, 2002 1:18 PM To: [EMAIL PROTECTED] Subject: Mysql arbitrarily rounds large floats Description: Mysql arbitrarily rounds large floats How-To-Repeat: create table temp ( id int unsigned not null auto_increment primary key, amount float(8,2) ); insert into temp(amount) values(244409.29); insert into temp(amount) values(09.29); insert into temp(amount) values(777409.29); insert into temp(amount) values(999409.29); select * from temp; ++---+ | id | amount| ++---+ | 1 | 244509.30 | | 2 | 444509.28 | | 3 | 777509.31 | | 4 | 999509.31 | ++---+ 4 rows in set (0.00 sec) Fix: Not Known Submitter-Id: submitter ID Originator:Lonny Byrd Organization: Bake Crafters organization of PR author (multiple lines) MySQL support: none Synopsis: Mysql arbitrarily rounds large floats Severity: serious Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.42 (Source distribution) AND mysql 3.23.53 Environment: machine, os, target, libraries (multiple lines) System: FreeBSD secure.bakecrafters.com 4.4-RELEASE FreeBSD 4.4-RELEASE #8: Mon Apr 1 09:00:21 GMT 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/SERVERBERT i386 Also tried on System: Linux squiggle.kianta.com 2.4.9-31 #1 Tue Feb 26 07:11:02 EST 2002 i686 unknown running mysql Ver 11.18 Distrib 3.23.53, for pc-linux-gnu (i686) and got identical results Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/ bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='c++' CXXFLAGS='-O -pipe -f elide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1205382 Sep 18 2001 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Jan 23 2002 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 572876 Sep 18 2001 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --w ithout-debug --without-readline --without-bench --with-mit-threads=no --with -lib wrap --with-low-memory --enable-assembler --with-berkeley-db --prefix=/usr/l ocal i386--freebsd4.4 Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com
Re: mysql_real_query, insert floats as binary
* Dave Beck
[...]
charinsert_format[] = INSERT INTO %s VALUES (%hhd%hhd%hhd%hhd,
%hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd,%hhd%hhd%hhd%hhd);
The values should be quoted...
int build_real_insert(char *query, char *table, int frame, int
atom, float
x, float y, float z) {
unsigned char *fbp = (unsigned char *)frame;
unsigned char *abp = (unsigned char *)atom;
unsigned char *xbp = (unsigned char *)x;
unsigned char *ybp = (unsigned char *)y;
unsigned char *zbp = (unsigned char *)z;
return sprintf(query, insert_format, table, fbp[0], fbp[1],
fbp[2], fbp[3], abp[0], abp[1], abp[2], abp[3], xbp[0], xbp[1], xbp[2],
xbp[3], ybp[0], ybp[1], ybp[2], ybp[3], zbp[0], zbp[1], zbp[2], zbp[3]);
}
...and some bytes may represent an 'illegal' character, and thus must be
escaped by a backslash... this goes for the quote character you use ( or
'), the backslash character itself, and null bytes.
--
Roger
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql_real_query, insert floats as binary
...and some bytes may represent an 'illegal' character, and thus must be
escaped by a backslash... this goes for the quote character you use ( or
'), the backslash character itself, and null bytes.
Thanks, if I use mysql_real_query is this still the case?
--
David A. C. Beck
[EMAIL PROTECTED]
Valerie Daggett Laboratory
Biomolecular Structure and Design Program
Department of Medicinal Chemistry
University of Washington, Seattle
On Tue, 2 Jul 2002, Roger Baklund wrote:
* Dave Beck
[...]
char insert_format[] = INSERT INTO %s VALUES (%hhd%hhd%hhd%hhd,
%hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd,%hhd%hhd%hhd%hhd);
The values should be quoted...
int build_real_insert(char *query, char *table, int frame, int
atom, float
x, float y, float z) {
unsigned char *fbp = (unsigned char *)frame;
unsigned char *abp = (unsigned char *)atom;
unsigned char *xbp = (unsigned char *)x;
unsigned char *ybp = (unsigned char *)y;
unsigned char *zbp = (unsigned char *)z;
return sprintf(query, insert_format, table, fbp[0], fbp[1],
fbp[2], fbp[3], abp[0], abp[1], abp[2], abp[3], xbp[0], xbp[1], xbp[2],
xbp[3], ybp[0], ybp[1], ybp[2], ybp[3], zbp[0], zbp[1], zbp[2], zbp[3]);
}
...and some bytes may represent an 'illegal' character, and thus must be
escaped by a backslash... this goes for the quote character you use ( or
'), the backslash character itself, and null bytes.
--
Roger
sql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql_real_query, insert floats as binary
* Dave Beck ...and some bytes may represent an 'illegal' character, and thus must be escaped by a backslash... this goes for the quote character you use ( or '), the backslash character itself, and null bytes. Thanks, if I use mysql_real_query is this still the case? The doc's only mention null bytes (referred to as the '\0' character): URL: http://www.mysql.com/doc/n/o/node_686.html -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql_real_query, insert floats as binary
No takers? Any thoughts would be great.
--
David A. C. Beck
[EMAIL PROTECTED]
Valerie Daggett Laboratory
Biomolecular Structure and Design Program
Department of Medicinal Chemistry
University of Washington, Seattle
On Thu, 27 Jun 2002, Dave Beck wrote:
the question:
I am trying to use the C API to insert floating (4 byte) point data into a
mysql table. It is very desirable to put the data in as the binary
representation and not as a sprintf family converted string (to maintain
precision improve performance). My initial attempt is below - perhaps I
have misunderstood the usage of mysql_real_query? I should make two notes
on the following code: the host and source are the same machine so
endianism is (should) not be an issue and I am also trying to insert two
integers in the same manner. With the code as it is I get an error in
mysql statement.
the table description:
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| frame | int | | MUL | 0 | |
| atom | int | | MUL | 0 | |
| x | float | | | 0 | |
| y | float | | | 0 | |
| z | float | | | 0 | |
+---+-+--+-+-+---+
the code:
char insert_format[] = INSERT INTO %s VALUES (%hhd%hhd%hhd%hhd,
%hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd,%hhd%hhd%hhd%hhd);
int build_real_insert(char *query, char *table, int frame, int atom, float
x, float y, float z) {
unsigned char *fbp = (unsigned char *)frame;
unsigned char *abp = (unsigned char *)atom;
unsigned char *xbp = (unsigned char *)x;
unsigned char *ybp = (unsigned char *)y;
unsigned char *zbp = (unsigned char *)z;
return sprintf(query, insert_format, table, fbp[0], fbp[1],
fbp[2], fbp[3], abp[0], abp[1], abp[2], abp[3], xbp[0], xbp[1], xbp[2],
xbp[3], ybp[0], ybp[1], ybp[2], ybp[3], zbp[0], zbp[1], zbp[2], zbp[3]);
}
in the body:
query_len = build_real_insert(query,table_name,frame,i,x[i],y[i],z[i]);
if(mysql_real_query(mysql,query,query_len)) {
// error handling
Many thanks,
David
--
David A. C. Beck
[EMAIL PROTECTED]
Valerie Daggett Laboratory
Biomolecular Structure and Design Program
Department of Medicinal Chemistry
University of Washington, Seattle
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql_real_query, insert floats as binary
the question:
I am trying to use the C API to insert floating (4 byte) point data into a
mysql table. It is very desirable to put the data in as the binary
representation and not as a sprintf family converted string (to maintain
precision improve performance). My initial attempt is below - perhaps I
have misunderstood the usage of mysql_real_query? I should make two notes
on the following code: the host and source are the same machine so
endianism is (should) not be an issue and I am also trying to insert two
integers in the same manner. With the code as it is I get an error in
mysql statement.
the table description:
+---+-+--+-+-+---+
| Field | Type| Null | Key | Default | Extra |
+---+-+--+-+-+---+
| frame | int | | MUL | 0 | |
| atom | int | | MUL | 0 | |
| x | float | | | 0 | |
| y | float | | | 0 | |
| z | float | | | 0 | |
+---+-+--+-+-+---+
the code:
charinsert_format[] = INSERT INTO %s VALUES (%hhd%hhd%hhd%hhd,
%hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd, %hhd%hhd%hhd%hhd,%hhd%hhd%hhd%hhd);
int build_real_insert(char *query, char *table, int frame, int atom, float
x, float y, float z) {
unsigned char *fbp = (unsigned char *)frame;
unsigned char *abp = (unsigned char *)atom;
unsigned char *xbp = (unsigned char *)x;
unsigned char *ybp = (unsigned char *)y;
unsigned char *zbp = (unsigned char *)z;
return sprintf(query, insert_format, table, fbp[0], fbp[1],
fbp[2], fbp[3], abp[0], abp[1], abp[2], abp[3], xbp[0], xbp[1], xbp[2],
xbp[3], ybp[0], ybp[1], ybp[2], ybp[3], zbp[0], zbp[1], zbp[2], zbp[3]);
}
in the body:
query_len = build_real_insert(query,table_name,frame,i,x[i],y[i],z[i]);
if(mysql_real_query(mysql,query,query_len)) {
// error handling
Many thanks,
David
--
David A. C. Beck
[EMAIL PROTECTED]
Valerie Daggett Laboratory
Biomolecular Structure and Design Program
Department of Medicinal Chemistry
University of Washington, Seattle
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Floats
Hi List, I'm a little bit confused. I do the following: Create a table with 1 int and two floats. mysql create table testfloat(z int, ll float, ul float); Query OK, 0 rows affected (0.00 sec) inserting one row into the table mysql insert into testfloat values(1,-0.1,0.1); Query OK, 1 row affected (0.00 sec) check if values where inside mysql select * from testfloat; +--+--+--+ | z| ll | ul | +--+--+--+ |1 | -0.1 | 0.1 | +--+--+--+ 1 row in set (0.01 sec) and now I wants to take a look on special rows.. mysql select * from testfloat where ul=0.1; Empty set (0.00 sec) I didn't understand why I didn't get what I expect. Best regards Thorsten Guddack - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Floats
This is a standard 1st quarter computer science topic. You can't reliably test floating point numbers for equality. 0.1 has no exact floating point binary representation. Perhaps you should use the DECIMAL type which is stored internally as a string. Guddack Thorsten ICM MP SCM SCCM PI wrote: Hi List, I'm a little bit confused. I do the following: Create a table with 1 int and two floats. mysql create table testfloat(z int, ll float, ul float); Query OK, 0 rows affected (0.00 sec) inserting one row into the table mysql insert into testfloat values(1,-0.1,0.1); Query OK, 1 row affected (0.00 sec) check if values where inside mysql select * from testfloat; +--+--+--+ | z| ll | ul | +--+--+--+ |1 | -0.1 | 0.1 | +--+--+--+ 1 row in set (0.01 sec) and now I wants to take a look on special rows.. mysql select * from testfloat where ul=0.1; Empty set (0.00 sec) I didn't understand why I didn't get what I expect. Best regards Thorsten Guddack - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
