query table based on ID or column value

[hbarrington]

I'd love to be able to query a table using an ID or some value. Any plans to add this? I've poked around the bare minimum amount and it looks like you're using FFI to wrap the c libraries for MDB (pretty straightforward). How hard would it be to add some simplistic queries? Nothing complicated just table.find(ID => '123')

I wouldn't mind putting in a couple hours if you just want to point me in a direction.

[afiler]

Hi Hunter,

Being able to do table.find('ID'=>'123') is pretty easy if you're not
concerned about using indexes. Using the built-in indexes would make it a
bit more complex. I just pushed a change adding that basic functionality if
you'd like to take a look:
afb2916

Andrew

On Mon, Apr 14, 2014 at 1:58 PM, Hunter Barrington <notifications@github.com

wrote:

I'd love to be able to query a table using an ID or some value. Any plans
to add this? I've poked around the bare minimum amount and it looks like
you're using FFI to wrap the c libraries for MDB (pretty straightforward).
How hard would it be to add some simplistic queries? Nothing complicated
just table.find(ID => '123')

I wouldn't mind putting in a couple hours if you just want to point me in
a direction.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1
.

[hbarrington]

Looks good except I can't test it :-P

Any support suggestions for Mac OS X? I've installed mdbtools and had success using https://github.com/concordia-publishing-house/mdb but I don't like the approach of using the command line tools and parsing results... and it seems pretty slow for my needs.

I like your approach I just can't get it to load. Which OS are you using? I'm down to do some benchmarking and testing and feature additions.

Thanks for the feature additions!

[hbarrington]

Hi Andrew,

I've gone ahead and packaged it as a gem for my rails project and dealt with some of the requirement/dependency issues. I think it actually had to do with the version or ruby being used. 1.8, 1.9 and 2.x each handle "require" a bit differently.

I've gone ahead and forked it and will create a pull request if you're interested.

I'm currently dealing with two bugs... one is on line 82 of mdb.rb and is
TypeError: no implicit conversion from nil to integer
It looks like it is related to the new feature.

The other is when trying to use it from the rails console or irb and I get
Encoding::CompatibilityError: inspected result must be ASCII only or use the default external encoding
whenever I try to do something like db.table('Account')
It should be noted I can still do db.table('Account').rows.next and still get a record back.

[hbarrington]

on a second look it looks like the issue comes from the fact that when I try to ask a row for a particular key like "AccountID" it doesn't know what to do with it because row inherits from Array rather than Hash. I'll take a look and see if there's an easy way to fix that

[afiler]

Hi, sorry, I was composing an email and forgot about it, but it sounds like
you're farther along anyway. Row needs to inherit from Array to give it all
its array-like behaviors, I just added the [] method so you can do
row['AccountId'] on whats otherwise an array. What problem are you having
exactly?

On Mon, Apr 21, 2014 at 7:10 PM, Hunter Barrington <notifications@github.com

wrote:

on a second look it looks like the issue comes from the fact that when I
try to ask a row for a particular key like "AccountID" it doesn't know what
to do with it because row inherits from Array rather than Hash. I'll take a
look and see if there's an easy way to fix that

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1#issuecomment-40997197
.

[hbarrington]

well row['AccountID'] doesn't work in an array... i.e. row[0] works just fine but you can't do row['AccountID'], you would need a hash for that no? Can I take the column names from the table and use them to populate the keys for the row?

[afiler]

That's exactly what def does, in the class Row. It's not just an
Array anymore, it's a Row object that has all the properties of an Array
but also responds to non-numeric indexes.

What happens when you try something like this? Do you get a Ruby error
message?

table.find('FirstName'=>'John', 'LastName'=>'Smith').each do |row|
puts row['EmployeeID']
end

On Mon, Apr 21, 2014 at 7:22 PM, Hunter Barrington <notifications@github.com

wrote:

well row['AccountID'] doesn't work in an array... i.e. row[0] works just
fine but you can't do row['AccountID'], you would need a hash for that no?
Can I take the column names from the table and use them to populate the
keys for the row?

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1#issuecomment-40997709
.

[hbarrington]

still get TypeError: no implicit conversion from nil to integer

[afiler]

Where is the error occurring? My guess without seeing the stack trace is
that it's not finding the column name. Do you see the column you're trying
if you print out table.column_names? How about table.column_indexes?

On Mon, Apr 21, 2014 at 8:32 PM, Hunter Barrington <notifications@github.com

wrote:

still get TypeError: no implicit conversion from nil to integer

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1#issuecomment-41000799
.

[hbarrington]

looks like it all might stem from the encoding issues because I tried iterating through column names and got

??F
??F
??F
??F
??F
??F
??F
??F
??F
??F
??F
??F
??F
??F

[hbarrington]

Have any thoughts on the encoding issues, Andrew? I've perused the code base briefly and haven't seen anything but maybe you might have some ideas.

In the gem "mdb" they use utf-8/Windows-1252 explicitly when reading from the command line - https://github.com/concordia-publishing-house/mdb/blob/master/lib/mdb/database.rb#L122

I did some initial speed tests but they're not much use without the encoding issue fixed

[afiler]

Are the field names in Russian/Japanese/etc? If they're in a Western
European character set, they should be at least partly legible (with spaces
between letters if it's UTF-16, with garbled accented characters in other
situations, etc). Otherwise it seems like there might be some problem with
the underlying libmdb C library. But I take it the mdbtools command-line
tools can read the field names OK?
On Apr 22, 2014 4:48 PM, "Hunter Barrington" notifications@github.com
wrote:

Have any thoughts on the encoding issues, Andrew? I've perused the code
base briefly and haven't seen anything but maybe you might have some ideas.

In the gem "mdb" they use utf-8/Windows-1252 explicitly when reading from
the command line -
https://github.com/concordia-publishing-house/mdb/blob/master/lib/mdb/database.rb#L122

I did some initial speed tests but they're not much use without the
encoding issue fixed

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/1#issuecomment-41109895
.

[hbarrington]

It uses UTF-8 according to the command line tools and the other mdb gem does work and reads correctly. It seems that there are some records that use Microsoft characters (in past experience dashes and double quotes often have issues) and so sometimes accessing Account rows works and sometimes it doesn't (indicative that it depends what's inside).

Here's a gist based on command line tool responses - https://gist.github.com/hbarrington/11199758