Fully Using SQL Server as Database

The place for peer-to-peer support for developing client-server applications with VPME 9.1.

Fully Using SQL Server as Database

Postby rqabonk » Tue Nov 20, 2012 9:57 pm

i have a question, can we using full sql server as a database, not a dbf table including security free table? if can, how we set the security tables? thank you.
rqabonk
 
Posts: 1
Joined: Tue Nov 20, 2012 9:51 pm

Re: Fully Using SQL Server as Database

Postby stecenko » Wed Nov 21, 2012 2:38 pm

Sort of.

Yes, you can use SQL as your database engine. You will have to change a lot of your code. How much depends on whether your application uses tables or views?

As far as I know, no one has moved all of the Promatrix meta-data into SQL. I have moved Audit, Activity, and Error using VPME 9.1.12

The critical factor for you is what version of Promatrix are you using; if not VPME 9.1.12, you will have a lot of work ahead of you to move your data into SQL.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Fully Using SQL Server as Database

Postby Gerhard Schmidbauer » Tue May 28, 2013 12:25 am

stecenko wrote:Sort of.

Yes, you can use SQL as your database engine. You will have to change a lot of your code. How much depends on whether your application uses tables or views?

As far as I know, no one has moved all of the Promatrix meta-data into SQL. I have moved Audit, Activity, and Error using VPME 9.1.12

The critical factor for you is what version of Promatrix are you using; if not VPME 9.1.12, you will have a lot of work ahead of you to move your data into SQL.


Hi Richard,

could you elaborate a little more what one has to do when using VPME 9.1.12?

TIA
Gerhard
Gerhard Schmidbauer
URANUS Software GmbH
Germany
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: Fully Using SQL Server as Database

Postby stecenko » Tue May 28, 2013 10:42 am

To my mind, the critical factor is whether your existing application is 'pure views'.

If you are using tables with indices and replaces, then all that has to be converted to views.

The issue then becomes this...

Is it easier to re-do your existing application so it uses views and then move the views to SQL
or
re-write your existing application in VPME 9.1.12 to use remote views?

But it's not really that simple. For example, are you using surrogate keys? If not, that adds a another complexity to using SQL especially if you have compound primary keys and especially if you have compound primary keys that include a date field.

There is nothing you used to do with tables that you can't do with SQL. But, if you are going to keep all the 'old-fashioned' ways of doing things when you convert to SQL, then why bother?

You have to examine your reasons for converting.

If you want performance, then you have to re-consider table design. A slow, bloated database is bloated whether it is in VFP or SQL.

If you want to share your database with a web-based application, then you also have to consider table design.

If you are going to re-design the database, then you are probably going to re-write. If that's the case, then re-do it in SQL and VPME 9.1.12 using remote views. You can copy/paste code, re-use reports.

Remember, VPME is a rapid-development tool. You'll probably find that 75% of your forms can be re-build with the builders without adding any code. And 25% of your reports can be made more elegant using VFP 9

It all depends on why you are converting.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Fully Using SQL Server as Database

Postby stecenko » Tue May 28, 2013 11:07 am

Let me add a few more things.

The problem is not converting from DBFs to SQL, it's converting from old habits to new. And doing it all at once.

I started with VPME 6.0c in 2000.

During that time I learned a couple of good habits.

1) don't use on-form toolbars;
2) use a sophisticated naming convention (thank you Steve Buttress);
3) use surrogate keys;
4) upgrade and fix 'defects' as soon as you can (thank you Hank Fay, who constantly reminded us to never take short-cuts, never put off fixing defects);
5) use views (thank you everyone at ProMatrix);
6) never put a calculated column in a table (thank you Jerry).

So if you programmed an application a long time ago and watched passively while habits and best practises evolved, then converting your table-based application to SQL will be painful.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Fully Using SQL Server as Database

Postby Gerhard Schmidbauer » Wed May 29, 2013 8:09 am

Thank you Richard.

Could you please say what data type you use for Logical fields in MS-SQL. From first look I would use BIT, but I do not have any experience with MS-SQL.

TIA
Gerhard
Gerhard Schmidbauer
URANUS Software GmbH
Germany
Gerhard Schmidbauer
 
Posts: 152
Joined: Tue Jun 15, 2010 10:33 am
Location: Burtenbach, Bavaria, Germany

Re: Fully Using SQL Server as Database

Postby stecenko » Wed May 29, 2013 9:56 am

I would use integer.

I'm pretty sure that bit would work. But all the VPME meta data tables use integer and I believe My SQL uses integer.

Also, in MS SQL watch out for the date column type. It holds a date in the format yyyy-mm-dd, but is seen by VFP as char(10).

For now, I'm using dateTime and trying two options.
1) in the sDataDDF table changing the T to D
or
2) fidding with the textbox that has the dateTime and making it so narrow that the time portion doesn't appear.

The reasons I don't want to use MS SQL's date type are...
1) I don't get the VPME date picker;
2) I have to convert the char to date to do any arithmetic.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia


Return to VPM Enterprise 9.1 - Client Server

Who is online

Users browsing this forum: No registered users and 2 guests

cron