SQL Temp. Tables Problem with Parameters

[SHeidenreich]

Hi.

We´re migrating our application from the FoxPro-Database to Microsoft SQL.

To translate the SQL-Statements with Cursor-DBF-Joins we want to use the temporary tables of T-SQL:

nresult=SQLEXEC(nhandle,"select * into #curtemp from adresses")

This is working fine. We can access #curtemp as a table from this handle.
But if we have Parameters, this isn´t working, the temporary Table is not created, also there is no Error

nresult=SQLEXEC(nhandle,"select * into #curtemp from adresses where adr_nid = ?nid")

Result ist 1, but the table #curtemp can not be accessed.
Also working (and atm. our alternative)
nresult=SQLEXEC(nhandle,"select * into #curtemp from adresses where adr_nid = "+transform(nid))

but the use of parameters is normal the better choice.
What is the problem of parameter usage ?

 

[Olaf Doschke]

Why go through temp tables at all? You're adding transaction log stress to the tempdb of sql server, in the long run this can blow up your hard drive space usage and you might only be able to truncate the tempdb.ldf by putting the server offline for a moment and restarting. Even then a DBCC SHRINK is not always successful.

What's the goal of these queries in VFP? Within VFP you directly query

nresult = SQLEXEC(nhandle,"SELECT * FROM adresses WHERE adr_nid = ? nid","crsAddresses") and get the numeric result status value plus the cursor crsAddresses.

Even without a cursor name you'd get a cursor aliased "Query".

Bye, Olaf.

 

[SHeidenreich]

My problem is to translate the actual in-Code-Querys to SQL-Querys, when local cursors AND DBF-Tables are accessed in the same query
e.g.in FoxPro:

select * from adress where adr_lint = .t. into cursor curAdrInt

select * from jobs inner join curAdrInt on job_nadrid= job_nid into cursor curResult

This is only a very simple example, of course this can be translated in other ways.
But i try to translate much more complex statements with Local-Cursor-Joins and the #Temp-Tables seems to be a good way...

 

[Mike Lewis]

In the particular example you give, you could do it easily in one statement:

[tt]select * from jobs inner join (select * from adress where adr_lint = 1) on job_nadrid= job_nid[/tt]

I appreciate that you will have other examples that are more complex, but in general you rarely need to create temp tables in SQL Server.

Regarding your original question, when you say the temp table is not accessible, what exactly do you mean? Are you saying that executing, say, [tt]SELECT * FROM #CurTemp[/tt] returns an error in SQL Server? If so, are you sure you are trying to access it with the same connection? Temp tables (whose names are prefixed with a single #) are scoped to the connection.

Also, if the query returns zero errors, VFP might still correctly return a positive integer from SQLEXEC(). That doesn't indicate an error.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

So does the problem begin when using the temp table in successive queries? Test whether it works to SELECT * FROM #curTemp right in the same query string (after a semicolon ending the INTO query).

If you get data this way, but not in successive queries of further SQLEXEC, then your session changes and the #curTemp table already is dropped when you try to access it. It would depend on connection settings and sharable vs non shared handle, most probably. But before you dig into this, does it work for you to do the INTO query and right afterwards SELECT * FROM #curTemp? That would at least show the temp table creating queries work and you could dig into why you can't access them from further SQLEXECs.

Simple solution would be using ##curTemp, as that means a globally temporary table not scoped to the lifetime of a session or connection. You can't use a table name twice for different users then, though. Normal temp tables have a real name with extraordinary number of underscores and ending in some random alphanumeric part, so several temp tables don't collide in names with other sessions, but in global temp tables you have to think about some naming schema like that yourself. Also these tables don't automatically drop themselves even after all sessions and connections end.

Anyway, if you translate something like you said into making the first query create a temp table to use in the secondary query, there is a simpler way of using subqueries and their aliasing:

Overall the two queries you showed can be nested this way:

select * from jobs inner join [highlight #CCCC00](select * from adress where adr_lint = 1)[/highlight] [COLOR=#CC0000]curAdrInt[/color] on job_nadrid = job_nid

Bye, Olaf.

 

[Olaf Doschke]

And if you have situations users pick a few or even up to dozens of records and you want to do an inner join with the user picked data, what I do is use a table valued function turning a list of IDs into a table variable. That function can then also be used in a query as if using the VFP cursor.

You'll find such table valued functions, if you search for T-SQL tally tables or for T-SQL (csv) splitter function.

Bye, Olaf.

 

[SHeidenreich]

nresult=SQLEXEC(nhandle,"select * into #curtemp from adresses where adr_nid = ?nid")

After this, with the same handle:
nresult=SQLEXEC(nhandle,"select * from #curtemp) : return -1

Error is: #curtemp unknown.

This problem only occurs if i use parameters, not if i use
nresult=SQLEXEC(nhandle,"select * into #curtemp from adresses where adr_nid = "+transform(nid))
and then
nresult=SQLEXEC(nhandle,"select * from #curtemp) : return 1

I don´t understand the difference between pass the value as a parameter or put it direct in the query - the sql server should handle both statements equal ?

Thx so far,
Stefan

 

[SHeidenreich]

Update, i just read the posts from OlafDoschke

I tested within the same query like this:
nid = 1000
csql="select * into #curtest from adresse where adr_nid = ?nid; select * from #curtest"
SQlexec(nsqlhandle, csql)
This does work! But wy the #curtest is dropped. The handle is still the same...
The ## is not the right thing for this, i want the 'cursors' to get dropped if the handle is closed (like VFP Cursors are data-session-Bound).

 

[Mike Lewis]

Stefan,

Basically you are right when you say that there should be no difference in passing the value as a parameter and embedding it in the query. On the face of it, the two methods should give the same result.

Are you sure the parameter variable (nid in this example) is in scope at the point when you run the SQLEXEC()? (I mean "in scope" within VFP, of course, not in SQL Server). Off-hand, that's the only reason I can think of it for it not to work.

Also, you might try running a simple test using the simpelst possible query, without the distraction of temporary tables, etc.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Mike Lewis]

Stafan, having just read your update, I will repeat the last point I made in my previous post.

Run a test using just [tt]select * from adresse where adr_nid = ?nid[/tt]. If that still doesn't work, then you know the problem has got nothing to do with the temp table.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SHeidenreich]

The variable should be in scope. I tested it with public for the parameter variable. Also i get an input prompt if not in scope.
This is what i dont understand.
My problem occurs only if u use the temp. tables.

nid = 1000
Sqlexec(nsqlhandle,"select * from adresse where adr_nid = ?nid) && return 1, Result OK

Sqlexec(nsqlhandle,"select * into #curtest from adresse where adr_nid") && return 1, No result (of course, the into #temp doesn´t give results)
but also the handle is the same, i cant access #curtest,it seems to be lost.
Sqlexec(nsqlhandle,"select * from #curtest") # return -1, Error: curtest unknown

 

[Mike Lewis]

What happens if you run the whole thing from within SQL Server?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SHeidenreich]

begin
declare @test as int
set @test= 1000
select * into #curtest from adresse where adr_nid = @test
end
begin
select * from #curtest
end

Working

 

[Dan Freeman]

i want the 'cursors' to get dropped if the handle is closed (like VFP Cursors are data-session-Bound).

There's a mindset here you need to avoid. Don't tie one of SQL's hands behind its back forcing it to behave like Foxpro.

You've been given other options in this thread to get the result you want. Don't look for ways to use a new tool exactly the same way as you used the old tool. Use the new tool's unique strengths to your advantage.

 

[Olaf Doschke]

I second Dan,

on the subject of finding the reason the temp table is dropped sooner as you think: The same handle does not mean the same session, those are two separate things and SQL Server decides anout sessions, not you. That's why querying the temp table within the same SQLEXEC call works. To gain more control and find out what keeps the session scope you need to dig deeper into the connection properties.

Anyway, there are other suggestions than using temp tables for your needs. Instead of ensted aliased queries, which keep the syntax almost the same besides inserting a bracket with the query, you could also make use of CTE. While it seems more complex, it's just putting the subquery out of the main query and giving you extra options like recursion. And besides a splitter function turning something liek "12,42,232,455" into 4 records an IN clause could also be used in some places.

The main problem you have, if you have done many native VFP SQL queries in code is that you never thought about a class structure of data access classes working with a core concept of retreiving data. Your queries than would most probably be less scattered in the application code and be more centralized and easier to transfer to T-SQL queries or views. And thaat's another option: You may define views in the server and use and combine them into more complex queries.

Bye, Olaf.

 

[SHeidenreich]

A code concept of retreiving data would be nice...
but i inherited that application and have to migrate it to SQL asap. So the temp. tables looks like a good First-Step Soulution to 1:1 migrate the statements.
We have to maintain the old code, so it helps the others, if the code looks similair to the "as we now is for years".
I´ve done other things like refactoring the code with subselects or dynamic build inlists like your example, but at some points, i tryed it with the temp.tables to far and was wondering about this parameter problems.

Ty to all helpers.

Stefan