Which one....???

[NEE4NEE]

Moving DBC to SQL Server so many question ...

1) Want Free Database Server for Commercial Use
Having good GUI Tools available to admin.
Mysql/Postgre/Ms Sql ...?

2) Type of connection to access database
ADO , ODBC which is best.

3) Cursor Adepter or SPT

Please share your opinion.

 

[Olaf Doschke]

1) I actually don't know all license models but think MySQL is not free for all commercial usages and MS Sql Express or Compact is. I'd say you find administrative tools for all DBs more or less comfortable.

2) Yes, ODBC is more performant in my experience, too. ADO is an option you need to connect to things being no traditional database, the ADO model allows for more than databases, tables, views, fields, stored procs, that's it's advantage, disadvantage is it's less performant for database access, as it's overhead (in foxpro) is creating a ADO.Recordset for queries, especially for CAs based on ADO this is done additional to creating a vfp cursor. That's which simply costs time.

3) CA is good for simple data access, that is querying tables quite like updatable remote views with some more possibilities than views allow. SPT is good for complex queries or exec of stored procs, etc, while you can also put such calls and whole SQL Scripts into a CA's SelectCmd property, CA's primary usage is to create a single cursor, SPT can be used quite liek a procedural code, having no result (eg alter table, create dataabase/table), and also having multiple results.

Use a mixture of both. Use CA with an ODBC driver/connection, then you can share that connection handle with SPT. Not that this is the main advantage, but it's one more point against ADO, you only go through one ODBC driver.

Bye, Olaf.

 

[craigber]

I ran a series of tests a few years ago that compared different data access methods from VFP to SQL Server. In the end, it didn't really matter which method you used, ADO, ODBC, Remote Views, SPT, or CursorAdapter. They all ran pretty much the same speed. The difference is negligible in most usage conditions.

The only use that had a clear winner, was importing large amounts of data. SPT was much, much faster than other methods. However, I did not test again Bulk Insert.

Olaf: Can you clarify your statement in your last paragraph. If you're going through ADO *and* ODBC, you're doing it wrong.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Olaf Doschke]

Hello Craig, what as unclear about my last paragraph?

The mixture I talked about was CA and SPT, not mixing ODBC and ADO.

I just said it's one more point for using CA via ODBC, that you can do both SPT and CA with the same ODBC driver and share the connection.

Bye, Olaf.

 

[craigber]

What you said was, "but it's one more point against ADO, you only go through one ODBC driver.". I know that English isn't your native language, but the sentence implies that you go through more than one ODBC driver to use ADO. When you properly go through ADO, you don't use any ODBC driver.

Craig Berntson
MCSD, Visual C# MVP,

http://www.craigberntson.com/blog

 

[Mike Lewis]

NEE4NEE,

You say you want to move to SQL Server, but then you ask for advice about which database to use. SQL Server is a database. It is exactly the same kind of product as MySQL, PostgresQL, Oracle, etc.

If you definitely want to go with SQL Server, and you want a free version, the only option is SQL Server Express. This is a very good choice, provided you can live with its limitations on the size of the database (4GB in SQL Server Express 2005; higher in later versions).

MySQL is also a good choice from a technical viewpoint, but it is not completely free. Rather, it is open source. That means there will be restrictions on how you use it in a commercial environment that might be unacceptable to you.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Craig: What I mean with this part is:

That you can go through only one driver, the ODBC) driver. And that is an advantage of using ODBC and so disadvantage for using ADO.

Bye, Olaf.