Devart Forums

Hi ...

I'm writing an app (Delphi 2010, indy 10), whe one of our customers call us we need to read his phone number, we're doing it with www.callerid.com hardware.

For that, I use indy component TidUDPServer VCL component incluided in delphi 2010 ide.

All works great, I get the phone number in the OnUDPRead event of this component...

The main purpose of the phone number is to get our customer data in a popup form, for that I need to query in our database for that phone number of our customer's.

That where I'm having problems to read the database, I use a TMyConnection and TMyQuery (5.90.0.60) but when I'm trying to query the database simple it doesn't work. The open TMyquey.open can't get any data.

I read that MyDac is thread safe and TidUSDServer works with threads, so I search for this article http://www.devart.com/forums/viewtopic. ... ght=thread and I write a single unit to query the database I test the unit in a button onclick event and I works, but not under TidUDPServer.OnUDPRead event.

Unit for query database:

unit pacienteDatos;

interface

uses
SySUtils, Classes, Db, MemDS, DBAccess, MyAccess,callerid_data;

type

// callerid_data unit....
{
TPaciente = record
especialidad_id : integer;
especialidad : String;
paciente_id : integer;
expediente : String;
nombre : String;
ciudad : String;
end;
TCaller_id = record
LineNumber : String;
inout : String;
startend : String;
timeofcall : String;
dateofcall : String;
PhoneNumber : String;
Paciente : Array of TPaciente;
end;
}

TPacienteDatos = class(TThread)
private
qDatos : TMyConnection;
qPaciente : TMyQuery;
fOwner: TComponent;
datos : TCaller_id;
protected
procedure Execute; override;
public
property datos_paciente:TCaller_id read datos write datos;
constructor Create(caller : TCaller_id);
destructor Destroy; Override;
end;

implementation

constructor TPacienteDatos.Create(caller : TCaller_id);
begin
inherited Create(True);
datos := caller;
qDatos := TMyConnection.Create(fOwner);
qPaciente := TMyQuery.Create(fOwner);
qPaciente.Connection := qDatos;
qDatos.ConnectionTimeout := 15;
qDatos.Database := 'diagnopack2011';
qDatos.Username := 'develop';
qDatos.Password := '3676';
qDatos.Server := 'odontologicas';
qDatos.LoginPrompt := False;
Execute;
end;

destructor TPacienteDatos.Destroy;
begin
FreeAndNil(qPaciente);
FreeAndNil(qDatos);
inherited;
end;

procedure TPacienteDatos.Execute;
var
i : integer;
begin
inherited;

i := 0;
qDatos.Connected := True;
with qPaciente do
begin
Close;
SQL.Clear;
SQL.Add('CALL callerid_buscar_paciente_contactos(:contacto);');
ParamByName('contacto').AsString := datos.PhoneNumber;
Open;
First;
while not Eof do
begin
SetLength(datos.Paciente, i+1);
datos_paciente.Paciente.paciente_id := FieldByName('newpacid').AsInteger;
datos_paciente.Paciente.especialidad_id := FieldByName('especialidadlkpid').AsInteger;
datos_paciente.Paciente.especialidad := FieldByName('especialidad').AsString;
datos_paciente.Paciente.expediente := FieldByName('expediente').AsString;
datos_paciente.Paciente.nombre := FieldByName('fullname').AsString;
datos_paciente.Paciente.ciudad := FieldByName('ciudad').AsString;
i := i + 1;
Next;
end;
Close;
end;
qDatos.Connected := False;
end;
end.

TidUDPServer.OnUDPRead event:


// NOTE: I use PerlRegEx unit from http://www.regular-expressions.info/delphi.html

procedure TfmCallerID.IdUDPServer_listenerUDPRead(AThread: TIdUDPListenerThread;
AData: TBytes; ABinding: TIdSocketHandle);
var
mensaje: UTF8String;
Regex1: TPerlRegEx;
Regex2: TPerlRegEx;
txt : UTF8String;
caller : TCaller_id;
forma : TfmPaciente;
datos : TPacienteDatos;
begin
Regex1 := TPerlRegEx.Create;
txt := TEncoding.UTF8.GetString(AData);
Regex1.RegEx := '\^\^\';
Regex1.Compile;
Regex1.Subject := txt;
if( (Regex1.Match) and (Length(txt) > 20) )then
begin
Regex2 := TPerlRegEx.Create;
Regex2.RegEx := '(\d{2}) ([IO]) ([ES]) (\d{4}) ([GB]) ((.)(\d)) ((\d{2})\/(\d{2}) (\d{2}):(\d{2}) [aApP][mM]) (.{8,15})';
Regex2.Compile;
Regex2.Subject := txt;
if Regex2.Match then
begin
Regex2.StoreGroups;
mensaje := 'Linea: ' + Regex2.Groups[1] + ''#13''#10'' +
'I/O:' + Regex2.Groups[2] + ''#13''#10'' +
'S/E:' + Regex2.Groups[3] + ''#13''#10'' +
'duracion: ' + Regex2.Groups[4] + ''#13''#10'' +
'fecha: ' + Regex2.Groups[9] + ''#13''#10'' +
'Tel.: ' + Regex2.Groups[14] + ''#13''#10'';
memTelefonos.Lines.Add('match:');
memTelefonos.Lines.Add(ABinding.PeerIP);
memTelefonos.Lines.Add(mensaje);
memTelefonos.Lines.Add('Original: ' + Regex2.Groups[0]);
memTelefonos.Lines.Add('');
memTelefonos.Lines.Add('');
caller.LineNumber := Regex2.Groups[1];
caller.inout := Regex2.Groups[2];
caller.startend := Regex2.Groups[3];
caller.timeofcall := Regex2.Groups[4];
caller.dateofcall := Regex2.Groups[9];
caller.PhoneNumber := Regex2.Groups[14];
end;
end;
if((caller.inout = 'I') AND (caller.startend = 'S')) then
begin
try
datos := TPacienteDatos.Create(caller);
finally
datos.Free;
end;
forma := TfmPaciente.Create(Self);
forma.paciente_contacto := caller.PhoneNumber;
forma.Show;
end;
end;

Any help will be appreciate..

Hello,

Please check that you set the contacto parameter for which the callerid_buscar_paciente_contactos stored procedure returns data. For this, you can set the breakpoint at the "SetLength(datos.Paciente, i+1);" line and check if it is executed.

Hello AndreyZ

Thanks for the answer, I alredy did it but I still get stuck..

1. I've rewrite my code I get this much cleaner solution, but I'm triying to use the TMyQuery has a part of unit and execute under the thread:

unit callerID_udp;

interface

uses
Classes, SysUtils, PerlRegEx, IdBaseComponent, IdComponent, IdUDPBase, IdUDPServer, IdSocketHandle, MyAccess;

type
TUDPSendCallerEvent = procedure(caller : TStrings; consulta:TMyquery) of object;

TcallerID_udp = class(TThread)

private
caller : TStrings;
coneccion : TMyConnection;
consulta : TMyQuery;
caller_id : TIdUDPServer;
caller_evento : TUDPSendCallerEvent;
caller_puerto : Word;
procedure get_filtro_callerid(AData:TBytes);
procedure UDPRead(AThread: TIdUDPListenerThread; AData: TBytes; ABinding: TIdSocketHandle);
procedure enviar_telefono;
procedure get_datos;
protected
procedure Execute; override;
public
constructor Create(puerto:word);
destructor Destroy; override;
property onCaller_evento : TUDPSendCallerEvent read caller_evento write caller_evento;
end;

var
EvHandler: TUDPSendCallerEvent;

implementation

{ TcallerID_udp }
constructor TcallerID_udp.Create(puerto:word);
begin
inherited Create(True);
caller_puerto := puerto;
caller := TStringList.Create;

coneccion := TMyConnection.Create(nil);
consulta := TMyQuery.Create(nil);
consulta.Connection := coneccion;
coneccion.ConnectionTimeout := 15;
coneccion.Database := 'diagnopack2011';
coneccion.Username := 'develop';
coneccion.Password := '3676';
coneccion.Server := 'odontologicas';
coneccion.LoginPrompt := False;
//coneccion.Pooling := true;
coneccion.Connected := True;
Execute;
end;


destructor TcallerID_udp.Destroy;
begin
caller_id.Active := False;
FreeAndNil(caller_id);
inherited;
end;

procedure TcallerID_udp.enviar_telefono;
begin
if Assigned(caller_evento) then
caller_evento(caller, consulta);
if Assigned(EvHandler) then
EvHandler(caller, consulta);
end;

procedure TcallerID_udp.Execute;
begin
inherited;
caller_id := TIdUDPServer.Create(nil);
caller_id.DefaultPort := caller_puerto;
caller_id.OnUDPRead := UDPRead;
caller_id.Active := true;
end;

procedure TcallerID_udp.get_datos;
begin
with consulta do
begin
Close;
SQL.Clear;
SQL.Add('CALL callerid_buscar_paciente_contactos(:contacto);');
ParamByName('contacto').AsString := caller.Strings[5];
Open;
end;

end;

// NOTE: I use PerlRegEx unit from http://www.regular-expressions.info/delphi.html
procedure TcallerID_udp.get_filtro_callerid(AData: TBytes);
var
mensaje: UTF8String;
Regex1: TPerlRegEx;
Regex2: TPerlRegEx;
txt : UTF8String;
begin
Caller.Clear;
Regex1 := TPerlRegEx.Create;
txt := TEncoding.UTF8.GetString(AData);
Regex1.RegEx := '\^\^\';
Regex1.Compile;
Regex1.Subject := txt;
if( (Regex1.Match) and (Length(txt) > 20) )then
begin
Regex2 := TPerlRegEx.Create;
Regex2.RegEx := '(\d{2}) ([IO]) ([ES]) (\d{4}) ([GB]) ((.)(\d)) ((\d{2})\/(\d{2}) (\d{2}):(\d{2}) [aApP][mM]) (.{8,15})';
Regex2.Compile;
Regex2.Subject := txt;
if Regex2.Match then
begin
Regex2.StoreGroups;
caller.Add(Regex2.Groups[1]);
caller.Add(Regex2.Groups[2]);
caller.Add(Regex2.Groups[3]);
caller.Add(Regex2.Groups[4]);
caller.Add(Regex2.Groups[9]);
caller.Add(Regex2.Groups[14]);
end;
end;

end;

procedure TcallerID_udp.UDPRead(AThread: TIdUDPListenerThread; AData: TBytes;
ABinding: TIdSocketHandle);
begin
inherited;
get_filtro_callerid(AData);
if caller.Count > 0 then // Check if caller is not empty
begin
get_datos;
Synchronize(enviar_telefono);
end;
end;

end.

2. The TidUDPServer component on the OnUDPRead event works pretty good, I get the phone numbers just great
but the problem is when I query the database in this procedure:

procedure TcallerID_udp.get_datos;
begin
with consulta do
begin
Close;
SQL.Clear;
SQL.Add('CALL callerid_buscar_paciente_contactos(:contacto);');
ParamByName('contacto').AsString := caller.Strings[5];
Open;
end;
end;

I get empty the query even thougt there's records in the DB that match the phone is inserted as a parameter (caller.Strings[5]; stores the phone number)inside the query (I've check caller.Strings[5] with the debugger to check in runtime the phone).
I've double check the store procedure few times with phones that callerid has read from the network...

Any help will be great!

Please check that the develop user has rights to read from the table that is used in the callerid_buscar_paciente_contactos stored procedure. If the correct rights are given, please post here a script to create the callerid_buscar_paciente_contactos stored procedure.