[Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
Hi folks, Can someone please give me a hint with this problem. I'm new to Firebird, but I needed a database which could be used in embedded fashion, and seemed to have a choice of one under Linux. What I need to do is to return a value generated by a Firebird generator into my Pascal code. Doing it what seemed to me to be the obvious way returns zero rows, do I need to update an interim table and then query that table? Or is there some other method which I've missed? Brian. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
brian wrote: Hi folks, Can someone please give me a hint with this problem. I'm new to Firebird, but I needed a database which could be used in embedded fashion, and seemed to have a choice of one under Linux. What I need to do is to return a value generated by a Firebird generator into my Pascal code. Doing it what seemed to me to be the obvious way returns zero rows, do I need to update an interim table and then query that table? Or is there some other method which I've missed? Embedded in what way? I use Firebird on occasion as an alternative to PostgreSQL (i.e. in a client-server scenario), but I'd have thought for a single-system application SQLite would have been worth a look. Having said that, apart from Firebird's poor documentation I've had no significant problem querying and updating tables. What exactly are you trying to do? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues] -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
On Sat, 25 Apr 2015, brian wrote: Hi folks, Can someone please give me a hint with this problem. I'm new to Firebird, but I needed a database which could be used in embedded fashion, and seemed to have a choice of one under Linux. What I need to do is to return a value generated by a Firebird generator into my Pascal code. Doing it what seemed to me to be the obvious way returns zero rows, do I need to update an interim table and then query that table? Or is there some other method which I've missed? Do a SELECT GEN_ID(YOUR_GENERATOR,1) AS THEVALUE FROM RDB$DATABASE in a TSQLQuery instance. Then call "Open" and you can get the value. Something like: With TSQLQuery.Create(Nil) do try Database:=MyDatabase; SQL.Text:='SELECT GEN_ID(YOUR_GENERATOR,1) AS THEVALUE FROM RDB$DATABASE'; Open; MyGenValue:=Fields[0].AsLargeInt; Finally Free; end; Michael. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
On Sat, 25 Apr 2015 14:12:44 +, you wrote: >brian wrote: >> Hi folks, >> >> Can someone please give me a hint with this problem. I'm new to >> Firebird, but I needed a database which could be used in embedded >> fashion, and seemed to have a choice of one under Linux. >> >> What I need to do is to return a value generated by a Firebird >> generator into my Pascal code. Doing it what seemed to me to be the >> obvious way returns zero rows, do I need to update an interim table >> and then query that table? Or is there some other method which I've >> missed? > >Embedded in what way? I use Firebird on occasion as an alternative to >PostgreSQL (i.e. in a client-server scenario), but I'd have thought for >a single-system application SQLite would have been worth a look. > Embedded as in there is no requirement for the user to do anything except unpack a zip file and run the executable. No installation of any kind of server on the host system. >Having said that, apart from Firebird's poor documentation I've had no >significant problem querying and updating tables. What exactly are you >trying to do? > Retrieve a value generated by a Firebird generator (in other words, an autoincremented value stored within the database) and get that incremented value into a Pascal variable. The SQL code to increment the value in the Firebird database is SELECT (,) FROM RDB$DATABASE; but I'm struggling to find a way to retrieve that incremented value. Simply attaching a data source to the query returns zero rows when the SQL is executed. I'm wondering whether I need to use a dummy table and then use the generator to update a field, then retrieve the data with a separate query. Brian. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
On Sat, 25 Apr 2015 16:18:18 +0200 (CEST), you wrote: > > >On Sat, 25 Apr 2015, brian wrote: > >> >> Hi folks, >> >> Can someone please give me a hint with this problem. I'm new to >> Firebird, but I needed a database which could be used in embedded >> fashion, and seemed to have a choice of one under Linux. >> >> What I need to do is to return a value generated by a Firebird >> generator into my Pascal code. Doing it what seemed to me to be the >> obvious way returns zero rows, do I need to update an interim table >> and then query that table? Or is there some other method which I've >> missed? > >Do a > >SELECT GEN_ID(YOUR_GENERATOR,1) AS THEVALUE FROM RDB$DATABASE > >in a TSQLQuery instance. Then call "Open" and you can get the value. > >Something like: > >With TSQLQuery.Create(Nil) do > try > Database:=MyDatabase; > SQL.Text:='SELECT GEN_ID(YOUR_GENERATOR,1) AS THEVALUE FROM RDB$DATABASE'; > Open; > MyGenValue:=Fields[0].AsLargeInt; > Finally > Free; > end; > >Michael. Ahh.. OK, I knew it was something obvious. Thanks, Michael. I guess I've forgotten more of my (Oracle) SQL than I thought. I see what I've been doing wrong. Brian. -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
2015.04.25. 16:52 keltezéssel, brian írta: Retrieve a value generated by a Firebird generator (in other words, an autoincremented value stored within the database) and get that incremented value into a Pascal variable. The SQL code to increment the value in the Firebird database is SELECT (,) FROM RDB$DATABASE; but I'm struggling to find a way to retrieve that incremented value. Simply attaching a data source to the query returns zero rows when the SQL is executed. I'm wondering whether I need to use a dummy table and then use the generator to update a field, then retrieve the data with a separate query. Hi, Use RETURNING clause with INSERT or UPDATE is a better solution. Gabor -- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
Re: [Lazarus] Returning value from a Firebird (embedded) generator into Lazarus code
On Sat, 25 Apr 2015, Gabor Boros wrote: 2015.04.25. 16:52 keltezéssel, brian írta: Retrieve a value generated by a Firebird generator (in other words, an autoincremented value stored within the database) and get that incremented value into a Pascal variable. The SQL code to increment the value in the Firebird database is SELECT (,) FROM RDB$DATABASE; but I'm struggling to find a way to retrieve that incremented value. Simply attaching a data source to the query returns zero rows when the SQL is executed. I'm wondering whether I need to use a dummy table and then use the generator to update a field, then retrieve the data with a separate query. Hi, Use RETURNING clause with INSERT or UPDATE is a better solution. Correct but this is not a solution if you just need the value, without INSERT or UPDATE statement. Michael.-- ___ Lazarus mailing list Lazarus@lists.lazarus.freepascal.org http://lists.lazarus.freepascal.org/mailman/listinfo/lazarus
