Opening DDFs in VB.NET

Last post 11-12-2007 1:56 PM by Mike Reynolds. 14 replies.
Page 1 of 1 (15 items)
Sort Posts: Previous Next
  • 11-01-2007 12:39 PM

    Opening DDFs in VB.NET

    I want to access my PSQL database from VB.NET. Presently I access the database other programs, such as Crystal Reports, through the DDF files that describe the database.

    So I'm hoping to find a stub of a VB.NET program that opens a database based on access to DDF files.
  • 11-01-2007 3:28 PM In reply to

    Re: Opening DDFs in VB.NET

    I would suggest using the Managed Provider. It is the PsqlConnection and PsqlCommand (and other) namespaces. It's available as a download for PSQL v9 for VS.NET 2003 and VS.NET 2005 at http://www.pervasive.com/developerzone/access_methods/adonet.asp.
  • 11-06-2007 2:41 PM In reply to

    Re: Opening DDFs in VB.NET

    It doesn't look to me like it takes DDFs as input. DDFs are all I have, I have no other means of connecting to this database.
  • 11-06-2007 2:47 PM In reply to

    Re: Opening DDFs in VB.NET

    The DDFs are used by the engine. You would need to create an ODBC DSN and Database Name in order to access the files.
    DDFs are not normally opened directly. They are used by various interfaces to describe the underlying data files.
  • 11-06-2007 2:59 PM In reply to

    • pdc
    • Top 150 Contributor
    • Joined on 04-23-2008
    • Posts 45

    Re: Opening DDFs in VB.NET

    You would need to set up DSN's(DATA SOURCE NAME) that point to the DDFs then use a VB app to connect to the DSN's. When you setup a database in the PCC it creates the DSN for you.
    You should be able to find plenty of examples that way. It only takes a few lines of code.
    something like this
    Dim objConn As New PsqlConnection
    objConn.ConnectionString = Provider=PervasiveOLEDB;DATA SOURCE=IS1; servername=PDC2005

    objConn.Open()
    Dim strselect As String
    Dim rval As Integer

    strselect = "select "
    'strselect = strselect & "order_lines.order_num,sum(order_lines.cost*order_lines.qty) as tcost "
    strselect = strselect & "count(distinct order_lines.order_num) as tcount "
    strselect = strselect & " from order_lines ,order_header "
    strselect = strselect & "where order_lines.vendor='" & Session("Number") & "'"
    strselect = strselect & " and order_lines.newpo='1' and order_lines.updated='' "
    strselect = strselect & " and order_header.webshow='1' "
    strselect = strselect & " and order_header.order_num=order_lines.order_num "
    Dim Cmd As New PsqlCommand(strselect, objConn)
    rval = Cmd.ExecuteScalar()
    Label9.Text = rval
  • 11-07-2007 11:37 AM In reply to

    Re: Opening DDFs in VB.NET

    quote:
    Mirtheil Software wrote:
    The DDFs are used by the engine. You would need to create an ODBC DSN and Database Name in order to access the files.
    DDFs are not normally opened directly. They are used by various interfaces to describe the underlying data files.

    I spent days looking at dozens of different types of ODBC, OLEDB and ADO.NET code examples, none of which compile or work at all in my environment. The only way that I have successfully connected to the database to date is through the DDFs using Crystal Reports. Even that is flaky, the old version 8.5 will read the DDFs, but the latest version XI will not.

    I created a DSN that pointed too the server#%92s IP address, and the ODBC Administrator lists the three 3 *bogus* databases that come standard with a P.SQL installation, but the functioning Apex medical databases isn#%92t listed. I have exactly the same problem in PCC and DDF Builder. They also only list the three standard bogus databases, DEMODATA, DefaultDB and TempDB. When I click on the plus next to them the plus does not expand, it just goes away. I can#%92t perform even the simplest of tasks with it because it doesn#%92t list any functioning databases.

    quote:
    Roy Wellborn wrote:
    You would need to set up DSN's(DATA SOURCE NAME) that point to the DDFs then use a VB app to connect to the DSN's. When you setup a database in the PCC it creates the DSN for you.

    Now that sounds extremely promising, but I couldn#%92t find any way to get the ODBC Administrator to ask me for a DDF. The Pervasive provider only asks for IP address of the server, and then it does give you a list of databases to choose from, but as mentioned before, it only lists the 3 bogus databases and does not list the functioning Apex medical database.

    quote:
    Roy Wellborn wrote:You should be able to find plenty of examples that way. It only takes a few lines of code.


    I#%92ve been fiddling with SQL for fifteen years at just about every other contract I#%92ve had, and I#%92ve never had this much trouble trying to get off the ground. It#%92s been a week and I haven#%92t gotten past square one…
  • 11-07-2007 11:52 AM In reply to

    Re: Opening DDFs in VB.NET

    Mike,
    The DDFs are referenced by the Database Name. When the Database Name is created, a "Dictionary Path" is specified. THis is the location of the DDFs. You do not explicitly specify individual DDFs because they are a set and cannot be used individually.
    quote:
    he three 3 *bogus* databases that come standard with a P.SQL installation, but the functioning Apex medical databases isn#%92t listed.

    Apex probably doesn't create a database for it's data. A database is not required unless you are using relational access (or Btrieve security). You will need to create one. This link might help: http://www.pervasive.com/library/docs/psql/10/usrguide/usrguide-03-5.html#wp145758.

    As far as the "bogus" databases, only DEMODATA should have tables in it. TEMPDB is used for Temporary tables and DEFAULTDB is used for security settings when Btrieve files don't have DDFs.
    If DEMODATA is empty, it's possible that the PSQL engine was installed through a Terminal Server (or RDP) session and DEMODATA wasn't created properly.
  • 11-07-2007 12:25 PM In reply to

    • pdc
    • Top 150 Contributor
    • Joined on 04-23-2008
    • Posts 45

    Re: Opening DDFs in VB.NET

    Mike
    open pervasive control center and expand your server if listed. If not register a new server. Then right click on databases and select new database. Fill in database name with what ever you like (test1 maybe) then for the location select the directory where your ddf's are. Uncheck bound and uncheck create ddf's ( since you already have them). Leave create DSN checked. Click finish. You should now see and be able to open your database in the Pervasive control center. You will aslo have a DSN created to work with in a program. your connection string would be
    objConn.ConnectionString = Provider=PervasiveOLEDB;DATA SOURCE=test1;servername=(whatever your server is called)
  • 11-07-2007 3:58 PM In reply to

    Re: Opening DDFs in VB.NET

    Thanks to both of you, now I understand why it isn't working. Unfortunately I lack the proper privileges. I don't have a username and password for the SQL server that allows me to do that, and apparently no one here does. Only the software vendor does, and my contact there is extremely slippery and evasive about giving me any meaningful help at all...

    But now I know what to hit him up on. Thank you very much, both of you!
  • 11-08-2007 2:16 AM In reply to

    Re: Opening DDFs in VB.NET

    quote:

    The only way that I have successfully connected to the database to date is through the DDFs using Crystal Reports. Even that is flaky, the old version 8.5 will read the DDFs, but the latest version XI will not.



    Not much consolation, but I've had the same experience. One of my customers has been having issues with Crystal Reports/Enterprise 10 and Pervasive.SQL 2000i, on a few of their most complex reports. I took a look at version XI to see if an upgrade would help and got nowhere. Every table with an autoincrement key seemed to cause errors.

    It feels like every release of Crystal seems to get worse when it comes to Pervasive.SQL support.
  • 11-08-2007 11:40 AM In reply to

    Re: Opening DDFs in VB.NET

    Business Objects' tech support (makers of Crystal Reports) told me the same thing. Fortunately I don't appear to be using any of the features that are not supported.

    As to my problem, I found out today that in order to create the DSN and make the databases visible from the Pervasive Control Center you have to be logged directly into the server's console. You can't do it as a remote user. That was what had me hung up.
  • 11-08-2007 12:27 PM In reply to

    Re: Opening DDFs in VB.NET

    I meant to say, in order to create a DSN you have to first make the databases visible, which can only be done at the server console. In that operation there is actually a check box in PCC for automatically creating a system DSN for the database you make visible.
  • 11-12-2007 12:37 PM In reply to

    Re: Opening DDFs in VB.NET

    I was very happy at first when I was finally able to see my databases in PCC and get DSNs for them, but after I got a chance to look at it in greater detail, very little of it is actually working. Fewer than half of the tables are accessible.

    Patient & Daysheet, the two largest tables which are the roots of the relational queries used most often, are missing in action. When I try to open them in PCC, I get either of the following errors:

    1. [LNA][Pervasive][ODBC Engine Interface][Data Record Manager]The Btrieve file directory is invalid(Btrieve Error 35)

    2. [LNA][Pervasive][ODBC Engine Interface][Data Record Manager]The MicroKernal cannot find the specified file(Btrieve Error 12)

    3. Or it may come up without an error, but with the grid empty and displaying the message: “No data available for display”.

    If I try to bring up the properties I get a different error: a dialog comes up titled “Error” with the message “1: Failed”, followed by another dialog also titled “Error” which says, “Unexpected Error Occurred: NullPointerException”. Alternately, the second dialog my just say “Unknown Error: -19”.


    In DDFBuilder most of the tables don#%92t come up at all. The few that do loose their field names and most of their fields. I can#%92t see Patient or Daysheet in the list. If I click on any of the few tables that do show up I get the warning: “Errors found in the original table defintion. See Definition Errors View for more information.” The Definition Errors are subtle variations on the following error: “Column: unnamed_3 DDFBLDR-6: Undefined bytes in table definition. Unknown column added.”

    All of the few remaining fields are titled unnamed_# with the # being a number of seven or less, while these tables typically have over a hundred fields.

    Finally, when I first open a database I get the warning: “Btrieve files with pre 6.X file formats were found in "D:\pathlist". In order to operate on these files in DDFBuilder, you must rebuild the files to 6.X or greater file format.” When I look in the DDFBuilder log file the files it complained about were four small tables that are hardly ever used.

    ===============================

    So basically nothing in these DDFs work in PCC or DDFBuilder. They work fine in Crystal Reports v8.5, but that is the only place I can get them to work, Crystal version XI will not open the DDFs either, nor will VS2005.
  • 11-12-2007 1:02 PM In reply to

    Re: Opening DDFs in VB.NET

    Sounds like you've got some really bad DDFs.
    For Number 1 and 2 (error 35 and 12), you've probably got a path in the DDF. You'd need to find out what the exact directory structure is being used by the DDFs and make sure your data files are in that structure.
    I've never seen a “No data available for display” error but the -19 usually indicates the DDF definition doesn't match the Btrieve file.
    From the description, your DDFs may not define all of the fields ("DDFBLDR-6: Undefined bytes in table definition").
    The warning about the pre-6x files is just that if you don't need those files.
    WHat I would suggest would be to go back to the vendor of the application that's generating the data files and get valid DDFs.

  • 11-12-2007 1:56 PM In reply to

    Re: Opening DDFs in VB.NET

    The application is functioning using the current DDFs and Crystal Reports v8.5 works fine with the DDFs, although Crystal XI doesn't recognize them, nor does VS2005. I#%92m having a lot of problems with this vendor, so I would need to be very specific as to what I ask him to do.

    The vendor has already sent me alternate DDFs with all the path-lists stripped out, which was a ridiculous thing to do since the data files are in different directories, so this was guaranteed not to work at all. I#%92m not sure if he is malicious (or rather, trying to protect his turf by any underhanded means that he can) or just stupid, but either way I have to either fix the DDFs myself, or be extremely precise as to what alterations I want if he is going to do it for me. If he is malicious then he will continue to give me “what I asked for” in a manner that doesn#%92t work, unless I am extremely specific.

    quote:
    From the description, your DDFs may not define all of the fields ("DDFBLDR-6: Undefined bytes in table definition").

    I think this is a bug in DDFBuilder, it is saying this for the tables that work fine in PCC. While PCC has problems with the global tables, DDFBuilder just drops them completely and doesn#%92t display them at all in the pick list.

    There are field definitions for all the tables that DDFBuilder is complaining about and PCC is able to read those tables just fine. DDFBuilder clobbers the field definitions for every single table it touches in my databases.

    quote:
    For Number 1 and 2 (error 35 and 12), you've probably got a path in the DDF. You'd need to find out what the exact directory structure is being used by the DDFs and make sure your data files are in that structure.
    I've never seen a “No data available for display” error but the -19 usually indicates the DDF definition doesn't match the Btrieve file.

    There are only two directories for each DDF set, the local directory and the global directory. In the FILE.DDF file, the global path is specified for all of the global tables, while the local tables have no entry in the File Location field, with blank here presumably meaning the same directory that the DDF files are in.

    Most of the local tables which have no pathlist specified in the DDF file work find in PCC and they at least display as existing tables in DDFBuilder, even if DDFBuilder falls on its face trying to look at their content. The global tables that are in another directory are the ones that don#%92t work in PCC and don#%92t appear at all in DDFBuilder. The directory that is specified is the correct path for all of these global files, and everything works fine in Crystal Reports v8.5.

    I#%92m sure there is a problem here, some kind of finicky pickiness such as some apps requiring the file name to be included in both the pathlist field and in the filename field of the table#%92s entry in the DDF. However, it is not a simple straightforward pathlist error, the pathlist points too the valid directory name of the location of the global tables.
Page 1 of 1 (15 items)