Before anyone else gets confused Samba doesn’t use MySQL.
I need to work a way to query a MySQL database for a planned integration with PBX phone system (for a start).
Also have future plan of setting up an integration for a WiFi radius server but thats a way off yet.
@QMcKay you probably the best one to ask on that…
Is there any clean way to query a MySQL database?
Only things on the forum I could find is people asking about samba running from MySQL database.
I just want to query a database to call data.
Once I can do this I can do whatever I need within the script.
At the minute I struggled to find a JScript solution, all suggested using PHP but obviously these questions are mostly regarding websites.
I have managed to find and used a powershell script to run MySQL queries and thinking maybe can work something out like with the internet test but would much prefer a method not needed external files and scripts.
Was going to post picture of a successful query as was well chuffed with myself but since its call data has all my telephone numbers mobile, landline etc plastered all over it LOL
This works for MS SQL using either of the first listed drivers, so if you have a valid ODBC driver installed for MySQL, then something like this should theoretically work:
function myConnect() {
var driver = "";
driver = "ODBC Driver 11 for SQL Server";
driver = "SQL Server Native Client 11.0";
//driver = "mySQL ODBC 3.51 Driver";
var conString = "Driver={"+driver+"};";
conString += "Server=127.0.0.1;";
//conString += "Port=4900;";
conString += "Database=SambaPOS5;";
conString += "Uid=sa;";
conString += "Pwd=sambapos;";
conString += "Option=3";
var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
//conn.open ="Driver={mySQL ODBC 3.51 Driver};Server=xxx.xxx.xxx.xxx;Port=4900;Database=my_dbname;Uid=user;Pwd=pass;Option=3";
conn.open = conString;
var sSQLStatement = "SELECT count(*) FROM Entities";
rs.Open(sSQLStatement, conn);
/*
rs.MoveFirst;
rs.MoveNext;
rs.RecordCount;
rs.Fields.Count;
rs.Fields(i).Name;
rs.Fields(i).Value;
rs.Fields.Item(0)
*/
rs.MoveFirst;
var result = rs.Fields(0).Value;
rs.close;
conn.close;
return result;
}
To find your installed drivers, search Windows for ODBC, then add a DSN to see what is listed as being installed …
Thanks @QMcKay
I did find a site with mentioned ODBC but couldn’t get to work.
Got a message about no driver which you mention.
Will look how to add this.
Does this go one the client or the db server?
Hey, this is exactly what I’m looking for, but i’m confused what I need if the mySQL datbase is not locally hosted and is externally hosted, do I still need to install that driver?
Yes, you need to install the driver so that you can access the driver via JScript and invoke a connection to the MySQL DB. That is the easy part.
The part you will probably have trouble with is setting up the external host to allow the connection. This will depend on your host and what type of control or access you have.
I couldn’t find anything I could get to work to directly process into an array so opted to pragmatically build a JSON array.
function getCallDataDateRange(startDate,endDate) {
var driver = "";
//driver = "ODBC Driver 11 for SQL Server";
//driver = "SQL Server Native Client 11.0";
//driver = "MySQL ODBC 5.3 ANSI Driver";
/*
rs.MoveFirst;
rs.MoveNext;
rs.RecordCount;
rs.Fields.Count;
rs.Fields(i).Name;
rs.Fields(i).Value;
rs.Fields.Item(0)
*/
driver = "MySQL ODBC 5.3 Unicode Driver";
var conString = "Driver={"+driver+"};";
conString += "Server=10.88.1.114;";
conString += "Port=3306;";
conString += "Database=asteriskcdrdb;";
conString += "Uid=USERNAME;";
conString += "Pwd=PASSWORD;";
conString += "Option=3";
var conn = new ActiveXObject("ADODB.Connection");
var rs = new ActiveXObject("ADODB.Recordset");
conn.open = conString;
//--ADOBA CDR COUNT
var recordCount = "SELECT count(*) FROM cdr WHERE calldate > '"+startDate+"' AND calldate < '"+endDate+"'";
rs.Open(recordCount, conn);
var callRowCount = rs.Fields(0).Value;
rs.close;
//--ADOBA CDR ARRAY BUILDER
var recordCount = "SELECT * FROM cdr WHERE calldate > '"+startDate+"' AND calldate < '"+endDate+"'";
rs.Open(recordCount, conn);
rs.MoveFirst;
var listJSON = '{"calls":[';
for (r = 0; r < callRowCount ; r++) {
var rowArray = new Object();
var callDateField = rs.Fields(0).Value;
var callDateParse = new Date(callDateField);
var callDateFormatted = formatDate(callDateParse);;
rowArray.calldate = callDateFormatted;
rowArray.clid = rs.Fields(1).Value;
rowArray.src = rs.Fields(2).Value;
rowArray.dst = rs.Fields(3).Value;
rowArray.dcontext = rs.Fields(4).Value;
rowArray.channel = rs.Fields(5).Value;
rowArray.dstchannel = rs.Fields(6).Value;
rowArray.lastapp = rs.Fields(7).Value;
rowArray.lastdata = rs.Fields(8).Value;
rowArray.duration = rs.Fields(9).Value;
rowArray.billsec = rs.Fields(10).Value;
rowArray.disposition = rs.Fields(11).Value;
rowArray.amaflags = rs.Fields(12).Value;
rowArray.accountcode = rs.Fields(13).Value;
rowArray.uniqueid = rs.Fields(14).Value;
rowArray.userfield = rs.Fields(15).Value;
rowArray.did = rs.Fields(16).Value;
rowArray.recordingfile = rs.Fields(17).Value;
rowArray.cnum = rs.Fields(18).Value;
rowArray.cnam = rs.Fields(19).Value;
rowArray.outbound_cnum = rs.Fields(20).Value;
rowArray.outbound_cnam = rs.Fields(21).Value;
rowArray.dst_cnam = rs.Fields(22).Value;
var rowJSON = JSON.stringify(rowArray);
var callNumber = r+1;
listJSON += '{"call":'+callNumber+',"data":['+rowJSON+']}';
if (r+1 < callRowCount) {
listJSON += ',';
}
rs.MoveNext;
}
listJSON += ']}'
rs.close;
conn.close;
return listJSON;
}
And what do you know, first time I got a valid JSON array well had to change the if for the last loop to be +1 vs record count as obviously the loop starts at 0;
well had to change the if for the last loop to be +1 vs record count as obviously the loop starts at 0;