Problems writing to a LongVarChar Field (VB)

[MarvinK9]

Hi all,

I have a strange problem. I'm a VB Programmer and I'm trying to write to a predesigned database that has a longvarchar field. Editing the structure of the database is not an option as it is for a third party, pre-existing software.

The basic issue is that the database structure is as follows.
FIELD1 - CHAR - SIZE 2 - POSITION 1
FIELD2 - CHAR - SIZE 15 - POSITION 3
FIELD3 - CHAR - SIZE 23 - POSITION 18
FIELD4 - CHAR - SIZE 10 - POSITION 41
MEMO - LONGVARCHAR - SIZE 2048 - POSITION 51
MEMO_KEY - CHAR - SIZE 64 - POSITION 51

The pre-existing software writes to the MEMO_KEY field first and if the data is longer than 64 characters it writes the remaining text into the MEMO field.

I'm trying to write an Import utility to add data to this database, But I can not manage to write anything into the MEMO field.

Even using Pervasive Control Center and a standard SQL statement

INSERT INTO "INVE_WB_TEXT" VALUES ('LD','12345',' ','TESTTHIS','O THE END THAT SHOULD BE WRITTEN TO THE MEMO FIELD','THIS IS A 64 CHARACTER STRING WITH ADDITIONAL CHARACTERS ADDED T')

.... nothing is being written to the MEMO field and no errors are returned from my insert Statement.

I can program whatever I need to to fix this, but I don't understand why it doesn't work.... so fixing it, is kinda difficult, when I don't understand the problem.

Any help would be GREAT!

Thanks

 

[mirtheil]

WHat version of PSQL are you using?
A few observations/questions:
1. Your structure is wrong. You can't have two fields starting at the same position:
MEMO - LONGVARCHAR - SIZE 2048 - POSITION 51
MEMO_KEY - CHAR - SIZE 64 - POSITION 51

What is the exact record structure?

2. What interface are you using (Btrieve, ODBC, etc)?
THis probably won't work with ODBC(or SQL) without modifying the DDFs/data file. WOuld you be able/willing to use the Btrieve API instead of the ODBC/SQL interface?
3. Does the table pass a database consistency check?

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com

 

[MarvinK9]

Hey Mitheil,

I'm using 2000i SP4 7.94.251.003

#1. you are right! I thought the structure was wrong too! But my problem is... that "IS" the structure. Unfortunately I'm not the one who designed the database.. just the poor guy who has to figure out how to get data into it! Weird as the structure is... the above structure is correct. I already attempted to "FIX" the structure, which was fine for me... but for the software that the Database belongs to, it won't work with modifications. So I'm stuck with the Structure as it is.

#2. I've switched from ODBC to Btrieve API, to try and get my data in, in the correct manner.
(I have a Btrieve control that seems to allow data to be added without much of a problem, But I'd rather figure out how to do it, without another 3rd party control).

#3. The data Consistency check? not sure what you mean.. The database is for a 3rd party software that's been in widespread use for several years... and within the software there are no issues.

Some things that I've found interesting today are....that the Memo_key field and the Memo Field contain the EXACT same data(memo_key contains only 64 chars)... which I would suspect is because they have the same positions. Opening the File in a Text Editor shows no Duplication of the data though.. ie: This is cut directly from the db in a text editor THIS IS A NEW NOTE ITE IS ADDED BY ME AND THIS IS WHAT IT SAYS. NOT THAT I SUSPECT IT IS WORKING So as you can see the text is in the database as one field... no duplication.

They use the MEMO_KEY in the Index as well... which is a pain. I have managed to get data into the MEMO_KEY but not the MEMO field.

I'm now trying API (which I'm new at) so it's kinda labour intensive! Is there a "quick" way to use API?? just to write data into the database!

Thanks for your help

MarvinK9

 

[mirtheil]

#1. you are right! I thought the structure was wrong too! But my problem is... that "IS" the structure. Unfortunately I'm not the one who designed the database.. just the poor guy who has to figure out how to get data into it! Weird as the structure is... the above structure is correct. I already attempted to "FIX" the structure, which was fine for me... but for the software that the Database belongs to, it won't work with modifications. So I'm stuck with the Structure as it is.

That structure is invalid in terms of SQL and the vendor of the application needs to fix it. You won't be able to fix the data without a change to the application.

#2. I've switched from ODBC to Btrieve API, to try and get my data in, in the correct manner.
(I have a Btrieve control that seems to allow data to be added without much of a problem, But I'd rather figure out how to do it, without another 3rd party control).

THe only way to access the data without a control is to use the Btrieve API directly. It'll probably be the best because it is exactly the byte structure of the record.

#3. The data Consistency check? not sure what you mean.. The database is for a 3rd party software that's been in widespread use for several years... and within the software there are no issues.

I can tell you that this table will not pass a consistency check. If you right click the database in the Pervasive Control Center and select Tasks then Check Database, you'll get to the Consistency Checker.

Some things that I've found interesting today are....that the Memo_key field and the Memo Field contain the EXACT same data(memo_key contains only 64 chars)... which I would suspect is because they have the same positions. Opening the File in a Text Editor shows no Duplication of the data though.. ie: This is cut directly from the db in a text editor THIS IS A NEW NOTE ITE IS ADDED BY ME AND THIS IS WHAT IT SAYS. NOT THAT I SUSPECT IT IS WORKING So as you can see the text is in the database as one field... no duplication.

If you open the table in the Pervasive Function Executor, do you see the duplication of the data? Most likely you won't.

I'm now trying API (which I'm new at) so it's kinda labour intensive! Is there a "quick" way to use API?? just to write data into the database!

It's quick for me but I've been doing Btrieve API for almost 11 years now. Other than that, it's not a quick piece until you've got the basic operations down.
One thing you'll probably want to do is in your structure, you'll only want to define the MEMO field and not the MEMO_KEY field. You can, in your application, trim the Memo field to 64 bytes.

Mirtheil
Certified Pervasive Developer
Certified Pervasive Technician

http://www.mirtheil.com