PSQL v11

PSQL 11 with Crystal Reports X Standard

wowacomputer@yahoo.com — Fri, 18 Mar 2011 00:25:20 GMT

Ok I am going absolutely bonkers trying to figure out how to get PSQL 11 & Crystal Reports X Standard to play nice. It appears that only the ODBC driver plays nice with Crystal Reports X as far as passing dates in SQL Command or Stored Procedure. I have tried the Betrive File Data Source, Pervasive OLEDB Driver (which fails to maintain information in Crystal) and a few other things. Of course the ODBC driver is not a licensed feature in Crystal Reports X Standard which adds to the headache but I have found that using a Microsoft Data Link file to the ODBC system datasource gets around that for development, but other issues creep up when running it from the viewer software (Resort Data Processing [RDP]). The issue when using something other than ODBC (even though RDP releases reports that work with Btrieve File Data Source) a Pervasive error about date conversion is thrown. Depending on answers below this might become an issue again.

In this project I have found numerous errors with how PSQL ODBC & OLEDB drivers interprets Select commands when combined with Union Statements, User-Defined-Functions (UDF) etc. For example, if a Select of a Select with union which calls UDF at first level just passes all null arguments to the UDF rather than actually processing the function, move it up to the outer select statement and it will work. Or another. Select of Select w/Where and then grouped to count or sum will ignore the where statement of the inner select. Kind of makes it hard to get anything counted properly when you only want to count based on specific criteria. Or yet another. When attempting to use a temporary table or table variable it will crash the PSQL server all together.

One of the issues is I must union 2 tables to get to the data that needs to be reported. Combined they are about 100 MB. So I have gone the route of stored procedure (SP) to insert just the desired records to a structured table in the database I created then retrieve the desired result out as a table from the SP This also gets me around some of the problems in paragraph 2.

Now I have interesting issues in Crystal. I can add 1 stored procedure and it will execute in Crystal (not from RDP). If I add a second stored procedure and relate appropriately Crystal simply closes generating and unexpected closer report. I have tested the SPs in another application I created as well as the control center and get exactly the results I want. The other application I created uses the Pervasive OLEDB provider.

This particular database is using Btrieve Classic Security & metadata version 1 as it has been updated from older PSQL versions. There is only 1 user associated and any grant statements simply return a message saying they do not matter because the database does not have security. The DSN points to the Pervasive ODBC driver and is fully functional (set to Normal - NOT read only),

This is what I get when I try to call a stored procedure that does the insert to a different table and return the records:

Failed to open a rowset
Details: ADO Error Code: 0x80040e09
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Pervasive][ODBC Client Interface][LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng][Pervasive][ODBC Engine

Interface]Access denied to a READ-ONLY data source.
SQL State: 42000
Native Error: -5099

This error is what occured from trying to run the created report in the RDP viewer that used the Microsoft Data Link file pointing to the system DSN all of which reside on the same server.

Logon Failed
Details: ADO Error Code: 0x80004005
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [Microsoft][ODBC Driver Manager] Data Source name not found and no default driver specified
SQL State: IM002

From what I can tell, it appears that they Crystal crash relates to these two, because I can get them here and there with only 1 stored procedure within crystal as well.

I am at a loss can anyone provide some best practices for PSQL 11 with crystal reports? Or shed some light on the above problems.

Thanks

Does PSQL Support an UPSERT / MERGE Statement

mattwilkinson — Wed, 27 Apr 2011 15:46:51 GMT

I have done a quick search in the docs for this but can't find a mention of it - does such a command exist?

please help me , how to use case ... when ... then...

dc0106@126.com — Wed, 04 May 2011 04:04:28 GMT

There is a SQL , but it has happend a error, who can fix it? please helpe me!

The Error : [LNA][Pervasive][ODBC Engine Interface][Data Record Manager][SPEng]34: ';' : Syntax error

There is a syntax error, but I do not know how to fix it!

CREATE PROCEDURE SP_SetDrugMaster
(
IN :ID CHAR(8),
IN :BPRICE CHAR(9),
IN :DIN CHAR(8),
IN :TRDNAM CHAR(26),
IN :GENNAM CHAR(26),
IN :DFRM CHAR(12),
IN :PRCDATE CHAR(8),
IN :STRNGTH CHAR(12),
IN :Type CHAR(50)
);
BEGIN
CASE :Type
  WHEN 'Add' THEN
   INSERT INTO DrugMaster
    (ID, BPRICE, DIN, TRDNAM, GENNAM, DFRM, PRCDATE, STRNGTH)
   VALUES
    (:ID, :BPRICE, :DIN, :TRDNAM, :GENNAM, :DFRM, :PRCDATE, :STRNGTH);
  WHEN 'Update' THEN
   UPDATE DrugMaster
   SET
    BPRICE = :BPRICE,
    DIN = :DIN,
    TRDNAM = :TRDNAM,
    GENNAM = :GENNAM,
    DFRM = :DFRM,
    PRCDATE = :PRCDATE,
    STRNGTH = :STRNGTH
   WHERE
    ID = :ID;
  WHEN 'Delete' THEN
   DELETE FROM DrugMaster WHERE ID = :ID;
END;
END

Select statement returns no data

hagen681 — Thu, 28 Apr 2011 09:35:37 GMT

Hi!

My ddf for the table has two fields i use as parameters for a selection. Both fields are separatly not uniqe, in field one is a object-number und field two is a option number. Each object can have various options. The sum of both fields is uniqe. The data definition is from the original software manufdacturer, i can not ask him for help, and now my problem:

the select statement:

select * from "TEST.BTR" where (option=1 and ObjID=88809)

but there are definitivly two records with the ObjectID 88809 one with the option=1 and one with the option 2. If i replace the option=1 by option>1 then the result contains both records, but i think option>1 is logical false for the first roe (option=1). I have no idea how to solve this. Is it possible that the ddf-declaration for the option-field is wrong?

best regards

Hagen Schloemer

status code 161

bernard@kel.fr — Fri, 15 Apr 2011 09:15:14 GMT

2 PC use the same datas stored on one of them. A licence "Pervasive v11 Workgroup 1 user" is installed on each PC but the two users can't access the datas at the same time. In this case, there's a status code 161.

Do I have to replace my current installation from two "Pervasive v11 Workgroup 1 user" to one "Pervasive v11 Workgroup 1 user" and one "Pervasive v11 Workgroup user count increases 1 user" ?

Thanks in advance,

Bernard

Equivalent ODBC statement for this ActiveX Control code

mattwilkinson — Mon, 11 Apr 2011 15:45:59 GMT

We ship out product with just the .DDF files so that when the application starts for the first time the tables are created using the following code:

with VAccessCtl

           .TableName = FileName
           .DDFPath = DDFLocl
       .Location = DDFLocl & MKDName & ".MKD"

Rfg = .Open

if Rfg=12 then

      .PageSize = 4096
                .RefreshLocations = True
                 Rfg = .Create(True)

end if

end with

Can someone tell me what the equivalent SQL statement is for the above?

Read 70,000 records once into an array of structures.

bob81947 — Fri, 08 Apr 2011 13:32:16 GMT

I want to read all the records of a file into a memory array once, at the beginning of the program, and manipulate it there for an incremental listbox view. In another post get next extended was suggested and would be great for filtering etc but is somewhat overkill. I use PowerBasic and have handled upwards of a half million records in a listview. Speed is no problem for any string arrays manipulated in memory. Since the file I want is fairly static, I believe this would be a faster easier way.

Also it seems that the size of the data buffer is limited to 65,000 or bytes with Get Next Extended.

With Powerbasic and the file I want was a random file with the same structure, I could read it in one get. The individual string limit is 2 gig.

Bob

Populate a listbox of 70,000 records. Fastest option

bob81947 — Thu, 07 Apr 2011 14:53:08 GMT

A customer has requested a fuzzy search with incremental matching records loaded into a listbox as each letter is typed. They can use * before, after or not at all to indicate that the it needs to be at the beginning of the field, somewhere in the middle or trailing the field.

With every letter typed, the entire file has to be traversed in the case of the non-indexed field. I'm thinking it would be nice to read just certain fields into a memory array or string and search in memory for the records and lookup the partially matching records from the file based on finding the information in the memory array into the listbox. This would be an incremental search.

Get Next is fine on smaller files but there is a noticeable, annoying wait on larger files..

I don't know how to use GET NEXT EXTENDED and how to translate that into individual search array. The purpose is to find just one record where the search criteria may be in any position within the non-indexed field.

What is the fastest way to dump certain fields from a record of a 70,000 record file into a memory array. Doing it just once seems to me to save a lot of time when the user isn't quite sure of what he is looking for. The field may be a long description of an inventory item or even a comment.

The user may also be using the lookup several times in one session in a program so I'd probably keep the array till they left the program

Memory management of the array is not an issue. Powerbasic has extremely fast in memory string handlng.

With Random Files it's easy. I just use a single Get and put the entire 70,000 records into a memory variable and search based on the known record size and position. Subgroups are almost instantaneous this way.

SQL statement is not an option here. Only btrieve operation code.

Bob

Connecting to Pervasive .DTA files With .NET Provider

HLeyes — Tue, 05 Apr 2011 17:52:56 GMT

Can someone direct me to the download that would install the .dll on my machine that would enable me to use the PervasiveODEDB provider for data access in a .NET 2008 project?

Thanks,

HL
Fort Myers, FL

User Function Troubles

dpbouchard — Fri, 25 Mar 2011 21:27:39 GMT

Can anyone tell me what is wrong with the below code? Every time I try to run it by calling it with SELECT md(120199) I get the particularly non-helpful error "Expression evaluation error". The problem I am trying to solve is that my customer's business app stores some of its dates as integer values with the month as the 1st 2 digits, the day as the next 2, and the year as the last 2. This function taken from a successful SQL Server implementation is meant to return a correct date field. Please don't suggest that this integer field should be changed to a date field, I know that, but the app is not going to change just for me! Also, I know the leap year functionality isn't factually correct, but it is for this application.

Thanks in advance for any help!

CREATE FUNCTION "md" (IN :intDate INTEGER)
RETURNS DATE AS
BEGIN

    DECLARE :intMonth INTEGER;
    DECLARE :intDay INTEGER;
    DECLARE :intYear INTEGER;
    DECLARE :intCentury INTEGER;
    DECLARE :strDate CHAR(6);

    IF :intDate < 10000 OR :intDate > 123199 THEN
        RETURN NULL;
    END IF;

    SET :strDate = REPLACE(CONVERT(:intDate, CHAR(6)), ' ', '0');

    SET :intMonth = CONVERT(SUBSTRING(:strDate, 1, 2), SQL_INTEGER);
    SET :intDay = CONVERT(SUBSTRING(:strDate, 3, 2), SQL_INTEGER);
    SET :intYear = CONVERT(SUBSTRING(:strDate, 5, 2), SQL_INTEGER);

    IF :intMonth < 1 OR :intMonth > 12 THEN
        RETURN NULL;
    END IF;

    IF :intDay = 0 THEN
        SET :intDay = 1;
    END IF;

    IF :intDay < 1 OR :intDay > 31 THEN
        RETURN NULL;
    END IF;

    IF :intMonth = 2 AND :intDay > 29 THEN
        RETURN NULL;
    END IF;

    IF :intMonth = 2 AND :intDay = 29 THEN
        SET :intDay = 28;
    END IF;

    SET :intCentury = 1900;
    IF :intYear <= 29 THEN
        SET :intCentury = 2000;
    END IF;
    SET :intYear = :intCentury + :intYear;

    RETURN CONVERT(CONVERT(:intYear, CHAR(4)) + '-' + REPLACE(CONVERT(:intMonth, CHAR(2)), ' ', '0') + '-' + REPLACE(CONVERT(:intDay, CHAR(2)), ' ', '0'), SQL_DATE);

END;

Bind controls to a PervasiveOLEDB recordset

DJJenkins99 — Sat, 26 Mar 2011 16:21:35 GMT

Does anyone know how to bind controls with VB or VBA on a form to a recordset created using PervasiveOLEDB?

I know how to create a unbound form but I need to create a subform which is harder to do with unbound controls. I tried the ActiveX pervasive controls but the results were crude and not very compatible with the MS Access programming environment.

(I posted this once already by mistake because I now realize it should be a new thread)

PervasiveOLEDB returns only one record to a recordset

DJJenkins99 — Fri, 25 Mar 2011 20:33:08 GMT

Can anyone tell me why this code is only returning one record from a table with 72 records?

cn.CursorLocation = adUseServer
    cn.ConnectionString = "Provider=PervasiveOLEDB;Data Source=SPORNTEST0311"
    cn.Open

    rs.Open "POHDR", cn, adOpenDynamic, adLockBatchOptimistic, adCmdTableDirect

I'm using MS Access 2010, PSQL 11 Workgroup edition

PSQL 11 odbc Error

DJJenkins99 — Fri, 25 Mar 2011 14:34:25 GMT

I am using MS Access 2010 and PSQL 11 Workgroug edition connecting through ODBC running Windows 7 ultimate.

I am trying to connect to a local PSQL 11 table using a PSQL ODBC connection and I am getting this error: "Record is too large". The explination of the error is as follows:

<< "You defined or imported a table with records larger than 4K. This error occurs when you enter data into the record — not when you define the table structure. Redefine the table by making some fields shorter, removing unneeded fields, or moving some fields to other tables." >>

This table belongs to a proprietary system that we have been using for years. The table has less than 20,000 records. When I export all the data to MS SQL Server with the identical table structure I don't get the same error through ODBC.

Is there a workaround this? Should I use a different method to connect to the table that doesn't impede performance too much? I cannot change the data or modify the records and I need real time access to the data.

Problem need 9 licence - buy 3*3 user Pervasive PSQL v11 Workgroup

m.brylowski@jemar.eu — Tue, 22 Mar 2011 16:59:18 GMT

We need 9 licenses pervasive client. We bought 3 of “3 user Pervasive PSQL v11 Workgroup” because we did such as PSQL v9.
We have a problem with the installation on SBS2008 server or any machines, activates the license for 3 users and do not want to add another license. How do we install that? what we do wrong?

Mayby I bought wrong what can i do?

Workgroup verus Client edition

DJJenkins99 — Fri, 11 Mar 2011 21:14:03 GMT

We just upgraded to PSQL 11 server edition from PSQL 10. I have been running PSQL 10 workgroup edition with a local test copy of our server tables on my desktop in order to do development without using server resources. I installed the Client version of PSQL 11 so I can connect to the server. Now my local test database is not accessible.

Do I have also have to install the Workgroup edition of PSQL 11 in order to have access to my local test database? Is it wise to have the client edition and the Workgroup edition of on the same workstation?

Cache engine on W2k3 Terminal Client

OK-BBN — Tue, 15 Mar 2011 13:48:01 GMT

Hello,

we're running PSQL v11 on a Win 2008 64bit Server. A Win2003 Server (32 bit) acts as a Terminalserver. Users are working only on the terminalserver. On the terminalserver is PSQL Client 32 bit installed. (as a Service).

The Problem now is, that I have no acces to the settings in the control center. I want to turn on the cache engine. I check it on, and when restarting the controlcenter, it's unchecked again. The cache engine really is not working.

Now I tried the setting direct in the registry. Now the cache engine is running, but my application says status code 94. Permission error. On the Server 2008 direct, the application is still running. All modifications I've done as adminstrator. Also starting the application.

Have anyone an idea, what I can do to turn the cache engine on the terminalserver on?

Many thanx.

Michael

Set up a Connection to a Pervasive server on a network

GvG-Consulting@telenet.be — Wed, 23 Feb 2011 09:31:44 GMT

Dear, I always use "System DSN' to make a connection with a DB. (Programming in Visual Studio). Everything works fine. Now I want to go further on and I am trying not to connect to a local DB but some DB on a network PC. If i am doing the same, creating a Pervasive ODBC Engine DSN and I fill the Directory Location (for example \\fileserver\DB\001) the test blocks. I can’t connect. How do I have to make a connection to a network DB? tnx

How to get a list of existing databases?

F.Gaudet@maestro.ca — Fri, 18 Feb 2011 19:37:41 GMT

Hi all, Using a SQL query or storedproc, is there a way to get the list of existing databases in PSQL, as listed in the PCC? Is using the DTO the only way? I thought I had read somewhere the DTO was to be deprecated - is this true or something I imagined? Thanks, FG

PDAC: Calculated fields in TPvQuery?

SlawekT — Thu, 17 Feb 2011 09:44:16 GMT

Has anybody noticed this?

Delphi 2010, Turbo Delphi 2006

PSQL v11 update 1

Latest (I think) PDAC (drm.dll 11.1.12.0)

Calculated fields work as expected using PvTable. Using PvQuery, integer fields seem to work, but setting the value in OnCalcFields has no effect. With string fields, exceptions are raised...

This works with PDAC 9.50 & Turbo Delphi 2006, against PSQL v8.70 and v11 (same as above).

Slawek

Is there a separate 64 bit ODBC / OLEDB Driver Download

mattwilkinson — Mon, 14 Feb 2011 11:26:45 GMT

I have installed 32 Bit Pervasive v11 on a 64 Bit Windows 7 machine. Some of our applications are .Net Any CPU which means that they will run in a 64 Bit process on a 64 Bit OS, but they cannot find the 64 Bit Driver (as it is not installed by default on a 32 Bit Pervasive Install) so is there somewhere we can download a separate 64 Bit Driver for ODBC / OLEDB to install on this machine.

Enumeration tables & Databases

luc@scriptingit.be — Fri, 11 Feb 2011 07:45:40 GMT

Hi,

I'm currently working on a 'proof of concept' for a new reporting tool based on pervasive databases, to have a generic approach for the user I need to have a list of all available databases + all available tables in a selected database generated during runtime

Is a code snippet C# or Vb available which ennumerates databases and enummerates tables in a selected database?

Thx for your quick response!

Grtz,

Luc

BTRV Error Code 10

mgregory — Mon, 07 Feb 2011 17:27:15 GMT

I have a client with Btrieve 6.15. They have been running fine up until a week ago. They are now getting a BTRV Error 10 when they try to run this one report. I do not have access to the workstations or server. It happens at the same point on different workstations. From what they tell me over the phone everything sounds correct. They confirmed the settings are the same we have used for years. Can anyone help me with what to look for or what to ask? IT says nothing has changed and they do not have Pervasive or any programs requiring Pervasive on the server.

Thanks

Mark

Pervasive Client Install

ken@thegeneralstore.com — Thu, 27 Jan 2011 15:41:40 GMT

Working with a client using Win 2008 Server (64 bit) and Windows 7 Pro workstations (64 bit). They purchased the 32 bit Pervasive C/S. Clients connect to the server fine (not domain members), and recognize the PSQL engine. However....two issues:

1. To make any changes at all in the Control Center on the workstations, we must navigate to the \Pervasive Software.....\..\..\bin folder, right click pcc.exe and 'Run as Administrator'. If this is not done, any changes made in the PCC will not be saved. Is this by design?

2. To use the software that requires PSQL, we must have the option 'Embedded Spaces' unchecked in the pcc. However, even when we turn off the option, our software generates an 'Error Code 11 - The specified filename is invalid'. This is the same error we get if Embedded Spaces is left checked in the pcc....Again, pcc shows this option as OFF (we used the 'run as administrator' option directly on pcc.exe.....yet we still get the error. It almost appears as though PSQL Client is still looking for Embedded spaces even though we have turned it off.

We did the server configuration in our offices, then shipped the server to the client. We made the necessary changes to pcc in the server with no problems, and the software runs normally on the server with no errors.

Users on the workstations are Adminsitrator level. All workstation users have unique server accounts, and are members of a security group that has full permissions to the shared resource.

Pervasive server is installed to the defaults on the server, and client to the defaults on the workstations. Network share is on a separate drive letter.

Enlist Transaction ADO.NET

JimB — Tue, 11 Jan 2011 21:05:17 GMT

What are current plans concerning support for Enlist Transaction in the ADO.NET provider for Framework 4? If I create a connection for the Entity Data Model which allows enlist transaction, the connect() succeeds, but gives an error on the first access to the database. If the connection does not enlist, then I can access data, but I'm in the neverland of performing operations without transactions. Yuck!

It is my understanding that you do not support Enlist at this time because you do not support distributed transactions, and the enlisted connection may be escalated to a distributed transaction by the .NET transaction manager. Do you plan to add support for distributed transactions in the future, and if so, what is the timeline?

Problem with Pervasive ADO.NET Provider and EntityFramework in VS 2010

krzewinski@sitstudio.pl — Mon, 24 Jan 2011 13:48:46 GMT

Hi,

I've installed Pervasive ADO .NET Provider 3.5 from pervasive website. the file name is PSQL-SDK-Dotnet3.5-11.00.263.000.exe

Unfortunatelly i can't generate an EF proxy while there following error apears:

Unable to generate the model because of the following exception: 'An error occurred while executing the command definition. See the inner exception for details.
Pervasive.Data.SqlClient.Lna.k: [LNA][Pervasive][ODBC Engine Interface]Numeric value out of range.

I found you have a patch for dealing with that problem, but when i install it, the Pervasive provider disapears from the providers list in VS 2010.

How can I get this fixed?

Best regards

Peter