Powerbuilder pour les completement Geeks !

Le forum (ô combien francophone) des utilisateurs de Powerbuilder.

Recherche rapide

Annonce

Certaines rubriques, dont des cours, sont uniquement visibles par les membres du forum ^^.
Dans la rubrique Liens & Références, vous avez accès à un sommaire de téléchargement, profitez-en !
Il existe maintenant un nouveau TOPIC "Votre CV en Ligne" accessible uniquement par demande.

#1 15-09-2010 18:33:40

mattdamon  
Le Tuniso-Parisien
Lieu: Livry-Gargan 93190
Date d'inscription: 29-12-2007
Messages: 569
Pépites: 89
Banque: 77,512,666,613,392,940

[RESOLU] MAJ Specify update Properties d'une DW

Bonsoir à tous,

Qulequ'un peux m'expliquer clairement la différence dans "Specify update Properties" entre  les ratios button  :

Where Clause for update/delete
Key Columns
Key and Updatable Columns
Key and Modified Columns

et

Key Modification
Use Delete then insert
Use Update

Je sais que pour pouvoir updater les données d'une datawindow dans la base il faut mettre à jour la proprietés de la DW en choisissant un de Where Clause for update/delete et un de Key Modification mais pourquoi je sais pas !!!

Merci d'avance

Dernière modification par mattdamon (24-09-2010 16:53:36)

Hors ligne

 

#2 15-09-2010 19:37:15

erasorz  
Admin
Lieu: Babylone
Date d'inscription: 23-11-2006
Messages: 5121
Pépites: 97,197
Banque: 2,147,483,647

Re: [RESOLU] MAJ Specify update Properties d'une DW

avant de poster, merci de consulter l'aide PB...

F1 / ctrl-c / ctrl-v =>

Specifying the WHERE clause for update/delete
Sometimes multiple users are accessing the same tables at the same time. In these situations, you need to decide when to allow your application to update the database. If you allow your application to always update the database, it could overwrite changes made by other users:



You can control when updates succeed by specifying which columns PowerBuilder includes in the WHERE clause in the UPDATE or DELETE statement used to update the database:


UPDATE table...SET column = newvalueWHERE col1 = value1AND col2 = value2 ... DELETEFROM tableWHERE col1 = value1AND col2 = value2 ...


Using timestamps Some DBMSs maintain timestamps so you can ensure that users are working with the most current data. If the SELECT statement for the DataWindow object contains a timestamp column, PowerBuilder includes the key column and the timestamp column in the WHERE clause for an UPDATE or DELETE statement regardless of which columns you specify in the Where Clause for Update/Delete box.

If the value in the timestamp column changes (possibly due to another user modifying the row), the update fails.

To see whether you can use timestamps with your DBMS, see Connecting to Your Database .

Choose one of the options in Table 21-1 in the Where Clause for Update/Delete box. The results are illustrated by an example following the table.

Table 21-1: Specifying the WHERE clause for UPDATE and DELETE  Option Result
Key Columns The WHERE clause includes the key columns only. These are the columns you specified in the Unique Key Columns box.
The values in the originally retrieved key columns for the row are compared against the key columns in the database. No other comparisons are done. If the key values match, the update succeeds.

Caution Be very careful when using this option. If you tell PowerBuilder only to include the key columns in the WHERE clause and someone else modified the same row after you retrieved it, their changes will be overwritten when you update the database (see the example following this table).

Use this option only with a single-user database or if you are using database locking. In other situations, choose one of the other two options described in this table.
Key and Updatable Columns The WHERE clause includes all key and updatable columns.
The values in the originally retrieved key columns and the originally retrieved updatable columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.

Key and Modified Columns The WHERE clause includes all key and modified columns.
The values in the originally retrieved key columns and the modified columns are compared against the values in the database. If any of the columns have changed in the database since the row was retrieved, the update fails.


Example
Consider this situation: a DataWindow object is updating the Employee table, whose key is Emp_ID; all columns in the table are updatable. Suppose the user has changed the salary of employee 1001 from $50,000 to $65,000. This is what happens with the different settings for the WHERE clause columns:

If you choose Key Columns for the WHERE clause, the UPDATE statement looks like this:

UPDATE EmployeeSET Salary = 65000WHERE Emp_ID = 1001

This statement will succeed regardless of whether other users have modified the row since your application retrieved the row. For example, if another user had modified the salary to $70,000, that change will be overwritten when your application updates the database.

If you choose Key and Modified Columns for the WHERE clause, the UPDATE statement looks like this:

UPDATE EmployeeSET Salary = 65000WHERE Emp_ID = 1001       AND Salary = 50000

Here the UPDATE statement is also checking the original value of the modified column in the WHERE clause. The statement will fail if another user changed the salary of employee 1001 since your application retrieved the row.

If you choose Key and Updatable Columns for the WHERE clause, the UPDATE statement looks like this:

UPDATE EmployeeSET Salary = 65000WHERE Emp_ID = 1001       AND Salary = 50000       AND Emp_Fname = original_value       AND Emp_Lname = original_value       AND Status = original_value       ...

Here the UPDATE statement is checking all updatable columns in the WHERE clause. This statement will fail if any of the updatable columns for employee 1001 have been changed since your application retrieved the row.

Specifying update when key is modified

The Key Modification property determines the SQL statements PowerBuilder generates whenever a key column—a column you specified in the Unique Key Columns box—is changed. The options are:

Use DELETE then INSERT (default)
Use UPDATE

How to choose a setting
Consider the following when choosing the Key Modification setting:

If multiple rows are changed, DELETE and INSERT always work. In some DBMSs, UPDATE fails if the user modifies two keys and sets the value in one row to the original value of the other row.
You might choose the setting here based on your DBMS triggers. For example, if there is an Insert trigger, select Use Delete then Insert.
If only one row can be modified by the user before the database is updated, use UPDATE because it is faster.


N'envoyez jamais un humain faire le travail d'un programme.

Hors ligne

 

#3 24-09-2010 13:34:23

Yanis  
Modérateur
Lieu: paris
Date d'inscription: 16-06-2010
Messages: 349
Pépites: 665
Banque: 150

Re: [RESOLU] MAJ Specify update Properties d'une DW

Salut,

     Si les informations données ci dessus répondent à ta question alors peut tu le mettre à résolu :   

Cdt
Yanis

Hors ligne

 

#4 24-09-2010 16:53:17

mattdamon  
Le Tuniso-Parisien
Lieu: Livry-Gargan 93190
Date d'inscription: 29-12-2007
Messages: 569
Pépites: 89
Banque: 77,512,666,613,392,940

Re: [RESOLU] MAJ Specify update Properties d'une DW

Yanis a écrit:

Salut,

     Si les informations données ci dessus répondent à ta question alors peut tu le mettre à résolu :   

Cdt
Yanis

Oui, désolé j'étais pas au courant   

Hors ligne

 

Pied de page des forums

Propulsé par FluxBB 1.2.22