Hello,

I need detailed informations on how to develop database drivers. I found in the SDK Documentation the following line:

How to develop database drivers will be added to the SDK in the future

I am looking forward to it.

Please as soon as possible. I have a little bit spare time and want to deal with that issue.

Regards, Carlo

For which database do you want to develop a driver?

It was initially a plan to add the docs to the SDK, but it was more cumbersome than anticipated, mainly because drivers often have to jump through hoops to get the info out of the db.

To build support for a given db X, you have to write: - driver - DQE - specific templates

DQE and templates can often be based on an existing version, by changing some code. This is also specialistic code per db, as often specific things have to be done per db, so code-reuse through inheritance isn't that useful however you can borrow parts of routines.

Otis wrote:

For which database do you want to develop a driver?

There is already a thread - Foxpro DBF Driver But I want to give it a try.

Please give me the necessary information, that I can start my research

Regards, Carlo

Rushmore wrote:

Otis wrote:

For which database do you want to develop a driver?

There is already a thread - Foxpro DBF Driver But I want to give it a try.

Please give me the necessary information, that I can start my research

Regards, Carlo

Ok, first step is the driver. Foxpro needs teh OleDb provider for everything so I'd do the following: - copy the MS Access driver sourcecode and rename files/project and the like so they match foxpro instead of access - change the GUID in foxprodbdriver.cs -> DBDriverID and also in the driver.config file - create a folder 'Foxpro' in llblgenpro installation folder\Drivers - place driver.config and the compiled driver into that folder. Also create a Templates folder in that driver folder

Ok, now several things have to be setup. First you have to create type lists. Open foxprodbdriver.cs and at the top you'll see a list of enum defines, e.g AccessDbTypes. You define there all NATIVE types of the db. So in terms of sqlserver, you would see there int, bit, varchar, text, image etc.

Then, you've to create 3 mapping tables. Theses are filled in FillDBTypeConvertArrays(). The first, DbTypesAsProviderTypes is the OleDbType.xxxxx name for each native type. The rest of the 3 tables speaks for themselves.

You can re-use a lot from the access driver, as it generally works the same. Browse through the foxprodbdriver.cs file to see if you need to alter more things (I'm not familiar with the inner workings of foxpro), like the connectionstring format.

Next: FoxproConnectionData control. That's the control which is inserted into the create project wizard and gathers file name and other info about the db to be able to connect to it and read schema data.

Next: FoxprocatalogRetriever. This is the retriever for each schema. Foxpro uses a single file per db, so this is largely the same as MS Access' code, also because ms access code uses solely OleDb for schema retrieval, so you can simply re-use this code.

Next: FoxproSchemaRetriever, the actual schema data retriever. Now, the RetrieveSchema routine is where it all starts. Start there and work down. If foxpro supports identity fields, you've to add a sequence object. This is then later on used to retrieve / set the newly inserted pk value.

If foxpro supports procs (I've no idea it does or doesn't), you might need to port code from the other drivers to this one, but if you don't need that leave it at this for now.

The code which retrieves the PK fields is custom, though shoudl also work for foxpro (populatealltablefields routine).

If you have this working, please report back so we can go look at the templates/dqe.

Thank you.

Now I can select the new database driver (Microsoft VisualFoxpro Driver), select the database file and the UI retrieves the database schema. Also the primarykeys and relations are displayed.

I tried this with the northwind.dbc sample file.

Ok, let´s start with the DQE/Template thing

Rushmore wrote:

Thank you.

Now I can select the new database driver (Microsoft VisualFoxpro Driver), select the database file and the UI retrieves the database schema. Also the primarykeys and relations are displayed.

I tried this with the northwind.dbc sample file.

Ok, let´s start with the DQE/Template thing

Excellent!

The DQE for Foxpro and the templates take the following steps: Again, base your stuff on the ms access material available. The DQE: simply copy the MS Access DQE code and rename the files and port the code over if required. This won't be a walk in the park I think, you need to alter a lot of stuff, potentially, but it might be easy, I don't know how much foxpro queries differ from ms access sql. You should start with the dbspecific creator implementation, there you create parameters, names for fields, tables etc. Then you move on by porting the 4 query routines.

The templates are usable without a modification I think, as both use OleDb and both don't support procs. You only need to alter the template .config files of course.

Hi Otis,

you are right, it isn´t a walk through the park I haven´t found a solution for the "@@identity" stuff so far.

Rushmore wrote:

Hi Otis,

you are right, it isn´t a walk through the park I haven´t found a solution for the "@@identity" stuff so far.

Does Foxpro have identity fields/autonumber fields? If so, how do you normally read back the value inserted, e.g. when you're using normal ADO.NET. ?

Does Foxpro have identity fields/autonumber fields?

Foxpro (Version >= 8 ) has autoincrement functionality. The given projects are developed with Foxpro 6 SP5.

If so, how do you normally read back the value inserted, e.g. when you're using normal ADO.NET

That is the problem, in ADO.NET I didn´t have the desire so far. I use local views in Foxpro, that means if I add a new row, the primary key value is generated by a stored procedure (DefaultValue of the field), and immediatly available in the new row. The primary key is a 20 byte calculated unique string.

If I add a new row by ADO.NET (INSERT INTO) the new Id is also generated but I have no idea how to retrieve this new id without reading it back.

You could opt to call the proc in your code using a generated action procedure call, then set that value as normal field value in the entity and save it. So for your DQE (and driver!!) Identity fields don't exist.

Do you mean that I have to manage the unique id generation in the dqe insert statement?

Rushmore wrote:

Do you mean that I have to manage the unique id generation in the dqe insert statement?

No, in your own code. int newId = 0; ActionProcedures.GetNewId(ref newId); MyEntity e = new MyEntity(); e.Id = newId; //... e.Save();

Also, if your foxpro doesn't support identity columns, you'll never run into an identity column in your db, as the driver can't know that.

How can I debug my foxpro driver during database schema retrieval?

Rushmore wrote:

How can I debug my foxpro driver during database schema retrieval?

Place the .pdb file which you get when you build a debug build also in the drivers\Foxpro folder. Then you start the LLBLGen Pro designer, you also have the driver source in vs.net and you press cntrl-alt-P, which allows you to attach to a process, in this case LLBLGenPro.exe. You set a breakpoint, and you go to the designer and start a project. You then should hit your breakpoint

Ok Otis,

I will give it a try in a couple of hours. I have created a database with all datatypes, and I am wondering why OleDbType.DateTime is .NET string? It isn´t clear for me. The datatype mapping is in my opinion correct.

All other datatype mappings are correct.

Ok, another idea: What about Database modelling tools like xCase or whatever?

xCase has the ability to copy one dbms to an other dbms e.g. Access to MySql, MySql to DB2 etc, but the source for the datadictionary is always the same. The product is based on dbf files

The datadictionary contains the whole schema. In the datadictionary there are entities, fields, indexes, constraints and relations etc. There are also user defined datatypes called domains.

Regards, Carlo

Rushmore wrote:

Ok Otis,

I will give it a try in a couple of hours. I have created a database with all datatypes, and I am wondering why OleDbType.DateTime is .NET string? It isn´t clear for me. The datatype mapping is in my opinion correct.

Why do you think it's a string? If you read the data, it's a string type?

Ok, another idea: What about Database modelling tools like xCase or whatever?

xCase has the ability to copy one dbms to an other dbms e.g. Access to MySql, MySql to DB2 etc, but the source for the datadictionary is always the same. The product is based on dbf files

The datadictionary contains the whole schema. In the datadictionary there are entities, fields, indexes, constraints and relations etc. There are also user defined datatypes called domains.

Regards, Carlo

Don't they store meta data in a set of tables? I'm unfamiliar with xCase.

Why do you think it's a string? If you read the data, it's a string type?

I found the problem and solved it. Now all Foxpro datatypes are mapped correct.

Foxpro DateTime is OleDbNative 135 and date is 133. That wasn´t defined. The FoxproOleDBToNativeDBType() Method returned 11 (default) instead.

Thanks for the debug hint . Debugging is wonderfull

Yeah !

I have inserted my first row in a foxpro table with LLBLGen. More informations coming soon.

I have to debug the DQE because the values of the parameter collection are not set . Maybe that is an datatype issue.

have to debug the DQE because the values of the parameter collection are not set . Maybe that is an datatype issue.

That was my fault I have initialized myEntity.Integer with 0 (myEntity.Integer = 0). All along I thought I have set the MyEntity.Integer to 4.

Distressing

Cool it finally starts to give results! Keep me posted.

I have spent a lot of time to test the DQE (insert, delete, update and select). Everything works fine after a couple of modifications (I hope ) .

Now I thing, it is time to run that against our database. We have at least 355 entities, more than 600 views and nearly 13.000 columns. The views are uninteresting.

Ok, the tables are gathered. But if the columns are gathered, it runs into an exception. Stackoverflow!

How can I prevent that exception? Any suggestions?

Rushmore wrote:

I have spent a lot of time to test the DQE (insert, delete, update and select). Everything works fine after a couple of modifications (I hope ) .

Now I thing, it is time to run that against our database. We have at least 355 entities, more than 600 views and nearly 13.000 columns. The views are uninteresting.

Ok, the tables are gathered. But if the columns are gathered, it runs into an exception. Stackoverflow!

How can I prevent that exception? Any suggestions?

You're using recursion somewhere? Run the driver in the debugger, then when a pause seems to happen, hit break in vs.net and see in which routine you're looping.

// get table and view definitions. All system tables are filtered out. base.SubTaskProgressTaskStartHandler("Retrieve all table and view definitions."); RetrieveAllTablesViews(connection); base.SubTaskProgressTaskCompletedHandler();

This line runs directly into the hell

// Get all columns. DataTable columns = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);

// populate tables with fields. base.SubTaskProgressTaskStartHandler("Retrieve all table/view field definitions."); if((_fetchFlags & SchemaFetchFlags.Tables)==SchemaFetchFlags.Tables) { PopulateAllTableFields(connection, columns); }

Huh, that's a call into the oledb driver... That line goes straight into stack overflow? Hmm...

Hi Otis,

I think, I have a workaround for that:

Reading the "TABLE" meta data aren´t the problem. That gives me the idea, to read the "COLUMN" meta data for each table separately. And miraculously - that method reads all the columns (more than 11.000) without an exception.

Now I have to modify the Golden(Schema)Retriever haha.