Schultz’s PowerBuilder Notes

Sybase and Nulls


If you have not read about how PowerBuilder treats nulls, you may want to read  nulls first.

Continue reading

Advertisements

January 7, 2009 Posted by | Database | , , , , | 1 Comment

Guaranteeing a Singleton


Because the datetime on the client machine may not be accurate, I wanted to write a function to get the current date and time from the server.  

For Sybase DBMS, this is simply achieved with the GetDate() function.  The problem is developing the SQL to return exactly one row everytime the function is called.  (It will still work if the SQL returns multiple rows, but you will have a lot of unecessary database traffic.)

Continue reading

September 19, 2008 Posted by | Database, Powerscript | , , , , | Leave a comment

Why Does the Show Plan Not Use an Index?


A table scan will be done in lieu of an index if:

  • One or more of the columns composing the index is primarily populated with nulls
  • The table has a small number of rows.
  • A join is being done between different data types (int and numeric(10.0) for example)

May 20, 2008 Posted by | Database | , , | Leave a comment

Avoiding the Use of Cursors in a Stored Procedure


This example Stored Proc (Sybase DBMS) loops through a result set one row at a time without the use of a cursor. It also has a recursive call.

Continue reading

May 20, 2008 Posted by | Database | , , , , , , , | 1 Comment

Sybase Error: 2403 “WARNING! Some character(s) could not be converted…


I was working on a application that I am not too familiar with. While running the app in the PowerBuilder developer environment, I was getting the following database error:

Database error code: 2403

Select error: WARNING! Some character(s) could not be converted into client’s character set. Unconverted bytes were changed to question marks (“?”).

Continue reading

May 12, 2008 Posted by | Database | , , , | Leave a comment

Temp Tables


How do I destroy a temp table (Sybase)

Example:

Continue reading

May 6, 2008 Posted by | Database, Powerscript | , , , , , | 1 Comment

Extended Attributes


What is the PowerSoft Repository?

The Powersoft repository (AKA Powersoft Catalog) is a series of 5 tables containing extended attributes pertaining to a data table and its columns. These attributes contain information such a s labels, headings, display formats, validation rules, and edit styles.

Do not confuse the database catalog with the repository. The database catalog contains column definitions, initial values, constraints, and referential integrity rules and has no relationship with the PowerSoft repository.

How can I modify a table with out dropping it and clobbering the extended attributes?

1. Use the “Object|Export syntax to log” menu selection to save the data table syntax to the painter log.

2. Select Design|Save Log As to save the log to a file. The log contains both the syntax and INSERT statements that populate the repository with the extended attributes for the table.

3. Make changes to the SQL statements in the saved file, import the file into the Database Administration painter notepad, and execute the statements to recreate the table and populate the repository.

Or you could use a database design tool

How do I synchronize the repository?

When data tables are deleted outside the Database painter, for example using DROP table from SQL Plus, extended attributes associated with the deleted table remain in the repository. This information is now orphaned and takes up space.

To remove orphaned attributes from the repository, select Design|Synchronize PB Attributes in the Database painter. This menu items compares all attribute information to the list of existing tables and purges attributes for tables that no longer exist.

Warning: make sure you have access to all the data tables, otherwise you risk deleting extended attributes for tables that you can’t see.

What is DWEAS Utility?

The DataWindow Extended Attribute Synchronizer (DWEAS) lets you update many of the attributes of an existing DataWindow object with the current extended attribute values from the Powersoft repository.

This is important because when a new DataWindow is built, its extended attributes are gathered from the current values in the repository. When the values change, the object attributes are not automatically updated.

May 6, 2008 Posted by | Database, Datawindow Painter | , , , , | Leave a comment

Transaction Object


Properties for database communication

Property

Type

Description

DBMS

String

Name of the DBMS

Database

String

Name of the database

UserID

String

User ID for database logon

DBPass

String

Password

Lock

String

Isolation level

LogID

String

User ID for server logon

LogPass

String

Password

ServerName

String

Name of the server

AutoCommit

Boolean

Turns transaction processing on or off, where supported

DBParm

String

DBMS specific settings

“Live” Properties

When you assign a value to most properties, the value is stored and no immediate action is taken. However, AutoCommit and DBParm are “live” properties. If you are connected to the database, and you assign a value to either of these properties, a message is sent to the database.

Properties that return status information

Property

i) Type

Description

SQLCode

long

Status code for the last SQL operation 0 Ok 100, No result set -1 error

SQLNRows

long

Number of rows affected by most recent SQL operation.

SQLDBCode

long

DBMS specific error code

SQLErrText

string

DBMS specific message

SQLReturnData

string

DBMS specific

April 22, 2008 Posted by | Database | , , , , , | 1 Comment

Database Glossary


Concurrency

Several clients sharing a resource

Contention

Multiple users try to access a resource at the same time resulting in waits or event deadlocks.

Deadlocks

A deadlock occurs when two or more transactions are both in a wait state, with each one waiting for the other to release a lock before it proceeds.

Deadlocks are automatically detected and resolved by the DBMS. The DBMS rolls back one of the transaction usually the one with the least amount of CPU time.

Exclusive lock

A DBMS always places exclusive locks whenever a transaction performs an update operation (insert, update, delete)

A transaction cannot obtain an exclusive lock on a page or row while other transaction has any lock (shared or exclusive)

A DBMS holds an exclusive lock for the duration of a transition (until a COMMIT or ROLLBACK)

Lock Granularity

Identifies the lowest level at which a DBMS provides locking. Some DBMS offer locking at the row level while others do so at the physical database page level

Optimistic concurrency

The updated rows are checked to determine if another user has changed them since they were originally read. If the data has been updated by someone else, the transaction is rolled back and a message informs the user accordingly.

This technique assumes that the likelihood that two users will attempt to select and update the same information at the same time is low. In such cases, it also accepts that one user may lose his or her updates.

Pessimistic concurrency

Is implemented using a database management system’s locking facility to acquire and leave locks on the rows being read. A user is guaranteed data consistency because no other user can update the data while it is locked.

Pessimistic concurrency is used when the likelihood that other users will update the same data is high and absolute isolation between users is required. This type of control comes at the expense of concurrency. The can be significant with DBMS using page locking.

Shared Lock

Some DBMS place shared locks on pages while they are being read after a transaction issues a SELECT statement, and then releases them after reading each page or row.

A transaction cannot obtain a shared lock on a page while another transaction has an exclusive lock on it. However, multiple transactions can obtain shared locks on the same page or row.

April 22, 2008 Posted by | Database | , , , , , , | Leave a comment