www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - Mysql query result access by field name

reply ipkwena <ipkwena gmail.com> writes:
I have started learning D and I am enjoying it so far.

How does one access the columns fields in a Mysql query results 
by the column name. Currently I have to use the method as shown 
in a couple of example by indexing array values (f being a struct 
variable):
		
Data f;
f.name = to!string(allrows[0][0]);
f.surname = to!string(allrows[0][1]);
f.title  = to!string(allrows[0][2]);

I am using the mysql-native package or DB connectivity.

Regards
May 20 2018
next sibling parent reply kdevel <kdevel vogtner.de> writes:
On Sunday, 20 May 2018 at 16:08:03 UTC, ipkwena wrote:

 How does one access the columns fields in a Mysql query results 
 by the column name.


[...]


 Data f;
 f.name = to!string(allrows[0][0]);
 f.surname = to!string(allrows[0][1]);
 f.title  = to!string(allrows[0][2]);

 I am using the mysql-native package or DB connectivity.


According to the source code 
https://github.com/mysql-d/mysql-native/blob/master/source/mysql/result.d it
should be possible to write the rowdata into the struct:

    Data f;
    allrows[0].toStruct (f);

I haven't checked this.
May 21 2018
parent reply Steven Schveighoffer <schveiguy yahoo.com> writes:
On 5/21/18 9:39 AM, kdevel wrote:

 On Sunday, 20 May 2018 at 16:08:03 UTC, ipkwena wrote:

 How does one access the columns fields in a Mysql query results by the 
 column name.


 
 [...]
 

 Data f;
 f.name = to!string(allrows[0][0]);
 f.surname = to!string(allrows[0][1]);
 f.title  = to!string(allrows[0][2]);

 I am using the mysql-native package or DB connectivity.


 
 According to the source code 
 https://github.com/mysql-d/mysql-native/blob/master/source/mysql/result.d it 
 should be possible to write the rowdata into the struct:
 
     Data f;
     allrows[0].toStruct (f);
 
 I haven't checked this.


This only works if your struct has exactly the same layout as the fields.

So if, for instance, your rows are selected "title", "name", "surname", 
but your data type orders them name, surname, title, you won't be happy 
with the result.

-Steve
May 21 2018
parent reply kdevel <kdevel vogtner.de> writes:
On Monday, 21 May 2018 at 14:17:23 UTC, Steven Schveighoffer 
wrote:

     Data f;
     allrows[0].toStruct (f);
 
 I haven't checked this.


 This only works if your struct has exactly the same layout as 
 the fields.

 So if, for instance, your rows are selected "title", "name", 
 "surname", but your data type orders them name, surname, title, 
 you won't be happy with the result.


Haven't seen this. Then there is no more field-safety than in the 
OP's "assembler" code.

In the other post you wrote

| 1. Use ResultRange instead of the Row interface. This provides
| a couple of ways to use column names, .asAA to get all the data
| in a nice AA format (they are still variants),

The AA format can than be used to fill the struct automatically 
(detecting missing and excess fields) like in this code:

    T toStructX(T) (string[string] a)
    {
       T t;
       bool[string] bookkeep;
       foreach (i, m; t.tupleof) {
          string key = T.tupleof[i].stringof;
          if (key !in a) {
             stderr.writefln ("missing key <%s>", key);
             continue;
          }
          t.tupleof[i] = a[key].to!(typeof (m));
          bookkeep[key] = true;
       }
       foreach (x, y; a)
          if (x !in bookkeep)
             stderr.writefln ("excess key-value pair <%s>:<%s>", 
x, y);
       return t;
    }
May 21 2018
parent Steven Schveighoffer <schveiguy yahoo.com> writes:
On 5/21/18 10:59 AM, kdevel wrote:

 On Monday, 21 May 2018 at 14:17:23 UTC, Steven Schveighoffer wrote:

     Data f;
     allrows[0].toStruct (f);

 I haven't checked this.


 This only works if your struct has exactly the same layout as the fields.

 So if, for instance, your rows are selected "title", "name", 
 "surname", but your data type orders them name, surname, title, you 
 won't be happy with the result.


 
 Haven't seen this. Then there is no more field-safety than in the OP's 
 "assembler" code.
 
 In the other post you wrote
 
 | 1. Use ResultRange instead of the Row interface. This provides
 | a couple of ways to use column names, .asAA to get all the data
 | in a nice AA format (they are still variants),
 
 The AA format can than be used to fill the struct automatically 
 (detecting missing and excess fields) like in this code:
 
     T toStructX(T) (string[string] a)
     {
        T t;
        bool[string] bookkeep;
        foreach (i, m; t.tupleof) {
           string key = T.tupleof[i].stringof;
           if (key !in a) {
              stderr.writefln ("missing key <%s>", key);
              continue;
           }
           t.tupleof[i] = a[key].to!(typeof (m));
           bookkeep[key] = true;
        }
        foreach (x, y; a)
           if (x !in bookkeep)
              stderr.writefln ("excess key-value pair <%s>:<%s>", x,
y);
        return t;
     }


Yes, this is very similar to what I do in my serialization library, 
except I don't use the AA, I keep a map of indexes based on the index of 
the field in the tuple to avoid allocation associated with an AA.

Note that your incoming AA is going to be a Variant[string].

-Steve
May 21 2018
prev sibling parent Steven Schveighoffer <schveiguy yahoo.com> writes:
On 5/20/18 12:08 PM, ipkwena wrote:

 I have started learning D and I am enjoying it so far.
 
 How does one access the columns fields in a Mysql query results by the 
 column name. Currently I have to use the method as shown in a couple of 
 example by indexing array values (f being a struct variable):
 
 Data f;
 f.name = to!string(allrows[0][0]);
 f.surname = to!string(allrows[0][1]);
 f.title  = to!string(allrows[0][2]);
 
 I am using the mysql-native package or DB connectivity.


This is one of the weak spots of mysql-native -- the Row object has no 
knowledge of the column names, so you have to "know" the order of the 
columns you got from the server.

So what you can do is:

1. Use ResultRange instead of the Row interface. This provides a couple 
of ways to use column names, .asAA to get all the data in a nice AA 
format (they are still variants), .colNames to get the list of column 
names ordered by the row fields, or .colNameIndicies which gives you an 
AA of names to indices. Note that the AA generating versions will 
allocate a lot of throw-away data.

2. Write a complicated serialization library like I did :) In this case, 
I'm turning my ResultRange from a range of Rows to a range of the data 
type I want, all serialized by column name instead of index. 
Unfortunately, this is not open source so I can't share it.

At some point, I want to fix this part of mysql-native. I'm a bit 
annoyed that we have to do Variants instead of writing directly to the 
data type we are going to use anyway.

-Steve
May 21 2018