A question about Sets

ashlar64 · Post by **ashlar64** » Tue 18 Jul 2006 18:17

Hello!

I have a Field that is a Set. How can I get a listing of all text choices that are allowable in that Set?

ashlar64 · Post by **ashlar64** » Tue 18 Jul 2006 18:19

Ohh and does anyone know of any components that are designed for Sets?

Antaeus · Post by **Antaeus** » Wed 19 Jul 2006 09:54

> How can I get a listing of all text choices that are allowable in that Set?
You can get all items of SET fields by executing SHOW COLUMNS FROM command and manually analysing received result set.
But we recommend you to use an extra table as a dictionary instead of SET field.

Guest · Post by **Guest** » Wed 19 Jul 2006 18:55

Thanks for the response Antaeus.

One other question about sets.

How do I figure out what items are currently in a set with a TMyQuery object and also how do I manipulate those items?

Antaeus · Post by **Antaeus** » Thu 20 Jul 2006 12:12

MySQL Server does not give any information to client whether selected field is a SET field. To find out if there is a SET field in result record set, additional request to server is required.
You can work with SET fields like with string fields or like with integer fields. MySQL Server applies bit encoding to values of SET fields if you use following query

Code: Select all

    SELECT c_set+0 as c_set_as_integer, FROM tb_with_set

Following INSERT statements are allowed

Code: Select all

    INSERT INTO tb_with_set (c_set) VALUES('Val1,Val2');
    INSERT INTO tb_with_set (c_set) VALUES(3);

The result of these statements are the same if c_set field are defined as SET('Val1','Val2'...)

ashlar64 · Post by **ashlar64** » Thu 20 Jul 2006 19:39

Hmm I am almost getting it.

I am wondering how I can do this.
If my TMyQuery object is pointing to lets say id_num 5 and the field name for the set is status_set....how can I pass the integer value from this location to my program?

---Dave

Antaeus · Post by **Antaeus** » Fri 21 Jul 2006 14:07

You can work with c_set_as_integer field (see SELECT statement above) like with usual integer field. MyDAC does not know if this is field of SET type.

Guest · Post by **Guest** » Fri 21 Jul 2006 19:15

I did try doing this....it works fine except for the fact it only passes the first 255 characters of the set string to s1.

AnsiString s1 = MyQuery_Site_ID->FieldByName("Site_Status")->AsString;

Unless there is another way to get more than 255 characters passed to s1 I will need to get the set as a int. (I assume a __int64 since a set can have 64 bits?)

So if I were to use a TMyCommand object to get the int value out of the set...what would the command look like? I am also curious as how to pass parameter out of a SQL (as opposed to in to). I have tried many different ways and am having no luck at all.

Guest · Post by **Guest** » Fri 21 Jul 2006 19:36

This is my code so far. My SQL statement is probably wrong....but I have tried many different variations....

TParam *newParam = MyCommand_General_Use->Params->CreateParam(ftInteger, "int_site_status", ptOutput);

MyCommand_General_Use->SQL->Text = "SELECT Site_Status+0 AS int_site_status, From Site_ID WHERE ID = 168";

MyCommand_General_Use->Execute(1);

int i = MyCommand_General_Use->ParamByName("int_site_status")->AsInteger;

Antaeus · Post by **Antaeus** » Mon 24 Jul 2006 10:13

> it works fine except for the fact it only passes the first
> 255 characters of the set string to s1.
We couldn't reproduce the problem. Please send us (evgeniyD*crlab*com) a complete small sample to demonstrate it, including script to create and fill table.
Also supply us following information
- Exact version IDE
- Exact version of MyDAC. You can see it in About sheet of TMyConnection Editor
- Exact version of MySQL server and MySQL client. You can see it in Info sheet of TMyConnection Editor

> I am also curious as how to pass parameter out of a SQL
MySQL Server data transferring protocol does not support output parameters. Please refer to this topic: http://crlab.com/forums/viewtopic.php?t=2790.

> This is my code so far. My SQL statement is probably wrong....
> but I have tried many different variations....
Your query seems to be correct. But you should use TMyQuery component and set this query to its SQL.Text property. After opening TMyQuery you can access result record set using FieldByName method of TMyQuery.
Please read Delphi Help about usage of TDataSet class.

Guest · Post by **Guest** » Thu 27 Jul 2006 12:47

Hello Antaeus,

I believed I discovered the problem with the code above only returning a string of 255 characters and not the whole string. It appears that MySql 4 will only return 255 characters and that MySql 5 will return strings longer than that length.

Many thanks for your Help Antaeus.

---Dave