HMGforum.com

Posted: **Tue Mar 02, 2010 2:44 pm**

raumi75 wrote: I understand. After all there was no problem saving the data to pgrdd.

This is partially true

As I've already posted, according my tests (I must do a lot more yet) it is possible to save changes to the backend table, but you must respect some rules that are different that ones for standard RDDs.

With PostGre, if you want to add a record and put some value in an field of it, you could do the following:

Code: Select all


Append Blank

Replace SomeField With Somedata

The problem is that if 'SomeField' is not a primary key, the operation will fail with an terrible error message

.

This is reasonable since this is a rule imposed by the server, but it is different than the dbf RDDs behavior.

So, in a non-buffered environment if a Grid user adds a record and then add a value to a non-primary key field it can't be processed.

Working fully buffered will be the processing easier, but, that limitation will still exist.

So, I guess that the user should be informed at 'Save' time if a primary key field was not filled to avoid the fatal PostGre RDD error.

To handle that we should know which is the primary key field.

If Postgre RDD can inform that to us, there is no problem, if not, we should add a property to grid for that.

As I've already said, I must do more tests yet. I could missed something and I could have a wrong perspective about that.

Posted: **Tue Mar 02, 2010 3:15 pm**

IMHO, if we link grids and sql tables (data-bound), the SQL DB shall be updated by a record as a whole and not by every field (by sending an update query).

Posted: **Tue Mar 02, 2010 4:30 pm**

rathinagiri wrote:IMHO, if we link grids and sql tables (data-bound), the SQL DB shall be updated by a record as a whole and not by every field (by sending an update query).

This is my idea too and being the grid buffered now, it will be easy to do, but, if an user adds a new row and don't fill all the fields, leaving the primary key blank, we are in trouble

Posted: **Tue Mar 02, 2010 4:56 pm**

Roberto Lopez wrote:
raumi75 wrote: I understand. After all there was no problem saving the data to pgrdd.
This is partially true

As I've already posted, according my tests (I must do a lot more yet) it is possible to save changes to the backend table, but you must respect some rules that are different that ones for standard RDDs.

With PostGre, if you want to add a record and put some value in an field of it, you could do the following:
Code: Select all
Append Blank

Replace SomeField With Somedata
The problem is that if 'SomeField' is not a primary key, the operation will fail with an terrible error message .

This is reasonable since this is a rule imposed by the server, but it is different than the dbf RDDs behavior.
...

Yes, PostgreSQL will not accept your INSERT statement, if the server side rules are broken. Those rules are typically NOT NULL or REFERENCES to other tables. Anything else will not be accepted - not even for a millisecond. It really annoyed me, when I started with PostgreSQL, but now I understand, that it is a very important feature. No matter, what you do, you never get an entry that is not valid. Even if you lose electricity at a crucial point, you always have data, that makes sense. (If your rules are well-written)

I often use SERIAL fields as primary key. That way, PostgreSQL will fill in that field by itself and you do not need to worry about "Replace SomeField With Somedata" for the primary key.

Raumi

Posted: **Tue Mar 02, 2010 5:39 pm**

raumi75 wrote:
I often use SERIAL fields as primary key. That way, PostgreSQL will fill in that field by itself and you do not need to worry about "Replace SomeField With Somedata" for the primary key.

I don't knew about 'serial' fields in PostGre (I'm just researching about it) but you must consider that we should provide a generic solution suitable for most common situations.

If there is a way to know which field has a primary key via PostGre RDD, the implementation could work for any table. A validation process, telling the user when he left blank the key field could solve the situation in an 'elegant' way.

Posted: **Tue Mar 02, 2010 5:43 pm**

Roberto Lopez wrote:
rathinagiri wrote:IMHO, if we link grids and sql tables (data-bound), the SQL DB shall be updated by a record as a whole and not by every field (by sending an update query).
This is my idea too and being the grid buffered now, it will be easy to do, but, if an user adds a new row and don't fill all the fields, leaving the primary key blank, we are in trouble

If you use the primary key as a substitute for the recno, you can use a SERIAL field. Then the user does not need to type in a primary key value. (Actually, the user should never edit a serial-field. That causes trouble

)

But besides the primary key, there might be a lot of other rules. (Not Null-Constraints, Maximum numbers, foreign keys, etc.)

A dbf-file will accept a record, that does not make sense, but a well-structured SQL-Database will cause an error if a rule gets violated.

No matter, if you use SQL-Server or dbf-file, you need code that makes sure, you do not attempt to save a record that is not valid.

In PHP, you try to catch those errors early. Javascript is for the best user experience, because it is fast. At the next level, your php-code should check it (because some users turn javascript off and some users are evil) before sending the INSERT or UPDATE command. The server-side-constraint is only the third and last line of defense. Usually, your program should not cause an ugly SQL-error.

With hmg you can write VALID codeblocks for every control. In case of grid, it is the COLUMNVALID array. The userexperience is quick like javascript, but it can not be turned off. Unfortunately, if you have NOT NULL contraints, your VALID codeblocks only work, if the user entered the particular field. If the user presses Alt+S, before visiting all fields, the grid will try to save an invalid column and the programer can do nothing about it. If you use dbf, you now have an incomplete record. If you use SQL, you now get an ugly error.

I have two ideas to prevent this.

Would it be possible, to get an ONSAVE event for grid? That way, you could check your columns, before sending them.

Or could we have a VALID codeblock for a row? If a record does not comply with that codeblock, the user can not leave it (comparable with the valid codeblock of a text-field) and you can not save.

What do you think?

Raumi

Posted: **Tue Mar 02, 2010 5:54 pm**

Roberto Lopez wrote: If there is a way to know which field has a primary key via PostGre RDD, the implementation could work for any table.

Pgrdd has a way to read the primary keys. I have seen it in the source code. More precisely, we need a way, to find all columns with NOT NULL constraints. I will look into the source of pgrdd, to find something like that.

A few minutes ago, I thought, that a programmer will have to write VALID codeblocks by hand, that do the same as the server-side constraints. But your idea is a lot more intuitive. Look into the table-definition and let hmg generate that code. mmh. I like that.

But it looks difficult.

Posted: **Tue Mar 02, 2010 6:39 pm**

Roberto Lopez wrote: If there is a way to know which field has a primary key via PostGre RDD, the implementation could work for any table.

harbour-2.0.0/contrib/hbpgsql/tpostgre.prg

The class TPQQuery contains an array aKeys. Those are the primary keys. But that is the low-level-code. I do not know enough about harbour. I do not know if it can easily be accessed from your code.

You can however ask the database-server directly:

Code: Select all

SELECT c.attname FROM pg_class a, pg_class b, pg_attribute c, pg_index d, pg_namespace e 
             WHERE a.oid = d.indrelid
             AND a.relname = 'mytablename'
             AND b.oid = d.indexrelid
             AND c.attrelid = b.oid
             AND d.indisprimary
             AND e.oid = a.relnamespace
             AND e.nspname = 'public';

mytablename must be replaced by the table name. If any other schema but public is used, you must exchange it in the last line, too.

Posted: **Tue Mar 02, 2010 6:57 pm**

I have looked a little deeper into postgresql. It does store all table definitions in tables. In fact all rules (= constraints) are stored in the table "pg_constraint". In a perfect world, you could read them and generate corresponding VALID codeblocks. But there are so many possibilties. It just blows my mind for now.

Not only could you get the constraints from the SQL-server and automagically get validating code, you could also get the column-type and choose the right column-control (a checkbox for boolean, a numeric field for numbers, a combobox filled with the right data for a foreign key, etc.) That would be awesome and I have never heard of a program like this.

But that would be a huge task!

So, if we just had a ROWVALID codeblock in grid, a programer could write his own checking function.

Yours

Raumi

Posted: **Wed Mar 03, 2010 11:08 am**

raumi75 wrote: I have two ideas to prevent this.

Would it be possible, to get an ONSAVE event for grid? That way, you could check your columns, before sending them.

Or could we have a VALID codeblock for a row? If a record does not comply with that codeblock, the user can not leave it (comparable with the valid codeblock of a text-field) and you can not save.

What do you think?

Interesting...

Implementation of any of the both ideas needs some deep work, since currently, the buffer is intended for internal use only and access to it is very difficult.

The buffered data should be presented to the HMG programmer in a way that be easy to write validation code.

I'll think about that.

HMGforum.com

HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23

Re: HMG 3.0.23