How to use SQL in My Money
From MTHWiki
My Money 2.0 Manual | Password protect Money<<Previous | Next>>Back up your Money file
How to use My Money Shell
My Money shell can be activated by selecting "View-Show console" or clicking on the shell icon:
My Money shell is a general purpose Java shell based on a BeanShell 2.0. As such, it is perfectly capable of processing simple and complex commands in the Java language syntax.
For example, to print a "Hello, World" line to the console use print command:
money.bsh% print("Hello World");
Hello World
To print todays date, or to do a simple calculation:
money.bsh% print ("Todays Date: " + new java.util.Date());
Todays Date: Wed Nov 21 12:32:49 EST 2007
money.bsh%
money.bsh% print ("Value: " + 100*52/100);
Value: 52
money.bsh%
My Money is shipped with a full version of BeanShell and all of the BeanShell scripts. For example to get a directory listing for the current directory use dir() script:
money.bsh% dir();
rw_ Nov 12 2438 .classpath
rw_ Oct 3 44 .cvsignore
rw_ Nov 6 503 .project
rw_ Nov 6 0 .settings/
rw_ Nov 20 0 bootstrap/
rw_ Sep 12 0 build/
rw_ Nov 12 0 classes/
rw_ Jul 18 0 config/
rw_ Sep 13 0 deploy/
rw_ Nov 21 378 derby.log
rw_ Nov 21 0 docs/
rw_ Sep 11 0 install/
rw_ Apr 23 5 latest_version.txt
rw_ Oct 15 0 lib/
rw_ Aug 15 0 src/
rw_ Nov 1 0 style/
rw_ Nov 12 0 system/
rw_ Aug 16 0 testdata/
money.bsh%
Please refer to BeanShell Manual for a complete list and examples of shell commands
How to use SQL in My Money
My Money is shipped with a embedded shell that is capable of processing commands to database in a Structured Query Language (SQL) syntax. The default database in My Money installations is a Apache Derby RDBMS, which is compliant with a SQL-92 standard.
My Money allows you to query and update underlying database right from the shell. To query the database you can use 'runsql' script and to update data you can use 'runsqlupdate' script.
For example, to see the list of all accounts in the database:
money.bsh% runsql("select ID, ANAME, ATYPE, OFXBANKID from mmy_account");
Executing>> select ID, ANAME, ATYPE, OFXBANKID from mmy_account
ID|ANAME|ATYPE|OFXBANKID
--------------------------------------------------
1|Test|0|
--------------------------------------------------
2|test2|0|
--------------------------------------------------
done..
money.bsh%
To find out how many transactions are stored in the database:
money.bsh% runsql("select count(*) from mmy_transaction");
Executing>> select count(*) from mmy_transaction
1
--------------------------------------------------
9
--------------------------------------------------
done..
money.bsh%
Find out how many transactions are on account named 'Test'
money.bsh% runsql ("select count(*) from mmy_transaction, mmy_doubleentry, mmy_account
where mmy_account.aname='Test'
and mmy_account.id=mmy_doubleentry.leftaccntid
and mmy_transaction.id=mmy_doubleentry.lefttranid");
Executing>> select count(*) from mmy_transaction, mmy_doubleentry, mmy_account
where mmy_account.aname='Test'
and mmy_account.id=mmy_doubleentry.leftaccntid
and mmy_transaction.id=mmy_doubleentry.lefttranid
1
--------------------------------------------------
7
--------------------------------------------------
done..
money.bsh%
Please refer to the My Money Database Model for a complete list of My Modey data tables and relationships.
Latest Apache Derby manuals, including SQL syntax could be found at Derby Documentation web site.
My Money 2.0 Manual | Password protect Money<<Previous | Next>>Back up your Money file
