Memory-Only SQL?

[Scott McNair]

I think I already know the answer ("NO"), but I figured I'd ask anyway:

Is there a way to populate a SQL-like object entirely in memory, without
having to save it in a SQL-like environment like MySQL, MS-SQL, or Access?

I'd like to take the contents of some CSV's and dump them into a virtual
SQL table, and then call from that table using a SQL statement ("SELECT *
FROM VirtualTable WHERE Foo='Bar'"). I know that in .NET I could use a
data-grid to do basically that, but I was wondering if there was a classic
ASP solution. More of a curiosity than any pressing need.

Regards,
Scott

 

[Scott McNair]

You can't use XML or files either?

Sure I could; it was more a point of curiosity than anything else. I'm
tons more familiar with straight SQL than I am with XML, which is why I'd
asked about SQL-friendly memory-resident tables.

 

[Tom Kaminski [MVP]]

I think I already know the answer ("NO"), but I figured I'd ask anyway:

Is there a way to populate a SQL-like object entirely in memory, without
having to save it in a SQL-like environment like MySQL, MS-SQL, or Access?

I'd like to take the contents of some CSV's and dump them into a virtual
SQL table, and then call from that table using a SQL statement ("SELECT *
FROM VirtualTable WHERE Foo='Bar'"). I know that in .NET I could use a
data-grid to do basically that, but I was wondering if there was a classic
ASP solution. More of a curiosity than any pressing need.

How about trying the ODBC text file driver with ADO?
http://msdn.microsoft.com/library/en-us/odbc/htm/odbcjetmicrosoft_desktop_database_drivers.asp

--
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsserver2003/community/centers/iis/
http://mvp.support.microsoft.com/
http://www.iisfaq.com/
http://www.iistoolshed.com/ - tools, scripts, and utilities for running IIS
http://www.tryiis.com

 

[Chris Hohmann]

I think I already know the answer ("NO"), but I figured I'd ask anyway:

Is there a way to populate a SQL-like object entirely in memory, without
having to save it in a SQL-like environment like MySQL, MS-SQL, or Access?

I'd like to take the contents of some CSV's and dump them into a virtual
SQL table, and then call from that table using a SQL statement ("SELECT *
FROM VirtualTable WHERE Foo='Bar'"). I know that in .NET I could use a
data-grid to do basically that, but I was wondering if there was a classic
ASP solution. More of a curiosity than any pressing need.

Regards,
Scott

Here's an idea:

1. Persist an empty recordset of the table in question to a file using
ADODB.Recordset.Save
2. On ASP.Application.Start, load the persisted file into an
MSXML2.FreeThreadedDOMDocument30 object and store that object in the
Application scope. This is the exception to the rule. Normally, storing
objects in the Application/Session scope is not a good idea.
3. As needed, open an ADODB.Recordset object, specifying as the source, the
IStream interface implemented by the MSXML2.FreeThreadedDOMDocument30
object.
4. Do stuff with the recordset.
5. Close and deallocate recordset.

I'm sure there's a way to do this without recordsets, using only connection
objects, but I'll leave that as an exercise for the reader. This method also
presents some interesting possibilities. For instance, you could opt to use
XPath expressions instead of SQL/Recordset.Filter to query data. An of
course since the data is already in XML format, transforming hierarchical
data become a much simpler affair.

 

[thorpe]

"Is there a way to populate a SQL-like object entirely in memory"

sql is a query language so it cant store anything. if you mean a recordset
like object, then yes you can.

just create and use a disconnected recordset.

dim rs : set rs = createobject("adodb.recordset")
rs.cursorlocation = 3
rs.cursortype = 3
rs.fields.append "field1",200,255,&H00000020
rs.fields.append "field2",200,255,&H00000020
rs.open
rs.addnew
rs.fields("field1") = "bob"
rs.fields("field2") = "smith"
rs.update

 

[Bob Barrows [MVP]]

Only store ADO objects in Application or Session if you've made the registry
change to cause them to be created free-threaded. This is not recommended if
using Access on the server.

http://www.aspfaq.com/show.asp?id=2053

Bob Barrows

 

[thorpe]

who said anything about storing it in an application or session?

 

[Bob Barrows [MVP]]

You're right. I thought that's what the original poster meant by
"in-memory", but a quick re-read reveals that may have been a wrong
assumption (despite the fact that other responders in this thread also made
that assumption)

Bob Barrows

 

[Scott McNair]

You're right. I thought that's what the original poster meant by
"in-memory", but a quick re-read reveals that may have been a wrong
assumption (despite the fact that other responders in this thread also
made that assumption)

Correct; by "in-memory", I meant basically a scratch table that resides in
memory for the scope of the current page, not one that would be kept alive
from page to page.