Just a quick question to the guys who were so good in offering
assistance with my postgres database client and server;

How do extract information from the database?

I did some work on a database using Delphi some years ago and I can't seem to remember how it works...

I remember that the Query automagically populates the DBGrid, but I can't remember how it functions.

What do I do if I want to get the next available receipt number from the database and use it on my form?

Le May 26, 2008 ? 2:43 PM, Dave Coventry a ?crit :

Just a quick question to the guys who were so good in offering assistance with my postgres database client and server; How do extract information from the database?

What kind of information ?

Damien,

Thanks for the reply.

The situation is getting pretty urgent.

What I'm doing is this:
The user enters in preliminary information which creates a sequential partial record in the database using nextval(''reg_seq'').

Further information is added to the record later.

What I need is to populate my form with the fields from the database.

I need to access the number of the record assigned by nextval(''reg_seq''), and then I need to get the fields from the record to place in edit boxes so that the user can update them....

Damien this is the code I'm using:

      sqlq:='INSERT INTO registration
(enquiryreferencenumber,fname,sname,initials,id,titlecombobox,postaladdy,town,country,pcode,cell,email,emailtld,enquirydate)
';
      sqlq:=sqlq+'VALUES
(nextval(''reg_seq''),'''+FName.Text+''','''+SName.Text+''','''+Initials.Text+''','''+ID.Text+''','''+TitleComboBox.Text+''','''+PostalAddy.Text+''','''+Town.Text+''','''+Country.Text+''','''+PCode.Text+''','''+Cell.Text+''','''+email.Text+''','''+emailTLD.Text+''','''+EnquiryDate.Text+''')';
      PQConnection1.Connected:=True;
      PQConnection1.ExecuteDirect('Begin Work;');
      PQConnection1.ExecuteDirect(sqlq);
      PQConnection1.ExecuteDirect('Commit Work;');
      PQConnection1.Connected:=False;
      SQLQuery1.SQL.Clear;
      SQLQUery1.SQL.Append('SELECT enquiryreferencenumber FROM
registration WHERE fname = '''+FName.Text+''' AND sname = '''+
Sname.Text+''';');
      SQLQuery1.Open;
      enquiryreferencenumber.Text:=SQLQuery1.Fields[0].AsString;
      SQLQuery1.Close;

The error I get is because SQLQuery1.Fields[0]. is out of range, so there is nothing in SQLQuery1.Fields.

(I'm also, rather clumsily trying to access the last record using the fname and sname; I would prefer to have a more reiable way of retrieving it)

Le May 27, 2008 ? 10:15 AM, Dave Coventry a ?crit :

Do not forget you can directly get the id with INSERT or UPDATE

CREATE TABLE Foo (
uID INT4 PRIMARY KEY DEFAULT nextval('reg_seq'),
Caption VARCHAR(255) NOT NULL
) WITHOUT OIDS;

The following query will returns the new id :
INSERT INTO Foo (Caption) VALUES ('A caption') RETURNING uID It is better than using currval()

If I understand well, you want to get the list of columns. I don't think it is the best way to do it. However here is the way to do it :

All tables in your current schema :
SELECT tablename FROM pg_tables WHERE schemaName = current_schema();

To get all rows :
SELECT ordinal_position,column_name,is_nullable,data_type FROM information_schema.columns
WHERE table_catalog = ? AND table_schema = current_schema() AND table_name = ? ORDER BY ordinal_position;

Table_catalog is the name of your database in your schema table_name is obviously the name of your table

Fetch the sequence first in a before post event in your app (in case you have a new record). Set the ID yourself. If you used a sequence as datatype in the DB, it won't generate a new one when an ID is passed.

Marc

Le May 27, 2008 ? 10:28 AM, Dave Coventry a ?crit :

As said before, you *should not* use nextval in your sql queries. Use a default value for the column instead and use the keyword RETURNING

PQConnection1.Connected:=True; PQConnection1.ExecuteDirect('Begin Work;');

Transactions are useless here.

I am not an expert of SQL using fpc but if I remember well you should have a `Count` value to know how many rows you get. I suppose that only means you got no results.

You should use `LIMIT 1` in your query to be sure to have a single row.

Instead of Clear/Append may be you should use SQLQuery1.SQL.Text :=
'<my query>' but ask for confirmation.

(I'm also, rather clumsily trying to access the last record using the fname and sname; I would prefer to have a more reiable way of retrieving it)

This should be your last record :
SELECT * FROM registration ORDER BY enquiryreferencenumber DESC LIMIT 1

If you want some advice about your table structure you can send me a private mail.

I would highly recommend you use GUID's for the unique row identifier. It will even be unique across all tables in the database as well.

Pros:
* You know the ID before you even do the insert.
* No extra round trips to the database to retrieve the ID
* Unique ID in the table and across the whole database.
* Easy to implement
* Works on all database servers (char(36) to store the GUID)
* Works great with many concurrent users working on the same table.

Cons:
* Yes it's longer than an Integer type and uses more space, but with today's hard drive sizes that is totally irrelevant.

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Okay, just supposing that I have the SQL Queries working ok, how do I access the fields from inside Lazarus?

If I query using "SELECT * FROM registration", where do the resultant fields end up?

Or perhaps I was on the right track and they ARE accessible from SQLQuery1.Fields[i].AsString?

Here are step-by-step instructions in using Databases with Lazarus (normal Delphi style).

http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=fcldbnotes http://z505.com/cgi-bin/powtils/docs/1.6/idx.cgi?file=index&unit=sqldb

A quick search on the Lazarus wiki found the following. I think they might be handy as well.

http://wiki.lazarus.freepascal.org/Contacts_Database http://wiki.lazarus.freepascal.org/Databases
http://wiki.lazarus.freepascal.org/Databases_in_Lazarus

Here is a post I found in the lazarus mailing list. There are many more.

------------[ copy ]----------------

You should never use FieldValues, this is a horrible construct which Delphi introduced for Visual Basic "compatibility".

You should use

   tFileName:=mainForm.SQLQuery1.FieldByName('TFileName').AsString;

instead.

Michael.
------------[ end ]----------------

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Graeme,

If I do as Damien suggests and use:

PQConnection1.ExecuteDirect(''INSERT INTO Foo (Caption) VALUES (''A caption'') RETURNING uID;')

and, assuming it returns the field uID, how do I place the uID field into an edit box(read only of-course!).

Graeme Geldenhuys wrote

I would highly recommend you use GUID's * Works on all database servers (char(36) to store the GUID)

char(32)?

Joao Morais

The size on disk is not relevant, but the size in memory is. Speed is an issue, and the smaller the data is, the more can be kept in memory, and the faster the engine can traverse it when doing queries.

It's the very reason why I don't use GUIDs as primary keys.

If you have a busy server, 8 CPU's, lots of memory, but 500 simultaneous connections, such seemingly unimportant details all of a sudden do become important.

Michael.

Le May 27, 2008 ? 1:05 PM, Dave Coventry a ?crit :

As Graeme said through his links. I think they are very good. With the keyword RETURNING, Your INSERT/UPDATE will have the same behavior than a SELECT statement.

I forgot to mention that you should prefer full column names (SELECT Col1,Col2... FROM...) instead of `*` (SELECT * FROM...)

Le May 27, 2008 ? 1:29 PM, Michael Van Canneyt a ?crit :

Indeed. It is a bad idea.

No, CHAR(36) in my case... :)
GUID's have the default format: '{xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx}'

I only remove the curly braces
eg: '72AE84A0-1258-4C81-B2CF-1FC27EFBCD9C'

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Memory is cheap too! :)

The pros still outweigh the cons.

* Guaranteed uniqueness, so we can easily merged different franchisees databases into one master database for statistics generation.
* No extra round trips to the DB Server (which could be over a slow connection on a remote site)
* DB Server independent
* Really easy to implement and guaranteed to work.
- MS SQL Server's @@identify prone to errors
- Firebird/Interface triggers don't return identity value like Postgress
- Batch ID retrieval (say 20 at a time) in combination with Amblers High/Low algorithm waists numbers. But saves on DB round trips.
etc...

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Damien Gerard wrote

As Graeme said through his links. I think they are very good. With the keyword RETURNING, Your INSERT/UPDATE will have the same behavior than a SELECT statement.

Yes, but how do I access whatever is returned ? Whether I run a SELECT or RETURNING, how do I get the data returned?
In other words, where does PQConnection store the data?

Le May 27, 2008 ? 2:14 PM, Graeme Geldenhuys a ?crit :

Anyway, with experience, you can not make code 100% database independant.
Each database has its features, strong and weak parts and you can not design the same DB structure in an efficient way

Do you know a server that can handle memory to keep a 2Gb database 500 times in memory ? And the database is expected to grow in size considerably. I don't even mention backup and restore times :(

I understand and very much appreciate the technical advantages of a GUID, but the database size at a certain point simply dwarfs the importance of all other factors...

I'm eagerly awaiting the Firebird 2.5 release, which should have proper SMP support in the superserver architecture. That should make it possible to keep the database in memory only once, instead of a copy per connection as in classical server. And then size suddenly will be much less of an issue.

But until then: size matters - to paraphrase 90% of spam in my mailbox ;-)

Michael.

Le May 27, 2008 ? 2:14 PM, Graeme Geldenhuys a ?crit :

Your problems a real. However I don't think it is the better way to do it.
Your app should not never be dependant of Primary keys. Finding the good id should be the work of you DB and not your app.

I agree others DB than postgres have not all required features but they have their own one.

It is not really a problem of disk usage (even if I disagree here : INT4/INT8 vs CHAR(36) * RowCount ...) but when you have huge data databases, milliseconds are (very) important and you can not afford to use indexes on varchar/char (whatever the DB is)

So remove also the "-" and save 4 bytes =)

Joao Morais

Damien Gerard wrote

Your app should not never be dependant of Primary keys. Finding the good id should be the work of you DB and not your app.

My app does not create the primary keys, it's the job of the Object Persistence Layer (tiOPF) that I use. Each business object (descendant of TtiObject) has a OID: TtiOID property. The developer can register what OID Manager to use (Strings, GUID, Integer, Int64 etc) You can even mix them (multiple OID Managers) in a single application.

The OPF manages the loading and persisting of my business objects. I simply call Load or Save. Even if I switch the backend storage from MS SQL Server to Firebird (or even CSV, Tab Files, XML, DBF etc), it makes no difference in my code. The process of loading/saving the data stays the same.

I also use proxy objects (I think that's what you call it). I load a 'lite' version of my objects (to conserve memory) and only load the 'full' remainder of the data per object when needed. So having many concurrent users with a large database doesn't cause much problems. We don't keep everything in memory, we free things off when not needed.

I agree others DB than postgres have not all required features but they have their own one.

And with a product as popular as MS SQL Server, @@identify is a nightmare. Knowing you have a guaranteed unique ID from the start is a big plus.

It is not really a problem of disk usage (even if I disagree here : INT4/INT8 vs CHAR(36) * RowCount ...) but when you have huge data databases, milliseconds are (very) important and you can not afford to use indexes on varchar/char (whatever the DB is)

When you have database tables with a couple million records you quickly learn never ever to do a 'select * from tablename'. You quickly become very specific with what data you need and only return that.

But now I'm lost... I can't remember what this original thread was about... ;-)

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Good point! :)

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Graeme Geldenhuys wrote

And with a product as popular as MS SQL Server, @@identify is a nightmare. Knowing you have a guaranteed unique ID from the start is a big plus.

Just nitpicking:

A guid is not guaranteed unique. It's just very very unlikely that doubles appear, given that it is a 64-bit number.

But: a chance of one in a million is something that happens every day...

Michael.

Le May 27, 2008 ? 3:46 PM, Graeme Geldenhuys a ?crit :

Actually you see the problem from another point of view and it is interresting.
I will take a glance at tiOPF.

When you have database tables with a couple million records you quickly learn never ever to do a 'select * from tablename'. You quickly become very specific with what data you need and only return that.

Sometimes no choice ... :(

But now I'm lost... I can't remember what this original thread was about... ;-)

Indeed :)

Michael Van Canneyt wrote

A guid is not guaranteed unique. It's just very very unlikely that doubles appear, given that it is a 64-bit number. But: a chance of one in a million is something that happens every day...

Ok, ok... :)
It's a chance we are willing to take.

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

Sure, it wasn't meant as a serious argument either... Irony and emails are not a good match :-)

Michael.

Oh well, I have my answer, so I'm happy...

Why? Btw you don't need to know the ID before store the objects since you are using a persistence framework.

Just nitpicking: A guid is not guaranteed unique. It's just very very unlikely that doubles appear, given that it is a 64-bit number.

It's very very very very unlikely, and it is a 128 bits number.

Joao Morais

As I said in a previous post, our business objects don't reference each other via there OID, so no it's not important for me. And yes the OPF frameworks takes care of the nitty gritty stuff.

For those not using the tools I use, might not be so luck. In example... Using the standard DB components in Delphi or Lazarus and using Master/Details grids and tables, you need to save the master record first, find out the unique identifier of the master record, include that in the SQL for the detail records.

In the this example you need a extra round trip to the database if your application can't generate a unique identifier itself. GUID's make this a no brainer. :)

Regards,
- Graeme -

_______________________________________________________ fpGUI - a cross-platform GUI toolkit using Free Pascal http://opensoft.homeip.net/fpgui/

I think you are wrong about Firebird.It doesn't keep database in memory , only page buffers which is adjustable and should be really set to lower values then for Firebird Superserver.

GUID's are bad however because of indexing. They should be reversed or something , I don't know exactly to limit time required to search over indexed GUID field in Firebird database.

Regards
Boguslaw

I know this very well, but then the queries run unacceptably slow. We are forced to increase the number of pages kept in memory, sometimes almost to the complete database size.

Michael.

Graeme,

I'd like to point your attention to the fact mentioned long time ago on Firebird list by Ann Harrison:

"As you are transforming your GUID, try to keep the least volatile bytes at the front and the most volatile at the end. Firebird uses prefix compression in its indexes and the saving in space on the index pages absolutely will improve performance on all but the most trivial data sets.

Ann"

Simply using GUID's in VARCHAR or CHAR column slowdown queries much after 4 millions of rows approached due to ineffectively used indexes.

Regards
Boguslaw

2008/5/28 Bogus?aw Brandys :

Thanks for the information.

Sorry, but this might sound dumb... What what exactly does this mean? Am I supposed to change the GUID somehow?

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Yes. It's best that the first characters of the GUID are the same as much as possible. This way the index can be compressed better.

With totally random GUIDs, this is of course not possible, and therefore the indexes on GUIDs are much larger than they would be with numerical IDs.

Michael.

Sorry,I don't know details either , just found a bunch of discussion from old times...

"Luke,

if you really have to use GUID's, than one possibillity would be to reverse the GUID, so that the
equal parts are not in front of the GUID-string. This speeds up index searches.
If you do not have to use GUID's, use Generators.

Uwe"

"straight GUIDs (e.g. generated by Delphi written application) has their significant part at the end which does not provide for optimal indexing. Use of the UDF generating UUID (reverse GUID) is said to improve performance over the PK dramatically.
As for fieldtype - there are some suggestions here on the gourp as to what datatype to use.
Here I quote a recent discussion with some pros and cons:

(read from the bottom perhaps)
It is a valid point, but you need to do the math for the specific problem being resolved to decide how valid. With small index column sizes you need much more detail than I have at my fingertips to compute a real value, but let's say that you go to a 4 byte integer key. You lose 34 bytes from the index entry footprint, giving 50-34=16 bytes per entry plus some overheads. On an 8k page this gives you roughly 500 entries. At two levels in the B+ tree you address 250,000 rows. At three levels you can address 125,000,000 rows.

If you table space is expected to grow to 4,000,000 rows then you still have to expand the index to three levels, but you can go a lot further on three levels than you can with a larger key.

GUID's are ideal for distributed systems, but are not the most efficient way of handling centralized systems.

It's a matter of the favorite hammer. Sometimes you need a paintbrush, and a hammer just won't do. You can't let your (or my) favorite solution be the only solution you know because it is not appropriate for every situation."

2008/5/28 Bogus?aw Brandys :

"straight GUIDs (e.g. generated by Delphi written application) has their significant part at the end which does not provide for optimal indexing. Use of the UDF generating UUID (reverse GUID) is said to improve performance over the PK dramatically.

I'll try and find the code for the 'UDF generating UUID (reverse GUID)' and create a tiOPF OID generator for it.

Thanks for the information!

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

2008/5/28 Graeme Geldenhuys :

Under Linux, it seems FPC gets the kernels UUID when you ask to create a GUID via CreateGUID(). Plus the GUID/UUID returned is totally random, to parts are the same as the previous request.

eg;

graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid ca65c9ce-b097-4ff0-8a1a-7f41628e9982
graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid c29650af-5562-4669-967d-5227402c0252
graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid f757c538-70eb-4432-b094-dc3ad5b824f5
graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid 4a8dca85-2040-4fe5-afa2-dfeb5066c731
graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid e576429b-fdf9-485a-b9f5-3319a2067b24
graemeg at graemeg:~$ cat /proc/sys/kernel/random/uuid f7335eb4-26bc-4467-8d99-04a3ef8702fd

So I can't see the point in reversing the returned string. Maybe Delphi or Windows works different and some parts are the same as the previous request.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

Yes:

The MAC address of the network card was used as part of the GUID (because normally the MAC address is unique). However, this was discovered to be 'traceable', so maybe the windows implementation has changed meanwhile.

Michael.

This is where the low-high-scheme may be a good appraoch.

Get the high part from the key server at startup or when needed because the lower part space is exhausted once and add a timestamp for the lower part on every new key. That way the ids are pretty compressable.

Marc

2008/5/28 Marc Santhoff :

Interesting...

Looking at the other OID Generators available in tiOPF. I see there is a OIDHex Generator. Similar to GUID. It uses 32 character in Hex format and the Ambler High/Low algorithm. What I don't like is that it works the same as the Int and Int64 generators. It requires a Next_ID table to store the last used numbers. The HexOID will be a lot more compressible as parts are similar.

But at least now I know that if the GUID's and Indexing speed becomes a issue (I'll do a lot more testing), I should be able to switch to the HexOID without any major hassles.

Regards,
- Graeme -

_______________________________________________
fpGUI - a cross-platform Free Pascal GUI toolkit
http://opensoft.homeip.net/fpgui/

This is the unit I use for this.

Hope it?s usefull for you.

Horacio

Graeme Geldenhuys escribi?:

-------------- next part --------------
An embedded and charset-unspecified text was scrubbed... Name: untUuid.pas
Url: http://www.lazarus.freepascal.org/pipermail/lazarus/attachments/20080528/e77c7c95/attachment-0001.pl