Hi,

I am in the process of testing our applications that currently use PSQL2000i
on PSQL9. The applications that use the Btrieve API work (as you would
expect) however I am having difficulties with applications that access the
data using OLEDB.

Code currently working on PSQL2000i

Function cstest()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Const connstring As String = "Provider=PervasiveOLEDB;Data
Source=\\server\pathtoddffolder;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open connstring
Set rst = New ADODB.Recordset
rst.Open "SELECT * FROM INVOICE_HEADERS WHERE Customer_Name='Customer'",cnn,
adOpenDynamic, adLockReadOnly
rst.MoveFirst
Do
MsgBox rst!Customer_Name
rst.MoveNext
Loop Until rst.EOF = True
rst.Close
cnn.Close
Set cnn = Nothing
End Function

However, to get the same results on PSQL9 I have to ammend the code to

Function cstest()
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Const connstring As String = "Provider=PervasiveOLEDB.9.11;Data
Source=DatabaseName;Location=Server;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open connstring
Set rst = New ADODB.Recordset
custname = "Customer"
custname=custname& Space((50 - Len(custname)))
rst.Open "SELECT * FROM INVOICE_HEADERS WHERE Customer_Name='" & custname &
"'", cnn, adOpenDynamic, adLockReadOnly
rst.MoveFirst
Do
MsgBox rst!Customer_Name
rst.MoveNext
Loop Until rst.EOF = True
rst.Close
cnn.Close
Set cnn = Nothing
End Function

Changing the connection string is easy enough but I have to space out the
field used in in the WHERE clause to match the field size in the table
structure.

This spacing out is also necessary when I try and execute a SQL statement in
the PCC.

Is there any option, registry modification etc that will allow execution of
the SQL statements without the modification of the variable used in the
WHERE clause.

Many thanks for your anticipated help.

Chris

Is this data type (as defined in the DDF's) a CHAR or VARCHAR field?
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***


Chris Smith wrote:

Thanks for replying Bill,

The datatype is VARCHAR. I have been reading about null termination of
VARCHAR fields. I have also spoken with the company who controls the data
who inform me that they for VARCHAR data fields to be padded out with spaces
to fill the entire field.

I have confirmed this too by returning data in VARCHAR fields and then
printing the ascii numbers for each of the characters returned.

This said, and if I read correctly trailing spaces are considered
insignificant in string comparisons.

I can return data correctly if I modify the SQL statement:

From
SELECT * FROM INVOICE_HEADERS WHERE Customer_Name='Customer'"
To
SELECT * FROM INVOICE_HEADERS WHERE rtrim(Customer_Name)='Customer'"

Forcing SQL to strip the trailing spaces.

This works correctly but contradicts the information in the PSQL9 Engine
Reference Documentation.

Something just doesnt add up, but I cant see to be able to work it out.

Many thanks for your help.

Chris



"Bill Bach" <goldstar (AT) speakeasy (DOT) net> wrote

Sounds like a silly developer to me. A space-padded character field is
actually a CHAR field, not a VARCHAR field. Changing to a CHAR data
type would probably eliminate the problem. However, this isn't an
option for you, as you're just along for the ride.

The only option may be to modify the SQL query.
Goldstar Software Inc.
Building on Btrieve(R) for the Future(SM)
Bill Bach
BillBach (AT) goldstarsoftware (DOT) com
http://www.goldstarsoftware.com
*** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
*** Chicago: Pervasive DataExchange Class - March, 2006 ***

Chris Smith wrote: