Recent

Author Topic: TsqlQuery Insertsql  (Read 3970 times)

talorigomat

  • Jr. Member
  • **
  • Posts: 96
TsqlQuery Insertsql
« on: October 19, 2017, 07:05:14 pm »
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][+][-]
  1. select org.ORGID, org.ORG_NME, org.LEVEL_NUM,
  2.       org.parent, org.Inuse, och.depth
  3. from ORGANISATION org
  4. inner join ORGCHART och on och.CHILDID= org.ORGID
  5. where och.PARENTID = 1 and depth = 1

and the insertsql is set as:

Code: Pascal  [Select][+][-]
  1. INSERT INTO ORGANISATION (ORG_NME, LEVEL_NUM, PARENT, INUSE)
  2.  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][+][-]
  1.     dmMain.qryDiv.Append;
  2.     dmMain.qryDiv.FieldByName('Orgid').Required:=False;   <--value for this field is auto generated by database
  3.     dmMain.qryDiv.FieldByName('Parent').AsInteger:= 1;
  4.     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?
Lazarus 1.8.0Rc4, Windows 10

sash

  • Sr. Member
  • ****
  • Posts: 366
Re: TsqlQuery Insertsql
« Reply #1 on: October 19, 2017, 09:07:16 pm »
Shouldn't it be Params.ParamByName instead FieldByName?
Lazarus 2.0.10 FPC 3.2.0 x86_64-linux-gtk2 @ Ubuntu 20.04 XFCE

rvk

  • Hero Member
  • *****
  • Posts: 6111
Re: TsqlQuery Insertsql
« Reply #2 on: October 19, 2017, 10:14:00 pm »
Shouldn'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.
« Last Edit: October 19, 2017, 10:15:47 pm by rvk »

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Re: TsqlQuery Insertsql
« Reply #3 on: October 20, 2017, 10:51:55 am »
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.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

taazz

  • Hero Member
  • *****
  • Posts: 5368
Re: TsqlQuery Insertsql
« Reply #4 on: October 20, 2017, 11:26:11 am »
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][+][-]
  1.   dmMain.qryDiv.Fields[3].Value := 1;
  2.  
Good judgement is the result of experience … Experience is the result of bad judgement.

OS : Windows 7 64 bit
Laz: Lazarus 1.4.4 FPC 2.6.4 i386-win32-win32/win64

 

TinyPortal © 2005-2018