CREATE TABLE accessct (recordid int, testid int, dspeed int, uspeed int, qos tinyint, rtt smallint, maxpause mediumint, maxroutespeed int, maxlinespeed int, concurrenttcp float, tcpforcedidle float, tcprxpooo mediumint, tcprxbooo mediumint, tcprxpow mediumint, tcprxbow mediumint, tcprxdop mediumint, tcprxdob mediumint, tcprxpdp mediumint, tcprxpdb mediumint, tcprxcrcerr mediumint, tcprxboff mediumint, tcprxts mediumint, tcpbyteslost mediumint, uqos tinyint, umaxpause mediumint, umaxlinespeed int, uconcurrenttcp float, utcpforcedidle float, ethrxftl mediumint, ethrxnoal mediumint, ethrxsf mediumint, ethrxcrcerr mediumint, ethrxovr mediumint, ethrxtrunc mediumint, dtesttype varchar(6), version varchar(128), runtime mediumint, freeq mediumint, PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE mycapacity (recordid int, testid int, dcapacity int, ucapacity int, dpackets mediumint, upackets mediumint, packetsize smallint, qos tinyint, PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE myiptv (recordid int, testid int, jitter1 float, loss1 float, order1 float, jitter2 float, loss2 float, order2 float, jitter3 float, loss3 float, order3 float, PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE myroute (recordid int, testid int, ipto varchar(64), hops tinyint, endms smallint, maxms smallint, endloss smallint, maxloss smallint, ipfrom varchar(64), nameto varchar(128), PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE myspeed (recordid int, testid int, dspeed int, uspeed int, qos tinyint, rtt smallint, maxpause mediumint, avgpause mediumint, bandwidth int, routespeed int, forcedidle tinyint, routeconc float, dtesttype varchar(6), utesttype varchar(6), PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE myvideo (recordid int, testid int, ajitter float, vjitter float, aloss tinyint, vloss tinyint, adiscards tinyint, vdiscards tinyint, PRIMARY KEY (recordid), UNIQUE KEY (testid));
CREATE TABLE myvoip (recordid int, testid int, jitter float, djitter float, loss float, dloss float, uorder float, dorder float, discards float, mos float, PRIMARY KEY (recordid), UNIQUE KEY (testid));
To enable MySQL support under MyConnection Server, please follow these steps:
- Download Connector/J from the MySQL website
- Extract the contents of the archive to the ext directory under MyConnection Server's install directory.
First enter your SQL database information such as URL and database name etc then enter the following SQL commands to pass data to the database tables you created in step one.
Below is the syntax for every single variable in every single test. Copy and paste the text below into the 'SQL Statements to Run' text box and make sure the 'Run this profile automatically on all future tests posted to this database' check box is checked.
INSERT INTO tests (recordid, testid, time, timeint, detaillink, sid, ip, dnsname, cc, isp) VALUES (%RECORDID%, %TESTID%, '%TIME%', %TIMEINT%, '%DETAILLINK%', '%SID%', '%IP%', '%DNSNAME%', '%CC%', '%ISP%');
[speed] INSERT INTO myspeed (recordid, testid, dspeed, uspeed, qos, rtt, maxpause, avgpause, bandwidth, routespeed, forcedidle, routeconc, dtesttype, utesttype) VALUES (%RECORDID%, %TESTID%, %SPEED.DSPEED%, %SPEED.USPEED%, %SPEED.QOS%, %SPEED.RTT%, %SPEED.MAXPAUSE%, %SPEED.AVGPAUSE%, %SPEED.BANDWIDTH%, %SPEED.ROUTESPEED%, %SPEED.FORCEDIDLE%, %SPEED.ROUTECONC%, '%SPEED.DTESTTYPE%', '%SPEED.UTESTTYPE%');
[voip] INSERT INTO myvoip (recordid, testid, jitter, djitter, loss, dloss, uorder, dorder, discards, mos) VALUES (%RECORDID%, %TESTID%, %VOIP.JITTER%, %VOIP.DJITTER%, %VOIP.LOSS%, %VOIP.DLOSS%, %VOIP.ORDER%, %VOIP.DORDER%, %VOIP.DISCARDS%, %VOIP.MOS%);
[video] INSERT INTO myvideo (recordid, testid, ajitter, vjitter, aloss, vloss, adiscards, vdiscards) VALUES (%RECORDID%, %TESTID%, %VIDEO.AJITTER%, %VIDEO.VJITTER%, %VIDEO.ALOSS%, %VIDEO.VLOSS%, %VIDEO.ADISCARDS%, %VIDEO.VDISCARDS%);
[iptv] INSERT INTO myiptv (recordid, testid, jitter1, loss1, order1, jitter2, loss2, order2, jitter3, loss3, order3) VALUES (%RECORDID%, %TESTID%, %IPTV.JITTER1%, %IPTV.LOSS1%, %IPTV.ORDER1%, %IPTV.JITTER2%, %IPTV.LOSS2%, %IPTV.ORDER2%, %IPTV.JITTER3%, %IPTV.LOSS3%, %IPTV.ORDER3%);
[route] INSERT INTO myroute (recordid, testid, ipto, hops, endms, maxms, endloss, maxloss, ipfrom, nameto) VALUES (%RECORDID%, %TESTID%, '%ROUTE.IPTO%', %ROUTE.HOPS%, %ROUTE.ENDMS%, %ROUTE.MAXMS%, %ROUTE.ENDLOSS%, %ROUTE.MAXLOSS%, '%ROUTE.IPFROM%', '%ROUTE.NAMETO%');
[capacity] INSERT INTO mycapacity (recordid, testid, dcapacity, ucapacity, dpackets, upackets, packetsize, qos) VALUES (%RECORDID%, %TESTID%, %CAPACITY.DCAPACITY%, %CAPACITY.UCAPACITY%, %CAPACITY.DPACKETS%, %CAPACITY.UPACKETS%, %CAPACITY.PACKETSIZE%, %CAPACITY.QOS%);
[act] INSERT INTO accessct (recordid, testid, dspeed, uspeed, qos, rtt, maxpause, maxroutespeed, maxlinespeed, concurrenttcp, tcpforcedidle, tcprxpooo, tcprxbooo, tcprxpow, tcprxbow, tcprxdop, tcprxdob, tcprxpdp, tcprxpdb, tcprxcrcerr, tcprxboff, tcprxts, tcpbyteslost, uqos, umaxpause, umaxlinespeed, uconcurrenttcp, utcpforcedidle, ethrxftl, ethrxnoal, ethrxsf, ethrxcrcerr, ethrxovr, ethrxtrunc, dtesttype, version, runtime, freeq) VALUES (%RECORDID%, %TESTID%, %ACT.DSPEED%, %ACT.USPEED%, %ACT.QOS%, %ACT.RTT%, %ACT.MAXPAUSE%, %ACT.MAXROUTESPEED%, %ACT.MAXLINESPEED%, %ACT.CONCURRENTTCP%, %ACT.TCPFORCEDIDLE%, %ACT.TCPRXPOOO%, %ACT.TCPRXBOOO%, %ACT.TCPRXPOW%, %ACT.TCPRXBOW%, %ACT.TCPRXDOP%, %ACT.TCPRXDOB%, %ACT.TCPRXPDP%, %ACT.TCPRXPDB%, %ACT.TCPRXCRCERR%, %ACT.TCPRXBOFF%, %ACT.TCPRXTS%, %ACT.TCPBYTESLOST%, %ACT.UQOS%, %ACT.UMAXPAUSE%, %ACT.UMAXLINESPEED%, %ACT.UCONCURRENTTCP%, %ACT.UTCPFORCEDIDLE%, %ACT.ETHRXFTL%, %ACT.ETHRXNOAL%, %ACT.ETHRXSF%, %ACT.ETHRXCRCERR%, %ACT.ETHRXOVR%, %ACT.ETHRXTRUNC%, '%ACT.DTESTTYPE%', '%ACT.VERSION%', %ACT.RUNTIME%, %ACT.FREEQ%); |
Now any test you run in MyConnection Server will automatically enter the results into your newly created MySQL database.
To do this use the 'select * from 'table_name'' command.
For example typing
select * from myspeed;
will display all the results in the myspeed table.
|
