VistaDB 5 + DA + VS 2012 + .NET 4.0 + DA 8.2.89.1153

Data Abstract
1

Hello,

I have followed the directions on the Driver FAQs to add VistaDB but some modifications were necessary to update it for VistaDB Version 5. Note that this is a Visual Studio 2012 / C# / .NET 4 project. Data Abstract version 8.2.89.1153.

<VistaDB5.NET>
  <AssemblyName Value="VistaDB.5.NET40, Version=5.0.0.0, Culture=neutral, PublicKeyToken=dfc935afe2125461" />
  <ConnectionClassName Value="VistaDB.Provider.VistaDBConnection" />
  <DataAdapterClassName Value="VistaDB.Provider.VistaDBDataAdapter" />
  <CommandBuilderClassName Value="VistaDB.Provider.VistaDBCommandBuilder" />
  <ConnectionStringBuilderClassName Value="VistaDB.Provider.VistaDBConnectionStringBuilder" />
  <Description Value="VistaDB Data Provider" />
  <Profile Value="VistaDB" />
  <ParamMappings Database="Data Source" Server="" />
  <ParameterHandling Type="ByName" SQLSymbol="@" NameSymbol="@" />     
  <OptionalParameters>
  </OptionalParameters>
  <NewConnectionHelpText>
    <p class="h1">VistaDB.NET Driver</p>
    <p>  This Driver allows you to connect to Vista Database via the VistaDB .NET driver.</p>
    <p class="h2">Step 2: Server Name</p>
    <p>  In this field, provide the host name or the IP address of the server to which you are connecting.</p>
    <p class="h2">Step 3: Login</p>
    <p>  Use these two fields to provide a username and password to access the database server.</p>
    <p class="h2">Step 4: Database</p>
    <p>  Specify the full path of the database to which you wish to connect (e.g. "c:\..\vistadb.vdb5").</p>
    <p class="h2">Step 5: Custom parameters</p>
    <p>  You can specify additional driver specific options as a semicolon separated list of name/value pairs.</p>
  </NewConnectionHelpText>
  <DriverNotFoundText>
    To use the VistaDB.NET driver you must install the VistaDB Express from http://www.vistadb.net.
  </DriverNotFoundText>
</VistaDB5.NET>

…

<VistaDB QuotingIdentifiers="[]">
  <GetTableNames SQL="SELECT name FROM [database schema] WHERE typeid = 1"/>
  <GetStoredProcedureNames SQL="SELECT name FROM [database schema] WHERE typeid = 14"/>
  <GetViewNames SQL="SELECT name FROM [database schema] WHERE typeid = 10"/>
  <GetLastAutoInc SQL="SELECT IsNull(@@Identity, 0) as LastAutoInc" SupportsAutoIncs="True"/>
  <DatabaseLocation Type="File" FileMask="VistaDB files (*.vdb5)|*.vdb5" />
  <ExecuteStoredProcedure WithParams="EXEC {0} {1}"/>
</VistaDB>

When I attempt to drag a table from the Connections Manager to the Schema Modeler I receive the following error message:

Unable to obtain metadata for Connection to {…filename…}. Please, specify target table.

When I execute “SELECT name FROM [database schema] WHERE typeid = 1” in the VistaDB Data Builder I get a result set with a single column “name” containing the table names.

When I execute “SELECT name FROM [database schema] WHERE typeid = 14” the result set is empty as there are no stored procedures (I do see the column header for name though).

The same is true for “SELECT name FROM [database schema] WHERE typeid = 10”, the result set is empty as there are no views.

The Schema Modeler fails to add the columns.

However, if I manually write the SQL statements and click Preview Data it shows me the data and asks if I’d like to update the fields and mappings (which succeeds).

What am I doing wrong??

Thank you,
Brian Wheatley

2

Hello

Seems you need to slightly fix the driver definition. Seems VistaDB returns untrimmed schema object names and this confuses the Schema Modeler.
Please change the definition to

<VistaDB QuotingIdentifiers="[]">
  <GetTableNames SQL="SELECT RTRIM(name) as name FROM [database schema] WHERE typeid = 1"/>
  <GetStoredProcedureNames SQL="SELECT RTRIM(name) as name FROM [database schema] WHERE typeid = 14"/>
  <GetViewNames SQL="SELECT RTRIM(name) as name FROM [database schema] WHERE typeid = 10"/>
  <GetLastAutoInc SQL="SELECT IsNull(@@Identity, 0) as LastAutoInc" SupportsAutoIncs="True"/>
  <DatabaseLocation Type="File" FileMask="VistaDB files (*.vdb5)|*.vdb5" />
  <ExecuteStoredProcedure WithParams="EXEC {0} {1}"/>
</VistaDB>
  </DatabaseProfiles>

Regards

3

That worked!