Re: C api - mysql_list_fields
Hi Mike, all, Mike Aubury wrote: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) You are missing the distinction between character and byte, which is brought to you by the ISO character sets which go far beyond ASCII. The moment you allow international characters (US-ASCII + German Umlauts + French accented vowels + Spanish cedilla + ... + Chinese + Korean + ...) in your data, storing one character may need more than one byte. The current encoding (versions 5.0 and 5.1) uses up to 3 bytes per character, that is the factor 3 you notice. With 6.0, a different encoding may be used, which uses up to 4 bytes per character. If you know you won't need arbitrary characters, you can use the charset (or character set) option in your create statements. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Basically - so I can display it in the same form as the orginal table.. Or - if you want the longer version I work with an Opensource project called 'Aubit4GL' (its a clone of Informix4GL - which allows you to write really nice screen based database oriented programs + reports), see http://sourceforge.net/projects/aubit4gl Anyway - part of that project is a tool called 'asql' (which is a replacement for the Informix 'isql' tool..), this tool is itself written using Aubit4GL. You can think of asql as an easy to use screen based (ncurses) equivilent of the mysql tool.. One of the options is a 'Table Info' - where you get a list of the columns and the datatypes etc. When you create a table with a char(20) - you dont want to see it appear as a char(60)! I have a workaround atm - but its really clunky - I do a separate select for each column in the form : SHOW COLUMNS FROM table LIKE 'column' for each column returned from mysql_list_fields BTW - I'll need some help with some of the other displays (indexes, status etc) - if anyone fancies lending a hand - we always welcome new volunteers! (The 'mysql' driver for Aubit4GL could probably do with some attention from someone who knows their way around) On Tuesday 07 October 2008 13:10:18 walter harms wrote: Mike Aubury schrieb: Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---+ + | Table | Create Table | +---+ + | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? why do you want to do that ? i would expect that mysql uses wchar_t for char() if utf8 is selected. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Mike, all, Mike Aubury wrote: [[...]] So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? In the information_schema database, which you can use to access schema information, MySQL differs between CHARACTER_MAXIMUM_LENGTH (the number of characters) and CHARACTER_OCTET_LENGTH (number of bytes). But this need not be the same factor for all columns: If you specify one with charset latin1, it is one octet (byte) per character. So I don't see any use in such a constant factor. And sorry, no, off-hand I don't know a way to programmatically ask the server for this factor. If you need to know the number of characters specified for some column, you should access the information_schema database and get all those details, including character set and collation. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
It works for me, I used your code:
Field =mycol Type=254 Length=20
so at least your code is fine, and the problem must be somewhere
else. I am using RH EL3.
cheers,
Doug
On 6 Oct 2008, at 19:52, Mike Aubury wrote:
I'm probably being a bit stupid - but I'm trying to determine (in
code) the
length of the string in the schema for a given table.
So - for example :
create table a (
blah char(20)
)
I want to return '20', but I'm getting '60' when I use
mysql_list_fields..
(Always seems to be 3x longer that I'm expecting)...
Am I missing something ? (or should I just divide by 3!!)
Heres an example :
#include stdio.h
#include stdlib.h
#include mysql.h
MYSQL conn;
int main(int argc,char *argv[]) {
// run with username port as arguments
char *tabname=a;
char *db=test1;
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
if (argc!=3) {
printf(usage : %s username password\n, argv[0]);exit(2);
}
u=argv[1]; p=argv[2];
mysql_init(conn);
if (!mysql_real_connect(conn, NULL,u,p,db,0,NULL,0) ) {
fprintf(stderr,
Failed to connect to database: Error: %s\n,
mysql_error(conn));
exit(2);
}
result = mysql_list_fields (conn, tabname, NULL);
field = mysql_fetch_field (result);
printf(Field =%s Type=%d Length=%d\n, field-name,
field-type, field-length);
}
Thanks in advance...
--
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 - mysql_list_fields
Mike Aubury schrieb: Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---++ | Table | Create Table | +---++ | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? why do you want to do that ? i would expect that mysql uses wchar_t for char() if utf8 is selected. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
Excellent - this seems to be the issue - the show create table shows : mysql show create table a\g +---++ | Table | Create Table | +---++ | a | CREATE TABLE `a` ( `blah` char(20) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 | +---+--- So - its utf8 (which I understand enough about to understand why its doing what its doing!) So - the next question is... Is there anyway in code I can find the 'fiddle' factor (1,3,or now possibly 4) that I need to use to divide by to get back to the character width specified in the CREATE TABLE ? On Tuesday 07 October 2008 12:07:28 Joerg Bruehe wrote: Hi Mike, all, Mike Aubury wrote: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) You are missing the distinction between character and byte, which is brought to you by the ISO character sets which go far beyond ASCII. The moment you allow international characters (US-ASCII + German Umlauts + French accented vowels + Spanish cedilla + ... + Chinese + Korean + ...) in your data, storing one character may need more than one byte. The current encoding (versions 5.0 and 5.1) uses up to 3 bytes per character, that is the factor 3 you notice. With 6.0, a different encoding may be used, which uses up to 4 bytes per character. If you know you won't need arbitrary characters, you can use the charset (or character set) option in your create statements. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] (+49 30) 417 01 487 Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C api - mysql_list_fields
[EMAIL PROTECTED] (Mike Aubury) writes: I'm probably being a bit stupid - but I'm trying to determine (in code) the length of the string in the schema for a given table. So - for example : create table a ( blah char(20) ) I want to return '20', but I'm getting '60' when I use mysql_list_fields.. (Always seems to be 3x longer that I'm expecting)... Am I missing something ? (or should I just divide by 3!!) Is the table or database using UTF-8? I think that if it is MySQL will allocate space for each character and is forced to allocate 3x20 bytes as a UTF-8 character can be up to 3-bytes in length. SHOW CREATE TABLE a\G should show if this is the case. Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C api - mysql_list_fields
I'm probably being a bit stupid - but I'm trying to determine (in code) the
length of the string in the schema for a given table.
So - for example :
create table a (
blah char(20)
)
I want to return '20', but I'm getting '60' when I use mysql_list_fields..
(Always seems to be 3x longer that I'm expecting)...
Am I missing something ? (or should I just divide by 3!!)
Heres an example :
#include stdio.h
#include stdlib.h
#include mysql.h
MYSQL conn;
int main(int argc,char *argv[]) {
// run with username port as arguments
char *tabname=a;
char *db=test1;
char *u;
char *p;
MYSQL_RES *result;
MYSQL_FIELD *field;
if (argc!=3) {
printf(usage : %s username password\n, argv[0]);exit(2);
}
u=argv[1]; p=argv[2];
mysql_init(conn);
if (!mysql_real_connect(conn, NULL,u,p,db,0,NULL,0) ) {
fprintf(stderr,
Failed to connect to database: Error: %s\n,
mysql_error(conn));
exit(2);
}
result = mysql_list_fields (conn, tabname, NULL);
field = mysql_fetch_field (result);
printf(Field =%s Type=%d Length=%d\n, field-name,
field-type, field-length);
}
Thanks in advance...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
