Greetings experts,
I have an odd request to make.
We have an app that was written in Perl language by a contractor.
This app worked just once and it worked with just one of six parameters.
Since then, our organization has had trouble getting him to fix the bugs until they decided to end the contract and have us redesign the app.
The help I am here to seek is NOT to translate the Perl code. I have already done that in VB.Net.
My issue is that there are several embedded queries within this Perl code.
The app uses six parameters with an OR filter.
In other words, there are six input parameters,
**uuid**
**call_list_id**
**phone_number**
**start (start date and time)**
**end (end date and time)**
**call_type**
A user can search with any of the input params.
For instance if user wants to search with phone_number, if the other boxes are empty, then display results based on the value of phone_number.
I have combined all of those embedded queries into one long query with joins.
The issue that I am having is that when I run the app based on the combined query, I get just one result.
However, given the only test we ran on that app before it broke down, each input param can produce results of up to 10 rows of data
I am wondering if someone would be kind enough to look at both my combined query and the embedded queries and suggest a better way of handling the query even if I have to do it using stored procedures.
Please forgive me for posting both my query and the queries embedded in the perl.
I just need a suggestion on a better way to handle the queries.
I tried passing call_list_id param just as example and this produced over ten records with the perl code but only one record with my combined query.
Many thanks in advance.
//Perl with embedded queries
if ($key eq "uuid") { $ARGV{'uuid'} = $val; $uuid = $val; }
elsif ($key eq "call_list_id") { $ARGV{'call_list_id'} = $val; $call_list_id = $val; }
elsif ($key eq "phone_number") { $ARGV{'phone_number'} = $val; $phone_number = $val; }
elsif ($key eq "start") { $ARGV{'start'} = $val; $start = $val; }
elsif ($key eq "end") { $ARGV{'end'} = $val; $end = $val; }
elsif ($key eq "call_type") { $ARGV{'call_type'} = $val; $call_type = $val; }
my %recordings = &GetValue(&GetHashFromSQL("SELECT uuid, 1 FROM cti.recordings"));
my @parameters;
my $sql = " SELECT phone_number, callerid, call_list_id, startttime at time zone '$timezone', "
. " connecttime at time zone '$timezone', endtime at time zone '$timezone', duration, fullduration, "
. " camapign_id, queue_id, call_type_id, roll_id, cause_code, uuid, box_id, trunk_name, uuid, customer_id "
. " FROM cti.qpcdr WHERE 1 = 1 ";
if ($uuid) {
$sql .= " AND uuid = ?";
push @parameters, $uuid;
}
$call_list_id && ($sql .= " AND call_list_id = " . $call_list_id);
$phone_number && ($sql .= " AND phone_number = '" . $phone_number . "'");
if ($start) {
$sql .= " AND startttime >= ?";
push @parameters, $start;
}
if ($end) {
$sql .= " AND startttime <= ?";
push @parameters, $end;
}
$call_type && ($sql .= " AND call_type_id = $call_type");
# $tenant_id && ($sql .= " AND customer_id = " . $dbh->quote( $tenant_id ));
# $tenant_id && ($sql .= " AND customer_id = " . $dbh->quote( $tenant_id ));
# print "SQL[$sql]<br/>\n";
($success, $error, @data) = &GetArrayFromSQL($sql . " ORDER BY startttime DESC", @parameters);
my ($csuccess, $cerror, %campaigns) = &GetHashFromSQL("SELECT campaign_id, campName FROM cti.campaign");
my ($qsuccess, $qerror, %queues) = &GetHashFromSQL("SELECT queueId, queueName FROM cti.queues");
my ($ctsuccess, $cterror, %calltypes) = &GetHashFromSQL("SELECT id, name FROM cti.call_types");
my ($esuccess, $eerror, %employees) = &GetHashFromSQL("SELECT r.roll_id, " . &EmployeeName() . " FROM cti.roll r, cti.employee e WHERE e.employee_id = r.employee_id");
my ($ccsuccess, $ccerror, %causecodes) = &GetHashFromSQL("SELECT cause_code, name FROM cti.isdn_cause_code");
my ($dsuccess, $derror, %dispositions) = &GetHashFromSQL("SELECT cq.uuid, ct.call_termination_type FROM cti.call_history_qpcdr_link cq, cti.call_history c, cti.call_terminations ct WHERE cq.call_history_id = c.call_history_id AND c.call_termination_id = ct.call_termination_id");
my ($ssuccess, $serror, %servers) = &GetHashFromSQL("SELECT a.value, b.name FROM cti.server_settings a, cti.servers b WHERE b.id = a.server_id AND a.attribute = 'asterisk_box_id' AND a.value IS NOT NULL");
my ($trsuccess, $trerror, %trunks) = &GetHashFromSQL("SELECT id, name FROM cti.trunks");
for (my $i = 0; $i < @data; $i++) {
## #if ($m->session->{tenant_id} && ($data[$i][2] || $data[$i][8])) {
if ($tenant_id && ($data[$i][2] || $data[$i][8])) {
if ($data[$i][17] && ($data[$i][17] != $tenant_id)) {
splice(@data, $i, 1);
$i--;
next;
}
# my $clid = &GetValueFromSQL("SELECT top 1 call_list_id from cti.call_list where call_list_id=?", $data[$i][2]);
# if ($data[$i][2] && !&GetValue(&GetValueFromSQL("SELECT call_list_id FROM cti.call_list WHERE call_list_id = ? ", $data[$i][2]))) {
# splice(@data, $i, 1);
# $i--;
# next;
# }
#if ($data[$i][8] && !&GetValue(&GetValueFromSQL("SELECT top 1 campaign_id FROM campaign WHERE campaign_id = ? AND customerId = ?", $data[$i][8], $m->session->{tenant_id}))) {
if ($data[$i][8] && !&GetValue(&GetValueFromSQL("SELECT top 1 campaign_id FROM cti.campaign WHERE campaign_id = ? AND customerId = ?", $data[$i][8], $tenant_id)))
//Below is my combined queries
SELECT cl.phone_number, qp.callerid, qp.call_list_id, startttime,
qp.connecttime, qp.endtime, qp.duration, qp.fullduration,
ca.campName, q.queueName, c.name, (e.first_name +' '+ e.last_name) employee, i.name, ct.call_termination_type, r.filename, qp.trunk_name, qp.uuid, qp.customer_id
FROM cti.qpcdr qp
inner join cti.queues q on qp.queue_id = q.queueId
inner join cti.campaign ca on qp.camapign_id = ca.campaign_id
inner join cti.call_types c on qp.call_type_id = c.id
inner join cti.employee e on qp.employee_id = e.employee_id
inner join cti.isdn_cause_code i on qp.cause_code = i.cause_code
inner join cti.call_list cl on qp.call_list_id = cl.call_list_id
inner join cti.call_history ch on qp.call_list_id = ch.call_list_id
inner join cti.call_history_qpcdr_link cq on cq.call_history_id = ch.call_history_id
inner join cti.call_terminations ct on ch.call_termination_id = ct.call_termination_id
inner join cti.recordings r on qp.call_list_id = r.call_list_id
where qp.call_list_id='8975151'As we don't know your data, it's hard to tell what will be the solution.
But I would try replacing the inner joins with left outer joins to see how far that would get me.
Looking at the code I'd also do a search for "sql injection" and make sure your haven't replicated this string concatenation to create a sql statement pattern in the new vb.net code (specifically the call_list_id and phone_number sections look problematic in the perl)
Thank you all for your response.
My biggest issue at the moment is figuring out why the query does not produce the correct results.
Once I get that working, then my VB.NET code uses parameterized query for counter sql injection attack.
I am not there yet.
I have tried left join to no avail.
The primary reason I posted the perl code is to see the way they were declaring variables and see if there is a way to replicate that in stored proc, hoping that's the reason I am not getting the results we are looking for.
It's been a long time since I looked at perl
I ** think ** that there is an "outer" query ($sql)
This has no joins and is only selecting data from cti.qpcdr into @data; there is then what looks like shenanigans as the @data array is iterated and "stuff" happens, so I dont think you can create a single query to rule them all
So, I think you need to query cti.qpcdr with the parameters supplied but with no joins and see where that gets you / do what BItsMed suggested and replace INNER with LEFT OUTER
Alternatively, stop trying to replicate the "shape" of the code of someone you have no confidence in and start from scratch
Thanks for your reply.
I agree that trying to replicate an existing code especially with an app that does not work efficiently is a bad idea.
As I indicated in my original post, only reason I am looking at their code is because the app broke.
Otherwise, what I had intended to do was reverse engineer the app, review the values being produced for each column and build my own query from there.
Unfortunately, however, as I indicated in my original post, the app does not even load on the browser, leaving to do exactly what I did not want to do and that is to review their code.
so hard to read your original post trying to scroll right but I think I understand now.
So what makes you state the results produced are not correct?
Are you comparing the perl code result vs the vb code result?
Are you pointing to the same database in each case?
OK sorry about that.
When the users determined that the app was being erratic meaning that it runs sometimes and doesn't most of the time, they asked us to rewrite the app in VB.NET.
In addition to the web app not working consistently, some of the search params, according to the users, cause the app to break by giving them errors.
That alone was the reason we wanted to rewrite the app by reviewing the data produced.
So, the first time we loaded the app to review the data that was being rendered when you search with a specific param, it worked.
For instance, when entered a value for call_list_id (which is the only param that produced results), we got about 10 rows of data; just based on a call_list_value.
The problem is that after that initial data display, it never worked again.
Unfortunately, we did not make a screenshot of that results page.
So, when I said my code is not producing the correct results, yes, I was comparing the results produced by perl using call_list_id and the results produced by my code.
Just for brevity, this app is supposed to track calls made to our call center.
So, it makes sense that one individual could call more than once in a given day or even weeks.
I hope this clarifies my dilemma.
I thank you very much for your help.
Still confusing your answers are not succinct
- Does the perl code currently work?
- Are you doing the same thing in vb that was being done in perl code?
3.Since it is dynamic query can you do a print in perl and ssme in vb and compare?
Please answer these question without going into other stuff?
No, the perl code does not work.
That is the problem and that is what I have been trying to explain.
So, since the perl code does not work and no way to examine the results it is supposed to produce, I am forced to look at the perl code.
Does this help?
I cannot print the perl code because it does not work.
When you try to run it on the browser, it just spins forever until it breaks.
I know nothing about perl.
Like I stated, I know this is SQL forum, not perl forum and because I am only interested in the embedded SQL in the perl code, I was hoping someone could make sense of how I could put the queries in perl code together and use them in my VB code.
Make a stored procedure you can call from vb code. Take the big select statement you have there and make it into a stored procedure and call it from vb.net
No, that doesn't make much sense sir.
Its like garbage in, garbage out.
The select statement has to work first before I can make stored proc out it.
Ok, so that happens when you run the query with no where clause. How much data do you get.
I can't run it with no where clause because it will hang.
Right now, I am testing with call_list_id since that was the only one that worked (only once) with the perl code.
This gives me only one row of data versus the 10 rows of data when managed to run it with the perl code.
There are 6 input parameters but we use the OR operator which means you can use one or more parameters to search.
Yes you already told us that. Can you show us the index on each of these table for the columns you are joining on.
I will start by saying I don't know perl - but looking at the code provided here is what I think is happening:
-
A base query is defined:
my $sql = " SELECT phone_number, callerid, call_list_id, startttime at time zone '$timezone', " . " connecttime at time zone '$timezone', endtime at time zone '$timezone', duration, fullduration, " . " camapign_id, queue_id, call_type_id, roll_id, cause_code, uuid, box_id, trunk_name, uuid, customer_id " . " FROM cti.qpcdr WHERE 1 = 1 "; -
If the UUID parameter is defined - add to the base query and pass in the parameter:
if ($uuid) { $sql .= " AND uuid = ?"; push @parameters, $uuid; } -
If the call_list_id or phone_number are defined - add to the base query:
$call_list_id && ($sql .= " AND call_list_id = " . $call_list_id); $phone_number && ($sql .= " AND phone_number = '" . $phone_number . "'"); -
If the start/end parameters are defined - add to the base query and pass the parameters:
if ($start) { $sql .= " AND startttime >= ?"; push @parameters, $start; } if ($end) { $sql .= " AND startttime <= ?"; push @parameters, $end; } -
If the call_type is defined - add to the base query:
$call_type && ($sql .= " AND call_type_id = $call_type"); -
Execute the query:
($success, $error, @data) = &GetArrayFromSQL($sql . " ORDER BY startttime DESC", @parameters);
Step 6 adds the data from the query into the @data array. The next section runs individual SQL statements to populate hash tables:
my ($csuccess, $cerror, %campaigns) = &GetHashFromSQL("SELECT campaign_id, campName FROM cti.campaign");
my ($qsuccess, $qerror, %queues) = &GetHashFromSQL("SELECT queueId, queueName FROM cti.queues");
my ($ctsuccess, $cterror, %calltypes) = &GetHashFromSQL("SELECT id, name FROM cti.call_types");
my ($esuccess, $eerror, %employees) = &GetHashFromSQL("SELECT r.roll_id, " . &EmployeeName() . " FROM cti.roll r, cti.employee e WHERE e.employee_id = r.employee_id");
my ($ccsuccess, $ccerror, %causecodes) = &GetHashFromSQL("SELECT cause_code, name FROM cti.isdn_cause_code");
my ($dsuccess, $derror, %dispositions) = &GetHashFromSQL("SELECT cq.uuid, ct.call_termination_type FROM cti.call_history_qpcdr_link cq, cti.call_history c, cti.call_terminations ct WHERE cq.call_history_id = c.call_history_id AND c.call_termination_id = ct.call_termination_id");
my ($ssuccess, $serror, %servers) = &GetHashFromSQL("SELECT a.value, b.name FROM cti.server_settings a, cti.servers b WHERE b.id = a.server_id AND a.attribute = 'asterisk_box_id' AND a.value IS NOT NULL");
And finally - the code loops over each row in the @data array:
for (my $i = 0; $i < @data; $i++) {
There seems to be missing code that follows this - that code identifies how the hash tables are utilized. I am assuming the hash tables are used as lookup tables to get external values for related tables in the base query.
If that is the case - then the query you have written is not going to return the same values. I am guessing here - but most likely these other tables may not contain a value for the selected data from the base query which is why your query with inner joins isn't returning any data at all.
If you look at the queries that populate the hash tables - they are actually more than just joining to a table. For example:
my ($esuccess, $eerror, %employees) = &GetHashFromSQL("SELECT r.roll_id, " . &EmployeeName() . " FROM cti.roll r, cti.employee e WHERE e.employee_id = r.employee_id");
This one looks to the cti.roll and cti.employee table with an old style inner join - including a call to a function to return the employee name(s). This could be written as a CTE assuming the employee name exists in cti.employee.
WITH employees
AS (
SELECT r.roll_id, e.employee_id, e.employee_name FROM cti.roll r INNER JOIN cti.employee e ON e.employee_id = r.employee_id
)
SELECT ...
FROM cti.gpcdr qp
INNER JOIN employees e ON e.employee_id = qp.employee_id
This may not be the correct relationship though - as the original hash table does not include the employee_id. There may be something else that identifies the actual employee ID...
The dispositions hash table uses old style joins across 3 different tables - this one could also be rewritten using a CTE and then joined...but again - it depends on how that hash table is actually utilized in the rest of the code.
My recommendation would be to start with the base query and parameters - get that working and then add in the other tables as needed for the final results.
Thank you very much Jeff,
I did say that I don't know perl and as I have said before, is the reason I posted this query with perl code.
There seems to be missing code that follows this - that code identifies how the hash tables are utilized. I am assuming the hash tables are used as lookup tables to get external values for related tables in the base query.
I completely agree with the above narrative Jeff because after reviewing the items you itemized and looking at the base query, not all the standalone queries have relationship with the base table.
That explains in large part why the app does not work.
I have no earthly idea where they got this vendor.
They have been trying to reach him now for a long time to find a way to get the app to work even temporarily to give us enough time to go through each page.
As for all these below:
if ($uuid) {
$sql .= " AND uuid = ?";
push @parameters, $uuid;
}
That's why I wrote the queries with dynamic WHERE clause:
Dim struuid As String = ""
Dim strcall_list_id As String = ""
Dim strphone_number As String = ""
Dim strstartttime As String = ""
Dim strendtime As String = ""
Dim strcall_type_id As String = ""
Dim whereclause = ""
whereclause = " uuid = @uuid or call_list_id=@callerlist or phone_number=@phone or startttime=@start or endtime=@end or call_type_id=@calltype "
If struuid <> "" And strcall_list_id <> "" And strphone_number <> "" And strstartttime <> "" And strendtime <> "" And strcall_type_id <> "" Then
'//fill SqlDataReader
...
…
This way, when you click search, only the app pulls results with only the search box that is not empty.
But as you correctly stated, some query that bridges the base query with rest is missing.
I promise there is no code other than what I posted.
The base query does not include any of those other tables - and you have included those other tables in your query. By including those additional tables you are not going to get the same results and possible no results at all if the relationships are not correct.
For example - if there is no matching data in one of the other tables based on employee_id and your join is an inner join then no results will be returned.
Start with the base query - which only queries the one table with the dynamic where clause. Once that is working then add another table (or view or CTE or derived table) as needed.
Finally - review with the users the expected results. It may be easier to find out what this is supposed to do and build what is expected as opposed to trying to figure out what this code doesn't do...
Thanks Jeff,
I will do just that.
Thanks everyone for your contributions.
I am sure I will post final working code when I am done.
Greetings again experts.
I was able, based on your expert advises, was able to get the query to return correct results.
However, I still one issue that I hope to get your expert help on.
On the attached stored procedure, there are two queries embedded.
If I run each query separately and pass a value, say '1234', I get results for each query based on the passed value.
However, when I combine them into a stored procedure shown below, the second, smaller query returns an error that teleservers field name could not be found.
Any ideas what I need to do to correct this?
Thanks a lot as always.
ALTER PROCEDURE [dbo].[spGetLogs]
@uuid varchar(50),
@callerlist varchar(50),
@phone varchar(50),
@start varchar(150),
@Endd varchar(100),
@calltype int
AS
BEGIN
SELECT
cl.phone_number,
callerid,
qp.call_list_id,
startttime,
camapign_id,
q.queueName,
c.name as call_type,
connecttime,
endtime,
qp.duration,
fullduration,
ca.campName,
ct.call_termination_type,
qp.roll_id,
(e.first_name +' '+ e.last_name) employee,
i.name as cause_code,
box_id,
trunk_name,
qp.uuid,
customer_id
FROM cti.qpcdr qp
inner join cti.call_types c on qp.call_type_id = c.id
inner join cti.queues q on qp.queue_id = q.queueId
inner join cti.campaign ca on qp.camapign_id = ca.campaign_id
inner join cti.isdn_cause_code i On qp.cause_code = i.cause_code
inner join cti.employee e on qp.employee_id = e.employee_id
inner join cti.call_history ch On qp.call_list_id = ch.call_list_id
inner join cti.call_list cl on qp.call_list_id = cl.call_list_id
inner join cti.call_history_qpcdr_link cq On cq.call_history_id = ch.call_history_id
inner join cti.call_terminations ct On ch.call_termination_id = ct.call_termination_id
WHERE qp.uuid Like '%' + @uuid + '%'
AND qp.call_list_id LIKE '%' + @callerlist + '%'
AND cl.phone_number LIKE '%' + @phone + '%'
AND qp.startttime LIKE '%' + @start + '%'
AND qp.endtime LIKE '%' + @Endd + '%'
AND c.id LIKE '%' + @calltype + '%'
AND cl.phone_number IS NOT NULL
AND qp.uuid='12345'
SELECT b.name as teleservers
FROM cti.server_settings a,
cti.servers b
WHERE b.id = a.server_id
AND a.attribute = 'asterisk_box_id'
AND a.value = (SELECT box_id FROM cti.qpcdr WHERE uuid = '12345')
END