SQL problem

[nanix]

Hi

i wrote this code this morning but apparently my SQL knowledge is too short for this.Why it won't add records

Delphi-Quellcode:

for hwm_index := 0 to NBMonitors - 1 do begin
CPUIDSDK_GetHardwareMonitorName(hwm_index, ptrName);
ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Add('Insert into Sensors (Field,HWM_ID,Sensor_ID,Sensor_Class,Monitor_Name) values ( :Field, :hwm_id, :sensor_id,:sensor_class,:Monitor_Name );');
ABSQuery1.ExecSQL;

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_VOLTAGE)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_VOLTAGE);
end;

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_TEMPERATURE)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_TEMPERATURE);
end;

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_FAN)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_FAN);
end;
end;




end;

ABSQuery1.ExecSQL; on every loop did the trick

[mikhal]

Look at this:

Delphi-Quellcode:

ABSQuery1.SQL.Clear; 
ABSQuery1.SQL.Add('Insert into Sensors (Field,HWM_ID,Sensor_ID,Sensor_Class,Monitor_Name) values ( :Field, :hwm_id, :sensor_id,:sensor_class,:Monitor_Name );'); 

// Probably here prepare the Query!
// ABSQuery1.Prepared := True;

// fill SQL only once, the parameters will be filled in your for-struct
// where is the value for :Field? you must fill it!
for hwm_index := 0 to NBMonitors - 1 do begin 
  CPUIDSDK_GetHardwareMonitorName(hwm_index, ptrName); 

  // missing value for :Field... like following
  // ABSQuery1.ParamByName('Field').AsInteger = 4711; 

  for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_VOLTAGE)  - 1 do begin 
    ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index); // better: ABSQuery1.ParamByName('HWM_ID').Asinteger:=hwm_index; 
    ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname); 
    ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index); 
    ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_VOLTAGE); 
  end; 

  for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_TEMPERATURE)  - 1 do begin 
    ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index); 
    ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname); 
    ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index); 
    ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_TEMPERATURE); 
  end; 
 
  for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_FAN)  - 1 do begin 
    ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index); 
    ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname); 
    ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index); 
    ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_FAN); 
  end; 

  ABSQuery1.ExecSQL; // exec SQL after filling parameters

end;

greetings
mikhal

[Bernhard Geyer]

You shoult add

ABSQuery1.ExecSQL; in your for-loop.

[nanix]

Thanks 2 all , i thought everytime i need to execute SQL

But now if i want to update SQL i do it like this

Delphi-Quellcode:

procedure TForm3.Timer1Timer(Sender: TObject);
var
Value,Max,Min : single;
sensor_index,hwm_index,cpu_index,core_index,iValue,nbsensors,nbmonitors,
currOffset,lastOffset:integer;
szName : array[0..512] of Char;
ptrName : PChar;
sField,sMax,sMin,sValue:String;
begin
 ptrname:=szname;
 NbMonitors := CPUIDSDK_GetNumberOfHardwareMonitors();

if Item then begin


for hwm_index := 0 to NBMonitors - 1 do begin
CPUIDSDK_GetHardwareMonitorName(hwm_index, ptrName);
ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Add('Insert into Sensors (HWM_ID,Sensor_ID,Sensor_Class,Monitor_Name) values ( :hwm_id, :sensor_id,:sensor_class,:Monitor_Name );');


for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_VOLTAGE)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_VOLTAGE);
ABSQuery1.ExecSQL;
end;

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_TEMPERATURE)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_TEMPERATURE);

end;

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_FAN)  - 1 do begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Monitor_Name').AsString:=pansichar(ptrname);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_FAN);

end;
end;//end of loop

ABSQuery1.ExecSQL;
Item:=false;
end
else begin
ABSQuery1.SQL.Add('UPDATE Sensors SET Field="'   +sField+'"'     +','+'Value="'+sValue+'"'+','  +','+'Min="'+sMin+'"'+','  +','+'Max="'+sMax+'"'+',''WHERE (:HWM_ID,:Sensor_Class,:Sensor_ID);');
ABSQuery1.SQL.Clear;


for hwm_index := 0 to NBMonitors - 1 do

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_VOLTAGE)  - 1 do begin
if CPUIDSDK_GetSensorInfos(hwm_index,
sensor_index,
SENSOR_CLASS_VOLTAGE,
ptrName,
iValue,
Value,
Min,
Max)
then begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_VOLTAGE);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);

sField:=pansichar(ptrname);
sValue:=Format('%.3f V', [Value]);
sMin:=Format('%.3f V', [Min]);
sMax:=Format('%.3f V', [Max]);

ABSQuery1.ExecSQL;


end;
end;
end;






end;//End of loop

[ToFaceTeKilla]

Dont know ABSSql but shouldn´t he use mikhals solution + ABSQuery1.ExecSQL; in EVERY for-loop (like he added in his 1st post) and not at the end?

[mikhal]

Yes, it was my mistake. You must use ABSQuery1.ExecSQL in the last line before 'end;' of every For-loop.

Your attempt to update your table will be destroyed with the line 'ABSQuery1.SQL.Clear;'. Put the line first, then the statement.

Mikhal

[nanix]

Is there eqvivalent of Table.Locate function in SQL like this and parameters
To update Fields based on parameters


Would this work?
UPDATE Sensors SET (Field,Value,Max,Min) WHERE (:HWM_ID,:Sensor_Class,:Sensor_ID,:Field,:Max,:Min,:Value);

[mikhal]

Delphi-Quellcode:

  ABSQuery1.SQL.Text := 'UPDATE Sensors SET Field="' +sField+'"' +','+'Value="'+sValue+'"'+',' +','+'Min="'+sMin+'"'+',' +','+'Max="'+sMax+'"'+' WHERE hwm_id = :HWM_ID and sensor_class = :Sensor_Class and sensor_id = :Sensor_ID';
  AbsQuery1.ParamByName('HWM_ID').AsInteger := ...
...

Mikhal

[nanix]

Are you sure it will work?

[quote]First chance exception at $75B6B727. Exception class EABSException with message 'Token '=' expected, but 'Min' found at line 1, column 39 - Native error: 30221'. Process Project1.exe (4708)[/quote

Delphi-Quellcode:

end //Creating Stoped
else begin //We start update
ABSQuery1.SQL.Clear;
ABSQuery1.SQL.Text := 'UPDATE Sensors SET Field="' +sField+'"' +','+'Value="'+sValue+'"'+',' +','+'Min="'+sMin+'"'+',' +','+'Max="'+sMax+'"'+' WHERE hwm_id = :HWM_ID and sensor_class = :Sensor_Class and sensor_id = :Sensor_ID';






for hwm_index := 0 to NBMonitors - 1 do

for sensor_index := 0 to CPUIDSDK_GetNumberOfSensors(hwm_index,  SENSOR_CLASS_VOLTAGE)  - 1 do begin
if CPUIDSDK_GetSensorInfos(hwm_index,
sensor_index,
SENSOR_CLASS_VOLTAGE,
ptrName,
iValue,
Value,
Min,
Max)
then begin
ABSQuery1.ParamByName('HWM_ID').AsString:=inttostr(hwm_index);
ABSQuery1.ParamByName('Sensor_Class').AsString:=inttostr(SENSOR_CLASS_VOLTAGE);
ABSQuery1.ParamByName('Sensor_ID').AsString:=inttostr(sensor_index);

sField:=pansichar(ptrname);
sValue:=Format('%.3f V', [Value]);
sMin:=Format('%.3f V', [Min]);
sMax:=Format('%.3f V', [Max]);

ABSQuery1.ExecSQL;


end;
end;
end;

[ToFaceTeKilla]

Maybe you should cut out some + and some quotationmarks...

Zitat von mikhal:

Delphi-Quellcode:

  ABSQuery1.SQL.Text := 'UPDATE Sensors SET Field="' +sField+'",Value="'+sValue+'",Min="'+sMin+'",Max="'+sMax+'"WHERE hwm_id = :HWM_ID and sensor_class = :Sensor_Class and sensor_id = :Sensor_ID';
  AbsQuery1.ParamByName('HWM_ID').AsInteger := ...
...