TsqlQuery Insertsql
lazarus / 16-Databases

----------------------------------------------------------------------
talorigomat · 2017-10-19 17:05

I've read with interest the topic started by daveinhull as I've an
TSQLQuery component in which the select statement joins two tables:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i select
org.ORGID, org.ORG_NME, org.LEVEL_NUM,      org.parent, org.Inuse,
och.depthfrom ORGANISATION orginner join ORGCHART och on och.CHILDID=
org.ORGIDwhere och.PARENTID = 1 and depth = 1
and the insertsql is set as:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
ORGANISATION (ORG_NME, LEVEL_NUM, PARENT, INUSE) VALUES (:ORG_NME,
:Level_num, :parent,:inuse);
What I'd like to do is set the value for the fields level_num and
parent programatically when the user enters a value for Org_nme.  I've
tried:

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    
dmMain.qryDiv.Append;   
dmMain.qryDiv.FieldByName('Orgid').Required:=False;   <--value for
this field is auto generated by database   
dmMain.qryDiv.FieldByName('Parent').AsInteger:= 1;   
dmMain.qryDiv.FieldByName('level_num').AsInteger:=1;
however I get an error stating that the field 'Parent' cannot be
found.  What is the correct way to do this?

----------------------------------------------------------------------
sash · 2017-10-19 19:07

Shouldn't it be Params.ParamByName instead FieldByName?

----------------------------------------------------------------------
rvk · 2017-10-19 20:14

Quote from: sash on October 19, 2017, 09:07:16 pmShouldn't it be
Params.ParamByName instead FieldByName?
No, not for a TSQLQuery with a select statement.

Normally fpc can extract an insertsql but with a join that's not
possible. But after sql.insert you should be able to set the fields of
the insert statement.

Alternatively you can set the insert statement in the TSQLQuery.SQL
itself and then you can use parambyname before you do .ExecSql.

The only issue i can think of is case sensitivity.
But that could depend on the db used.

----------------------------------------------------------------------
mangakissa · 2017-10-20 08:51

I think sash is right. If you use datacomponents to insert a query,
SQLdb will use params automatically if insertsql is used. So if you
want to manually  params or parambyname must be used.

----------------------------------------------------------------------
taazz · 2017-10-20 09:26

rvk is right. the parameters will auto fill from the field values when
the method applyupdates is called what ever you put there will get
overwrite. It is futile to try to guess what is wrong with out a
compilable example and the complete error log/message. I can only
suggest to check for the field's name existence manually and use the
field index as a temporary workaround, eg
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i  
dmMain.qryDiv.Fields[3].Value := 1;