SQL problem

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:

zusammenfalten · markieren

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.

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

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

zusammenfalten · markieren

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

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**

markieren

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

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

zusammenfalten · markieren

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:

markieren

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 := ...

...

SQL problem

SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Re: SQL problem

Forumregeln

Bernhard Geyer Registriert seit: 13. Aug 2002 17.202 Beiträge Delphi 10.4 Sydney	#3 Re: SQL problem 7. Dez 2009, 13:55 You shoult add ABSQuery1.ExecSQL; in your for-loop. Windows Vista - Eine neue Erfahrung in Fehlern.
	Zitat

ToFaceTeKilla Registriert seit: 17. Mai 2006 Ort: Leipzig 283 Beiträge Delphi XE2 Professional	#5 Re: SQL problem 7. Dez 2009, 14:02 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? Billy Gerwitz "Bei der Softwareentwicklung suchen wir nicht den richtigen Weg, sondern den am wenigsten falschen." - frei nach V. Hillmann
	Zitat

mikhal Registriert seit: 11. Sep 2003 Ort: Linz am Rhein 796 Beiträge Delphi 11 Alexandria	#6 Re: SQL problem 7. Dez 2009, 14:05 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 Michael Kraemer Computer erleichtern die Arbeit... ...und die Erde ist eine Scheibe!
	Zitat

nanix (Gast) n/a Beiträge	#7 Re: SQL problem 7. Dez 2009, 14:09 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);
	Zitat