Re: Utilizing temporary tables via an ODBC connection?
Posted in 2005
Topics: Performance & Tuning, SQL Development & Query Writing, Stored Procedures & SPL, Connectivity: ODBC / JDBC / .NET
Thanks for the response, we've chosen not to make any server side mods, so adding views or SP's is not in the gameplan. I was hoping I would be able to create/use temp tables via SQL over the ODBC connection instead. -- William Fields MCSD - Microsoft Visual FoxPro US Bankruptcy Court Phoenix, AZ "I have no special talents, I am only passionately curious" - Albert Einstein "Andrew" <a@a.co.uk> wrote in message news:uTOVd.4954$Ol3.3082@newsfe3-gui.ntli.net... > > "William Fields" <Bill_Fields@azb.uscourts.gov> wrote in message > news:d056kf$5ml$1@apollo.nyed.circ2.dcn... >> Hello, >> >> We (my partner and I) had a situation come up yesterday that we were able > to >> get around, but I'd like input on what the best practice might be. >> >> I have a temporary resultset that I'd like to join with another SQL, or > use >> in a subquery in another SQL. FYI - I'm using Microsoft Visual FoxPro, >> connecting to an IDS v7.3 instance via the current ODBC drivers from IBM. >> >> What we ran into was that our initial SQL ended up as a local FoxPro > cursor, >> which of course could not be referenced within another SQL statement sent >> over the ODBC connection. The solution was to parse the cursor and > assemble >> a new SQL statement with an "WHERE IN (ValueList)" clause. >> >> Is there a way to create a temporary cursor on the Informix server that I >> can reference in other SQL statements via an ODBC connection? Any >> information would be helpful. >> >> Thanks. > hard to be sure, I'm not 100% sure what you're getting at - but I'd think > about one possibility. Have you considered writing a stored procedure (or > view or nest of views) to return that data? OK - you don't really have > the > kind of optimisation for this you get in - for example SQLServer, but, as > I'd assume that your server's probably rather more powerful than the > clients > it should be able to process the query faster. Also, particularly if the > datasets are large, processing on the server and returning the resultset > as > one recordset rather than several over the network and back, with > additional > processing, may well improve performance (if that's the issue). If you do > use a stored procedure, see if you can get by without cursors. > > hth > Andrew > >