Dyalog Forums

Does the introduction of ibeam 2010 and 2011 change the balance between ADO.NET and SQAPL in favor of the former?

Paul, what do these i-beams do? (The online-doc doesn't mention them)

Michael,

See Morton's Technical Paper number 1 here: (just scroll down to the last comment) (or search the forum on DataTable)

http://www.dyalog.com/forum/viewtopic.php?f=18&t=92&p=277&hilit=datatable#p277

These Ibeams provide a fast and efficient way of getting data to and from .NET DataTables.

I just getting ready to investigate them.

Morten wrote this back in December, before these ibeams were released:

I seriously think you would be better off using ODBC and the SQAPL tool which is included with APL, it will return data to you as an array which you can put straight into our grid object: Reading the data with this tool will be so much easier than looping to extract all the pieces of data as you seem to have to do with the MySql objects.

in this discussion:

http://www.dyalog.com/forum/viewtopic.php?f=12&t=54&p=122&hilit=ado#p122

And I wonder is it still operative.


Paul

I see, thanks Paul - I had completely missed that one...

Is ADO.NET applicable only to Microsoft databases, or can it be used for MySQL and SQLite as well?

ADO.Net provides access to any ODBC data sources that you have on the machine, so you will be able to use it with MySQL (etc).

Can someone who knows ADO.Net well summarize the advantages of using ADO.Net over SQAPL (other than fashion statements about it being a "more modern technology")? SQAPL is about 15 years old now and I had expected people to stop using it ages ago, but so far I haven't found any good reason to abandon it in favour of ADO.NET unless you are really "manipulating data sets" and can take advantage of the objects built on top of ADO.NET for this purpose. The new I-Beams are aimed at making this more efficient - but very many APL applications just want to suck a large chunk of data out of an SQL database and perhaps insert another large chunk of data somewhere else, rather than noodle about with a field in a record here and another field in a record there, which is what most of the ADO.NET functionality seems to be aimed at?

If this view of ADO.NET is incorrect, I would very much appreciate an update!

I shall investigate ADO.Net and/or the new I-beams.

I've been using SQAPL "ever since" and it's worked well for me - one thing that I've really appreciated is being able to migrate my data from its starting point in Microsoft Access to MySQL and SQLite with only the slightest changes to my application code (mostly foibles of the various ODBC drivers to overcome).

As for "how I use it" - my profile is pretty much as Morten describes. I extract a chunk of data, fiddle with it in APL (because APL syntax is so much more rational than SQL) and maybe write the modified stuff back, again as a chunk. Also collect new data on a record-by-record basis to insert new rows. You could argue that I'm replicating a lot of stuff that SQL could do for me (like inner/outer joins), but I don't care - I'm happy with what I'm doing. It's also reasonable to say that my databases are relatively small (thousands of rows, not millions).

Perhaps, if ADO.Net is going to be "the way ahead" we need to make ourselves a bit of documentation?

I've made a small start investigating ADO.NET and SQLite, if someone has already done this stuff please advise and save me the brain overload...

First thing I did was start looking at Microsoft's .NET documentation - that might as well have been written in Venusian.

Next a Web search on ADO.Net and SQLite brought up what looks like quite a few results for "products" - I decided to look at System.Data.SQLite (if only because the price was right). Would appreciate feedback about whether this was a necessary or wise decision.

Finally got some code together with a useable ⎕USING and did the "create a database" example.

The other examples I see are in pseudo-Venusian, but I think I may be able to translate some of that. I haven't found sample code at the right level (for me) yet - what I've seen seems to take the view that simple stuff isn't worth showing examples of.

The plan is to persevere, but progress may be slow (this sort of thing doesn't exactly thrill me). Next on my agenda is connecting to some existing databases and seeing what I can find out about what I already know (structure and content).

But, as I say, if anyone has already been there with an APL machete and written it up...

Here are the first fruits of severe brain-warp, offered in the hope that it might help a few more of the similarly-afflicted to make sense of non-APL documentation.

Now that I have retrieved something from a known SQLite database/table I can move on and find out how to do it properly. I shall probably be ashamed of this within the week.

The filename in the ⎕USING line represents where SQLite.NET puts the 64-bit Windows 7 executable by default.

At first glance it leads me to suspect that migrating databases to different engines might not be quite so simple as with SQAPL (all those pesky names to find/replace).

foo;⎕USING;s;⎕IO;⎕ML;scommand;sr;fc;w;t;rc
⍝ Experimenting with ADO.NET and SQLite
⎕IO ⎕ML←0 3
⎕USING←'System.Data.SQLite,c:\program files (x86)\SQLite.NET\bin\x64\System.Data.SQLite.dll'
w←'d:\dick\temp\test.db'
t←'this'
s←⎕NEW SQLiteConnection''
s.ConnectionString←⊂'Data Source=',w
s.Open
scommand←s.⎕NEW SQLiteCommand(('Select * from ',t)s)
sr←scommand.ExecuteReader''
fc←sr.FieldCount
⎕←'There are ',(⍕fc),' fields in the table'
⎕←'Field names ←→ ',∊' ',¨sr.GetName¨⍳fc
⎕←'Field types ←→ ',∊' ',¨sr.GetDataTypeName¨⍳fc
rc←1
:While rc
⎕←sr.GetValue¨⍳fc
rc←sr.Read
:EndWhile
s.Close

I've followed through on my investigation of SQLite and .Net, anyone still interested can read it at http://88.97.16.226/apl/APL/Papers/SQLi ... 20ADO.html

I think my generally pig-headed attitude means that I'll try migrating a couple of "real" database applications to use it.