Relational Engine Components
The set of Relational Engine components includes the following:
•
TPvSQLSession
•
TPvSQLDatabase
•
TPvQuery
•
TPvUpdateSQL
•
TPvStoredProc
•
TwwPvQuery
•
TwwPvStoredProc
The components replace Embarcadero ones as listed in the following table
PSQL Direct Access Component
Replaces This Embarcadero Component
TPvQuery, TwwPvQuery
TQuery
TPvSqlDatabase
TDatabase
TPvSqlSession
TSession
TPvStoredProc, TwwPvStoredProc
TStoredProc
TPvUpdateSQL
TUpdateSQL
The components can be used standalone (that is, without the Borland Database Engine (BDE) installed) or coincident with the BDE within a single application. Use of the components is the same as the corresponding BDE components, with exceptions noted under specific components where applicable and in PDAC Classes, Properties, Events, and Methods.
TPvSQLSession
Provides thread safety and Client ID support for PSQL. Functionality is similar to the TSession VCL component.
Functional Differences Between TPvSQLSession and Embarcadero Components
•
All alias management functions perform DSN management.
•
The type of Property 'Handle' has been changed to SQLHENV.
•
Method 'AddStandardAlias' adds an Engine DSN with default settings.
•
Property 'UpdateDsnType: TDsnTypes' has been added. 'TDsnTypes = (dsnSystem, dsnUser)'. 'UpdateDsnType' means:
•
In case of 'DeleteAlias' function - which DSN user is going to delete: System or User DSN?
•
In case of 'AddAlias' and 'GetAliasParameters' functions - hints which DSN type is preferred.
Related Information
See the following in this chapter pertaining to TPvSQLSession:
•
Class TPvSessionList/TPvSqlSessionList (under Supporting Classes)
•
Global Variables (under General Differences from VCL)
•
TPvSession and TPvSqlSession (under Specific Class Differences from VCL)
•
TPvSqlSession Specific (under Specific Class Differences from VCL)
TPvSQLDatabase
Provides for PSQL database connection-specific functionality, such as login control, transaction support and persistent database connections. Functionality is similar to the TDatabase VCL component.
Functional Differences Between TPvDatabase and Embarcadero Components
•
The type of Property 'Handle' has been changed to SQLHDBC.
•
Property 'IsSQLBased' is always true.
•
Properties 'Tables' and 'StoredProcs' have type 'TSqlTablesInfoCollection' and 'TSqlStoredProcInfoCollection' correspondingly.
Secure Databases and Prompting for User Name and Password
If you are connecting to a secure database, PDAC prompts you for the user name and password. If you want suppress these prompts, set up connection parameters in TPvSQLDatabase using the following as an example.
PvSession.ServerName:='ServerName';
PvSession.SessionName:='session1';
PvSession.Active:=True;
 
PvDatabase.AliasName:= 'DatabaseName';
PvDatabase.DatabaseName:='DB';
PvDatabase.SessionName:='session1';
PvDatabase.Params.Clear();
 
// here you specify user name and password to
// connect to remote database.
 
PvDatabase.Params.Add('User Name=UserName');
PvDatabase.Params.Add('password=Password');
PvDatabase.Connected:=True;
PvTable.DatabaseName:='DB';
PvTable.SessionName:='session1';
PvTable.TableName:='person';
PvTable.Active:=True;
DSN-Less Connections from a Client
PDAC's relational components are able to connect from a client machine without a DSN or Named Database to a remote server database. The server must have a DSN for the database.
This feature works through the use of the property, AliasNameIsConnectionString, which is available with the TPvSqlDatabase component. For more information, see Specific Class Differences from VCL.
1
Drop a PvSQLDatabase on a Delphi form.
2
Supply a fictitious DatabaseName—it can be anything.
3
Set the property AliasNameIsConnectionString to True.
4
Set the AliasName property (or the DatabaseName, leaving AliasName blank) to the Connection String.
The Connection String is the complete connection string for ODBC, including the DSN on the server and the name of the server.
Example:
DRIVER={Pervasive ODBC Client Interface};ServerName=DSLINUX2; ServerDSN=DEMODATA; UID=myuserid;PWD=mypassword
*Note: There are no quotation marks or line breaks in the connection string. If your editor wraps the preceding line, make it a single line in the property editor.
If a user name and password are required, they may be supplied as part of the Connection String; if they are not in the Connection String, the standard database login dialog displays if the LoginPrompt property is True.
5
Set the database to Connected and use as usual.
All these steps may be performed at design time or through code at run time.
Related Information
See the following in this chapter pertaining to TPvSQLDatabase:
•
TPvDatabase and TPvSqlDatabase (under Specific Class Differences from VCL)
•
TPvSqlDatabase Specific (under Specific Class Differences from VCL)
TPvQuery
Encapsulates a rowset based on a SQL statement, which provides full relational access including joins and cached updates to multiple tables. Functionality is similar to the TQuery VCL component.
Functional Differences Between TPvQuery and Embarcadero Components
•
Inherited from TQuery
•
The type of Property 'Handle' has been changed to SQLHSTMT.
•
The type of Property 'DBHandle' has been changed to SQLHDBC.
•
Property 'StmtHandle' has been dropped.
•
Property 'Text' always returns text from property 'SQL'.
•
Method 'CheckOpen' has been dropped.
•
All index and key related properties and methods (like 'GetIndexInfo') have been dropped.
•
If 'LoadBlobOnOpen' property is true, then all BLOBs will be cached in memory on query open. If 'LoadBlobOnOpen' is false, then BLOBs will be read as required.
BookMarksEnabled Property
TPvQuery contains a Boolean property BookmarksEnabled. If you do not use Bookmarks in your application, set this property to False to increase TPvQuery performance. The default value is True.
PvQuery.BookmarksEnabled :=False
Cursor Management
TPvQuery has a property, CursorType, that can be set to ctCached or ctDynamic. The following table describes the behavior of this property in more detail:
Cursor type
Behavior
ctCached
This setting corresponds to the previously-available fully cached, fully static Cursor Manager. It reads every record in the ResultSet before returning, which can be very slow for large tables, yet fast after it has opened for operations such as LookUps.
ctDynamic
This setting is the default; it uses the PSQL Engine's dynamic cursors, and offers much improved performance for most operations when compared with ctCached, especially with regard to large tables.
*Note: Dynamic cursors can see the Inserts/Updates/Deletes of their own or other clients.
You can change CursorType on a PvQuery by changing the Property, but only at run time if Active is False. If you change it in Design mode, and the query is Active, it will de-activate the query and change the cursortype but not reactivate. At run time, changing CursorType on an Active PvQuery will result in the exception “Cannot perform this operation on an open dataset.”
Case Where Dynamic Cursors Change to Static
If you request a Dynamic cursor (ctDynamic), but your SQL statement contains a construct that a Dynamic cursor cannot process, the engine will connect and return data, but with a Static cursor. For example, such a construct could be:
•
a view
•
a join
•
an ORDER BY on a column with no index defined
In the event the cursor is transformed into a static one, this static cursor will be engine-based one that cannot see inserts, updates, or deletes made by other clients. It will perform better than the static cursors on previous releases (that is, ones using ctCached as the cursor type.).
When the dynamic to static transformation occurs, the PSQL engine notifies the PDAC component, and sets a read-only Public Property named EngineCursor.
This EngineCursor property is not published, so it is not visible in the Object Inspector during design. It can have two values:
•
ecDynamic
•
ecStatic
You cannot modify this property, but you can check its value after opening a query. You might want to check this property for example if it is important that your application know whether your cursor includes updates made by other clients.
Related Information
See the following in this chapter pertaining to TPvQuery:
•
TPvTable, TPvQuery, and TPvStoredProc (under Specific Class Differences from VCL)
•
TPvQuery and TPvStoredProc Specific (under Specific Class Differences from VCL)
•
TPvQuery Specific (under Specific Class Differences from VCL)
TPvUpdateSQL
Allows transparent updating (including cached updates) of SQL rowsets not otherwise updateable (multiple-table joins, and so forth). Functionality is similar to TUpdateSQL VCL component.
Related Information
See the following in this chapter pertaining to TPvTable:
•
TPvUpdateSQL (under Specific Class Differences from VCL)
TPvStoredProc
Provides access to PSQL stored procedures; it allows parameterized use and can return rowsets the same as the TPvQuery component. Functionality is similar to TStoredProc VCL component.
Related Information
See the following in this chapter pertaining to TPvQuery:
•
TPvTable, TPvQuery, and TPvStoredProc (under Specific Class Differences from VCL)
•
TPvQuery and TPvStoredProc Specific (under Specific Class Differences from VCL)
•
TPvStoredProc Specific (under Specific Class Differences from VCL)
TwwPvQuery
Included with PDAC for InfoPower compatibility. It is directly derived from TPvQuery and has an extra property, “Control Type.”
TwwPvStoredProc
Included with PDAC for InfoPower compatibility. It is directly derived from TPvStoredProc and has an extra property, “Control Type.”
Database Security for ODBC and SQL
ODBC and SQL security is database security on the DDF level. There are no special methods in PDAC to set up SQL security. You should use external programs such as PSQL Control Center or any ODBC tool. See the PSQL User's Guide for more information on PSQL Control Center.
If the database has security, a dialog displays in which you enter the user name and password each time you open a table or connect to a TPvDatabase component. To verify if security is set up on the database, you can check the public property TPvDatabase.IsSecured. See also Secure Tables and Prompting for User Name and Password.
*Note: The MicroKernel Engine security uses owner names (see Table Security). If the database has SQL security enabled, owner name security is ignored.