Suggestion for VFP-MySQL Multiuser env.

Hi all,

I've just shifted from Foxpro tables to MySQL server based application. For a multiuser environment in VFP I used to have the temporary tables for the individual users in their own local machines. But in case of Mysql / oracle how to handle this situation

The temporary table that I 'm speaking of are generated when I want to create some reports or perform some maintenance on tables. For example consider a scenario of producing a Sales analysis report. In VFP based application I created a local table say temp and put the data from invoice, invoice return and all related tables. then I manipulated the temp table to suit my reports. There was no problem in multiuser env. since each user created their own 'temp' table in their own system.

If I follow the same procedure here I feel this will add a overhead to the network first.( I agree this was there in the VFP case also). I want to handle this situation with some SP in the backend. Please suggest me the ways for handling this type of situation.

Thanks in advance,

Markish.

Visual FoxPro1

You shouldn't use tables. Use Cursors instead. Cursors are created on the users local machine so your overhead issue is solved.

Hi,

Do you mean to say that my SPs should produce cursors Please explain.

Markish

Apparently you were doing it the wrong way in VFP. It's good you're not duplicating it now.

From your stored procedure return manipulated result. You might also have a view that represents 'manipulated' version and you could select from that view parametrically.

Hi,

Thanks for your inputs. What happens when more than one user execute the same SP simultaneously What about the resultant 'manipulated' result. (I'm bit confused, excuse me if I sound silly..!)

Consider a SP that produces a sales analysis data for a given quarter. If user A runs the SP with Q1 and the user B with Q2 and user C with Q3 ....simultaneously, what about the resultset What I'm asking is whether the SP should return the resultset or create a result set as table there in the server (back-end)

-Regards,

Markish

Hi,

I got clear. This can be achieved with the use of temporary tables. http://forge.mysql.com/wiki/TemporaryTables made me clear. Anyhow thanks for all your inputs.

Regards,

Markish.

OK think if in VFP,SQL server,Oracle,MySQL or whatever 3 users simultaneously call:

User_1

select * from customer where country = 'USA'

User_2

select * from customer where country = 'UK'

User_3

select * from customer where country = 'Germany'

Do you think anyone of those resultsets were mix If it did then those databases wouldn't be in use at all. Calling an SP is no different, each user gets what she/he asked for. Temp tables are a little special and generally they're used for ease of programming and not for that they should be used (users tend to create temp tables when there is a more elegant but code wise complex alternative such as a single select). In mySQL I don't know how temp tables work. But in SQL server there are 2 kinds of temp tables:

1) Specific to connection\session

2) Shared between all connections

If mySQL is also has a distinction then be carefull which one you're using. Otherwise you might gt the mixup you were afraid of.

Hi,

Thanks for your valuable suggestions.

But My question was not a single select.... like

User_1

select * from customer where country = 'USA'

User_2

select * from customer where country = 'UK'

User_3

select * from customer where country = 'Germany'

If the same select is something like "select * from customer where country='USA' into table temp1"....and it's executed by three users simultaneously as I told what happens to the temp1 table....Can we return the temp1 table from the SP as such

Moreover I agree your point on the usage of temp. tables.

Regards,

Markish

Markish,

That was the thing that I thought you were doing it the wrong way in VFP. If you select something into a table then that 'table' is something persisted on disk. It has a name that's same for all users just like any other file. In VFP for example instead you do:

select * from customer where country = "USA" into cursor temp

In that case 'temp' is the cursor name for all users who execute this SQL (or a similar one with 'temp' as cursor name). However all those 'temp' are just aliases to the real file on disk which have a unique name per user (and in many cases a file on disk is not created at all). It's like both you and me are doing:

myVar = 3 && you

myVar = 2 && me

We both have myVar as variable name but our memory space is different and thus we can use it with different values.

I don't know "temp tables" in mySQL. In SQL server they're created with a # prefix and even if the names are same between N simultaneous connections all are specific to callers. Should be similar in mySQL. SQL server has additional capabilities like creating a 'table variable', output table etc. (Why don't you use MSSQL BTW, if your databases are not large, if large then of course it should be paid and might not be feasible for you).

Hi,

I got it right now. In VFP I had something like differerent working folders for different users. I created the temporary files in that folder. So I didn't faced any problem. Anyhow in SQL server based one, I'll go with the cursors or temp tables.

Then regarding MSSQL, of course you are right, the client is not ready to invest extra for a database server. Once again thanks a lot.

Regards,

Markish