• August 2014
    S M T W T F S
    « Jul    
     12
    3456789
    10111213141516
    17181920212223
    24252627282930
    31  

    Tags

    Pervasive-Btrieve FAQ

    This page is the Systems Engineer FAQ that I built when I was employed at Pervasive Software from 1997 until 2004. This FAQ was taken from actual customer e-mails and the answers to them. It has not yet been updated it for Pervasive.SQL 2000i SP3 and SP4 nor for Pervasive V8 and later versions. If I get questions and/or answers for those later versions sent to btrieve@rothgeb.net I will post them here as well..

    Index
    Pervasive.SQL 2000 General
    Upgrades and Migration
    Competition
    Pervasive.SQL 2000 Server (Windows NT, NetWare, Linux, Solaris)
    Pervasive.SQL 2000 WGE
    Pervasive.SQL 2000 WE
    Pervasive.SQL 2000 SDK
    ________________________________
    Pervasive.SQL 2000 General

    Q: What is Pervasive.SQL 2000?

    A: Pervasive.SQL 2000 is a cross-platform embeddable database engine that combines both transactional and relational data access methods to a single data store in one box to provide you with the Best of Both Worlds. The small footprint database engine comes in three types; Workstation Engine (WE); Workgroup Engine (WGE), and Client/Server engine (C/S). The WE and WGE run on Windows 95, 98, NT 4.0, and 2000 and the C/S engines run on NT, NetWare, Linux, and Solaris with relational clients for Windows 95, 98, NT 4.0, 2000, Linux, and Mac, and transactional clients for Windows 95, 98, NT 4.0, 2000, and NetWare.

    Q: What does “Best of Both Worlds” mean to me as a developer?

    A: As a developer, you can now take advantage of the high-performance transaction processing speed of Btrieve for things like inserting or updating data in your database. You can also execute complex SQL queries for activities like decision support, report generation, or data warehousing, all against a common data store. Basically, the ability to use the appropriate access method to solve your business needs.

    Q: Is Pervasive.SQL 2000 backward compatible?

    A: Our database engines are fully backwards compatible, and will support Btreive 4.x through 7.5 file formats without conversion.

    Q: Is Pervasive.SQL 2000 designed solely for the limited-IT business market?

    A: No. Pervasive.SQL 2000 is designed for any enterprise that needs a low maintenance, scalable, high-performance database engine.

    Although a perfect fit for the limited-IT market, larger organizations are also looking for ways to leverage the computing infrastructure and the information stored on the corporate networks all while coping with compressed budgets and the need to support an ever changing and evolving technological environment. Additionally, these same organizations need to push these enterprise type applications out to field offices where often times there is no on-site DBA. Factors such as scalability, maintenance free operation, and low TCO are no longer exclusive to the traditional limited-IT market.

    Q: I have a Btrieve 6.15 database and I want to upgrade to P.SQL 2000. What do I have to do?

    A. Pervasive.SQL 2000 is compatible with all Btrieve 6.15 features and functions, including the Btrieve API and data file format (4.x, 5.x, and 6.x). Applications developed for the Btrieve 6.15 server or workstation engines will run on Btrieve 7.0. Moreover, 6.15 applications running in mixed environments with 6.15 and 7.0 engines on multiple platforms are also fully supported as long as the data files are in 6.x format. So as long as your application only used Btrieve API calls and nothing too abnormal you just need to upgrade the database engine from Btrieve 6.15 to P.SQL 2000 and rebuild the database and your application should just work.

    If you would like to integrate SQL function such as reporting or Relational calls you will want to create new Data Dictionay Files (DDFs).

    Q: Is Btrieve discontinued?

    A: No, Pervasive.SQL 2000 contains a full and complete version of Btrieve 7.82 which fully supports Btrieve 6.15 and earlier applications and Btrieve 6.x file formats without conversion

    Q: What are the new features in Pervasive.SQL 2000?

    A: The Pervasive.SQL 2000 page has more detailed information. A brief list of the major  features includes

    New ODBC interface that has been redesigned from the ground up.
    Expanded SQL syntax providing the ability to extend the capabilities of applications based on the relational interface
    Workgroup Engine providing data access to environments where a dedicated server is unavailable or not practical.
    New Server editions platforms for Sun Solaris And Linux
    Distributed Tuning Interface (DTI), a low level, direct API to the monitor, configuration and management component of Pervasive.SQL 2000.
    New enhanced utilities provide easier access to configuration and database design

    Oracle Interoperability provides applications based on a defined subset of the supported API can run against either a Pervasive.SQL 2000 or Oracle 8i database engine.

    Q: Where can I find Pervasive’s position on running Pervasive.SQL 2000 or Tango 2000 on Windows 2000?

    A: http://www.pervasive.com/support/support/Win2000.html

    Q: Where can I find info on Btrieve and Pervasive.SQL on the Pervasive Web site?

    A: You can a great deal of good information on Btrieve and Pervasive.SQL on the Pervasive Web site if you know where to look.

    Where to go online for Pervasive.SQL Information

    *    The Developer Zone on Pervasive Software Web Site – www.pervasive.com/developerzone
    *    Knowledge base for looking up support issues: http://support.pervasive.com/kb/
    *    Pervasive Education’s training site: http://www.pervasive.com/services/training/
    *    30 Evaluation of all Pervasive.SQL products. http://www.pervasive.com/products/download/
    *    Pervasive Web Forums – http://webforum/webforum.taf/
    *    Online Product Documentation Center available now @  http://www.pervasive.com/support/technical/product/index.html
    *    Updates and utilities http://www.pervasive.com/support/updates/
    *    E-mail Technical Support – techsupport@pervasive.com
    *    Documentation on FTP for download: ftp://ftp.pervasive.com/documentation/
    *    Pervasive Support – 1-877-412-1259 (7 a.m. to 7 p.m. CST M-F)
    *    Updates & Patches @ Pervasive Software FTP Site – ftp.pervasive.com <ftp://ftp.pervasive.com/>
    *    Beta Program – registerforbeta@pervasive.com Become a Partner – Apply to join Pervasive’s Partner Programs for access to the latest information and resources available only to Pervasive Partners. For more information email partners@pervasive.com.
    *    Your Inside Sales representatives (1-800-287-4383):
    *    Old Papers with some good info but not too relevant to Pervasive.SQL 2000: ftp://ftp.pervasive.com/support/refshelf/
    * Don’t forget the news group comp.databases.btrieve

    Q: Are there any books on Btrieve or Pervasive.SQL?

    A: Yes there are several that deal with programming to the Btrieve API. I recommend the following:

    *    Btrieve Complete  – A Guide for Developers and System Administrators By Jim Kyle, ISBN 0-201-48326-2 $39.95
    *    The Illustrated Guide to NetWare Btrieve 6.X – The Power of Client/Server Computing by Richard B. Trocino. ISBN 0-9640897-1-8
    *    Win32 Client/Server Developer’s Guide ByDouglas J. Reilly – ISBN 0-201-40762-0
    *    LAN TIMES Guide to SQL – Covers SQL 2 By James R. Groff & Paul N. Weinberg ISBN 0-07-882026-X

    Q: What ever happened to the Btrieve ISAM driver in Microsoft Access 2.0 and Visual Basic 4.0? How do I access my Btrieve files now from Access 95, 98, or 2000, VB 5.0, or 6.0?

    A: The Microsoft ISAM driver in Access 2.0, VB 4.0, and earlier allowed direct access to Btrieve data created by programs generally using the Btrieve API, however, Microsoft dropped the ISAM  driver and substituted the ODBC API in Access 95, VB 5.0, and later. Because the ODBC API is not only slower but requires the data to be marginally relational and is also much stricter in the data definition contained in the DDFs developers often have a great deal of difficulty switching from the ISAM driver to ODBC access.  There is more info in our Knowledge base:

    http://www.pervasive.com/support/knowledge/files/odbctt-98032501.html

    Q. I have a non-Pervasive database I would like to convert to P.SQL 2000. How can I at least convert the data?

    A. There is a product called Data Junction at www.datajunction.com <http://www.datajunction.com/>  that can do the trick. This product can convert MS-ACCESS or MS-SQL or any of 30 to 40 other database format to Btrieve or P.SQL databases.

    Q: I’m migrating from Btrieve 6.15 to P.SQL 2000. Where can I find more documentation on this migration path?

    A: In addition to the documentation you can find a migration technical paper on Upgrading from Btrieve 6.10 or 6.15 to Pervasive.SQL v7 or Pervasive.SQL 2000 v7.5 <http://www.pervasive.com/support/technical/papers/Migration%20Guide%206x%20to%20Pervasive.pdf>  at: http://www.pervasive.com/support/technical/papers/Migration%20Guide%206x%20to%20Pervasive.pdf

    Q: Are there any known issues/roadblocks to converting an existing PSQL (7 or 2000) on Novell to the same version of Pervasive.SQL on NT4? ( Is there a white paper on conversion? )

    A: No conversion is necessary when moving from one server platform to another (NT, NetWare, Linux, or Solaris) while staying on the same version of Pervasive.SQL. We do have white paper about upgrades, but none is necessary for platform changes. All you have to do is set up a new system and configure NT4 making sure that the workstations can connect to the server properly over the chosen protocol (IPX/SPX or TCP/IP). You then install the Pervasive.SQL engine on the server and configure it (files, handles, cache, etc..). Make sure you are updated to the latest patch level and run the client install on a test system noting the results of Install Scout as you step through the installation. Lastly you can copy the data from the NetWare system to the NT system and set up the application on the client workstations. There may be some extra steps in here depending on how you choose to configure your network and the recommended configuration from your application vendor, but these are the basic critical steps which often take under two hours once you have your NOS installed and configured.

    Q: We are currently running Pervasive SQL on a Netware 3.X machine, single 300mhz processor, and 400 ram. What kind of machine upgrade would we need to be able to run well under NT4?

    A: Recommendations here would be based on application, load, number of users, and other factors you have not included for consideration. Memory and rapid disk access are the two key factors in Pervasive.SQL performance though, so providing that you have proper bandwidth on the network with relatively low utilization (assuming you’re using Ethernet) and a good disk array (duplexed and RAID 0 arrays provide the fastest response with redundancy), I would think that a Pentium III or Xeon 500 or 600Mhz system with 512 MB RAM would be more than sufficient, though your existing system might even work well still. Though NT seems to require 50% to 100% more memory resources than NetWare 4.x, Pervasive.SQL performs about the same on both of them up to 50 or so users and then NetWare seems to have a slight edge because of NetWare’s lower overhead, though field experiences may vary based on many different factors.

    Q: Where can I find information on migration from Pervasive.SQL 7 to Pervasive.SQL 2000 where can I find migration and compatibility information on that?

    A: In addition to the documentation you can find a migration technical paper on upgrading from Pervasive.SQL v7 to Pervasive.SQL 2000 v7.5 <http://www.pervasive.com/support/technical/papers/Migration%20Guide%20Pervasive.SQL%207%20%20to%20Pervasive.pdf>  at: http://www.pervasive.com/support/technical/papers/Migration%20Guide%20Pervasive.SQL%207%20%20to%20Pervasive.pdf

    Q. I know there are workstation, Workgroup, and Client/Server setup’s of P.SQL 2000.  What do I need to know about them?

    A. Workstation is for a single user. The Workgroup is a peer to peer setup. The Workgroup performance does not decline until around 20+ clients but the pricing model makes it more economical to go to client/server after 8 clients. A peer to peer network administration and overhead goes up dramatically after 10 clients. The Client/Server model is ideal for networks of 8 or more clients. The network and database performance is of course directly related to how well your application is written and optimized.

    Q. What platforms does P.SQL support?

    A: Supported clients: Windows 3.x, Windows 95/98, Windows NT, DOS. Supported Severs: Windows NT 4.0, NetWare 3.12, 4.X, 5.X, Linux and Solaris Unix.

    Q: Will Pervase SQL 7 and SQL 2000 run on Windows 2000 Server?

    A: Yes, but there are some issues with Windows 2000 as a client operating system that will not be fully resolved until Pervasive.SQL 2000 SP2 ships in about a month. Pervasive.SQL 2000 runs on Windows 2000 as a server OK as long as clients are Win9x or NT and support will take your calls and any issues you may have but won’t guarantee they can resolve them before SP2 or SP3. we do not anticipate at this time doing any work to resolve Pervasive.SQL 7 issues on Windows 2000 as a client though it should run on Windows 2000 as a server with Win9x or NT 4.0 clients. See http://www.pervasive.com/support/support/Win2000.html  for the official Pervasive postion.

    Pervasive.SQL 2000 and Windows 2000: If you are running Pervasive.SQL 2000 on Microsoft Windows 2000, please be aware of the following issue:

    Issue: Microsoft has changed its implementation of “user” and “guest” accounts, and the rights of those users on all Windows 2000 platforms.  Thus, when a user is logged in as a “user” or “guest” and attempts to run applications on a Pervasive.SQL 2000 Server, Pervasive.SQL 2000 does not function correctly. NOTE: The changes DO NOT impact Pervasive.SQL 2000 applications on a local machine.

    Solution: To run applications on Windows 2000 accessing a Pervasive.SQL 2000 Server, ensure that the user account has at least “Power user” privileges. The Microsoft web site has the following description for Power Users:

    Power Users: Members of the Power Users group have more permissions than members of the Users group and fewer than members of the Administrators group. Power Users can perform any operating system task except tasks reserved for the Administrators group. The default Windows 2000 security settings for Power Users are very similar to the default security settings for Users in Windows NT 4.0. Any program that a User can run in Windows NT 4.0, a Power User can run in Windows 2000.

    Power Users can: Run legacy applications in addition to Windows 2000 certified applications. Install programs that do not modify operating system files or install system services. Customize system-wide resources including Printers, Date/Time, Power Options, and other Control Panel resources. Create and manage local user accounts and groups. Stop and start system services which are not started by default. Power Users do not have permission to add themselves to the Administrators group. Power Users do not have access to the data of other users on an NTFS volume, unless those users grant them permission.

    Warning Running legacy programs on Windows 2000 often requires modify access to certain system settings. The same default permissions that allow Power Users to run legacy programs also make it possible for a Power User to gain additional privileges on the system, even complete administrative control. Therefore, it is important to deploy certified Windows 2000 programs in order to achieve maximal security without sacrificing program functionality. Programs that are certified for Windows 2000 can run successfully under the secure configuration provided by the Users group. For more information, see Securing Windows 2000 Installations at the Microsoft Security Advisor Web site. Since Power Users can install or modify programs, running as a Power User when connected to the Internet could make the system vulnerable to Trojan horse programs and other security risks. For more information, see “Why you      should not run your computer as an administrator” in Related Topics.

    NOTE: Windows 2000 Users cannot:

    Install programs that can be run by other Users to prevent Trojan horse programs. You can run certified Windows 2000 programs that have been installed or deployed by administrators.  You cannot access other Users’ private data or desktop settings. You cannot modify system-wide registry settings, operating system files, or program files. You have full control over all of your own data files (%userprofile%) and your own portion of the registry (HKEY_CURRENT_USER). You can shut down workstations, but not servers. You can create local groups, but can manage only the local groups that you created.

    Q: Does garbage collection occur in the datafiles and indexes. i.e. space from deleted records is recovered/reused?

    A: Yes, space from deleted records is re-used on subsequent inserts. Space in files is never de-allocated back to disk.

    Q: Is database shadowing available allowing a complete up-to-data second copy of the database to exist on another drive or machine?

    A: Pervasive.SQL does not contain specific functionality for this, but many customers have successfully used hardware mirrored drive arrays and solutions like Vinca’s (now acquired by Legato) Standby Server to provide this functionality. There is also a current beta release of  Pervasive.SQL 2000 that supports data replication.

    Q: What would happen in a Client/Server environment if a few workstations were accessing the same data files with a workstation engine?

    A: You would get status 85s, 46s, or 91s and some workstations (depending on which engine got to the files first) would not be able to get access. This is generally avoidable by converting to the 7.x file format so that legacy v6.15 & 5.x engines cannot even open the files. Generally the P.SQL 7 and P.SQL 2000 workstation and client/server engines don’t experience issues like this unless there is a network protocol issue (broken transport or such) contributing to the problem. Pervasive.SQL 2000 is even less likely to experience this because requesters are built into the WE and WGE plus you have smart scout to diagnose these issues.

    Q: What symptoms would be noticed within the processing of files that could be accessed from both workstation/microkernel engine at the same time?

    A: Both Pervasive.SQL 7 and Pervasive.SQL 2000 do not allow more than one engine to open files at the same time. This is true of two workstation engines or any combination of WE, WGE,  or C/S engine. This is because the mechanisms within Windows (as opposed to DOS and NetWare) do not efficiently support the MEFS methods that were used in Btrieve 5.x and 6.15. The Btrieve v6.15 workstation engine (WE) was the last product to support MEFS.

    Q: What utility is referenced in Pervasive training as a clean-up product which is best to use to clear components from betas or previous versions of Pervasive?

    A: The “cleanup” utility that is mentioned in Pervasive training classes was developed to clean up all the Pervasive.SQL components and registry entries from a system that had our beta products installed so that a later beta or the released version of the product could be installed without the complications of having some ‘leftovers’ from the beta to cause potential problems. The utility is located:

    ftp://ftp.pervasive.com/support/outgoing/

    This utility is designed to clean up all Pervasive.SQL components shipped by Pervasive software but doesn’t clean up all ‘legacy’ Btrieve 4.x, 5.x or 6.x engines or components which were shipped by Softcraft, Novell, or Btrieve Technologies, Inc. Since those pieces were often embedded in applications on the client systems, on servers, or on network drives this would be a dangerous practice and could often result in broken applications. Thus all the ‘legacy’ Btrieve, XQL, Xtrieve, or ScalableSQL components that might be in an environment need to be cleaned up manually. The good news is that usually these bits and pieces don’t affect Pervasive.SQL much at all because of the “smart components” features of P2K and almost 100% of older applications that use the Btrieve API are fully supported by Pervasive.SQL 2000.

    Q: Is it true that Pervasive.SQL 2000 currently does not support clustering under Windows 2000 or NT. Are there any work arounds, future plans or fixes that will address high availability and failover requirements for customers?

    A: Yes, it’s true, but Pervasive has supported fail-over and high availability on NetWare SFT II and III server in the Btrieve v6.15 product and Pervasive.SQL is often used for installations on both NT and NetWare that require high availability. Pervasive.SQL 2000 is ideal for close to 24 x 7 availability (we have many references for over a decade to prove it) and unless 99.99%+ uptime is an absolute requirement; fail-over and clustering are generally not necessary to achieve 24 x7. Should fail-over and clustering be necessary there are solutions available that work and have been tested by Pervasive Software, specifically those by Legato Data Availability Division (formerly Vinca Standby Server – www.legato.com ) though they are not officially supported by Pervasive Software. Pervasive.SQL 2000 and it’s predecessors, Pervasive.SQL 7 and Btrieve v6.x have been known for over a decade for their 24 x 7 availability, especially on NetWare, and now we offer them on Linux and Solaris which are also extremely stable platforms as well. In the last couple of years 24 x7, fail-over, and clustering have gained new attention due to the Internet and also I believe due to Windows NT 4.0′s popularity as a server platform coupled with it’s relative instability compared to NetWare and Unix. A simple one server installation with proper attention to good hardware (at minimum a mirrored hot swap RAID 0 array for redundancy) and proper client and server installation and configuration should give 99% or better uptime and barring a catastrophic memory, motherboard, or CPU failure may deliver 100% uptime for one or two years or more. An installation running 24 x 7 with data critical to a business should never run without a backup plan and a server which can quickly be substituted for the primary server. All Pervasive products have offered features like continuous operations, archival logging, and transaction durability (which must be implemented at the application level and also configured at the engine level) to support 24 x 7 operations. Pervasive has now provided replication services to these features to provide another level of data security and extensibility. Provided there is a backup server with an up-to-date image of the data the worst possible scenario for a site with one database server that suffered a catastrophic failure is a possible reset of the client systems to point them at a new server with a different shared drive or UNC file path (a matter of five minutes or less if prepared properly) and the site is up and working again. In the case that the site is doing hundreds, thousands, or more transactions a minute at any time of the day or night (such as a popular international eCommerce Web site) then fail-over and/or clustering begin to become a desirable configuration. It is advisable to do some in depth evaluation of the actual requirements of the application and installation to make sure that the requested technical requirements actually fit the characteristics of the implementation and after that is done the actual requirements for fail-over or clustering can be correctly determined.

    Currently Windows NT 4.0 and Windows 2000 only provide 2 node clustering and won’t have 4 node availability until Windows 2000 DataCenter Server is available as promised sometime around the August 2000 time frame. http://www.microsoft.com/windows2000/guide/server/features/choosing.asp

    Q: Peachtree told my client that they used Btrieve 6.15.  She should remove the Pervasive 7.0 off every machine in the office.  To use Peachtree only 6.15 Btrieve was supported.  They also said to return the Pervasive 7.0.  This is actually better then the last time I had a problem with Peachtree.  Then the Peachtree people said to go out and buy a new computer for the second application.  This time they just said to make the other application run under 6.15 very slowly or get rid of the other program.

    Back in May of 1999 (when we had the first problem with Peachtree), I talked with Stacey Worsham.  She directed me to Randell who gave me the following instructions.

    1) Check the bti.ini file and make sure Local=Yes, Requester=No and Thunk=No.  Then copy the 16 bit engine into the Peachtree directory.

    2) Install the Pervasive 7.0.

    Since Peachtree was using 16 bit btrieve (6.0) and I use 32 bit Windows 95 and DosBox for the Dos end of my program we were able to run together.  Now that Peachtree uses the 6.15 engine we need your advice ASAP.

    Lastly,  what I can not figure out is:

    Q: WHY DOES PEACHTREE HARD CODE THE PERVASIVE FILE DATES & SIZES INTO THEIR PRODUCTS?

    We had one client who ran AccPac General Ledger and purchased our product.  Because 20 users were to use MM2000 (our product) we also sold her Pervasive 7.0.  She could not believe how installing MM2000 made her AccPac go so much faster.  She kept asking why the AccPac people did not suggest Pervasive when she complained to them about how slow it was.

    A: Yes, we’re aware of the difficulties that Peachtree causes users of other software. Peachtree was recently acquired by Sage and we are working to remedy the situation as quickly as possible.

    We are aware of these difficulties with Dac Easy (Sage) as well.

    How many clients can the client/server model handle?

    A: The client/server model is optimized for 250 to 500 clients, but we have many customers running over 500 clients simultaneously and the largest installations I have run across have about 2000 simultaneous users. This, of course, depends heavily on the application, network, servers, and configuration in order to get acceptable performance but it is possible to run very heavy loads against Pervasive.SQL. We also have customers who run extremely heavy transaction loads against Pervasive.SQL servers though they often do not have nearly as many clients as the sites I’ve mentioned here.

    Q: If I have Btrieve v6.15 up and running, what are the benefits of migrating to Pervasive.SQL 2000?

    A: There are a number of benefits to upgrading to Pervasive.SQL 2000:

    Improved performance
    Easier trouble-shooting during runtime
    Easier-to-use cross-platform utilities
    Availability on multiple new platforms
    New, more reliable, faster workgroup options
    High performance relational access to the database
    Support for files and tables up to 64GB
    Automatic, transparent recovery from server crash – never run roll forward manually again
    Vastly improved online documentation

    Refer to Pervasive.SQL 2000 Reviewer’s Guide for more features and details concerning Pervasive.SQL 2000.

    Q: Why should I choose Pervasive.SQL 2000 as the database for my application instead of MS SQL Server or Sybase SQL Anywhere?

    A: Pervasive.SQL 2000 is a cross-platform DBMS with both a rich transaction processing capabilities and a flexible relational data access model designed to be embedded by application developers requiring fast, reliable, and maintenance-free database operations. It scales easily from palmtop to full client/server, and is as profitable for developers to integrate as it is cost efficient for users to own and maintain. A market leader in embedded client/server database technology, Pervasive.SQL 2000 requires no dedicated database administrator (DBA). Microsoft and Sybase recommend dedicated DBAs for their products, require larger system resources, are not embeddable, and are not packaged and priced for mass deployment to end users.

    Q: What is the maximum amount of RAM P.SQL can handle

    A: It’s limited by available physical memory or 4GB whichever is less. The most I’ve seen on a server running our older products is 768MB of cache on a server with 1GB of memory, but you usually don’t ever need much more than 128 to 256MB of cache for most applications and databases for extraordinary performance. I would also say that’s even that is a bit high and the majority of installations run on around 8,16,32, or 64 MB of cache at the most.

    Q: How has the new ODBC driver’s performance been enhanced

    A: We have actually replaced the entire relational engine with one that utilizes native ODBC so that there is no translation overhead like there was with our Scalable SQL engine that provided the ODBC support in Pervasive.SQL 7 and Btrieve v6.15.

    Q: Do we have a specific ODBC driver for a Linux server and Linux client configuration?

    A: Yes we do have ODBC connectivity via ODBC from a Linux client to a Linux server running Pervasive.SQL 2000 via iODBC. Once you set up Pervasive.SQL on Linux you should have local ODBC as well as local Btrieve enabled and that would mean that you have the Pervasive.SQL 2000 ODBC driver for Linux installed. I haven’t seen any install docs, but then I haven’t looked for them, but you should just be able to set up the iODBC driver manager on the linux client and transfer and configure the the Pervasive.SQL 2000 ODBC driver for Linux on the client system providing the TCP/IP network transports are set up correctly and Samba is configured on the server. Currently the Btriev erequester for Linux is still in Alpha.

    Q. How many files and Handles comes default with P.SQL and what should I set them to?

    A: The default is differnt depending on the Service Pack. In FCS and SP1 I believe the default was 200, but with SP2a the default has been changed to 10000 because at least on NT those handles can be dynamically allocated and deallocated as needed up to the maximum limit as set which has a maximum limited only by server memory.

    Q. What is Thunking?

    A: Thunking is sending a call from a 16 bit component to a 32-bit component. Pervasive.SQL thunks when a 16 bit application calls wbtrcall.dll or w1btrv7.dll and later that database call is sent to the engine through W3bif1xx.dll, W3mif1xx.dll, and W3nsl1xx.dll.

    Q. When is the BTI.ini used vs. the registry?

    A: The BTI.INI is used by 16-bit components exclusively (the only exception was the Btrieve 6.15 32-bit engine for Windows 3.x wbtr32.exe) and all Pervasive 32-bit Windows components including the engines use the registry.

    Q. Will installing P.SQL 2000 destroy other versions of Btrieve from other programs on my hard drive? (Ex. Peachtree, Great Plains, or other 3rd party programs)

    A: Not usually, though if it were installed to the same directory that contained the older versions of Btrieve some DLL would be overwritten. That wouldn’t usually happen because the default for Pervasive.SQL 2000 is C:\PVSW and the default for Btrieve 6.15 is C:\BTI or the application directory. Also Pervasive.SQL 2000 doesn’t install any thing to the SYSTEM or SYSTEM32 directories.

    Q. What is the main difference between P.SQL 7 and P.SQL 2000?

    A: The main differnece between P.SQL 7 and 2000 is the relational engine, the ODBC driver, and the utilies. P.SQL 7 used the ScalableSQL engine for SQL and ODBC whereas P.SQL 2000 uses a native ODBC relational engine for SQL support called the SRDE.

    Q. What are the minimum system requirements needed for P.SQL 2000?

    A: They are on the box, but depending on the version of P.SQL and what interfaces are being utilized, it needs as little as 2 to 4 MB of memory, 4 to 8 MB of disk space. We recommend 32MB of additional memory above what the OS needs in most cases and though it can run on 486 class sytems we recommend pentium class machines, especially for server implementations.

    Q. What if I want to upgrade to Oracle?

    A: There is a white paper on a Pervasive/Oracle interoperability API should you need that capability.

    Q. How do I access your database? Is there a user-friendly GUI interface?

    A: There is the SQL Data Manager which is a SQL utilitiy, but ODBC capable desktop databases like Access or Paradox are probably the easiest user-friendly GUI interfaces.

    Q. How fast is your database compared to Oracle, Sybase, and other databases?

    A: Faster, especially through the transactional interfaces, but their license agreements don’t allow us to publish comparative numbers, though we have posted papers and a database with instructions on how to run your own benchmark.

    Q: How do you run P.SQL between multiple locations?

    A: There are several ways to do it depending on the specific needs of your environment and application including an upcoming release of a replication product for Pervasive.SQL.

    Q: How do you print out the Relationship Trees.

    A: The only way I know of do that is to use a 3rd party utility like Embarcadero.

    Q: We are in the process of upgrading our Netware 4.11 servers to Netware 5.1.

    A: To do this all you have to do is take the license key from the Pervasive.SQL 2000 box and apply it with the User Count Administrator to the P2K database engine already installed in NetWare 5.1. It would then be a good idea to apply the Service Pack 2a update the the server and to the client installs and then you can update the client installations at your convenience.

    Q: Can a Pervasive SQL7 clients attach to Pervasive 2000 server?

    A: Yes, but it’s not offically a ‘supported configuration’ becuase we support later requesters to earlier servers, but not all earlier requesters to later servers (can’t support everything). It should work just fine as long as the app is 100% transactional or Btrieve, but testing is higly advised and updating the clients to a supported configuration is a really good idea. The ODBC client in P.SQL 7 is completely different than the ODBC in P2K so that wouldn’t work, but it will access the data almost exactly the same so for example Access could get to the DEMODATA through either ODBC driver just fine.

    Q: Can Pervasive SQL 2000 Clients attach to a Pervasive SQL Server?

    A: Yes as long as they are pure Btrieve/transactional clients. Again the ODBC has changed for several pretty good reasons.

    Q: I’m trying to figure out the easiest way to upgrade the Server without having to do the clients all at the same time.

    A: You could start with the clients – move them up to the new requester while you are setting up, testing, and migrating data to the new server. Then bring the new server on-line while the old is still available and test. Then cut over to the new server. ODBC issues might give you a couple of problems if you use it extensively, but everything else should be quite easy as it is in almost all cases. We’ll be here to help if there is an exception.

    My current environment: Novell 4.11 network O/S, running Btrieve 6.15 using DOS Brequester, Windows 95/98 workstations, custom applications developed using Micro Focus Cobol and Magic in a DOS environment. I plan to develop apps for a Windows environment at the client and continue to use Novell as the network O/S. An upgrade to Novell 5.0 is probable.

    Q: Is the SQL engine going to interpret Btrieve calls or is it necessary to continue running the Btrieve.nlm?

    A: No, the SQL engine takes relational calls and the Btrieve module takes Btrieve calls and supports Btrieve applications just like it has for almost two decades. We split the Btrieve NLM into two peices: the MKDE (MicroKernel Database Engine – nwmkde.nlm) which does most of the dirty work for both the Btrieve NLM and the SQL engine which we call the SRDE.

    Q: I do not run the Microkernel NLM today. Will the 6.15 Btrieve NLM interface with the new microkernel engine or will the 7.x version of Btrieve have to be loaded to continue accessing the current Btrieve files?

    A: The 7.x version of the Btrieve NLM replaces the 6.15 and provides complete support for all Btrieve apps and Btrieve data files.

    Q: My current files are in a 5.x format. Will they have to be converted to be accessed by the SQL engine?

    A: No they don’t have to be converted, but it is highly recommended that you convert them to at least 6.x or 7.x file format for several very good reasons. We changed the data integrity algorithm between 5.x and 6.x in about 1991 and that is the same algorithm that 7.x uses. 5.x used preimage files and we got rid of them in 6.x and later for very good reaons. 7.x gives you a 64GB per file maximum over 6.x which was 4GB.

    Q: I have DDF files today that were created and used by a product called Xtrieve. They are in a 4.x file format. Can they be used by the SQL engine or will they have to be converted?

    A: They need to be converted and checked. There is a tool on our site that will help you do the first and you can use PCC for the second. This is after you convert to 6.x or 7.x format for the data files. 4.x is actually the same as 5.x and 3.x, 4.x, and 5.x files can be created by the 5.x engine the only difference being which features are used by the file.

    Q: Is there a tool that replaces Xtrieve?

    A: Yes, SQL DM, but you may need to know some SQL to use it. You can also use Access or any ODBC tool that you find more suitable, depending on what you were using Xtrieve for.

    Q: My custom apps use the Btrieve syntax for data retrieval through communicators, supplied by the application tool manufacturers, that talk to the Brequester. The act of opening a file, getting positioned in the file, selecting and index, reading next, inserting, deleting, etc. is done via a specific construct using Btrieve operation codes and providing a defined data buffer for the returned data and return codes indicating the success or failure of the operation. As apps are written for the SQL engine will the syntax be in the form of a select statement? Is there a similar construct for SQL requests and retrieval of data as in Btrieve?

    A: You can still use everything they way you have been using it and only use SQL when appropriate. SQL is slower than Btrieve and not the best way to do every database operation so we provide many different ways to access your data depending on your needs and your development environment.

    Q: Is there a client side requestor for SQL?

    A: Yes, there is a “requester” for the relational or SQL side which is the ODBC driver. It gets installed and configured when you run the client install.

    Q: Is ODBC the only method of access for SQL?

    A: No, you can use ADO with OleDB or PDAC or even JDBC. Actually it would be a bit more accurate to say is ODBC the only relational access method because you can use SQL queries and syntax (our native SQL syntax is ODBC native syntax which eliminates a translation layer) through most if not all of the relational access methods.

    Q: I expect to continue using my old apps and files and as they are developed use the new apps to access the same files through the SQL engine. Is this a false expectation?

    A: No, but SQL may not be the best way to go to access those files depending on the needs of the application and user. We think that the majority of the data access should be transactional (what we used to call navigation or the Btrieve side – on the transactional side there is Btrieve API, ActiveX, PDAC, JCL, and ADO IRowset through OleDB to choose from or the file handler in your favorite development environment) and Relational/SQL/ODBC should only be used where appropriate which is usually for adhoc queries and reporting.

    Q: Is there a single database file housing all the data, data definitions, stored procedures, security, table relationships, etc. as in Microsoft’s SQL server?

    A: No, each table is a data file and there are 3 to 9 files which contain the data definitions, stored procedures, security, table relationships, triggers, etc.. which are the DDFs. This provides better overall data integrity so that if one table gets corrupted then the whole database isn’t hosed (that’s a technical term :-).

    Q: Does the SQL engine have scheduler capabilities to run stored procedures or other VB scripts designed to access and affect data

    A: The SRDE (SQL Relational Database Engine) has full stored procedure and trigger capabilities which are data event triggered so it has no need of a scheduler. If you desire scheduled actions to take place and invoke stored procedure and/or triggers it is quite easy to write a small service to do so or even convert a script into a service on NT (chron will do it on Unix and I’m sure there must be a simliar service for NetWare) and make it happen.

    Pervasive.SQL 2000 Server (Windows NT, NetWare, Linux, Solaris)

    Q: Will Pervasive SQL7 and 2000 take advantage of multiple processors?

    A: Yes, even v6.15 took some advantage of multiple processors and ran find on dual and quad processor systems, but adding multiple processors is really the last thing that you want to do to a Pervasive.SQL database server, especially if you have not invested in plenty of memory, a good disk array, and adequate networking infrastructure. I’ve included some answers from an internal FAQ

    Q: I’m evaluating the differences between MS SQL 7 and your SQL 2000 product. One piece of information that I can’t seem to locate on your website is how many CPUs your software allows (and uses) in the server. We already have a multiple CPU NT server setup ready for our database solution, and Microsoft clearly states that we can have up to 4 CPUs with their basic product.

    A: We allow and use as many processors as the NOS (Network Operating System) supports. Currently NT 4.0 supports 4 CPUs out of the box and 8 CPUs in special configurations from manufacturers like Compaq. The interesting thing is that the “Enterprise” relational databases often require multiple CPUs just to provide adequate performance, but Pervasive.SQL is generally not CPU intensive so it benefits from having 2 or 4 CPUs, but usually they aren’t necessary. In fact, if it is a question of where to spend hardware dollars (CPUs, Memory, Disk Storage, or Network bandwidth) extra CPUs should be the last thing you add after memory and I/O bandwidth.

    Q: Is the MicroKernel Database Engine v7.0 that ships with Pervasive.SQL supported on NetWare and Windows NT SMP servers?

    A: The MicroKernel Database Engine (MKDE) provided with Pervasive.SQL v7.0 has been tested on Windows NT and NetWare SMP servers with 2 and 4 processors. Pervasive supports running Btrieve and Scalable SQL applications in this environment. However, the MKDE does not have any SMP specific code. Load balancing and processor selection is done by the network operating system itself, allowing multiple processors to be utilized in some configurations.

    On a NetWare 4.11 SMP server, the operating system runs on processor 0. Threads for other server processes (such as a Pervasive.SQL I/O or worker thread) run on processors >= 1. File management is also handled on processor 0 by the operating system. Therefore, if a process thread requires any file I/O, its execution will migrate to processor 0 to be executed by the operating system. When using Pervasive.SQL on a NetWare 4.11 SMP server, any time file I/O is performed on a Btrieve file, the worker thread servicing the Btrieve request will migrate to processor 0, causing I/O to be serialized. As a result the application may end up running slower due to context switching of the processor. This scenario may be handled differently in future releases of NetWare. When running NetWare 4.11 SMP, make sure Support Pack v4.0 is installed, along with the latest updates from the server hardware vendor.

    When running on a Windows NT SMP server, the I/O threads used by the MKDE may be spread out over multiple processors by the operating system, although the MKDE does not include code specifically to do this.

    In summary, Pervasive.SQL v7.0 has been tested and is supported on NetWare and Windows NT SMP servers. It does not take direct advantage of SMP, although an advantage may be seen due to the operating system’s SMP management.

    Q: What is the mechanism that allows the database to be backed up online. What happens if the server goes down in the middle of a backup with many open transactions?

    A: Continuous Operations allows you to put a setup of data files in a special “mode” so that they can be safely backed up while in use. While files are in continuous operations mode, they are not modified, and special delta files store any changes made to the files. After the backup is complete, the files must be removed from continuous operations mode, at which time the changes stored in the delta file are rolled into the live files.

    If the server goes down while files are in continuous operations mode, the next time the file is accessed the existing delta file will be detected and the changes rolled in at that time.

    Q: Does Pervasive.SQL support NetWare 3.11 or 3.12?

    A: Pervasive.SQL support NetWare 3.x but only the versions that were currently supported when the particular version of Pervasive.SQL was released. Pervasive.SQL 7 supports NetWare 3.12 but not 3.11 because Novell was discontinuing incident support for 3.11 prior to the release of Pervasive.SQL 7. Pervasive.SQL 2000 supports 3.2 but not 3.11 nor 3.12 because Novell does not any longer support these products with incident based support, nor with fixes. The following is the location of the data sheet for Pervasive.SQL 2000 and information on the platforms it supports.

    http://www.pervasive.com/products/psql/psql_servengine/netware/netwaredata.pdf

    See http://www.novell.com/products/netware3/letter_312.html for info on Novell’s support policy. Basically any Pervasive.SQL for NetWare will probably work on either 3.11 nor 3.12 but we did not test it and thus we really can’t support it on those platforms.

    Pervasive.SQL 2000 WGE

    Q: I am still not clear on how the workgroup engine works can you please explain it with reference to the Btrieve v6.15 workstation engine and MEFS?

    A:

    Q:  Do we need to put any files (or are any files created) on the file server (where the data resides)?

    A: No you don’t need to put any files except the data files on the server, but yes, one file is created in each directory – that is the locator file which keeps engines from conflicting and indicates which engine owns the files in that directory.

    Q: Can we put all the Workgroup engine files in an application directory on the local machine?

    A: Yes you can, but I wouldn’t recommend it. The workgroup engine files should be in the pvsw/bin directory because there might be multiple applications that use the engine running on one system and if each application puts the engine in the application directory then there could be many engines on the system leading to conflicts.

    Q: Is there any known conflict between using the workgroup engine and Arc Serv?

    A: No, ArcServe uses the requesters and the workgroup engine comes with the latest requesters which are fully backwards compatible. Since ArcServe currently uses P.SQL 7 there could possibly be some configuration problems once those are isolated and dealt with ArcServe should (and always has) work fine.

    Q: How does the engine keep track of how many people are accessing the data?  If people access the data with two engines, what happens?  Corruption?

    A: The engine keeps track of that just like the server engine does (i.e. each combination of a set of requesters and an application creates a unique ID but licenses are by system based on the unique ID for the NIC). The second engine gets locked out since the engines open the data files in exclusive mode (non-file sharing) so that corruption cannot happen from multiple engines having the data open when a workstation freezes or has to reboot without closing the data files.

    Q: Do you sell concurrent or per-seat licensing?  How does the engine keep track of this?

    A: Concurrent – generally based on the unique ID embedded in the NIC of each workstation – just like we’ve done for client/server since about 1986.

    Q: Aside from licensing, is there a limit to how many users can access the data simultaneously?

    A: No theoretical limits that I’m aware of, only practical limits of bandwidth, system processing power, and econonics (i.e. license cost as the client/server licenses are less expensive per seat once you get to 10 users).

    Q: What, if any is the main difference in capabilities between the WGE and the Client/Server engines?

    A: There are no real differences in capabilities between the engines except for security and the operating systems that the engines run on. Workgroup engines run on either Windows 9x or Windows NT and as such are generally a bit less stable than the Client/server engines which are designed to run on a true server OS and take advantage of the security built into those operating systems and networking subsystems.

    Pervasive.SQL 2000 WE

    Q:

    A:

    Pervasive.SQL 2000 SDK

    Q: What languages and development environments are able to be used with your SDK?

    A: We directly support Java, BASIC, COBOL, Pascal, C, and C++, but any language like Fortran or APL which can use a C library or access a DLL can be used with our SDK. We directly test with MS Visual BASIC 4.0, 5.0, and 6.0, Inprise/Borland/Corel Delphi 3.0, 4.0, and 5.0, MicroFocus and Realia COBOL, MS Visual C++, Inprise/Borland/Corel C++ Builder 4.0 and 5.0, Watcom C++ 10.0, & 11.0, Symantec Visual Café, J++ Builder, and MS Visual J++. In addition there are several other development environments that we know anecdotally that our customers use like Powerbuilder, IBM’s Visual Age Suite of tools, Tango, Visual DataFlex, Clarion, Magic, Niakwa, and others.

    > I am looking for an effective means of accessing Btrieve via Microsoft Access or perhaps Visual Basic.

    ODBC is the only supported method of accessing Btrieve via Microsoft Access, though you can also write SQL queries through the pass-through interface to ODBC if you care to. Though we do have an ActiveX control in our Pervasive.SQL 2000 SDK, it is primarily designed to work with Visual Basic and though it might work for most needs if a problem was encountered we probably would not address it as there are many higher priorities since ActiveX with Access is not considered a supported configuration. The good news is that the ODBC interface in Pervasive.SQL 2000 is an extremely fast one because it is the native interface and does not rely on a translation layer to do ODBC calls. In Visual Basic there are several supported interfaces: the Btrieve API, ActiveX, DAO (not recommended), RDO (not recommended), ADO, OelDB provider. You should purchase a copy of our SDK to examine and test these as the SDK is very inexpensive.

    > There are several other methods of accessing Btrieve files from either Access or Visual Basic. Pervasive’s ODBC driver allows access from either

    > Visual Basic or Access. There is also a Btrieve Application Programming Interface (API) for Visual Basic. In addition, Smithware’s Active X controls > for Btrieve can be used with Visual Basic. For information and pricing on these, please conatct sales@pervasive.com <mailto:sales@pervasive.com> .

    This is as noted above.

    > Can you tell me how these methods compare in terms of efficiency and simplicity, as well as their cost and how I could acquire them? Is the Pervasive ODBC driver the only means by which Access can connect? The other 2 options are valid for Visual Basic only?

    There is usually a direct tradeoff between efficiency (speed, overhead, and control) and simplicity (of use or programming). The Btrieve API being the most time consuming and tedious to program, but offering the most speed, control, and flexibility. Fortunately, ActiveX allows you to do most of what the native Btrieve API allows you to do much more easily and faster, while ADO is a native Microsoft method and with the faster native ODBC API in Pervaisve.SQL will perform quite well for almost all applications. The OleDB provider allows you to use ADO while taking advantage of the transactional speed of Btrieve and promises to be the fastest in terms of development time as well as the most flexible method since you will be able to program to a native Microsoft method and take advantage of both the relational and transactional access in Pervasive.SQL 2000. The other big advantage of using Pervasive.SQL is that with these multiple access methods you are not forced to use only one and can use more than one in a single application. For instance you might want to use ActiveX for a claims processing application, but since ActiveX does not support chunking you would use the Btrieve API to manipulate BLOBs which might contain scanned images and also you might use ODBC for some reporting functionality in a management module of the application because of it’s advanced query capabilities. The cost for all the methods is the same as they are all available in the SDK which is $149.00. Distribution costs are based on volume and which product is needed which Sales can discuss with you before you purchase or before deployment.

    Q: Is the Pervasive.SQL 2000 SDK backward compatible?

    A: Our database engines are fully backwards compatible, but each new SDK includes new features that didn’t exist when earlier product versions were released and so can’t be supported by those products. However, applications written with the Pervasive.SQL 2000 SDK which use a Pervasive.SQL 2000 database engine can transparently access Pervasive and Btrieve files and data in 7.x, 6.x, 5.x and 4.x file format. Also applications written with the Pervasive.SQL 2000 SDK which use the P2K requesters will access data on server running Pervasive.SQL 7 and Btrieve 6.15 database engines via the Btrieve API. Both of these configurations are fully supported by Pervasive Software. The Pervasive.SQL SDK includes features and APIs that are not supported by earlier versions of Pervasive’s products. Though programming to the Btrieve API would work in almost every case, it would not be advisable to use the ActiveX or PDAC from the Pervasive.SQL 2000 SDK with earlier versions of Pervasive.SQL or with Btrieve. The ODBC API does not exist in exactly the same form as in earlier products (and so should be close, but might not be 100% compatible) and the DTI API, for example, does not exist at all in Pervasive’s earlier products.

    Q: Can a developer use it to create an application that would run on Pervasive.SQL 7?

    A:  Yes, that can be done, but we would not generally advise that this would be the best approach and care would need to be taken that new features and interfaces introduced with Pervasive.SQL 2000 are not used because they weren’t supported (or in existence) with Pervasive.SQL 7. The best way to ensure backward compatibility is to use only the native Btrieve API which did not change between Pervasive.SQL 7 and Pervasive.SQL 2000 (v 7.5).

    Q: We are developing more heavily in Visual Basic 6.0 using the ADO data methods. Of course, along with that we need the ability to use SQL to join our tables, do sub-selects based on various criteria, order and group data etc. The current version of Pervasive SQL (7.0) looks great except for the fact that it doesn’t support the items above. We really need to look at the next version which will support this kind of functionality that we need.

    A: They can now use the MS OleDB provider for ODBC data sources with ADO until our OleDB provider is available in beta. The only problem with this is that this approach will add another minor layer of overhead to our current ODBC access and have all the current limitations of our current ODBC access including, but not limited to, no dynamic cursors or extended fetch. What this means is that if you recommend this to them they may have problems with it and the only remedy is to have a solid schedule of when these issues will be addressed in P2K service packs and when they can have the native direct OleDB provider with ICommand capabilities available in the P.SQL 8 beta. I would give this about a one in four chance of working and being able to land this customer unless Keith and Steve can come up with some compelling ideas or dates.

    Q: I have searched your web site but did not find any reference to Visual Cobol. Are you aware of anyone using Visual Cobol with Pervasive.SQL and if so what are issues associated with the combination?

    A: Yes, we do have many customers using MicroFocus Visual COBOL for development with Pervasive.SQL 2000. I’ll have to check references to get company names and contacts if you need that, but we taught the first two versions of the Pervasive.SQL – Btrieve API programming class using MicroFocus’ NetExpress v3.0 as one of the optional development environments with full Sample code.

    The advantages are that in general the MF file handler maps COBOL data types to Btrieve API data types quite easily and allows you to develop on a standalone workstation with our SDK yet deploy in single user, workgroup, or client/server configurations with no application changes. This provides you with the performance and scalability (we have some customers running 500 to 100+ users on a single Pervasive.SQL server engine) plus the very economical cost of ownership and cross platform compatibility that mean an ISV can address the largest possible market segment with his product. One other feature in our SDK that you might want to investigate taking advantage of could be the ActiveX control primarily designed for VB, that I understand that Visual COBOL now takes advantage of. I would be happy to discuss more details and advantages in depth as your evaluation process continues if you so desire.

    A: 2)The PDAC in the Pervasive.SQL 2000 SDK would be your best choice. Yes it should work fine, but I would of course want to do some testing before deploying.

    Q: We use ADO to connect to our MSSQL7 server, so if an OLEDB provider is available for Btrieve, that would probably be my first choice.

    A: While we have an OleDB provider for Pervasive.SQL 2000 retrofitting one to Btrieve 6.15 is not a very good option so that unfortunately is not available and I probably wouldn’t advise using the MS OleDB provider for ODBC datasources either in this situation unless you were willing to put up with the limitations of the old v2.04 Btrieve ODBC until you upgraded to Pervasive.SQL 2000 with your Great Plains upgrade.

    ActiveX (Visual Basic 4.0, 5.0, 6.0, & Visual C++ w/ MFC)

    Q:

    A:

    PDAC (Delphi 3.0, 4.0, 5.0, & C++ Builder – it is a BDE replacement)

    Q: We are using Btrieve v6.15 and now Delphi 5.  I am wondering what tools are available to allow me to connect to the Btrieve Database from Delphi?

    A: We have developed a BDE replacement much like Titan that comes in our Pervasive.SQL 2000 SDK that is called PDAC (Pervasive Direct Access Components). The PDAC is new and works well but we are continuing to enhance it and improve performance and so we value Delphi Developer’s input on this product so that we can make it a much better overall solution because it is fully supported as well. If you use only the Ttable then it will work with Pervasive.SQL 7 and should work with Btrieve v6.15 though it may be necessary to use the later requesters supplied with Pervasive.SQL 2000.

    Q: With Delphi 3, we used a product called Titan and while it worked ok, support was non-existent. Because of this I am hesitant to upgrade to the Delphi 5 compatible version.  We are hopefully going to be upgrading to Pervasive SQL (2000?) when we upgrade our Great Plains Dynamics, but that will be a ways down the road and I need something soon.   If there is a product that will support both, great, otherwise 6.15 is what we have now.

    A: The PDAC in the Pervasive.SQL 2000 SDK would be your best choice. It is fully supported and will be into the foreseeable future. It should also work fine with Btrieve v 61.5, but I would of course want to do some testing before deploying. Of course Pervasive.SQL 2000 is fully backwards compatible and will support Btrieve v6.15 applications so you should not have to stay with Btrieve v6.15 unless there are other non-technical reasons.

    Q: Does PDAC work with Delphi 3.

    A: The Pervasive Data Access Components (PDAC) is a set of Visual Component Library (VCL) components that allow direct access to Pervasive Database Engines from within the Inprise Delphi and C++ Builder Environments.  These components offer a complete replacement for the Borland Database Engine (BDE), while providing the complete functionality of the BDE. PDAC dramatically extends the database development options available to Delphi and C++ Builder developers.

    The Pervasive Data Access Components are provided in versions fully integrated with the Delphi 3, Delphi 4, Delphi 5, C++ Builder 3, and C++ Builder 4 development environments. Compatibility with later versions of the Delphi and C++ Builder development tools will be made available soon after those products are released by Inprise/Borland.  No support is provided or planned for Delphi 1.0 (16-bit) or 2.0.

    All versions of PDAC contain the non-visual components TPvSession, TPvDatabase, TPvTable, TPvBatchMove, TPvQuery, TPvStoredProc, TPvUpdateSQL, and TPvSqlDatabase with supporting classes for these components. These 32-bit components duplicate the properties, methods, and binding capabilities of the Inprise/Borland Data Access components, but without requiring the presence of the Borland Database Engine (BDE) at run time. These components are provided in “package” format, and offer all the design time and run time functionality of the built-in controls.  They will bind to the Borland Data Aware controls in the same way as the BDE components, as well as to fully compatible third-party bound controls.  Special components are provided for interoperability with the Woll2Woll InfoPower components.  The components included with this release make up the “Btrieve subset” of the full PDAC set; these components use no relational functionality, and do not require the Pervasive.SQL Relational Engine or ODBC at runtime.  TPvQuery, TPvStoredProc, TPvUpdateSQL, and TpvSqlDatabase components use Pervasive.SQL’s underlying relational functionality.

    The Pervasive Data Access Components (PDAC) allow Pervasive Database engines to work seamlessly within the Borland Delphi and C++ Builder environments. These components replace the functionality of the Borland Database Access components.

    The Pervasive non-visual components duplicate the properties, methods, and binding capabilities of the Inprise/Borland Data Access components, but without requiring the presence of the Borland Database Engine (BDE) at run time. They will bind to the Borland Data Aware controls in the same way as the BDE components do.

    Q: Will the PDAC components work with the Win32 client requestor packaged with Pervasive SQL 7.0 provided that the ODBC drivers are installed?

    A: The table-based components (TPvSession, TPvDatabase, TPvTable, etc.) will work with 7.0, and do not require ODBC on the client.  The query-based (ODBC) components require the 7.5 SRDE, and won’t run against 7.0.

    Q: I have searched your web site but did not find any reference to Visual Cobol. Are you aware of anyone using Visual Cobol with Pervasive.SQL and if so what are issues associated with the combination?

    A: Yes, we do have many customers using MicroFocus Visual COBOL for development with Pervasive.SQL 2000. I’ll have to check references to get company names and contacts if you need that, but we taught the first two versions of the Pervasive.SQL – Btrieve API programming class using MicroFocus’ NetExpress v3.0 as one of the optional development environments with full Sample code.

    The advantages are that in general the MF file handler maps COBOL data types to Btrieve API data types quite easily and allows you to develop on a standalone workstation with our SDK yet deploy in single user, workgroup, or client/server configurations with no application changes. This provides you with the performance and scalability (we have some customers running 500 to 100+ users on a single Pervasive.SQL server engine) plus the very economical cost of ownership and cross platform compatibility that mean an ISV can address the largest possible market segment with his product. One other feature in our SDK that you might want to investigate taking advantage of could be the ActiveX control primarily designed for VB, that I understand that Visual COBOL now takes advantage of. I would be happy to discuss more details and advantages in depth as your evaluation process continues if you so desire.

    OleDB Provider (useful with VB, VC, Delphi, C++ Builder or any environment that supports ADO or ODBC)

    Q:

    A:

    RSI (Row Set Interface)

    Q:

    A:

    JDBC

    Q: Is your JDBC driver type 3 compatible?

    A: JDBC Features: The following is a summary of features of the Pervasive JDBC driver:

    •           Supports thread safe operation

    •           Supports transactions isolation levels supported by the Pervasive.SQL 2000 server engine, for example READ_COMMITTED, serializable

    •           Performs result set caching to reduce network access

    •           Supports binary data through the longvarbinary datatype ( 2 GB limit )

    •           Supports long char data through the longvarchar datatypes ( 2 GB limit )

    •           Supports stored procedures with parameters

    •           Encrypts connection strings to provide security

    •           Transmits data flows between the server and client in a native binary format that is nontrivial to decode for added security

    JDBC Driver Version

    JDBC version 1.22 is included with JDK version 1.1. The Pervasive JDBC driver supports this JDBC 1.22 standard. Enhancements made to the JDBC API 2.0 specification are not included.

    The Pervasive JDBC driver is a pure Java type 3 net protocol-based driver. It conforms to the core JDBC 1.x requirements. The driver is similar to the ODBC client driver in functionality and depends on the Pervasive.SQL Engine’s ODBC Interface on the server side for the bulk of processing.

    Q: What is a type 3 JDBC driver?

    A: Types of JDBC technology drivers: JDBC technology drivers fit into one of four categories: 1.       A JDBC-ODBC bridge provides JDBC API access via one or more ODBC drivers. Note that some ODBC native code and in many cases native database client code must be loaded on each client machine that uses this type of driver. Hence, this kind of driver is generally most appropriate when automatic installation and downloading of a Java technology application is not important. For information on the JDBC-ODBC bridge driver provided by Sun, see JDBC-ODBC Bridge Driver <http://java.sun.com/products/jdk/1.3/docs/guide/jdbc/getstart/bridge.doc.html#996747> .

    A native-API partly Java technology-enabled driver converts JDBC calls into calls on the client API for Oracle, Sybase, Informix, DB2, or other DBMS. Note that, like the bridge driver, this style of driver requires that some binary code be loaded on each client machine.
    A net-protocol fully Java technology-enabled driver translates JDBC API calls into a DBMS-independent net protocol which is then translated to a DBMS protocol by a server. This net server middleware is able to connect all of its Java technology-based clients to many different databases. The specific protocol used depends on the vendor. In general, this is the most flexible JDBC API alternative. It is likely that all vendors of this solution will provide products suitable for Intranet use. In order for these products to also support Internet access they must handle the additional requirements for security, access through firewalls, etc., that the Web imposes. Several vendors are adding JDBC technology-based drivers to their existing database middleware products.
    A native-protocol fully Java technology-enabled driver converts JDBC technology calls into the network protcol used by DBMSs directly. This allows a direct call from the client machine to the DBMS server and is a practical solution for Intranet access. Since many of these protocols are proprietary the database vendors themselves will be the primary source for this style of driver. Several database vendors have these in progress.

    ODBC

    I*Net Data Server

    Q: I just looked on the most recent FAQ and I didn’t see anything about I’net data server, what it is, how many users can access it and if any of our dbase s/w is necessary for it.  Where can I find out more information about this?

    A: Very good question. I don’t think many folks know much about I*DS and I’d be surprised if there was much info on our site about it. The following tells what it is and is from the documentation included in the SDK. You need to have Pervasive.SQL 2000 (or 7) for Windows NT and it can theoretically support as many users as you have licenese for the  database I would not recommend more than 20 to 50 users or unless you really know what you are doing and set it up correctly – then I would imagine that you could get it to run with 100 or more if you wanted.

    The most economical and best way to get up to speed is have the customer get a copy of the SDK to test I*DS (for compatibility and performance) and to figure out how to package and deploy their application based on I*DS, then they can purchase the multi-user version(s) if it works for them.
    Pervasive Software’s I*net Data Server (IDS) provides a mechanism for Pervasive.SQL 2000 applications to access a Pervasive.SQL 2000 Server across the Internet or intranet. Pervasive.SQL 2000 Workstation and Server products allow your application to run from a workstation on a wide area network (WAN) or local area network (LAN). With this release, your Btrieve applications can access a Pervasive.SQL database over the Internet without changing a line of code.
    A single-user IDS is introduced in the Pervasive.SQL 2000 Software Developer’s Kit (SDK) to allow Java and ActiveX development of IDS-enabled applications. This release supports multi-user IDS deployment of those applications using a Windows NT server or workstation. With this release, your Btrieve applications also can access Pervasive.SQL data over the Internet/intranet.
    This release of IDS uses Windows NT domain and/or workgroup security. Pervasive.SQL IDS handles security differently than Pervasive.SQL workstation and client/server products in that it checks the user’s rights to a database set instead of their rights to individual files. (A database set is the combination of users, groups, and access paths.) Thus, you can use IDS security along with NT security to give users access to the database through IDS only and deny them operating system level access to the files that make up the database. Therefore, users cannot inadvertently delete a database file.
    IDS can support multi-user access to a Pervasive.SQL database through the concept of database sets as mentioned previously. IDS appends whatever path and file name the client sends to the database’s virtual root and then makes the Btrieve call. For example, if the IDS virtual root is C:\ACCOUNTING and the client tries to open file FY99/DATA.BTR, the actual Btrieve file is C:\ACCOUNTING\FY99\DATA.BTR. This concept gives administrators a level of flexibility in maintaining their IDS environment because the path used at the client does not need to be changed if they move the location of the database on the server.
    Pervasive.SQL client applications can access database sets on an IDS via a URL, UNC notation, or a mapped drive. Existing Btrieve applications can access IDS with only a few configuration changes, which are set in the new client-side IDSHOSTS configuration file. If you use UNC or mapped drives, edit the IDSHOSTS file and then set your IDS user name and password MicroKernel Router setting (configured in the Pervasive.SQL Win32 Setup utility). If you use a URL, specify the database set name in the URL, and the user name and password in the Pervasive.SQL Win32 Setup utility. Refer to “How Clients Access IDS” and “Configuring IDS Client Components” for specific details on accessing the IDS via a UNC, mapped drive, or URL.
    Figure 1-1        Applications Accessing Pervasive.SQL over Internet via IDS
    <file://///Ausintra1/Sales/resources/Images/ids.gif>
    Preparing IDS for Client Installations
    Now that you have installed your IDS server, there are some things you can do to prepare your client machines for installation. This section provides information and deployment tips for your IDS client installations in the following subsections:
    •           “How Clients Access IDS”
    •           “IDS Client Deployment Strategy”
    How Clients Access IDS
    As discussed in Chapter 1, remote client applications can access your I*net Data Server via a URL, UNC notation, or a mapped drive. There are a few configuration requirements that must occur on your client machines, which are discussed in the following subsections:
    •           “Specifying a Database Set”
    •           “Specifying User Name and Password”

    DDFs (PCC, DDF Ease, DDF Builder, & DDF Sniffer)

    Q: What happened to DDF Builder and DDF Sniffer?

    A: DDF Sniffer and DDF Builder were added to the Pervasive product line with the acquisition of Smithware in 1998. Neither of the products; DDF Sniffer nor Builder, are available anymore. They have since been replaced with DDF Ease which is part of Pervasive.SQL 7 database engine included in Crystal Reports 7 for Pervasive.SQL. DDF Sniffer and DDF Builder used the Btrieve API to manipulate DDFs which is much less desirable than doing it through the native relational interface of Pervasive.SQL and often causes problems that are difficult to isolate and fix. All the DDFEase functionality has been incorporated into the Pervasive Control Center or PCC in Pervasive.SQL 2000.

    Q: Can you tell me if DDF Ease has the same functions as the Sniffer?  Namely will DDF Ease real a .dat file(btrieve DOS) without a .ddf and analyze the file to help me build a .ddf file?

    A: Yes, it does in general, but it lacks some of the automation of DDF Sniffer in the area of recommending data types to use for non-indexed fields, however, it avoids many of the problems that DDF Sniffer and DDF Builder encountered in trying to create and maintain DDFs via the Btrieve API rather than through the relational engine. The bottom line is that you probably need to know a bit more about databases and data types than you would with DDF Sniffer, but that you will wind up with fewer problems in the long run. I would suggest that you give it a try and read a couple of papers from out Technical Papers page that I will give you links to.

    http://www.pervasive.com/support/technical/papers/ddf.html

    http://www.pervasive.com/support/technical/papers/ddfpaper.html

    Also Two or three papers here: ftp://ftp.pervasive.com/support/refshelf/DDFDepth.doc <ftp://ftp.pervasive.com/support/refshelf/DDFDepth.doc> , ddfpaper.doc <ftp://ftp.pervasive.com/support/refshelf/ddfpaper.doc> , and ddfwodbc.doc <ftp://ftp.pervasive.com/support/refshelf/ddfwodbc.doc>

    ftp://ftp.pervasive.com/support/toolbox/ has a copy of several tools including DDFCONV.ZIP Support Toolbox also has DDFCONV.ZIP

    http://www.pervasive.com/Support/updates/toolbox.tml

    3 rd Party/Unsupported DDF tools

    Visual DDF for Btrieve

    Although this tool can be used standalone to create standard Btrieve DDF files (File, Field and Index), it is primarily intended for developers who have existing Btrieve Data files and want to analyze them, create the DDF files and use that data with external products such as Microsoft Access or Microsoft Visual Basic.

    Disclaimer: May not produce a correct set of DDF’s

    http://www.vbonline.com/vbonline/prodat/btrieve/ <http://www.vbonline.com/vbonline/prodat/btrieve>

    Here is one of the tools off the Component Zone <http://www.pervasive.com/componentzone/>  that will ‘sniff’ and allow DDF creation. It is on no way pretty or user friendly, but it is a viable short term solution for a developer that knows the file layout. This is an eval copy only though.

    BTSFULL.EXE <file://///Ausintra1/Sales/resources/SalesFAQ_files/btsfull.exe>

    Using SQL DM

    execute a SQL Create Table Statement produces correct DDF for that particular table.

    Table Designer in PCC

    Relational Engine

    Q: Are there any books, manuals ?
    A: No ….etc…..
    Q: Are there any tools to retrieve and edit stored procedures?
    A: Not at the present time. The usual way we do this is to delete and reinsert a stored procedure. This is because Pervasive.SQL has been more an embedded database than a DBA’s database and generally if stored procedures were used the programmer kept a copy and it wasn’t a great hardship to drop and reinsert them.

    Q: Is it possible to extract the meta-data from the database and have it returned in SQL syntax? For example, if the following statement is used to create a table, can the statement later be returned from the database?
    Create table Test(Column1 Integer Not Null)
    A: No – not in SQL syntax form. However, the DDFs can be queried to collect this information.
    Q: Are user defined functions possible? That is, can a function written in c and stored in a dll be accessed from a stored procedure or sql statement, therefore extending the capabilities of the database?
    Answer: No.
    Q: Are cascading operations supported? i.e. When a parent record is deleted/updated the child record is also deleted/updated.
    Answer: Yes – this is defined in the context of setting up referential constraints (primary and foreign keys).
    Q: Are event alerters available? i.e. a client can register itself with an event defined in the database and be notified when that event takes place? If this is supported, how is it implemented? Does that database have to poll itself, or does it have some other mechanism?
    Answer: No; the only thing “close” is triggers (before or after insert, updates, or deletes) which can execute SQL syntax from a stored procedure.
    Q: What is the locking mechanism employed to ensure users do not modify each other’s data? Do readers ever block writers or writers ever block readers? Will a query that runs for several days successfully and accurately complete in a system with many transactions occuring to the data that is being queried? Will the result include any of the completed transactions that occurred during the several days the query was running? If so, how do
    we find out which ones were included? Is optimistic or pessimistic locking used?

    A: This depends, in part, on what interface the application is using. When coming through the SRDE, concurrent transactions will be automatically executed around each SQL statement to guarantee atomicity. Certain application interfaces support disabling the standard ODBC autocommit feature, where all SQL/ODBC calls are in a transaction until the user executes a commit or rollback, at which time a new transaction is automatically started.
    For applications using an interface that does not get processed by the SRDE (i.e. the Btrieve API, ActiveX, etc.), transactions can be explicitly implemented by the application. If not, the microkernel enforces passive concurrency to guarantee that users don’t unknowingly overwrite each other’s changes. This has always been a feature of the MKDE.
    When using transactions, writers block other writers, but do not block readers. If I’m not mistaken, all SRDE transactions are “no-wait” transactions, which means a blocking error will be returned to the application, which will have to re-try the operation. Btrieve API transactions can be wait or no-wait. Currently, the increment of locking employed for transactions is page-level. Writes in transactions lock the affected pages in the data file until the transaction has been completed. Other users can perform transactions on the same file, as long as they are affecting different pages.
    Q: Is it possible to modify the data files without the triggers and constraints being enforced? That is, if the engine is being accessed through the API or the files accessed through another tool will all triggers and constraints always be enforced?
    Answer: Referential Integrity constraints are always enforced, because this functionality is implemented by the Microkernel. So, no matter what interface is used by the application, the constraints are enforced.
    This is not true of triggers – the SQL engine enforces and calls the triggers as a result of insert/update/delete operations made by an SQL application. Since the MKDE does not currently support triggers, direct Btireve (non-SQL) write operations are disallowed if triggers are defined on a particular table. In other words, Btrieve write access is not available of files with defined triggers.
    Q: Are roles supported in the security model?

    A: No; users and groups are supported.
    Q: Can a single client have multiple concurrent transactions active within a single connection to the database?

    A: No; a database connection can only have one transaction active at a time. However, nested transactions within another transaction are supported in SQL/ODBC applications with the SavePoint and Rollback to Savepoint syntax.

    Q: Is a query optimiser implemented? Is it possible to see the plan that the engine will use to resolve the query?

    A: Note – this is not public information… it is on the CE intranet site.
    Yes, the SRDE does perform query optimization, and there is a query plan viewer.
    Following are the steps to make the Perasive.SQL 2000 Query Plan work. 1. In registry under Hkey_Local_machine/Software/Odbc/Odbc.ini, for the datasource, add registry key Dword value QryPlan=1 and String value QryPlanOutput=c:\test.qpf. 2. Open SQL DM, Run query to this datasource. This should create file test.qpf if everthing working correctly. 3. Use W3SQLQPV.EXE to open the query plan output file. W3SQLQPV.EXE should be in the PVSW/BIN dir. 4. Register SqlQuery.OCX. This can be done through SQL DM, Tools, Options. Add. Select SqlQuery.OCX in the PVSW/BIN dir.

    Q: Do you know of a ERD tool such as ER/Win, etc. that supports Pervasive.SQL? Can the tool reverse-engineer a Pervasive DB and also create a script to generate or alter the database?

    A: I don’t know other than ER/Win supported Btrieve 6.15 and should support Pervasive.SQL. ODBC compliant ERD tools should be able to provide some or all of this functionality and Data Junction may also provide some of this functionality.

    Q: Can table structure changes be made on a live (in production) database with users connected?

    A: ALTER TABLE to add or drop fields is supported, and the data files are adjusted accordingly. However, I believe this can only be done when there are no users currently accessing the file being modified.

    Q: How is a ‘client’ defined by the server engine. Is it a workstation (with possibly many connections into the database), is it a single database connection no matter where it comes from? To rephrase the question: Does a single workstation with many open connections to the database qualify as one
    client or many?

    A: Each connection counts as a session, but all the sessions on a single workstation count as one user (i.e. take up one user-count license). The only exceptions to this are if a DOS program using Brequest.exe or BreqNT.exe connects via IPX/SPX and one or more Windows programs connects from the same workstation via TCP/IP it may be counted as two users. This was entered as a bug some time ago and may have been resolved in an update.

    Q: What happens when the transaction log runs out of disk space. Is the database still usable, does the database shut down, does the database crash?

    A: I don’t know. We expect that an error will be returned , and logged to the PVSW.LOG, and it will continually return this error until disk space is cleaned up.

    Q: Are repeatable reads supported in the transaction model? That is, if a transaction is started and a select statement executed once today, and then > once tomorrow (the original transaction still being active), and every record is change by another user in another transaction between the two queries. Will the result of the queries be the same or different?

    A: Write operations in transactions are isolated until they are committed. However, if I start a transaction and read all the data for a given SELECT statement, and then, while in the same transaction, again read all the data for the same SELECT, I could get different results if another user has inserted/updated/deleted records and committed the changes.

    Q: Can custom exceptions be created in the database? That is, when some specific action happens can a user defined exception (that is stored in the  database) be created and raised?

    A: Only in the scope of triggers.
    Pervasive.SQL is an excellent database that fits the characteristics of 95% of the organizations and web sites that I have run across who need a database. It does not have the (overly?) rich feature set that enterprise databases such as Oracle, DB/2, or Sybase have so if a project/solution has a full time DBA, envisions queries running for hours/days, unlimited time/budget, or will have terabytes of data then Pervasive.SQL should probably not be considered. For all other cases I believe that Pervasive.SQL can a viable candidate and bring certain benefits to the project that no other database provides plus delivering comparable if not better performance depending on the interface used. The fact that it can have a high learning curve for the developer, but almost zero for the end user and that it comes in Tango provides a compelling reason to evaulate it more often.
    In the last few weeks I have fielded some questions from sales reps about our products that indicate to me that they are selling to a different type of customer than we are used to. The following is actual questions and answers from scenarios with two different very large customers interested in Pervasive.SQL. I know it’s long but there is some pretty good info in here.
    We are starting to sell to a new class of customer (rather than the installed base) who only knows about relational databases. Because of this they may question the features (or lack thereof) of our database, particularly on the ODBC or relational side. They may state that they cannot use our database unless it has exactly the same features that they are used to (in fact they may insist on features their current database does not even have!) It is important to understand what they are doing and why. If the person you are speaking to does not know enough to tell you, have them find someone who does know enough. If you don’t know enough to figure out whether or not we will work for what they are trying to do bring a technical person into the discussion (but get some solid info for them to go on first, please).
    Remember, our customers are usually not buying our product to fill a desktop or enterprise niche. They have been using a desktop or enterprise database to fill an embedded niche. As long as we can show then how they can do whatever they are trying to do with a reasonable effort then our database is the best for the job! I wouldn’t try to match up feature to feature, I’d emphasize what we do best, embeddability, speed, flexibility, price! Finally also remember, everything in computing is a tradeoff, we just want them to trade our way and be very successful at it.

    ============ Scenario 1 ===============
    These are some answers to questions that may help you to understand better how to sell to these buyers. The initial info I was given on the questions was extremely sketchy and it would have helped to know what the customers requirements for a database were and why – also what interface(s) was he wanting to use and what development environment?
    Q; X suggested I contact you on this one. I have a potentially huge opportunity with XYZ Corp.  They are looking at Pervasive.SQL, but are running into a problem with the memo field.  They were told that there can be only 1 memo field per row and that it has to be the last field.  Is that correct?
    A: With the SQL or ODBC interface that is correct, however, I’m not quite sure why they would want more than one memo field, in a table, since one could and should design a database so that that is not an issue. On the Btrieve side (and possiblly with ActiveX too) you can use that one variable length field for as many memos as you’d like, though I’d still design the database so that the memos or notes were in a different table.
    Q: Is there a way around this?
    A: In computers there is always a way around ANYTHING, but the question is “is there an easy way around it that is worth the effort?”. Not having any idea of what it is he’s trying to do I can think of potentially three ways around it: design the database with those restrictions in mind; use the Btrieve interface (of the ActiveX control) for the functions that the ODBC or ADO interface doesn’t handle; wait until the next release comes out which should have very large BLOBs and potentially handle multiple notes fields.  Again we need to know what his design requirements are and why? It may be that this gentleman has only used Access and Paradox in the past and just wants a bunch of notes fields because he is used to it. It may be that he really knows what he’s doing and we cannot yet meet his requirements via the SQL or ODBC interfaces. It may be that he just hasn’t thought his requirements through and can’t see the forest for the trees. We need more details – also what environments is he developing in?
    Q: Also, he mentioned something about the size maximum being around 32k.  If this info is correct, he is going to have a hard time using our database.
    A: Why is he going to have a hard time using it? I can see in some instances why they might want a bigger field than that, but 32K is a lot of notes. The Btrieve interface can handle only one variable length field as well, but it can be up to 4GB or 64GB with the 7.x file format. So there probably is a way around it unless he just wants to do it they way he wants to do it and that is that.
    ============ Scenario 1 ===============
    Customer: I was referred to you by one of your sales reps.  I work for X Corp and have a product that uses a database system. Today, out of the box, our customers get MS Access, but we would like to provide something better. I’ve looked at your web site and looked over the information provided about Pervasive.SQL. I have a few technical questions. Could you take a minute and address them, or forward them to one of your tech marketing people.
    Background: Server on WinNT 4.0. Clients on Win95, Win98 and WinNT 4.0.  We use ODBC drivers to talk to the database.
    A: This is good, ODBC and especially ADO, UDA, COM, DCOM, and OleDB are the relational development options of our future (DAO and Rdo are OK too).
    Questions:
    1.  Does P.SQL support Binary Large Objects (BLOB)?  If so, what is the data type?
    A: Yes, P.SQL supports BLOB via the Btrieve API. The data type is LVAR, NOTE, or user defined. We only use datatypes in the Btrieve API for indexing and as long as you know what to do with the data you put in Btrieve you can store it and retrieve it. On the SQL side you can have a Note or LVAR of up to 32,761bytes, but there are plans for BLOB support on the SQL side (thus ODBC) of probably up to 2GB which should be available sometime next year.
    Q: What version of ODBC do you support ?
    A:  Currently fully ODBC 2.0 compliant. We will have an OleDB provider in the future and probably skip ODBC 3.0 like other vendors (except Intersolve, who wrote the spec) are doing as well.
    Q: Who writes your current ODBC driver?
    A: It was originally written on contract, but it has been taken in house for almost a year now (the new one will be mainly written by a partner as well, but we don’t want to comment on that until it is in beta, and then marketing will determine what we say about it).
    Q: Does P.SQL take advantage of multi-processor machines?
    A:  Not directly, but we do often benefit from multiprocessor systems. Multiprocessor systems are not necessary since we are usually bound by I/O or Network constraints long before we run out of processor cycles. We do run on and test with two and four way SMP systems and are planning enhancements to take more advantage of SMP.
    Q: What is the max number of tables in a database?
    A:You can have an unlimited number of tables, but on the relational side you are limited to 65,535 minus the number of dictionary files you have (max of 11) per database. You are also
    limited to 64,000 concurrent open tables for the MicroKernel Database Engine.
    Q:  What is the max columns per table?
    A: You are limited by the 4K page size so you could have 4088 columns and 119 key segments in a table, but that would limit your column size to 1byte. You could have over 511 8 byte columns in a table. 32K total record length counting the variable length field on the SQL side (SQL Server 7 has an 8K record limit by contrast and SQL server 6.5 had a 2K record limit – limited by page size).
    Q: What is the max rows per table?
    A: Virtually unlimited. Actually limited by Table size which is 4GB in 6.X format and 64GB in 7.X format.
    Q: What is the max Table Name Length?
    A: 20 char.
    Q: What is the max Column Name Length?
    A:  20 char.
    Q: Max number of concurrent users?  I know this is a hard question to answer without more information.  I am interested in what others are doing.  Do they use 5-10 users?  20-30 users? or more on the level 200-300 users?
    A: While probably the majority of our installed base is under 100 users, we do have several customers who have installed bases of 400 to 1000 users and are extremely pleased with the performance. The site with the largest number of concurrent users that I’m aware of today runs with 2000 users. Our database is also used for high volume data acquisition in fields such as manufacturing, cell phones, utilities, and pagers which may not have as many concurrent users, but has extremely high numbers of transactions. Pervasive.SQL 2000 was optimized for 50 users and up, and we routinely test with 200+ users on the DB engine for throughput. We have customers using databases of up to 37GB and data warehouses of over 90 GB.
    Q: Have you done any tests with Microsoft Visual C++ v5.0 or v6.0?  Any problems?
    A:  We teach our programming classes on v5.0 and our maintenance engineers and Professional Services consultants use v6.0 right now. No known problems.
    Q: Any problems using MFC from Microsoft (i.e. Using the CDatabase and CRecordSet classes)?
    A: No, we use it in house and have seen no issues.
    Q: Any problems with ADO v2.0 from Microsoft?
    A:No, we have tested it for compliance and have samples in our SDK of ADO database access.
    Q: Is an integrated install with our product possible?
    A: Yes, the glue DLLs  can easily be integrated into your install with the Lite Installation Toolkit which also includes the client for the client/server product. To distribute the workstation engine with an integrated install you must get a Distribution license which comes in several levels. If you have the volume to become a manufacturing partner then you get the full Installation toolkit and rights to embed the client/server engine in your application. We currently have over 90 Manufacturing partners and sign 10 or more a quarter because of the business benefits.
    Q: Do you support encryption across the network?
    A:  No, but you can encrypt sensitive data with the Btrieve API and decrypt it with any method you like. It is possible to keep plain text from being transmitted across the network via compression as well.
    Q: Do you have any white papers that compare you to other product on the market?
    A:  Yes, see the paper at: http://www.pervasive.com/support/technical/white/w_compete.html

    If you’ve read this far I commend you and ask that if you run into features that stop sales please forward them to Product Marketing (along with the customer and sale it stopped) so that we can evaluate whether we can include it in a future release.

    Crystal Reports 7 for Pervasive

    Q: We have purchased Crystal Reports 6.0 and 7.0, there we found the w32mkde.exe, WBTRV32.DLL, w32mkrc.dll from Btrieve. Can we distributed royalty free this components? Or we have to contact Seagate Software?

    A: No, those are Pervasive Software components and they cannot be distributed royalty free from out of Seagate’s software package unless the license specifically states that you can do so (I’m very sure that it doesn’t but if you can fax or e-mail the parts of the license that state that specifically we can advise). Futhermore, those components are from the Btrieve v6.15 UDL (Unlimited Distribution License) that Seagate purchased many years ago and that product and license are no longer available or supported. We do have products that replace Btrieve v6.15 and the UDL and will be happy to put you in touch with one of our partners in Argentina who can advise you on the proper products to use and can supply you with those products.

    Support & Support Contracts

    Q:

    A:

    Professional Services

    Q:

    A:

    Education Services (Training)

    Q:

    A:

    Pervasive.SQL 7

    Q:

    A:

    Btrieve 6.15

    Q: We have purchased Crystal Reports 6.0 and 7.0, there we found the w32mkde.exe, WBTRV32.DLL, w32mkrc.dll from Btrieve. Can we distributed royalty free this components? Or we have to contact Seagate Software?

    A: No, those are Pervasive Software components from Btrieve v6.15 and they cannot be distributed royalty free from out of Seagate’s software package unless the license specifically states that you can do so (I’m very sure that it doesn’t but if you can fax or e-mail the parts of the license that state that specifically we can advise). Futhermore, those components are from the Btrieve v6.15 UDL (Unlimited Distribution License) that Seagate purchased many years ago and that product and license are no longer available or supported. We do have products that replace Btrieve v6.15 and the UDL and will be happy to put you in touch with one of our partners in Argentina who can advise you on the proper products to use and can supply you with those products.

    Q: What happened to Btrieve ? Was is discontiued?

    A: No, Pervasive.SQL 2000 contains a full and complete version of Btrieve 7.5 which fully supports Btrieve 6.15 and earlier applications and Btrieve 6.x file formats without conversion.

    Scalable SQL 4

    Q:
    A:

    Scalable SQL 3.01

    Q:
    A:

    Btrieve 6.30

    Q:
    A:

    Btrieve 6.10

    Q:
    A:

    Btrieve 5.X

    Q:
    A:

    The Actual Migration Process from Btrieve 6.15 to P.SQL 2000

    Workstation Install: For Dos Applications:
    <file://///Ausintra1/Sales/resources/SalesFAQ_files/image001.gif>
    Preface:
    When migrating a DOS application from Btrieve 6.15 to P.SQL 2000 the following assumptions will need to apply:
    1)   Only Btrieve API calls were used to access the database.
    2)   There is only one Btrieve database located on this one workstation.
    3)   All data is backed up and in a safe place.
    4)   P.SQL supports Windows 95/98 and does not support Dos 6.22 anymore.
    5)   Your application and the Btrieve database both reside on this one workstation.
    6)   You have access to a CD to install P.SQL 2000
    7)   Since each application is different the migration of each application is directly dependant on how it is written. So if nothing too abnormal is in the application the migration should be fairly clean.
    8)   When using a Windows NT or Windows 95/98 network always use TCP/IP.
    9)   Use IPX/SPX for Novell Netware networks.
    10) Turn off all power management. There are two settings, one in your screen saver and the other in the computer BIOS
    11) Use a actual port for all printing when using a Dos application. (EX. LPT1: //server/hp4plus)
    Step one – Install the current application onto a workstation. Make sure all service packs  (for the current application) are installed and test the application thoroughly for stability and speed.  This is a very important step because if we need to isolate any problems with the database we need to know how the application acted before the migration.
    Step two – Install the P.SQL 2000 database engine. Remember installing this engine will delete Btrieve off the workstation you are on. The actual database will remain intact and in a Btrieve 6.15 format. The format of the database will not be changed over until a rebuild occurs with the new engine installed and running. The database engine can be left in the Btrieve 6.15 format for use with the P.SQL 2000 database engine.
    Step three – Usually the Dos application is called from a batch file and in this batch many initializations occur. Also located in this batch file are the Btrieve.exe call to start the Btrieve database engine and the call to start the DOS application.  This Batch file is custom to each DOS application and must be modified manually.  Two new things must now occur with P.SQL 2000 to get a DOS application working.  One, a file called Bdosbox.exe must be executed. This file will create a window to run a program in the background. This window will minimize and you will see it located in the task bar. Only one occurrence of this program will be needed to run any dos applications that will be connecting to a P.SQL database. Bdosbox.exe only controls Dos based applications running on Windows 95/98/NT operating systems. (NOTE: Most Dos based application do not work on Windows NT operating system directly, this is dependent on how each application is written.)  Next, all calls of Btrieve.exe must be removed from the batch file. Also Btrieve log files do not need to be initialized on the Btrieve.exe line. The P.SQL 2000 engine will take care of all log files. Btdossub.exe is now used to start the P.SQL database engine. Once this file is called a P.SQL engine will now appear in the bottom right side of the taskbar, signifying that the P.SQL engine is started (for Windows 95/98)(NT runs as a service so no icon will appear).

    Step four – is to run the application batch file and run your program.  Check for stability and speed.

    Q: We at have been using Btrieve for over 10 years. Our database was developed with a very old version of Btrieve (older than v6.0). Do you have any application notes or a white papers that show how to convert Btrieve database files to Pervasive SQL 2000 database format such that I can use SQL to read the data from the database?

    A: There is a paper on migrating Btrieve 6.15 applications to Pervasive.SQL that actually will work for any application prior to Btrieve 6.15. One other note is that Pervasive.SQL 2000 will support any Btrieve DOS application and any Btrieve Windows 3.x (there are no Win32 applications prior to Btrieve 6.x) natively and P2K will support Btrieve data files back to 4.x format so no conversion is necessary. That said, conversion is highly desirable to at least 6.x file format for some underlying technical reasons. One approach to this would be to test the application as is with files in whatever format they are in until you are satisfied that the application runs (P2K is as close as humanly possibly to completely backwards compatible and most issues we might find would be considered bugs, but we aren’t perfect so testing is in order for a couple of reasons that can be addressedlater if you would like). Then I would convert the database to at least 6.x file format (this limits you to 4GB table size – not the 64GB of 7.x) You may use the file rebuild utility. Test further. Everything should run just fine as has been the experience in literally 99.9% of all cases (and hundreds (100s) have been done). Some of the configurations may not be intuitive and setting everything up to run without conflicts is usually the biggest hurdle experienced Btrieve developers and IT personel face in using Pervasive.SQL 2000. It is recommended to set up Pervasive.SQL 2000 on a clean system and testing it first, then move the data files and application over to the clean system without the old Btrieve components. Later you can install P2K on a system with older versions of Pervasive to see how it works in more “real world’ environments. In my experience developer systems are usually the ones that have the most problems, possibly becuase of all the registry settings and such and also becuase of all the different things developers tend to have installed on their systems and the special ways they configure their systems so be careful which systems you install it on initially. There are usually simple reasons for these issues and they usually don’t carry over into customer installs but they can leave a negative impression in a developer’s mind. That is why I usually recommend installing it on a clean system initially to gain confidence that our product installs simply and easily and will support all older Btrieve data and applications. Then go for the more challenging environments once a baseline has been established. We work very hard to make our installation foolproof, but with the complexity of systems and networks today noone can get 100% of the way to foolproof (that’s why we have our excellent Support department). The next step in doing what you require is to provide good DDFs or data dictionariy files for your Btrieve database. Basically the Btrieve program knows the schema of the database because it is coded into the application, but a third pary application like any SQL application needs the complete schema (which is not contained by the Btrieve files) including all the files (tables) in the database, the fields (columns) in each file, and the indexes (keys in Btrieve speak) on those columns. I’ve attached a paper that explains a lot more about this. We have tools in the PCC to help with doing this for existing Btrieve files, but for large databases I prefer to use SQL as a DDL tool because it’s more flexible than a GUI tool. It is suggested to use the wizard for the first table or two until you’re comfortable and confident in creating and editing DDFs. If you have older DDFs they will have to be checked and converted and most probably fixed because DDFs created with Xtrive, DDF Builder, and other older tools often created inaccurate DDFs that worked OK with older ODBC drivers, but cause problems with the more truly SQL and ODBC compliant relational engine in Pervasive.SQL 2000.

    Workstation Install:  For Window Applications:
    This migration is the same as the workstation install for Dos applications except that you can skip step three. The bdosbox.exe and btdossub.exe do not need to be run. Once P.SQL 2000 is installed the P.SQL database engine will be running by default look for the icon on the bottom right corner located on the task bar.
    Workgroup Install: For Dos and Window Applications:
    <file:///C%7C/Program%20Files/Apache%20Group/Apache/htdocs/Sales/resources/./SalesFAQ_files/image003.gif>
    A workgroup consists of two or more computers that are connected in a network and you are running a workgroup networking operating system. Windows 95/98 comes with this type of operating system.
    The installation for the Workgroup follows the same steps as the Workstation install for Dos applications, except that you do need to install the Workgroup engine instead of the Workstation engine. The workgroup engine is made for networking and the workstation is not.  Once the first workgroup station is up and running the following steps will help you connect the second workgroup station.
    After the first workgroup station is up and running you want to test the database connection just running on one station. You actually want the database up and running and connected while the other workgroup computers are being connected.  This is also a good time to run smartscout. This will test your network for connectivity. You can use the demo database that is included with each install of  the workgroup station to test the network. (The demo database is located on the c:\pvsw\demodata directory).
    The next step is to install the workgroup engine on the second computer now. After the workgroup engine is installed then you need to go to the Pervasive Control Center. The Pervasive Control Center or the PCC is located by clicking on the start / programs / Pervasive / Pervasive Control Center menus.
    The first place to go it to click on the Center / gateway locator pull down menu. This gateway locator is used to setup where the database will reside. When you run this menu item the a new window appears and the first question is what is the target directory.  This second computer will need to map a drive to the computer that is hosting the database. The Target directory is this mapped drive and directory that the database resides in. Once you set this directory then you want to select change. A file will now be created in this directory called “~pvsw~.loc”. This file will hold the hostname or computer name of the computer hosting the database. This file will become read only.  Whenever you call a P.SQL or Btrieve database the database engine will look for this ~pvsw~.loc file and know which computer is hosting this database.  Next,  go to the PCC and under the frame on the right side, or click on center and then find the configuration utility. After you click on this utility the left frame will open a new window. In this new window click on client and then access. You will need to maximize these windows so that you can see all of the keys.  You will now need to find the local and requestor keys.
    (This configuration utility will be found only on the workgroup and client/server installs) The local key is a yes and no question for which database engine are you going to use. This local key will be set to yes you want to use your local database engine to access a database and no if you want to use a non-local database engine to access a database. (Special case – if you have a local database and also need to connect to a database on another computer you will need to toggle local to “yes” or “no” depending which database you will be accessing. The Requestor key is also a yes or no question that is referring to which database engine that you want to use. The requestor key will be yes if you will be using a database engine that is no local and no to use the local database engine.
    Local key

    Requester key

    Workgroup with database on local computer.

    Yes

    No

    Workgroup with database on another computer (non-local)

    No

    Yes

    Database on another computer but you are the only one to access this database.

    Yes

    Yes

    Note for Dos applications: (See workstation install) For Dos applications you will need to include the steps for modifying your batch file, using Btdosstub.exe and using Btdosbox.exe.
    The last step is to call your application and test the database for speed, stability, and for printing.

    Client/Server Install: For Dos and Window Applications:
    <file:///C%7C/Program%20Files/Apache%20Group/Apache/htdocs/Sales/resources/./SalesFAQ_files/image005.gif>

    A client/Server network is a network that consist of at least one or more dedicated servers which include operating systems like Windows NT or Novell Netware.  With the price structure for P.SQL 2000 it is most economical to have a client/server network after 7 workstations in a network. To install the client/server version of P.SQL 2000 is actually easier to install than the workgroup model. First install P.SQL 2000 Client/Server version onto the Server of a network. This must be installed onto a Windows NT or Novell Netware server. Once P.SQL is installed follow the workgroup installing instructions for setting the gateway located. On the server the Local key should be set to yes and the requestor should be set to no. The Client/server Cdrom has a client piece of software that must be installed on each workstation on the network. The settings for each client should set local key = no and requestor key = yes. (See workgroup install for other networking settings)

    I ran across a developer doing exactly the same thing yesterday and there were a couple of things that he needed to change. His app relied on the  default directory (the same one he runs the app from) to find the database. If he is not using any path he may have to add that to his path. We also found some DOS characteristics (using a / not a \ to create a dummy file for the buffer) that kept his application from working in NT. Lastly he had some  5.X files that showed up as 3.x files and would not convert to 7.x files with the rebuild utility (though they would with his utility) that we are looking into. Altogether minor issues, but annoying to deal with and it took Bobby Dumbeck’s insight to quickly identify the / vs. \ issue. I’ll be writing FYIs on these issues as soon as I can. So the net-net is that yes there may will be some small issues that he will have to work through with developer support or an SE, but very minor and in fact there may be none at all depending on what he used to write his application and how he wrote it (the developer I worked with was using quick basic).

    Regards,
    JohnR
    —–Original Message—–
    From:
    Sent:              Thursday, February 03, 2000 4:48 PM
    To:                  John Rothgeb
    Subject:         DOS to Windows Apps
    Hi John,
    I was just talking to a developer Carl Miller and he has DOS based apps on either 5.x or 6.x and he is trying to figure what he would need to do to convert his app over to a Windows based application that would run on the workgroup engine.  You said before that he could still run his DOS based app through the DOS box with Pervasive.SQL 2000.  Would there be any reason that he would need to change his code base in order to do this.