First, I am very new to DBA, Pervasive, and all that is related.  Last week new.  I've been assigned the task of modifying a Delphi 6 application to insert data into the DBA database.  This app has pulled data from it for over 6 months, but now they want to "backfeed" sales/purchase/work orders from our custom software.
 
My problem:
It appears to me that some fields have brackets in them.  The specific ones I'm dealing with are in the BKARINV_BNB table:
 
BKAR_INV_CUSA2[  1] and BKAR_INV_CUSA2[  2]
 
A SELECT * in PCC will bring them back just fine, but trying to SELECT just those fields will result in a syntax error.  I need to put data into these fields in my "backfeed".
 
The error is:
 
ODBC Error: SQLSTATE = 37000, Native error code = 0  
Syntax Error: SELECT "BKAR_INV_CUSA2"  1<< ??? >>""
 FROM BKARINV_BNB
 
I am have tried a number of delimiters, including double quotes, with no success.
 
System:
Windows XP SP2, Pervasive 2000i SP4, DBA 2002.4 with ISTS Update 1/16/04.
 
Not sure what else is needed.  Any help greatly appreciated.
 
Phillip

Kelloggs,
 
Thank you very much for taking the time to thinking about this problem and respond.  Unfortunately, I get an error of "Invalid column name 'BKAR_CUSA2_1'"
 
Peace,
Phillip

I don't know why the tables are named the way they are.  I work for a technology company that is writing some custom software for use in a manufacturing environment and our client uses DBA for their accouting.  I was introduced to all of this last week when my boss said, "I've been pulling data out of DBA for the past 6 or so months, now I want you to write the code to push stuff back to DBA.  Have fun!"
 
So I got DBA 2002.4 (with ISTS Update 1/16/04) and Pervasive.SQL 2000i (SP4) installed, dug into his source code, and away I went.  Until this problem.
 
To answer your question, yes the query is executed using PCC.  As a database application developer, I generally get all my queries working with the engines "native" query tool, then move it to my application.  If I can't get it working in the "native" query tool, why bother trying to get it working in my application, eh?
 
I'm somewhat perplexed by this issue, as according to the documentation that I've either looked at or been informed of, brackets are not allowed as part of column names.  I found a post yesterday that points to a spreadsheet that describes many of the file layouts for DBA, and according to it, the field in question is actually an array (which makes sense), but I can find no information about how to work with arrays.  In fact, I couldn't even find anything about arrays in Pervasive's documentation, so I'm pretty much stumped.
 
Thanks for the time,
Phillip

I always get the unusual column names by letting the Pervasive Control Center  do the work.
 
It will give you the table name when you open the table.
When you click on the text results tab on the bottom, it will produce a first line with copiable ( Ctrl C)  field names:   like  BKAR_INV_CUSA2_1.    
 
The first line of the query text box will contain the table name.

the DDF's that are designed to be used with the ODBC, do NOT contain the extensions, they only contain the File ( hence Table ) name.
 
if you incorrectly used the DDF's that are in the Root dir of DBAMFG, which are only for use by Maintain Database, those DDF's DO List the files WITH their extensions.
 
consequently, use the ODBC for both read and write access.  
 
also, the DDF's again in the Root of DBAMFG, correctly show the original file / field structure, which are those Field Names that the programmer did use [ ] for multiple instances of the same name/type field. ( old programming technique...)  
 
the DDFs that are used for ODBC, have corrected that idiosyncracy, by omitting the [], and substituting the underscore  _  .
 
i think you are using the wrong DDF's for your development connection. since you listed the BKARINV_BNB table. the ODBC would only list that as: BKARINV  since the DDF's would reside in the same directory as your *.BNB files.
 
also, an added side note, it's not recommended to write back to the database. however, if you do, "push" data back in, make sure you follow the same logic that the software follows, as if the user had entered the data in the appropriate entry screen in the software. if not, there will be problems with data information not sync'd.
 
to create the proper DDF's for your specific company. make sure you are logged into DBA or EVO, in the NB company, go to Utilities on the System Manager menu, UT-A run a DBA program, type in ODBCDDF.RUN
 
then on your server, create a SYSTEM DSN that points to those DDF's in the NB directory. then on your Client workstation, create a SYSTEM DSN that points to your Server's DSN.
 
then if you use the pervasive development tools, oledb, or create your own db connection string, you can choose to use the odbc, rather than specifying the full db server connection. either way, you do need to use those DDF's that are ODBC compliant.
 
HTH,
dave