[question] How do I set Multi-Column seek condition?

[silverist]

The following SQL

UPDATE 
   position_history
SET
   position_num = 1,
   status = 0
WHERE 
         platform_code = 0
    and  magic_num = 0
    and  type = 0
    and  order_date = 0

corresponds to the following transactd client code or else?

table* tb = db->openTable(_T("position_history"));

tb->clearBuffer();
tb->setKeyNum(1);
tb->setFV(_T("platform_code"), 0);
tb->setFV(_T("magic_num"), 0);
tb->setFV(_T("type"), 0);
tb->setFV(_T("order_date"), 0);
tb->seek();

if (db->stat() == 0)
{
    tb->setFV(_T("position_num"), 1);
    tb->setFV(_T("status"), 0);
    tb->update();
}

tb->close();

[bizstation]

In the program with Transactd clients, the KEY of the table is very important, because Transactd always seeks the target record with KEY. (KEY can also be called INDEX.)

If two tables have same fields but have different KEYs, the Transactd program will be different (even if the same SQL can be used).

So we can not check the program without the table schema information.

(1) Can you tell me your table schema?

tb->setKeyNum(1);

This line means "Use KEY 1". What field(s) are specified as KEY 1?

(2) In your query, is the target a single record? Or multiple records? (In the other words, does the WHERE clause pick up a single record? Or some records?)

WHERE 
         platform_code = 0
    and  magic_num = 0
    and  type = 0
    and  order_date = 0

tb->update() method updates a single current record. If you want to update multiple records, use loop in program code.

[silverist]

(1) Can you tell me your table schema?

it's as follows.

exec codepage   932
table charset   22
field count     14

name    id
type    AutoIncrement
len     8
charset 1

name    platform_code
type    Integer
len     4
charset 1

name    magic_num
type    Integer
len     8
charset 1

name    order_index
type    Integer
len     8
charset 1

name    time_frame
type    Integer
len     4
charset 1

name    position_num
type    Integer
len     8
charset 1

name    type
type    Integer
len     4
charset 1

name    order_date
type    Integer
len     8
charset 1

name    order_rate
type    Float
len     8
charset 1

name    lots
type    Float
len     8
charset 1

name    ratio
type    Float
len     8
charset 1

name    status
type    Integer
len     4
charset 1

name    addtime
type    myTimeStamp
len     4
charset 1

name    updatetime
type    myTimeStamp
len     4
charset 1

What field(s) are specified as KEY 1?

it's (platform_code, magic_num, type, order_date).

(2) In your query, is the target a single record? Or multiple records?

The query's target is a single record.

[bizstation]

According to your information, it seems to me that your program is almost correct. Do you have any problems?

There is a small problem, you should check tb->stat after tb->seek or tb->update, not db->stat.

tb->seek();

if (tb->stat() == 0)
{
    // Seek succeeded.
    tb->setFV(_T("position_num"), 1);
    tb->setFV(_T("status"), 0);
    tb->update();
    if (tb->stat() == 0)
    {
        // Update succeeded.
    }
    else
    {
        // Update failed.
    }
}
else
{
    // Could not seek the target record.
}

If you have any problem after this fix, SHOW CREATE TABLE position_history is helpful for us.

[silverist]

According to your information, it seems to me that your program is almost correct. Do you have any problems?

I don't have a problem. I just want to confirm that my program is correct in advance.

I'll try to run my program after the fix, then I report it if I have any problem.