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

[Curt_C [MVP]]

You can't use XML or files either?

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in message
news:Xn*******************@207.46.248.16...

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]

"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in news:O5TMx$MoEHA.800
@TK2MSFTNGP14.phx.gbl:

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]]

"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in message
news:Xn*******************@207.46.248.16...

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...se_drivers.asp

--
Tom Kaminski IIS MVP
http://www.microsoft.com/windowsserv...y/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

[Curt_C [MVP]]

Ahh... then afaik the answer is nope.

--
Curt Christianson
Owner/Lead Developer, DF-Software
Site: http://www.Darkfalz.com
Blog: http://blog.Darkfalz.com
"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in message
news:Xn*******************@207.46.248.16...

"Curt_C [MVP]" <software_AT_darkfalz.com> wrote in news:O5TMx$MoEHA.800
@TK2MSFTNGP14.phx.gbl:

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.

[Chris Hohmann]

"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in message
news:Xn*******************@207.46.248.16...

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

"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in message
news:Xn*******************@207.46.248.16...

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

[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 wrote:

"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

"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in
message news:Xn*******************@207.46.248.16...

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[thorpe]

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

"Bob Barrows [MVP]" wrote:

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 wrote:

"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

"Scott McNair" <sc**********@sfmco.takethispartout.com> wrote in
message news:Xn*******************@207.46.248.16...

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

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[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
thorpe wrote:

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

"Bob Barrows [MVP]" wrote:

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 wrote:

"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.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Scott McNair]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in
news:uJ**************@TK2MSFTNGP11.phx.gbl:

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.