| Re: idl - Mysql interface [message #51398] |
Tue, 21 November 2006 02:20  |
Wox
Messages: 184
Registered: August 2006
|
Senior Member |
|
|
On Tue, 21 Nov 2006 11:12:26 +0100, Wox <nomail@hotmail.com> wrote:
> 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)"
Probably better to use IDLdbRecordset in the first two cases :-).
Anyway, this is just an illustration.
|
|
|
|
| Re: idl - Mysql interface [message #51399 is a reply to message #51398] |
Tue, 21 November 2006 02:12  |
Wox
Messages: 184
Registered: August 2006
|
Senior Member |
|
|
On 20 Nov 2006 08:59:41 -0800, "Brian Larsen" <balarsen@gmail.com>
wrote:
> 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.
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
|
|
|
|
|
|
|
|
| Re: idl - Mysql interface [message #51408 is a reply to message #51406] |
Mon, 20 November 2006 09:52 |
Rick Towler
Messages: 821
Registered: August 1998
|
Senior Member |
|
|
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
Brian Larsen wrote:
> 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
>
|
|
|
|
| Re: idl - Mysql interface [message #51668 is a reply to message #51398] |
Wed, 29 November 2006 16:29 |
Brian Larsen
Messages: 270
Registered: June 2006
|
Senior Member |
|
|
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)
As an example that wrote using help from earlier posts:
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
|
|
|
|
comp.lang.idl-pvwave archive
Members
Search
Help
Login
Home
