ADOX: Extending a fixed length Jet string field length

ADOX: Extending a fixed length Jet string field length

Post by Lee Gilli » Thu, 01 Jun 2000 04:00:00



Is it required to drop the column and re-add it, or can this be done
by simply changing a property of the column.  When I modify the
DefinedSize property, it resulted in an error.  I think Access can
change this without changing the order position of columns.  Can that
be done through ADOX ?

--
______________________________________________________________________
Lee Gillie, CCP                                Remove NOSPAM to E-Mail
Online Data Processing, Inc. - 3501 N. Haven -  Spokane, WA 99207-8500

 
 
 

ADOX: Extending a fixed length Jet string field length

Post by Dean Slinde » Tue, 06 Jun 2000 04:00:00


Yes, use the Alter DDL statement.  Char and Binary types can be lengthened

with ADOX.  The syntax is something like this:
Alter TABLE (tableName) Alter COLUMN (column name) Char (50).
Use Char, not WChar or VarWChar.
Dean Slindee


> Is it required to drop the column and re-add it, or can this be done
> by simply changing a property of the column.  When I modify the
> DefinedSize property, it resulted in an error.  I think Access can
> change this without changing the order position of columns.  Can that
> be done through ADOX ?

> --
> ______________________________________________________________________
> Lee Gillie, CCP                                Remove NOSPAM to E-Mail
> Online Data Processing, Inc. - 3501 N. Haven -  Spokane, WA 99207-8500


 
 
 

1. BCP OUT Fixed-Length Record: Truncated Length

Here's my BCP OUT format file -- it should explain my field structure:

6.0
13
1  SQLCHAR   0   10      ""      1     id
2  SQLCHAR   0   5       ""      2     prefix
3  SQLCHAR   0   25      ""      3     first
4  SQLCHAR   0   20      ""      4     middle
5  SQLCHAR   0   25      ""      5     last
6  SQLCHAR   0   5       ""      6     suffix
7  SQLCHAR   0   35      ""      7     address1
8  SQLCHAR   0   35      ""      8     address2
9  SQLCHAR   0   25      ""      9     city
10 SQLCHAR   0   2       ""      10    state
11 SQLCHAR   0   5       ""      11    zip5
12 SQLCHAR   0   4       ""      12    zip4
13 SQLCHAR   0   10      "\r\n"  13    phone

Challenge:  I must export a fixed-length record to an outside client, and
all records must be exactly the same length.

Situation:  In the above structure, some of the trailing fields (like ZIP4
and PHONE) are null

Problem:  BCP is truncating the record if, for instance, PHONE field is
null.  The record length should be 208 including CR/LF at end; if phone is
null, recl = 198.

Question:  Does anybody know of a way to set BCP to export the "pure" fixed
length of each and every field, including any final fields which may
contain nulls?  

I realize I could force-fill those nulls with zeroes or some such filler,
but I don't wanna pump filler into a 4-million record table -- that ain't
my style.  In the meantime, my only solution is to set up this file
structure in Foxpro, import the BCP-ed file, and then export it.  On
export, Fox will not truncate a record just because trailing fields are
empty.  But this step will be time-consuming if I'm working with a million
records.

Any ideas?  

John Morrow
WKA
Winston-Salem NC

2. Hardware requirements for Oracle running under WinNT?

3. Question on bulk loading of fixed length/fixed field records

4. Queries

5. ADOX, Jet OLEDB:Allow Zero Length ERROR...

6. How to ignore rows with duplicate 'code' field in SQL table

7. Fixed field text, and variable length last field

8. I apologize for quadruple post

9. String of Fix Length

10. How to build fixed length string

11. Convert number to fixed length string

12. Creating a database w/o fixed length strings

13. Fixed Length String