Pervasive SQL Support

[John]

Any way to add support for Perveaive SQL?

[mike.griffin]

Sure is, Do you know if they provide an OLEDB provider (that makes it easier on us). See if you can find out and I will as well.

[John]

I believe this is what you want?

http://www.pervasive.com/developerzone/access_methods/oledbado.asp

I could upload to you if you don't want to create a profile with them. Just let me know.

Thanks.

[John]

Any updates? Was the link what you were after? Please let me know.

Thanks

[mike.griffin]

To be honest we've been busy bringing most of the databases we support online with our dOOdads architecture, MySQL and SQLite come online with our next release.

At this post Pervasis is probably going to have to wait until MyGeneration 2.0, which will allow our users themselves to write and provide MyGeneration Meta-Data adapters. We have to cap functionality on MyGeneration 1.0 in order to keep moving forward, and there are technology changes coming in .NET 2.0 that compel us to do a rewrite from the ground up.

However, if you have time I can give you some guidelines and you could provide the queries and I could plug them in?

[mike.griffin]

It doesn't look like they've put out a .NET driver either yet, the last comment I've seen is \"that were working on one\" and that was 6 months ago, if they don't have a .NET managed driver the truth is us adding support for it wouldn't benefit us much because most of our users are .NET folks even though we can just as easilly generate java or anything else.

[John]

ok, I understand. I didn't look that close to pervasives web site. I though they finally had a .Net provider.

Yes, pass on your gidelines and I'll give it a try.

Thanks.

[John]

I was just at Pervasives web site and they now have a .Net provider. Any way you could provide me with some pointer's on how to get it to work with MyGeneration. I ask because I do data conversions at my work and the Destation database is our pervasive database. Right now I do the work in SQL then use a DST package to dump the data to the pervasive database. Works but is is all allot of work that I would rather do via my progoram. When there is a database change it thros everythign off.

Thanks.

[mike.griffin]

Try this, I think they said their OLEDB provider supports the OLEDB schema rowsets, if it does then it will much easier than digging it all out of system tables.

Code: Select all

OleDbConnection cn = new OleDbConnection();
cn.ConnectionString = \"Pervasive OLEDB connection string\";
cn.Open();

DataTable metaData = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Catalogs, null);

cn.close();

If you can get that to work we can probably pull it off ...

[John]

That funcution is not supported.

When you try to get the schema from a MS SQL server what do you do? I have installed the new Pervasive .Net provider so I'm hopeing that maybe it will support the ability to get the schema.

Thanks

[mike.griffin]

Try some of the other OleDbSchemaGuid.? schema's or are they all unsupported? I could sware their folks said they supported them? Without OLEDB schema rowset there are two other possiblities. If Pervasive supports the SQL-92 INFORMATION _SCHEMA views we're in good shape, check the documentation or search their site, there are a set of views which are described here:

SQL - 92 INFORMATION SCHEMA VIEWS
http://msdn.microsoft.com/library/defau ... z_4pbn.asp

Many databases support them.

Finallly, if those are not supported, it's going to take a couple of weeks of digging through system stored procedures to try to cobble the data together.

[mike.griffin]

Read the above post first, then try OleDbSchemaGuid.Schemata, I think
they're documenation is stating it works?

http://www.pervasive.com/library/docs/p ... mplm4.html

[John]

I see my problem. I'm not running the lasted SP which gives me thoes features. Downloading it now and going to install. I'll then give it a try.

Would this return what you wanted also?
PsqlDataReader.GetSchemaTable

[John]

ok after some more testing I have this working. Here is what I got to work.

Code: Select all

metaData = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, Nothing, \"TABLE\"})


And

Code: Select all

metaData = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, New Object() {Nothing, Nothing, sTableName, Nothing})

I get an error if I pass null to the 2nd pram of the GetOleDbSchemaTable function.

[mike.griffin]

I don't suppose you have a port a connection string you could give me to your database (of course I'd need the OLEDB provider) do you? Anyway, this is good news. We cutting the new release tonight or tomorrow night and then you and I can work together to add Pervasive SQL. The best way probably would be that I can either connect to your database?

[John]

I'll work on getting a port opened on our firewall to connect to our dev database. When I get this up can I PM you the information to connect to the database?

Thanks for all the help.

[mike.griffin]

Sure, that sounds great, thanx.

[John]

Mike, sent you a PM. Hope to finally finish this up. Let me know what you are you to this.

Thanks

[mike.griffin]

I saw it, just got back from vacation, need to respond to some email and posts and then I'll get back to you (soon).

[John]

Mike, Know your busy but if you have some time I'd like to see about making some progress on this. I have another conversion and having this type of access to the Prevasive data would be great. If not I'll just have to wait and do it the hard way.

Thanks....