Better way than using loads of params (from VFP)? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Better way than using loads of params (from VFP)?

Author

Topic

pjn
Starting Member

27 Posts

Posted - 2004-04-15 : 10:04:07

Dear all

Don't know if this is the right place to post because I'm not sure if FoxPro is at fault, the SQL Server ODBC driver, or my crappy code!

I'm currently porting a FoxPro app over to SQL Server 2000, and am wanting to use stored procedures with CursorAdapters instead of inline SQL.

However, for larger tables I'm having to pass in loads of parameters (one for each field) so some of my procedure calls run into 2500+ characters. The stored procedure is running okay, the data is getting inserted and everything appears to be working fine, but my output parameter (ie. the newly inserted PK) isn't being returned to FoxPro. When I run the stored proc in Query Analyzer, it all works perfectly and the PK is returned.

In FoxPro, it works fine on shorter commands (say 20 fields). At first I thought it was the 256 char limit in Fox, but as every field is getting inserted correctly then I'm not so sure now.

If I'm doing something totally and utterly stupid here then please let me know! I'm open to all suggestions because this has been driving me mad for days. Is there a better way to approach this?

Now for some code!

The SQL I'm using in FoxPro:


EXECUTE dbo.Tillsp_InsertTillCashUp
    @PK_TillCashUpID=?@PK_TillCashUpID,
    @CashUpDateTime=?CashUpDateTime,
    @FK_REFCompanyID=?FK_REFCompanyID,
    @FK_REFTillID=?FK_REFTillID,
    @FK_CardUserIDOperator=?FK_CardUserIDOperator,
    @FK_REFUnitID=?FK_REFUnitID,
    @FreeAmount=?FreeAmount,
    @CardAmount=?CardAmount,
    @CashAmount=?CashAmount,
    @ChequeAmount=?ChequeAmount

... etc etc for all fields

Note in FoxPro the line @PK_TillCashUpID=?@PK_TillCashUpID - the '?' means pass the contents of local field PK_TillCashUpID and the '@' means pass by reference, so it gets populated from the output parameter. This works if I'm only using, say, a dozen parameters.

And my stored procedure:


CREATE PROCEDURE dbo.Tillsp_InsertTillCashUp
(
	@TMPUserID	varchar(10)	= NULL,
	@PK_TillCashUpID	int	= NULL	OUTPUT,
	@timestamp	timestamp	= NULL,
	@CashUpDateTime	datetime	= NULL,
	@FK_REFCompanyID	char(3)	= NULL,
	@FK_REFTillID	char(3)	= NULL,
	@FK_CardUserIDOperator	varchar(10)	= NULL,
	@FK_REFUnitID	varchar(8)	= NULL,
	@FreeAmount	money	= NULL,
	@CardAmount	money	= NULL,
	@CashAmount	money	= NULL,
	@ChequeAmount	money	= NULL,
    etc
)
AS
	/* Generated by DEVsp_GenerateInsertCode */
	SET NOCOUNT ON

	DECLARE @LastError	int

	INSERT INTO dbo.TillCashUp
	(
		CashUpDateTime,
		FK_REFCompanyID,
		FK_REFTillID,
		FK_CardUserIDOperator,
		FK_REFUnitID,
		FreeAmount,
		CardAmount,
		CashAmount,
		ChequeAmount,
            etc
	)
	VALUES
	(
		COALESCE(@CashUpDateTime, getdate()),
		@FK_REFCompanyID,
		@FK_REFTillID,
		@FK_CardUserIDOperator,
		@FK_REFUnitID,
		COALESCE(@FreeAmount, 0),
		COALESCE(@CardAmount, 0),
		COALESCE(@CashAmount, 0),
		COALESCE(@ChequeAmount, 0),
            etc
	)

	SET @LastError = @@ERROR
	IF @LastError = 0
		SET @PK_TillCashUpID = SCOPE_IDENTITY()
	ELSE
		SET @PK_TillCashUpID = 0

	RETURN @LastError
GO

The error I normally get is:

"Connectivity error: Unable to retrieve specific error information. Driver is probably out of resources"

Even though SQL itself doesn't seem to be reporting any errors.

Thanks
Pete

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-16 : 20:03:43

Pretty sure this won't be a sql server problem.
You might try moving @PK_TillCashUpID to be the last parameter - doubt if it'll help though.

As an interim solution you could make it into two calls - one to insert the record and the next to update the values (can pass half the values on the first call). Not very nice and can cause fragmentation but will at lest get you going until yyou can find a good solution.

Bit concerned about what you are doing though. Sounds like you are creating stored procedures to access tables. That's not a good idea as it still binds the application to the database structure. You should use stored procedures to maintain business entities - then the database can be changed without affecting the app, just make sure the SPs have the same interface. If you do this you might find that the app doesn't have to send as many parameters per call (and you might find the app design to be cleaner too as it will be dealing with business entities only and doesn't have to worry about the database structure).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-04-17 : 02:08:30

If you're using SQL Pass Thru in VFP you're using ODBC not ADO which is just probably getting overloaded. VFP's CursorAdaptor has support for XML so you might be able to wrap the whole record into a single parameter and parse out the data in the SP instead. It's a little code intensive but it appears that hasn't slowed you down. (Me..I would have subbed the job out to some DTS junkie)

The other thing you might consider is breaking down your table structures. A lot of Foxpro stuff comes from the late 80' and early 90's where large table structures were used for convenience rather than design. Thanks to Foxpro's SQL based query language support and/or using SQL itself, cramming data into one table is no longer necessary. You could have say a Close Out master table with, Cash, Credit, etc child tables. You could do it without junking your old application.

A note for 'NR'. Most VFP apps these days usually fall into three categories. 1. Remote View based where the actual data is transported in and out of SQL into a Data management tier within the application itself. Basically turning SQL into nothing more than a data storage device. 2. SQL Pass Thru where the application mostly or completely accesses data thru SP's with parameters. If done right you will not find a single 'Select' statement against SQL in the entire app. 3. Migration apps designed to keep the company moving while the application is ported out of VFP altogether. All the rules are broken, data is accessed directly. Business rules are mixed in with the interface until the new application is done.

pjn
Starting Member

27 Posts

Posted - 2004-04-18 : 06:20:06

Thanks for the pointers. As you've probably guessed, this is a 10-year-old legacy application with just under 500 tables - the majority are un-normalised and totally horrendous. As usual, the powers that be want everything throwing into SQL Server in a matter of months. And to make it worse, the existing app has no centralised data routines, everything is cut & paste, and it is total spaghetti code. Unfortunately I've inherited the application and am the only programmer!!

Obviously I'd love to trash the entire app and do it again, but this isn't gonna happen

Currently we'll just be using SQL as a data-store (with reporting to come later) - the company just want to 'put a tick' in the SQL box to get more clients interested.

Now I'm wondering if stored procedures are the way to go for this application. I wanted to use stored procs so I don't have to give the clients full read/write access to all the tables. I was intending to have tons of SPs purely for insert/update/selecting the data from SQL - these SPs are auto-generated from a few other SPs I wrote, so that bit isn't too time-consuming.

The main problem here is time, which is why I was being lazy by maintaining an almost 1-1 relationship between the fox tables and SQL tables - I've got several thousand screens and programs to change to allow retrieval / updating of the data within SQL. And to make it worse, they want to retain the option to use FoxPro tables instead of SQL depending on the client! Therefore all the business rules will remain in the app for the forseeable future. Hence why I was trying to use CursorAdaptors - they seemed pretty handy for just getting the data in/out of SQL for our app to use.

I'd be really interested to see what people's opinions are to enable me to achieve this yukky task in the shortest time - I'm being pushed to show some results now and haven't really got very far over the last month! I've been going round in circles trying to think of the best way without wrecking the existing application! As Billpl mentioned below, should I spend more time and make the SQL structures more normalised? Should I scrap the idea of using SPs and just use let the FoxPro CursorAdaptors handle the data access?

Thanks for the comments so far - it's good to get ideas off others because I'm struggling to think outside of my original plan.

Pete

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources