Pervasive LongVarchar

[IssacMathews]

Hi Friends ,

Please help me on the following issue I am facing .

I am trying to insert / Update a longvarchar type data to pervasive database from Visual basic using OLEDB. But some how only longvarchar type data is not inserting/Updating .

My SQL looks like following , where Memo is Longvarchar type.


Update SUPP_WB_TEXT
SET
MEMO = 'More than 64 chars’,
MEMO_KEY = 'More than 64 chars'
Where
SUPP_NO = 'GST' and FIELD_NAME = 'Comment'

please give me your valuable advice in this matter.

Thanks in advance!!! and Best Regards



Issac mathews

Programmer

 

[mirtheil]

First off, are you getting any error when updating the record? WHat happens if you issue the Update statement using the ODBC driver (rather than the OLEDB provider)? What about the PCC?

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[IssacMathews]

Hi Mirtheil ,

Thank you for your response. I tried that SQL in PCC , it is also working the same way as OLEDB provider works from VB. Inserting or Updating is not giving an error . SQL statement just Inserting or updating all fields other than Longvarchar. I think length of the string also has no influence on the above issue , because I tried to insert a very small string (one char width) and somewhat big string (400 char width) to that field , but the result is same.

As I am very new to Pervasive , no idea where to start. Please Help.

Best regards

Issac Mathews

 

[mirtheil]

What version of Pervasive are you using? Using Pervasive.SQL V8 SP1, I issued the following statements and they worked correctly for me. Do they work for you?

create table LongVarTest (
f1 integer,
f2 char(10),
f3 longvarchar)#
create unique index idxF1 on LongVarTest (f1)#
insert into LongVarTest values (1, '1','LongVar Value')#
insert into LongVarTest values (2, '2','LongVar Value 2')#
insert into LongVarTest values (3, '3','LongVar Value 3')#
update LongVarTest set f2 = 'Update', f3 = 'LongVar Updated Value 3' where f1 =3#

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician

 

[IssacMathews]

Hi Mirtheil ,

Thank you so much for your help. Most interesting thing is all SQL statements you used and forwarded to me are working fine for me also. But still Inserting and Updating of Longvarchar is not working with the set of tables I want to work with.

So from that I think problem is not with pervasive but with the specific set of tables and its definition I am working now. But unfortunately I don’t have any authorization to create or alter those table definitions, because those are creating dynamically by other software. So is there any work around? Any idea why those tables are working entirely different way?


Thanks again for your valuable help.

Best regards

Issac Mathews

 

[IssacMathews]

Hi Mirtheil ,


One more thing I just noticed which can be a potential lead to solve the above issue.

When I open both tables (One I created with your create table statement and other table with the problem) in ‘Periscope’ and I noticed some difference in their data type and length even though both of them are showing Longvarchar in PCC.


Existing Table with problem:

FieldName : MEMO
Type : Variable Length Note
Null : unchecked
Size : 2048
Offset : 51
Dot : 0



Newly created table from the Create table you forwaded:

FieldName : f3
Type : LVarchar
Null : checked
Size : 8
Offset : 18
Dot : 0

Thnks again and best regards

Issac mathews

 

[mirtheil]

Ahh, so these are legacy tables. Do the tables pass the database consistency check? if they don't pass the consistency test, then you would want to find out if there is an updated set of DDFs from the vendor of the application that do pass the consistency test.

info@mirtheil.com
Custom VB and Btrieve development.
Certified Pervasive Developer
Certified Pervasive Technician