InsertRecord, ApplyUpdates

PaulReeves · Post by **PaulReeves** » Wed 21 Dec 2011 21:10

Clearly, I'm missing something obvious here (wouldn't be the first time). I have an application that uses a TPgQuery and displays the data on a TDBgrid. I allow the user to add data to the Query via a CSV file, using Query.InsertRecord. The new records are displayed on the DBgrid correctly. However, when I commit the data, I always get a blank record with all "NULL" data. Cached, not cached ... it always writes a blank record to the database table with no reported errors.

Sound familiar to anyone, or are there any debugging hints I should be pursuing?

Update: I installed dbMonitor (very nice, btw) and can see that in some cases, the SQL result of an "InsertRecord" method is:

INSERT INTO meas."EnImpact" DEFAULT VALUES

The same procedure results in the expected result in other cases (it is repeatable). So now my question is limited to: What causes the InsertRecord method to use "DEFAULT VALUES" instead of the specified array of intended fields?

bork · Post by **bork** » Thu 22 Dec 2011 09:54

Hello

I could not reproduce the problem.
Please send me a complete small sample to borism*devart*com to demonstrate it, including a script to create and fill table.

PaulReeves · Post by **PaulReeves** » Thu 22 Dec 2011 15:47

Thank you for replying so quickly. I believe I tracked down the cause of the default record being added instead of a populated one. For the cases the work as expected, the SQL script used to get the source table is a simple Select:

Code: Select all

SELECT 
"EnImpact"."Index",
"EnImpact"."EnergyImpactID",
"EnImpact"."Version",
"EnImpact"."VersionSrc",
"EnImpact"."LastMod",
"EnImpact"."IOU",
"EnImpact"."BldgType",
"EnImpact"."BldgVint",
"EnImpact"."BldgHVAC",
"EnImpact"."BldgLoc",
"EnImpact"."NormUnit",
"EnImpact"."NumUnits",
"EnImpact"."MeasArea",
"EnImpact"."ScalBasis",
"EnImpact"."ACustEUkWh", "EnImpact"."ACustEUkW",  "EnImpact"."ACustEUtherm",
"EnImpact"."ACustWBkWh", "EnImpact"."ACustWBkW",  "EnImpact"."ACustWBtherm",
"EnImpact"."AStdWBkWh",  "EnImpact"."AStdWBkW",   "EnImpact"."AStdWBtherm",
"EnImpact"."AStdEUkWh",  "EnImpact"."AStdEUkW",   "EnImpact"."AStdEUtherm",
"EnImpact"."ElecImpProfileID", "EnImpact"."GasImpProfileID"
FROM meas."EnImpact" 
WHERE "EnImpact"."EnergyImpactID" = 'RE-HV-ResEvapAC-17p4S' AND "EnImpact"."Flag" > 0 
ORDER BY "IOU", "BldgType", "BldgVint", "BldgHVAC", "BldgLoc";

The result of an InsertRecord command in this case is:

Code: Select all

INSERT INTO meas."EnImpact"
  ("Index", "EnergyImpactID", "Version", "VersionSrc", "LastMod", "IOU", "BldgType", "BldgVint", "BldgHVAC", "BldgLoc", "NormUnit", "NumUnits", "MeasArea", "ScalBasis", "ACustEUkWh", "ACustEUkW", "ACustEUtherm", "ACustWBkWh", "ACustWBkW", "ACustWBtherm", "AStdWBkWh", "AStdWBkW", "AStdWBtherm", "AStdEUkWh", "AStdEUkW", "AStdEUtherm", "ElecImpProfileID", "GasImpProfileID")
VALUES
  ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28)

But for the cases that write a blank, default record, the SQL script is calculating many of the fields:

Code: Select all

SELECT 
"EnImpact"."Index", 
"EnImpact"."EnergyImpactID", 
"EnImpact"."Version", 
"EnImpact"."VersionSrc", 
"EnImpact"."LastMod", 
"Com-InLtg-Exit"."IOU" as "IOU", 
"Com-InLtg-Exit"."BldgType" AS "BldgType", 
"Com-InLtg-Exit"."BldgVint" AS "BldgVint", 
"Com-InLtg-Exit"."BldgHVAC" AS "BldgHVAC", 
"Com-InLtg-Exit"."BldgLoc" AS "BldgLoc", 
"EnImpact"."NormUnit", 
"EnImpact"."NumUnits", 
"EnImpact"."MeasArea", 
"EnImpact"."ScalBasis", 
("EnImpact"."ACustEUkWh"   * 36) AS "ACustEUkWh", 
("EnImpact"."ACustEUkW"    * 36) AS "ACustEUkW", 
("EnImpact"."ACustEUtherm" * 36) AS "ACustEUtherm", 
("EnImpact"."ACustEUkWh"   * 36 * CAST("kWh/kWh" AS float))   AS "ACustWBkWh", 
("EnImpact"."ACustEUkW"    * 36 * CAST("kW/kW" AS float))     AS "ACustWBkW", 
("EnImpact"."ACustEUkWh"   * 36 * CAST("therm/kWh" AS float)) AS "ACustWBtherm", 
("EnImpact"."ACustEUkWh"   * 10) AS "AStdEUkWh", 
("EnImpact"."ACustEUkW"    * 10) AS "AStdEUkW", 
("EnImpact"."ACustEUtherm" * 10) AS "AStdEUtherm", 
("EnImpact"."ACustEUkWh"   * 10 * CAST("kWh/kWh" AS float))   AS "AStdWBkWh", 
("EnImpact"."ACustEUkW"    * 10 * CAST("kW/kW" AS float))     AS "AStdWBkW", 
("EnImpact"."ACustEUkWh"   * 10 * CAST("therm/kWh" AS float)) AS "AStdWBtherm", 
"EnImpact"."ElecImpProfileID", "EnImpact"."GasImpProfileID" 
FROM meas."EnImpact" 
INNER JOIN ie."Com-InLtg-Exit" on "EnImpact"."BldgType" = "Com-InLtg-Exit"."BldgType" AND "EnImpact"."BldgLoc" = "Com-InLtg-Exit"."BldgLoc" AND "EnImpact"."IOU" = "Com-InLtg-Exit"."IOU" 
WHERE "EnImpact"."EnergyImpactID" = 'Com-Iltg-dWatt-IEbase-Exit'
ORDER BY "IOU", "BldgType", "BldgVint", "BldgHVAC", "BldgLoc";

The result of an InsertRecord command is now:

Code: Select all

INSERT INTO meas."EnImpact" DEFAULT VALUES

Perhaps a more informed user would not expect the latter case to work; I can certainly develop a work-around.

bork · Post by **bork** » Mon 26 Dec 2011 10:01

Hello

As I said in my previous post, we need SQL script for creating "EnImpact" and "Com-InLtg-Exit". Without these tables it will be very difficult to reproduce your issue.

Also please provide us the values that you were trying to insert in the last query, for example:
"EnImpact"."Index" = 1
"EnImpact"."EnergyImpactID" = 123
"EnImpact"."Version" = "ver 1"
...
etc.