Online Help

driverString := "com.mysql.jdbc.Driver";

$\mathrm{driverString}:=''com.mysql.jdbc.Driver''$

jarFile := "c:/drivers/MySQL/mysql-connector-java-3.0.14-production-bin.jar";

$\mathrm{jarFile}:=''c:/drivers/MySQL/mysql-connector-java-3.0.14-production-bin.jar''$

url := "jdbc:mysql://achilles/TestDB";

$\mathrm{url}:=''jdbc:mysql://achilles/TestDB''$

driver := Database:-LoadDriver( 'driver'=driverString, 'classpath'=jarFile );

$\mathrm{driver}:=\mathbf{module}\left(\right)\mathbf{option}\mathrm{unload}\=\mathrm{Close}\;\mathbf{local}\mathrm{handle}\,\mathrm{driver}\,\mathrm{path}\;\mathbf{export}\mathrm{OpenConnection}\,\mathrm{Close}\;\mathbf{end\; module}$

connection := driver:-OpenConnection( url, "test", "test" );

$\mathrm{connection}:=\mathbf{module}\left(\right)\mathbf{option}\mathrm{unload}\=\mathrm{Close}\;\mathbf{local}\mathrm{handle}\,\mathrm{driver}\,\mathrm{url}\,\mathrm{user}\,\mathrm{saveOptions}\;\mathbf{export}\mathrm{ExecuteQuery}\,\mathrm{ExecuteUpdate}\,\mathrm{CreateStatement}\,\mathrm{CreatePreparedStatement}\,\mathrm{Commit}\,\mathrm{Rollback}\,\mathrm{Close}\,\mathrm{SetOptions}\,\mathrm{GetOptions}\,\mathrm{GetTables}\,\mathrm{GetColumns}\,\mathrm{GetCatalogs}\,\mathrm{GetSchemas}\,\mathrm{Save}\;\mathbf{end\; module}$

connection:-ExecuteUpdate( "CREATE TABLE marks ( studentid INTEGER PRIMARY KEY, assignment1 FLOAT, assignment2 FLOAT, midterm FLOAT, assignment3 FLOAT, assignment4 FLOAT, assignment5 FLOAT, final FLOAT, average FLOAT )" );

$0$

addStudent := connection:-CreatePreparedStatement( "INSERT INTO marks( studentid, assignment1, assignment2, midterm, assignment3, assignment4, assignment5, final ) VALUES( ?, ?, ?, ?, ?, ?, ?, ? )" );

$\mathrm{addStudent}:=\mathbf{module}\left(\right)\mathbf{option}\mathrm{unload}\=\mathrm{Close}\;\mathbf{local}\mathrm{handle}\;\mathbf{export}\mathrm{Execute}\,\mathrm{NextResult}\,\mathrm{Close}\,\mathrm{SetOptions}\,\mathrm{GetOptions}\;\mathbf{end\; module}$

for i from 1 to 200 do
addStudent:-Execute( RandomTools:-Generate( integer( range=100000..999999 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ),
RandomTools:-Generate( float( range=1.0..100.0, method=uniform, digits=4 ) ) );
end do:
connection:-Commit();

data := connection:-ExecuteQuery( "SELECT midterm, studentid FROM marks", 'output'=Array );

[ 2 x 200 2-D Array ]
[ Data Type: anything ]
data := [ Storage: rectangular ]
[ Order: Fortran_order ]

sdata := [seq( data[i][1], i=1..200 ) ]:

stats[describe,mean]( sdata );

$49.81130020$

per := stats[describe,percentile[90]](sdata);

$\mathrm{per}:=93.4800033569335938$

res := connection:-ExecuteQuery( sprintf( "SELECT studentid, midterm FROM marks WHERE midterm > %a ORDER BY midterm", per ) ):

while ( res:-Next() )
do
printf( "%a %a\n", res:-GetData( "studentid" ), res:-GetData( "midterm" ) );
end do:

896323 93.7399978637695314

609830 94.4599990844726564

324457 94.7799987792968750

565262 94.9800033569335938

482587 95.0199966430664063

501432 95.5000000000000000

568821 96.4100036621093750

711138 96.9400024414062500

275474 97.0599975585937500

942666 97.1200027465820313

876509 98.0100021362304688

822684 98.0400009155273438

216985 98.3799972534179688

726280 98.5500030517578126

422179 98.5599975585937500

175996 98.8300018310546876

655788 98.9899978637695312

746577 99.4000015258789063

836032 99.4800033569335938

525989 99.9400024414062500

x := min( op( sdata ) );

$x:=1.06599998474121094$

member( x, sdata, '`id`' );

$\mathrm{true}$

data[id][1];

$1.06599998474121094$

data[id][2];

$379028$

connection:-ExecuteQuery( sprintf( "SELECT * FROM marks WHERE studentid = %d", data[id][2] ), output=table );

$\mathrm{table}\left(\left[\left(1\,''assignment4''\right)\=91.8700027465820314\,\left(1\,''average''\right)\=\left(\right)\,\left(1\,''assignment5''\right)\=34.2500000000000000\,\left(1\,''assignment3''\right)\=42.7099990844726564\,\left(1\,''midterm''\right)\=1.06599998474121094\,\left(1\,''studentid''\right)\=379028\,\left(1\,''assignment1''\right)\=55.3199996948242188\,\left(1\,''final''\right)\=22.1000003814697266\,\left(1\,''assignment2''\right)\=80.3600006103515625\right]\right)$

resultSet := connection:-ExecuteQuery( "SELECT * FROM marks" );

$\mathrm{resultSet}:=\mathbf{module}\left(\right)\mathbf{option}\mathrm{unload}\=\mathrm{Close}\;\mathbf{local}\mathrm{handle}\;\mathbf{export}\mathrm{Next}\,\mathrm{Previous}\,\mathrm{Last}\,\mathrm{First}\,\mathrm{GetRowIndex}\,\mathrm{GotoRow}\,\mathrm{GetRowCount}\,\mathrm{InsertRow}\,\mathrm{DeleteRow}\,\mathrm{UpdateRow}\,\mathrm{GetData}\,\mathrm{UpdateData}\,\mathrm{GetType}\,\mathrm{GetName}\,\mathrm{GetColumnCount}\,\mathrm{SetOptions}\,\mathrm{GetOptions}\,\mathrm{Close}\,\mathrm{ToMaple}\;\mathbf{end\; module}$

while ( resultSet:-Next() )
do
ave := ( resultSet:-GetData( "assignment1" )+
resultSet:-GetData( "assignment2" )+
resultSet:-GetData( "assignment3" )+
resultSet:-GetData( "assignment4" )+
resultSet:-GetData( "assignment5" )+
3*resultSet:-GetData( "midterm" )+
5*resultSet:-GetData( "final" ) )/12.0;
resultSet:-UpdateData( "average", ave );
resultSet:-UpdateRow();
end do:

connection:-Commit();

resultSet := connection:-ExecuteQuery( "SELECT average FROM marks WHERE average > 80" );

$\mathrm{resultSet}:=\mathbf{module}\left(\right)\mathbf{option}\mathrm{unload}\=\mathrm{Close}\;\mathbf{local}\mathrm{handle}\;\mathbf{export}\mathrm{Next}\,\mathrm{Previous}\,\mathrm{Last}\,\mathrm{First}\,\mathrm{GetRowIndex}\,\mathrm{GotoRow}\,\mathrm{GetRowCount}\,\mathrm{InsertRow}\,\mathrm{DeleteRow}\,\mathrm{UpdateRow}\,\mathrm{GetData}\,\mathrm{UpdateData}\,\mathrm{GetType}\,\mathrm{GetName}\,\mathrm{GetColumnCount}\,\mathrm{SetOptions}\,\mathrm{GetOptions}\,\mathrm{Close}\,\mathrm{ToMaple}\;\mathbf{end\; module}$

while ( resultSet:-Next() )
do
printf( "%a\n", resultSet:-GetData( "average" ) );
end do:

83.4332962036132813

87.3992004394531250

89.5199966430664064

87.0036010742187500

86.1983032226562500

85.5774993896484376

84.4216995239257813

90.2457962036132813

connection:-ExecuteUpdate( "DROP TABLE marks" );
connection:-Commit();

$0$

connection:-Close();