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 :oops:

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 :mrgreen:

[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

Delphi-Quellcode:

ABSQuery1.ExecSQL;

in your for-loop.

[nanix]

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

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 +

Delphi-Quellcode:

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? :gruebel:

SQL-Code:

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:

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

[nanix]

Sorry was a little typo which happens if you type faast! :)

But now no updated records :(

Delphi-Quellcode:

Item:=false;
end
else begin
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]

What are :Sensor_Class, :HWM_ID, :Sensor_ID? Are these Delphi-Variables? Then you must quote and add them (if they are strings! otherwise you must convert them to string!)

Delphi-Quellcode:

'...WHERE (HWM_ID = '+:HWM_ID+') and (Sensor_Class = '+:Sensor_Class+') and (Sensor_ID ='+ :Sensor_ID+')';

And put brackets () around every logical statement!

edit: rubbish

[nanix]

I thought with parameters is good?

delphi variables are

Delphi-Quellcode:

var
sField,sMax,sMin,sValue:String;
begin

[mkinzler]

Zitat:

And put brackets () around every logical statement!

And why? Just makes statement less readable

Zitat:

What are :Sensor_Class, :HWM_ID, :Sensor_ID? Are these Delphi-Variables? Then you must quote and add them (if they are strings! otherwise you must convert them to string!)

No this are Parameters. Type is automatically set by dbms.

[ToFaceTeKilla]

Oh :oops: my fault. Just overlooked the whole parameter thing... Then move on Mr ;-) I don't know, how to address the parameters here.

Ok, brackets arent necessary. Just a habit of me oO

[nanix]

Yes setting long SQL texts is trouble for most 3rd party databases..

[mkinzler]

Ist just oen reason for parameters. But the main reasons are type-conversion, type formats (e.g. date values) and the performance boost of using prepared statements for mass import/updates etc.

[nanix]

I don't understand why it won't update Records :wall:

Edit:

Delphi-Quellcode:

Edit1.Text:=ABSQuery1.SQL.Text;

Edit1 reports this

SQL-Code:

UPDATE Sensors SET Field="",Value="",Min="",Max="" WHERE HWM_ID = :HWM_ID and Sensor_Class = :Sensor_Class and Sensor_ID = :Sensor_ID

[mkinzler]

Is an error shown or just nothing happens?

[ToFaceTeKilla]

Does the code compile? Do you get any error messages or arent there just any updated records?

If it runs and there are just no records updated then set breakpoints and step through your code. Maybe one of the functions (CPUIDSDK_GetNumberOfSensors or CPUIDSDK_GetSensorInfos) returns a wrong value?

[nanix]

No code compiles runs OK no errors.But ofcourse all functions work becouse previously some same functions are used to INSERT records :oops:

1.Set all parameters;

Delphi-Quellcode:

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);

2.Then populate the variables;

Delphi-Quellcode:

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

3.Execute SQL script;

Delphi-Quellcode:

ABSQuery1.ExecSQL;

I am speculating the SQL text... :|

[mkinzler]

Conversion is not needed

Delphi-Quellcode:

ABSQuery1.ParamByName('HWM_ID').Value := hwm_index);
ABSQuery1.ParamByName('Sensor_Class').Value := SENSOR_CLASS_VOLTAGE;
ABSQuery1.ParamByName('Sensor_ID').Value := sensor_index;

You could use here parameters too:

SQL-Code:

UPDATE Sensors SET Field = :field ,value = :Value, Min = :min, Max = :max WHERE HWM_ID = :HWM_ID and Sensor_Class = :Sensor_Class and Sensor_ID = :Sensor_ID';

[nanix]

I tryed executing after and it makes no diffrence :(

Delphi-Quellcode:

for hwm_index := 0 to NBMonitors - 1 do begin

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.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';
ABSQuery1.ExecSQL;
Edit1.Text:=ABSQuery1.SQL.Text;



end;
end;
end;
end;

[mkinzler]

Try parameters

[ToFaceTeKilla]

Zitat:

Zitat von nanix

I don't understand why it won't update Records :wall:

Edit:

Delphi-Quellcode:

Edit1.Text:=ABSQuery1.SQL.Text;

Edit1 reports this

SQL-Code:

UPDATE Sensors SET Field="",Value="",Min="",Max="" WHERE HWM_ID = :HWM_ID and Sensor_Class = :Sensor_Class and Sensor_ID = :Sensor_ID

Do you still get these empty strings? Looks like the Values ([Value],[Min],[Max]) are empty or Format can`t convert them?!
Maybe the parameter '%.3f' breaks the function (doen't know, never worked with Format)?

[nanix]

No i get this in edit1

SQL-Code:

UPDATE Sensors SET Field="VIN3",Value="1.536 V",Min="1.536 V",Max="1.536 V" WHERE HWM_ID = :HWM_ID and Sensor_Class = :Sensor_Class and Sensor_ID = :Sensor_ID

and it says this even tho its on timer 1 s interval :x

[ToFaceTeKilla]

Ok then i would guess its a database issue. What are the values of the parameters (:HWM_ID,...)? Are there database entries which match these? Because no match with "WHERE" - no update ;-)

[nanix]

I uploaded unit and db file i use its ABSOLUTE DATABASE.

[ToFaceTeKilla]

Sorry, but I don't know Absolute DB and how to use it. Can't you just lookup if your DB contains entries which match the parameters? Maybe you have something like a management tool for this kind of DB?

[nanix]

Its like any other DB..

http://www.componentace.com/download...hp?editionid=1

[ToFaceTeKilla]

Sorry i can't (and don't want to) install stuff, just to check your DB for you.
You can proceed as follows:
1.) Debug and check your parameters (:HWM_ID,...)
2.) Check the entries in your DB. If there is no way to access the DB from outside of Delphi (i wasn't able to find any tool) just make a query with 'SELECT * FROM...' and check the recordset.
3.) Compare the content of your DB with the values of your parameters. Is there any match? If not, then you probably there is a bug in the functions which fill the params!

[nanix]

I already fixed it :lol:

But thanks for support,parameters was a problem when you are writting such long SQL text readability suffers :dp:

[ToFaceTeKilla]

Fine. So what was the problem? I'm curious :-)