Syntax error in From clause

[Serge_G 87]

1- you don'd need any trigger if you use generated identitity

2- for date use date type or timestamp type
3- for amountex use something like  NUMERIC(8,2) if currency

[taariq 0]

Good day

I got Flamerobin now to auto generate the id when I insert new record via Flamerobin
But on my Delphi side it is still asking for valid ID?

Sorry for all the question, but learning....

 

[taariq 0]

Good day

I got it to work now from Delphi side when I manual add the id...

Now I see that the sort by does not work and I have to do it by SQL..
So now I am trying to figure out how to sort the db grid to get last ID record to get the next ID - is this correct ?

please point me in the right direction....

 

Thank you
 

[Serge_G 87]

1 hour ago, taariq said:

But on my Delphi side it is still asking for valid ID?

Perhaps because you define your fields and let the parameter required value to true

27 minutes ago, taariq said:

Now I see that the sort by does not work and I have to do it by SQL..

Yes if the datasource of the grid is a Table I understand sort is not working, but one time it's depending on components you use 

On my side, I quite don't use FDTable, only FDQuery and FDUpdateObjects, but as I remember FDTable can be sorted using properties as IndexFieldName

 

PS. You don't indicate if you are on the VCL or the FMX side of Delphi and I don't remember seeing version you use
 

[Guest]

if "ID" is auto-increment by default on table, then, dont inform it on inserts --> you dont need know what next id, your database will know it!

INSERT INTO tableX ( MyName, MyBirth, etc...) VALUES( 'John', '01/01/1901', etc....)

UPDATE tableX set MyName = 'Mary'  WHERE ID= :ID   (by param)     .... ID = 2  (literal)

DELETE tableX WHERE ID= :ID   (by param)     .... ID = 2  (literal)

 

never allow updates IDentify values, because you can "break" all chain... of course, it's possible if using "CASCADE updates".

 

sorting records:

SELECT * FROM tableX  ORDER BY fieldX, FieldY, etc...  (it's no mandatary create indexes)

Edited February 22, 2022 by Guest

[taariq 0]

Good day

I am VCL

I got my table ordered correctly now via SQL
It is ordering DESC
I got the last ID now in a DBText2
 

So all I have to figure out now is how to add +1 to the value and I assume update the following...

 

iID:= StrToInt ( Inputbox ( 'ID' ,   'ID'  , '002'  ) ) ;

or

dmTaS.tblJobCard['ID' ] := iID ;

 

 

So the Delphi passes the next id on ....
I think...

 

 

 

[haentschman 92]

Hi...😋

Quote

table

...not query?

[Guest]

with Firebird (Interbase and others), if the field is Auto-Increment you dont worry about the next values... let the database control it.

you can discovery what next ID autoincrement using the "NEXT VALUE" or "GET_ID(GeneratorName, nIncrement)" on SQL expression, but really it's not necessary at all.

https://firebirdsql.org/refdocs/langrefupd21-nextvaluefor.html

https://www.firebirdsql.org/pdfmanual/html/generatorguide-sqlsyntax.html

 

GetId( MyIDField, 0) ==>   just catch the last value and no increment nothing!

[taariq 0]

Good day

Yes sorry I used FDQuery to sort table...
Sorry  still learning...
 

[taariq 0]

2 minutes ago, joaodanet2018 said:

GetId( MyIDField, 0) ==>   just catch the last value and no increment nothing! 

can you give me a code example and where will I use it - sorry dont understand 100%

[Guest]

my big advice would... go to Firebird site help (above) and read more info...ok?

GetID -> it's a internal function from Firebird, and rare use at all. but you can use some like this:

select GetID(myIDgenerator,0) as MyLastIDForTest, MyLastID, MyName, MyBirth, etc...   from  mytableX

 

now you'll have a new colunm in your FDQuery fields. you see?

Edited February 22, 2022 by Guest

[taariq 0]

31 minutes ago, joaodanet2018 said:

if "ID" is auto-increment by default on table, then, dont inform it on inserts --> you dont need know what next id, your database will know it! 

That is the issue I got ?
In Flamerobin I can add a record and it generates the id for the next record...
But in Delphi it asks for ID?

My code that I currently got where it asks for id...
I get error > Field 'ID' must have a value.

 

procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
//iID : integer;
begin
//asign values to input box
//iID:= StrToInt ( Inputbox ( 'ID' ,   'ID'  , '002'  ) ) ;

sOrderNo := Inputbox ( 'OrderNo' ,   'Enter OrderNo'  , 'QU000'  ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name'  ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD'  ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD'  ) ;
sAmountEx := Inputbox ( 'AmountEx' ,   'Enter Amount Ex'  , 'R '  ) ;
//insert values to DBgrid
dmTaS.tblJobCard.Insert ;
//dmTaS.tblJobCard['ID' ] := iID ;
dmTaS.tblJobCard['OrderNo' ] := sOrderNo ;
dmTaS.tblJobCard['JobName' ] := sJobName ;
dmTaS.tblJobCard['StartDate' ] := sStartDate ;
dmTaS.tblJobCard['CompletionDate' ] := sCompletionDate ;
dmTaS.tblJobCard['AmountEx' ] := sAmountEx ;
dmTaS.tblJobCard.Post ;
//refresh db grid
DBGrid1.DataSource.DataSet.Refresh;
//show mesagges

end;

 

But if I do it like the following and add the ID manually it works - so I sorted the query via sql to get the last ID - I have the last ID in DBText2 - so now trying to figure out how to + 1 and give the value to iID

procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
iID : integer;
begin
//asign values to input box
iID:= StrToInt ( Inputbox ( 'ID' ,   'ID'  , '002'  ) ) ;
sOrderNo := Inputbox ( 'OrderNo' ,   'Enter OrderNo'  , 'QU000'  ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name'  ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD'  ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD'  ) ;
sAmountEx := Inputbox ( 'AmountEx' ,   'Enter Amount Ex'  , 'R '  ) ;
//insert values to DBgrid
dmTaS.tblJobCard.Insert ;
dmTaS.tblJobCard['ID' ] := iID ;
dmTaS.tblJobCard['OrderNo' ] := sOrderNo ;
dmTaS.tblJobCard['JobName' ] := sJobName ;
dmTaS.tblJobCard['StartDate' ] := sStartDate ;
dmTaS.tblJobCard['CompletionDate' ] := sCompletionDate ;
dmTaS.tblJobCard['AmountEx' ] := sAmountEx ;
dmTaS.tblJobCard.Post ;
//refresh db grid
DBGrid1.DataSource.DataSet.Refresh;
//show mesagges

end;

 

 

 

[Stano 143]

I don't want to read your entire code. Please be aware of one thing. This is a forum to help solve problems. Not distance learning.

• As said: read the DB manual
• Read some general book about DB

Not to be bad:

• FB has generators. Study it
• Enter PrimaryKey using FireDAC. You will find it quickly
• You can specify PrimaryKey using

MyQuery.SQL.Text: =
   'INSERT INTO USERS' +
   'SELECT GEN_ID (DEPOSIT_ID, 1), CAST (: FKOWNERFLAT AS INTEGER), ...,' +

MyQuery.SQL.Text: =
   'INSERT INTO USERS' +
   'SELECT GEN_ID (DEPOSIT_ID, 1), CAST (: FKOWNERFLAT AS INTEGER), ...,' +

 

[taariq 0]

Good day

Thank you so much I will have a look - this is my first FlameRobin and Firecad database - I had it all working 100% with a Access database...
So now just trying to understand Flamerobin and Firecad ......

But for now if I want to take the ID as I got it in DBText2 and + 1 their how will I set the following to get the value from a label or the dbtext

iID:= StrToInt ( Inputbox ( 'ID' ,   'ID'  , '002'  ) ) ;

[Stano 143]

PrimaryKeys are an internal DB issue. You have nothing to do with them. The user must not suspect that such a thing exists.
I repeat: you must use the Generator to insert new PrimaryKeys.
If you need to know the new PrimaryKey, then

1. Get its value from Generator
2. Use it in the new record and for your purpose. If something goes wrong, you throw it away!

[Serge_G 87]

Why don't you use this in your code ?

 procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
begin
sOrderNo := Inputbox ( 'OrderNo' ,   'Enter OrderNo'  , 'QU000'  ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name'  ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD'  ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD'  ) ;
dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?)', [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// if you want to retrieve last id you can use ExecSQLScalar
//LID:=dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?) RETRUNING ID into :id',  [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// put LID as private or public of the form
// do refresh Grid 
end;

Note : I really dislike those InputBox it's so easy to have DBEdits in a form

 

Edited February 22, 2022 by Serge_G

[taariq 0]

14 minutes ago, Stano said:

PrimaryKeys are an internal DB issue. You have nothing to do with them. The user must not suspect that such a thing exists.
I repeat: you must use the Generator to insert new PrimaryKeys.

 

Good day

I did create Generator BUT for some reason Only works in Flamerobin and not in Delphi...

[taariq 0]

9 minutes ago, Serge_G said:

Why don't you use this in your code ?

 procedure TMainForm.Button1Click(Sender: TObject);
//declare my var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
begin
sOrderNo := Inputbox ( 'OrderNo' ,   'Enter OrderNo'  , 'QU000'  ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name'  ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD'  ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD'  ) ;
dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?)', [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// if you want to retrieve last id you can use ExecSQLScalar
//LID:=dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,amount) values (?,?,?,?,?) RETRUNING ID into :id',  [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// put LID as private or public of the form
// do refresh Grid 
end;

Thank you - let me do this one will get back to you asap
Thank you so much for your time....

 

[taariq 0]

Good day

I get runtime error? Sure I did something wrong - I did check my typing

 

[Serge_G 87]

3 minutes ago, taariq said:

Good day

I get runtime error? Sure I did something wrong - I did check my typing

 

Mine typing  it's ExecSQL or ExecSQLScalar

[taariq 0]

Good day
Thank you - getting closer now....

I updated the code to include amountex - hope I did it correct - it runs and then I get this error...

 

 

My current records

The updated code

 

procedure TMainForm.Button1Click(Sender: TObject);
//declare var to enter new job card
var sOrderNo, sJobName , sStartDate, sCompletionDate, sAmountEx: string ;
begin
sOrderNo := Inputbox ( 'OrderNo' ,  'Enter OrderNo'  , 'QU000' ) ;
sJobName := Inputbox ( 'JobName' ,   'Enter JobName'  , 'Company Name' ) ;
sStartDate := Inputbox ( 'StartDate' ,   'Enter Start Date'  , 'YYYY/MM/DD' ) ;
sCompletionDate := Inputbox ( 'CompletionDate' ,   'Enter Completion Date'  , 'YYYY/MM/DD' ) ;
sAmountEx := Inputbox ( 'AmountEx' ,   'Enter Amount Ex'  , 'R '  ) ;
dmTas.tblJobCard.Connection.ExecSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,AmountEx) values (?,?,?,?,?)', [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);

//
// if you want to retrieve last id you can use ExecSQLScalar
//LID:=dmTas.tblJobCard.Connection.ExeSQL('INSERT INTO jobCard (orderno,jobname,StartDate,Completiondate,AmountEx) values (?,?,?,?,?) RETRUNING ID into :id',  [sOrderNo,sJobName,sStartDate,sCompletionDate,sAmountEx]);
// put LID as private or public of the form
// do refresh Grid

end;

 

 

[Serge_G 87]

check your database and sequence value

[Guest]

47 minutes ago, Stano said:

PrimaryKeys are an internal DB issue. You have nothing to do with them.

Ok, that is true depending on context. A bit hard to say "nothing to do with them" considering you are communicating with the DB architect.

If you need to be informed about "new ID", then read up on Firebirds "RETURNING Clause".

IMHO it is the best way, most efficient to make your "DB client app" aware of that ID.

To agree with Stano, if you need that value in your application, it is not stupid to assume you have an architectural mistake. (I agree with Stano, not just the way he phrases it).

In older DACs and in SQL (esp FB flavour) it is very doable to query a generator, even increment one.

But the need to do that is mostly for "edge cases" or legacy apps.

[Serge_G 87]

You can also consider this way 

fdquery1.SQL

SELECT  r.ORDERNO, r.JOBNAME, r.STARTDATE, r.COMPLETIONDATE, r.AMOUNTEX,
    r.DESCRIPTION
FROM JOBCARD r ORDER BY r.ORDERNO

 

 

with fdupdateSQL sentences

DeleteSQL

DELETE FROM JOBCARD
WHERE ID = :OLD_ID

FetchRowSQL

SELECT ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION
FROM JOBCARD
WHERE ID = :OLD_ID

insertSQL

INSERT INTO JOBCARD
(ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE,
  AMOUNTEX, DESCRIPTION)
VALUES (:NEW_ORDERNO, :NEW_JOBNAME, :NEW_STARTDATE, :NEW_COMPLETIONDATE,
  :NEW_AMOUNTEX, :NEW_DESCRIPTION)
RETURNING ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION

modifySQL

UPDATE JOBCARD
SET ORDERNO = :NEW_ORDERNO, JOBNAME = :NEW_JOBNAME, STARTDATE = :NEW_STARTDATE,
  COMPLETIONDATE = :NEW_COMPLETIONDATE, AMOUNTEX = :NEW_AMOUNTEX,
  DESCRIPTION = :NEW_DESCRIPTION
WHERE ID = :OLD_ID
RETURNING ID, ORDERNO, JOBNAME, STARTDATE, COMPLETIONDATE, AMOUNTEX, DESCRIPTION

 

and just using Grid and Navigator
Or
 

procedure TForm23.Button1Click(Sender: TObject);
var abookmark : TBookMark;
begin
fdQuery1.DisableControls;
fdQuery1.Insert;
fdQuery1.FieldByName('oderno').AsString:=Edit1.Text;
fdQuery1.FieldByName('jobname').AsString:=Edit2.Text;
fdQuery1.FieldByName('StartDate').AsDateTime:=DateTimePicker1.Date;
fdQuery1.FieldByName('CompletionDate').AsDateTime:=DateTimePicker2.Date;
fdQuery1.FieldByName('amount').AsCurrency:=StrToFloatDef(Edit3.Text,0);
fdQuery1.Post;

// refreshing Grid
Abookmark:=fdquery1.GetBookmark;
fdQuery1.Open();
fdquery1.GotoBookmark(aBookmark);
// not sure of the above lines cause : open can change bookmarks methinks
fdQuery1.EnableControls;
end;

You can note here I give "good" type for data structure
 

CREATE TABLE JOBCARD
(
  ID INTEGER GENERATED BY DEFAULT AS IDENTITY  NOT NULL,
  ORDERNO VARCHAR(12),
  JOBNAME VARCHAR(100),
  STARTDATE DATE,
  COMPLETIONDATE DATE,
  AMOUNTEX NUMERIC(8,2),
  DESCRIPTION VARCHAR(50),  -- rename DESC (keyword) to DESCRIPTION
  CONSTRAINT PK_JOBCARD PRIMARY KEY (ID)
);

 

 

 

Edited February 22, 2022 by Serge_G

[taariq 0]

19 minutes ago, Serge_G said:

check your database and sequence value

It is finally working... Thank you so much
I am definitely going to redo it now and learn learn....

Thank you so much for your time....