brian larsen

Hello, anyone have any experience interfacing IDL to a local mysql
server? The idl help seems nearly incoherent when it comes to this.

The situation is that I have a large (~15M records) database that I
would like to play with the data in idl and using the power of the
database I can do an appropriate select of the data before I read it
into idl. I would have to imagine this is possible and am looking for
any examples folks may have. I know how to do it with the idl-java
bridge and idl-c interface but being able to avoid that would be a huge
advantage (in programmer time).

Cheers and thanks,

Brian


--


---------------------------------------------------------------------------
Brian A. Larsen |

| When you are in it up

| to your ears,
Dept. of Physics | keep
your mouth shut.
Space Science and Engineering Lab (SSEL)|
Montana State University - Bozeman |
Bozeman, MT 59717 |

| ---------------------------------
---------------------------------------------------------------------------
HOMEPAGE: http://solar.physics.montana.edu/larsen
Maia: http://www.ssel.montana.edu/maia
MEROPE: http://www.ssel.montana.edu/merope

"I'm one of the most durable and fervent advocates of space
exploration,
but my take is that we could do it robotically at far less cost and far

greater quantity and quality of results," - Dr. James Van Allen

rick towler

There has been quite a few posts regarding IDL<->db connectivity.
Search the group.

Of course there is Dataminer. There is supposedly a package called
Datajiver that was recently released by a group from CNRS that provides
this interface too. An individual posted to the group regarding it, but
hasn't responded to my email queries. A google search digs up a
powerpoint presentation but that's it. A search of the CNRS website
turns up empty.

One other option if you are on windows *may* be using the ADODB COM
object. ITT and I seem to have differing opinions on this but I feel
that IDL's COM interface is a bit broken and incomplete. But, if you
can live with executing queries that return sets of data of a single
type (say all float or all char) this may be a winning solution. I have
taken this as far as I can with Oracle but I believe the Oracle driver
returns arrays of arrays as type VT_VARIANT (a vector of vectors) and
IDLcomIDispatch can't handle this, returning only the first element.
I'm guessing, but the ODBC driver returns a 2d array of VT_VARIANT where
IDL determines the type from the first element of the array. This is
why mixed type queries don't work. I have a good bit of code that you
can use to experiment with this if needed. It would be pretty trivial
to wrap all of this up into an object and performance is actually quite
good (Based on my limited experience with this in IDL and a lot of
experience with my ADODB implementation in MATLAB.) I'm just waiting
for IDLcomIDispatch to be completed before I do the work myself.

But like I said, search the group.

-Rick

I don't have idl-Mysql experience (yet), but if you want some specific
examples you might also look at Marc Buie's codes (see link below). I
know he does idl-mysql all the time.

http://www.lowell.edu/~buie/idl/idl.html#categ8

cheers,
-henry

marshall perrin

Let me second this recommendation. I make extensive use of these routines
and they're quite good. It's very easy to get data into or out of mysql.

The only hiccup is that if you try a malformed
SQL query or otherwise confuse mysql, the IDL/MySQL link becomes unresponsive.
For personal use it's easy enough just to reset and open up a new session,
but you might want to be careful about using this code in a production
environment - or at least, be sure to check all your SQL carefully!

- Marshall

wox

On 20 Nov 2006 08:59:41 -0800, "Brian Larsen" <balarsen@gmail.com>

The dataminer was already mentioned. I don't know much about
databases, but I'd guess you can use the "WHERE"?

Some code to play around with is displayed below. Check IDLdbDatabase
methods for setting and getting data. (Maybe you know much more about
this? If not, this might get you started.)

You could also use Marc Buie's code, as mentioned, to save you some
work :-).

I don't know whether this is what you were asking for...

pro test

objDB = OBJ_NEW('IDLdbDatabase')

;On Windows:
ODBCstr="DRIVER={MySQL ODBC 3.51 Driver};SERVER=servername;DATABASE=databasename; USER=username;PASSWORD=*****;OPTION=3;"
objDB->Connect,connection=ODBCstr

; On Linux I could't get this to work, so I did this:
; Add this to .odbc.ini:
;
;[ODBC Data Sources]
;...
;MyODBC=MySQL ODBC 3.51 Driver
;.
;.
;.
;[MyODBC]
;Driver = /usr/lib/libmyodbc3.so
;Description = MySQL ODBC 3.51 Driver
;SERVER = servername
;USER = username
;Password = ******
;Database = databasename

; Uncomment for linux (comment previous lines) ;ODBCstr="MyODBC"
;objDB->Connect,datasource=mysqlinfo


if OBJ_VALID(objDB) then begin
table='tablename'
; Do your database stuff (check IDL's help on IDLdbDatabase)
; Some examples:

author='Brian A. Larsen' a=3 v=3
objDB->ExecuteSQL, $
"UPDATE "+table+" SET a = "+string(a)+" WHERE v= "+string(v)
objDB->ExecuteSQL, $
"DELETE FROM "+table+" WHERE v1= "+string(v)
objDB->ExecuteSQL, $
"INSERT INTO "+table+"(author,v,datetime) VALUES
('"+author+"',"+string(v)+",CURRENT_TIMESTAMP)"
endif

obj_destroy,objDB


end

wox

Probably better to use IDLdbRecordset in the first two cases :-).
Anyway, this is just an illustration.

brian larsen

Good Stuff, I will look into Marc Buie's library, also searching the
forum (which I should have done a better job of first) yielded maybe
the easiest solution which I have made work which is simply using spawn
(which is certainly far from elegant but does work)

make a file called 'script.sql' -- can of course be any name
that has the query you want to perform

use simfile; select count(*) from header;

you can pre create these which is not too useful or your idl program
could create them.
then run this idl:
idl> ans=''
idl> spawn, 'mysql --password="password" < script.sql', ans
idl> PRINT, ans count(*) 1004
idl> help, ans
ANS STRING = Array[2]

Which gets the job done.

- Brian

---------------------------------------------------------------------------
Brian A. Larsen
Dept. of Physics
Space Science and Engineering Lab (SSEL)
Montana State University - Bozeman
Bozeman, MT 59717