Creating array from Oracle table to Foxpro Cursor

[MyNeckHurts]

I have had to change several Oracle table field lengths and types from CHAR to VARCHAR(2). I ran a rtrim on all of the tables and fields in Oracle after the change. The problem comes when I pass the table structure from Oracle to VFP (see below).

local array laField[1]
aFields(laField, tcAlias)
create cursor (tcCursorName) from array laField

I end up with extra spaces in the VFP cursor fields that were converted in Oracle. I pass some of the values in other queries and even though I rtrim or alltrim them the spaces remain. I think that VFP is not seeing them spaces but as some sort of block.

Is there a way to trim off the extra spaces or whatever they are from the cursor fields?

 

[Mike Lewis]

MyNeckHurts,

I'm not quite clear what you are saying. You appear to be creating an empty cursor with the same structure as your Oracle table. You can achieve that more easily by creating a remote view based on the table, and open it with the NODATA option. Or, send this command to the server: SELECT * FROM MyTable WHERE 0=1

OK, assuming you now have your cursor, where do the trailing spaces come it? Are you saying that the actual field names have trailing spaces? Or that the cursor contains data with trailing spaces, or what?

In general, if you create a cursor or view from a back-end database, trailing spaces are inevitable, as VFP does not handle Varchar fields. This is normally only an issue when you use the view or cursor to update the server, but that doesn't appear to be what you are doing.

Perhaps you could clarify the problem.

Mike



Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[MyNeckHurts]

Thanks,

I'm using SELECT * FROM MyTable WHERE 0=1 to create the structure. The problem is that the data that is copied into my cursors contain extra spaces. For instance we changed the field length in Oracle from 6 to 10 and the type from char to varchar(2). Now when I pull data from Oracle into VFP the field data contains:
"mydata ". The extra 4 spaces do not appear to be spaces at all but maybe null space. The question is since rtrim and alltrim do not work is there another way to get rid of the extra space?

 

[rgbean]

Since VFP has no concept of variable length fields, it will create a cursor field width based on the data in the "first" record it selects. If you want to have specific widths, you should create the cursor with explict sizes or "help" using the substr(), trim() and pad() functions in your field selection list.

Rick

 

[MyNeckHurts]

Thank you Rick.

The problem is that we create an updateable cursor (tcCursorName) from the structure of the specified local FoxPro alias (tcAlias). We do not pass any field names until we query Oracle (SELECT * FROM MyTable WHERE 0=1). We then populate the VFP cursor with data from another query returned from Oracle. That is where the problem starts. I understand where you are coming but for this application it would mean re-writing many many lines of code and the reason we wrote it this way to start with was to avoid making code changes when a field length changed in Oracle. The problem only exist because we were trying to change the type from CHAR to VARCHAR(2) not the length.

My only solution so far is to change the fields in Oracle back to CHAR. If you know of any other way to remove trailing null values from cursors fields please let me know.

Thanks again

 

[Mike Lewis]

MyNeckHurts,

Can you clarify: Is the problem that the trailing spaces appear in the FoxPro side or on the Oracle side?

If the problem is that they are on the FoxPro side, then, as far as the cursor is concerned, you are stuck with them. As Rick and I both pointed out, VFP can only deal with fixed-length fields in a table or cursor.

If your problem is that you want to get the trimmed fields into a variable, then you would normally just apply the ALLTRIM() function. But you say that the padding are not real spaces but "null spaces". I don't know what a null space is. Is it a binary zero -- CHR(0) -- character? If so, you can get rid of it like this:

MyVariable = LEFT(MyCursorField,AT(CHR(0),MyCursorField)-1)

If the problem is that the trailing spaces are on the Oracle side (in other words, they appear on the server after you have updated the VFP cursor and sent the updates back), then the solution is not to use an updateable cursor (or view). You will have to send the updates using the UPDATE command. This might sound like a lot of trouble, but there is no other way of doing it.

Finally, I would question why you are using Varchar(2) in the first place. Varchars carry an overhead compared to Chars and are generally less efficient. The reason to use them is when you want to save space because the data is highly variable -- if a name can vary between three and thirty characters, for instance. The potential saving with Varchar(2) is never going to be very much, and in my opinion not worth the effort of using them.

Mike


Mike Lewis
Edinburgh, Scotland

http://www.ml-consult.demon.co.uk

 

[MyNeckHurts]

Thanks Mike,

The spaces are on the FoxPro side in the cursor. The alltrim function doesn't work because as you pointed out they are fixed in length. I did try the
MyVariable = LEFT(MyCursorField,AT(CHR(0),MyCursorField)-1)
solution and it did not solve the issue. We have decided to go back to the char type in Oracle and this solves our problem.

Thanks Rick and Mike

Carl