Re: Upgrading Pervasive 2000i to V9

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@xxxxxxxxxxxxx> wrote in message
news:frWdnV294InsE1jenZ2dnUVZ_tOdnZ2d@xxxxxxxxxxxxxxxx
> 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@xxxxxxxxxxxxxxxxxxxx
> http://www.goldstarsoftware.com
> *** Chicago: Pervasive.SQL Service & Support - March, 2006 ***
> *** Chicago: Pervasive DataExchange Class - March, 2006 ***
>
>
> Chris Smith wrote:
>
> > 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
>


.

Relevant Pages

  • Re: Upgrading Pervasive 2000i to V9
    ... The applications that use the Btrieve API work ... > Dim cnn As ADODB.Connection ... > Dim rst As ADODB.Recordset ... > Set cnn = New ADODB.Connection ...
    (comp.databases.btrieve)
  • Upgrading Pervasive 2000i to V9
    ... I am in the process of testing our applications that currently use PSQL2000i ... Dim cnn As ADODB.Connection ... Dim rst As ADODB.Recordset ... Set cnn = New ADODB.Connection ...
    (comp.databases.btrieve)
  • Re: Looping through a table for a form
    ... Sub ADOTest() ... Dim cnn as New ADODB.Connection ... Dim rst as New ADODB.Recordset ... Set cnn = CurrentProject.Connection 'N.B. ...
    (microsoft.public.access.formscoding)