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
#include
#include

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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
> #include
> #include
>
> 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/mysql?unsub=junk@ipercolate.net
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: C api - mysql_list_fields

mysql@aubit.com (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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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 c=
ode) the=20
> length of the string in the schema for a given table.
>=20
> So - for example :=20
>=20
>=20
> =09create table a (
> blah char(20)
> =09)
>=20
>=20
> I want to return '20', but I'm getting '60' when I use mysql_list_f=
ields..
> (Always seems to be 3x longer that I'm expecting)...
>=20
> Am I missing something ? (or should I just divide by 3!!)

You are missing the distinction between "character" and byte, which i=
s
brought to you by the ISO character sets which go far beyond ASCII.

The moment you allow international characters (US-ASCII + German Umla=
uts
+ French accented vowels + Spanish cedilla + ... + Chinese + Korean +
..) in your data, storing one character may need more than one byt=
e.

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

--=20
Joerg Bruehe, MySQL Build Team, joerg@mysql.com (+49 30) 417 01 4=
87
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstett=
en
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boem=
er
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: C api - mysql_list_fields

Excellent - this seems to be the issue - the show create table shows :=20

mysql> show create table a\g
+-------+--------------------------------------------------- ---------------=
=2D---------------------+
| Table | Create=20
Table =
|
+-------+--------------------------------------------------- ---------------=
=2D---------------------+
| a | CREATE TABLE `a` (
`blah` char(20) default NULL
) ENGINE=3DMyISAM DEFAULT CHARSET=3Dutf8 |
+-------+-----------


So - its utf8 (which I understand enough about to understand why its doing=
=20
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)=20
that I need to use to divide by to get back to the character width specifie=
d=20
in the CREATE TABLE ?=20








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, joerg@mysql.com (+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/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

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/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: C api - mysql_list_fields

Mike, all,


Mike Aubury wrote:
> [[...]]
>=20
> So - the next question is...
> Is there anyway in code I can find the 'fiddle' factor (1,3,or now =
possibly 4)=20
> that I need to use to divide by to get back to the character width =
specified=20
> in the CREATE TABLE ?=20

In the "information_schema" database, which you can use to access sch=
ema
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 th=
e
server for this factor.

If you need to know the number of characters specified for some colum=
n,
you should access the "information_schema" database and get all those
details, including character set and collation.


HTH,
Jörg

--=20
Joerg Bruehe, MySQL Build Team, joerg@mysql.com (+49 30) 417 01 4=
87
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstett=
en
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boem=
er
Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB1610=
28


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg