Pervasive.SQL
Pervasive.SQL is a relational database management system. With the Pervasive.SQL connector, Map Designer can read and write Pervasive.SQL 7 data tables.
Note To connect to Pervasive.SQL tables, you need to own the necessary API files, purchase them from a software vendor, or use ODBC 3.x. If you attempt to connect to Pervasive.SQL without installing the required API files, you receive a 25547 error: Setting connector (Pervasive.SQL) name: Unable to find required module .dllname.
You must have Pervasive.SQL installed on your system and you must have access to Pervasive.SQL. The Pervasive Database Manager must be running. For more information about Pervasive Software, see their web site at http://www.pervasive.com.
If you attempt to make a live connection to a database that you do not have direct access to, Map Designer automatically switches to the simulator connector for that application. A message indicates that the connection is simulated and normal connection information appears. For information on simulated connections, see 数据库模拟器 on page -8.
Pervasive.SQL 2000 Users
Pervasive.SQL 2000 database connectivity is not supported with this connector; however, you can use “ODBC 3.x” as your source or target connector. See the setup procedure below. Alternatively, you may use the Btrieve connector.
This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write 8 Japanese characters, data is truncated (the Kanji characters all take 2 bytes).
Using ODBC to Connect to Pervasive.SQL Databases
1
2
Start by selecting ODBC Administrator and select the Pervasive ODBC Engine Driver. Put in a DSN name. Next, click Create to create a new database. Then follow through the dialogs, filling in the information requested. Once finished, your engine DSN is created for your new database.
3
Create a client DSN for each client you want to access that database on that server. Select ODBC Administrator and select the Pervasive ODBC client Driver. Put in a DSN name.
4
Type the server name or the server’s IP address. Select Get DSN List. Then select the DSN you created in step 1.
This should complete the creation, setup and registration of the database and ODBC connections.
Note To connect to a data table in a Pervasive.SQL database, the Pervasive.SQL engine must be running in the background.
Procedure
1
From the Source tab, click the Source Connection arrow. The Select Connection dialog appears. Click the Factory Connections tab, select Pervasive.SQL or Scalable SQL, and click OK.
2
Tab to Server and type the name of your server. If Pervasive.SQL is installed locally, you can leave this line blank.
3
At Source Database, type in the database file name.
4
Tab to User ID and, if needed, type a user name for the database. Otherwise, leave it blank.
5
6
SQL Source: Select one of the following:
Table/View (default): Click the arrow to choose the Table/View you want from the list.
Note If your database has many tables, selecting the table from a list may take more time than typing the UserID.Tablename value in the Source Table/View box. If you enter the table name without the user ID and then connect to the database, you can reselect the table in the source data browser to view it.
Query Statement: Select Query Builder or Query Statement. For information on Query Builder, see “使用 Querybuilder 来构建查询”. Click the ellipsis to open the Query Statement Box and enter, edit or paste your query statement as required.
Note If possible, create query statements using the source database application, so that syntax and validity can be checked by the native application. You can then copy the query statement and paste it in the Query Statement Box.
Query File: To use a query file, select Query File. Click the ellipsis to browse to your query file and select it.
7
Click Connect.
To view the structure of a Pervasive.SQL source table, click the Source Schema icon in the toolbar. For details, search for the words “source schemas” in the online documentation.
You can view the contents of the selected table by clicking the Source Data Browser icon in the toolbar.
1
From the Target tab, click the Target Connection arrow. The Select Connection dialog appears. Click the Factory Connections tab., select Pervasive.SQL or Scalable SQL, and click OK.
2
Tab to Server and type in the Server name where your database is located (if applicable).
3
Tab to Target Database and either type the name of the existing database FOLDER where your tables are located (including drive and directory path), or click the arrow to navigate to the existing database file folder.
4
Tab to User ID and, if needed, type a user name for the database. Otherwise, leave it blank.
5
6
Tab to Target Table/View and select a table or view from the list.
7
Output Mode is the last option on the Target tab. If you want to create a new table or overwrite an existing table, leave the default Replace Mode. If you are writing data to a new table in a Pervasive.SQL database, enter the table name.
If it already exists, a warning alerts you.
Click OK to continue. To change the output mode, see the next step.
8
If you want an output mode other than Replace, choose one of the following from the list:
For details about output modes, see “在“目标”选项卡上定义输出模式”.
9
10
You can view the structure of the selected table by clicking the Target Schema icon in the toolbar. For details, see “从“全部映射”选项卡创建模式”. View the contents of the selected table by clicking the Target Data Browser icon in the toolbar.
Limitations
These limitations also apply to Scalable SQL on page -823.
DateValMask
Map Designer does not support milliseconds in DateValMask This means that data is committed at the end of the transformations. Although a mask is provided for them, Map Designer always returns zeros in those places.
Pervasive.SQL 2000
Pervasive.SQL 2000 database connectivity is not supported with this connector, however, you can use “ODBC 3.x” as your source/target connector. Alternatively, you may use the Btrieve connector.
Property Options
These property options also apply to Scalable SQL.
You may need to specify properties for your source or target data. After you have made your selections, click Apply.
Source Properties
WhereStmt (Source Only)
Provides a pass-through mechanism for SQL connectors where advanced users can construct the Where clause of the SQL query themselves. It can be used as an alternative to writing a lengthy query statement in the Query Statement text box. This might be used to get the SQL database server to filter the data based on a condition before it is sent to Map Designer. Omit "WHERE" when you enter the clause. No default exists for this property.
Note Do not apply the WhereStmt when the source connection is a Select statement. If you are using a Select statement, you should include the Where clause in that statement. The intent of this property is to enable data filtering when selecting a table.
Target Properties
ConstraintDDL (Target Only)
This is a pass-through mechanism that allows you to specify any additional data definition language (DDL) statements to be executed when a new table is created. DDL is used to define keys, indexes, constraints and integrity rules for SQL databases. There is no default.
Source and Target Properties
CodePage
The code page translation table tells Map Designer which encoding to use for reading and writing data. The default is ANSI, which is the standard in the US. The following code pages are available:
Source Schema
This section also applies to Scalable SQL.
The Source Schema icon is active in the toolbar when Pervasive.SQL is the source connector, but the structure is locked and cannot be modified. This feature is only for viewing the schema.
Target Schema
This section also applies to Scalable SQL.
The Target Schema icon is active in the toolbar when Pervasive.SQL is the target connector. Click the icon to define the structure of your target Pervasive.SQL file. For instructions, see Structured Schema Designer User’s Guide.
If there are data dictionary files present, you may write the data to an existing table, either in "Replace" or "Append" mode. However, you cannot create a new table or change the structure of an existing table that you are writing data to.
The following options are applicable for Pervasive.SQL files.
Target Field Name
These are the field names as they are stored in your target data file or table.
Target Field Expression in the Map Tab
For instructions on mapping target data one record at a time using a wizard user interface, search for the words “mapping data” in the online documentation.
Available options allow you to map the data exactly as it is in the source data file, or to make modifications to the actual data in any field. You may perform calculations, concatenate data to the existing data, combine data from two or more fields, write data in a blank field and make a number of other modifications. To do so, write an expression in the target field expression cell for a field. For details on writing the mapping expressions, search for the words "writing expressions" in the online documentation.
Type
These are the data types as they are stored in your target data file or table. The following data types are available: