Transferring a DB result set, aka Dataset

Foz · Post by **Foz** » Sun Jun 29, 2008 5:18 pm

Here's something that people may find useful - especially if you couple it with a server/client set up - for example with the XML-RPC example that I posted a while ago.

What this does is : you can query the database, and the result set it converts into an SQL dump string, i.e. it starts with a CREATE TABLE statement and then is followed up with a bunch of INSERT statements to fill it.

This can be sent via the network which then processes the string and rebuilds the result set, except it uses a MEMORY database to rebuild it. The memory database can then be used, at your leisure to do what ever you want with it.

The best thing is that you can build up the the SQL dump string, so you could select the id and description from a menu table, id, dll name and function from the assembly table, etc. The SQL Dump does not care - it just executes one statement at a time when rebuilding it in the memory database.

It is a very raw solution and will be full of holes (such as using semi-colons in data - I haven't fixed that yet, and giving a table an alias - I couldn't work out the regex to extract that - help will be appreciated to solve these

)

So, without further ado, this is the sql needed to create "test.db" (the SQLResultsDump is to thank for these

)

Code: Select all

CREATE TABLE Menu ([Menu ID] INTEGER,[Description] TEXT,[Assembly Name] TEXT,[Interface] TEXT,[Folder] TEXT);
INSERT INTO Menu VALUES (1,'Hello World','app1.dll','HelloWorld','Components');
INSERT INTO Menu VALUES (2,'Consignment Entry','ConsignmentEntry.dll','ConsignmentEntry','Components');

and here is the pb code:

Code: Select all

; Helper Functions
Procedure.b ToDataSet(pSQL.s, *DB.LONG)
  If Not CreateRegularExpression(0, ".*?;")
    Debug RegularExpressionError()
    ProcedureReturn #False
  EndIf
  
  Dim Result.s(0)
  Protected NbFound.l = ExtractRegularExpression(0, pSQL, Result())
  If NbFound = 0
    ProcedureReturn #False
  EndIf
  
  *DB\l = OpenDatabase(#PB_Any, ":memory:", "", "")
  
  Protected i.l = 0
  For i = 0 To NbFound-1
    DatabaseUpdate(*DB\l, Result(i))
  Next
  
  ProcedureReturn #True
EndProcedure

Procedure.s SQLResultsDump(pConnectionString.s, pSQL.s)
  Protected db.l = OpenDatabase(#PB_Any, pConnectionString, "", "")
  If Not DatabaseQuery(db, pSQL)
    ProcedureReturn ""
  EndIf
  
  ; extract the table name for the CREATE statement
  ; nice little regex - but it may need some more work
  ; nb: ignore case: (?i), multiline: (?m), ignore case and multiline: (?im)
  Protected TableName.s = "Table" ; default table name
  If Not CreateRegularExpression(0, "(?im)(?<=from).*?(?=\s(left|inner|join|where)|$)")
    Debug RegularExpressionError()
    ProcedureReturn ""
  EndIf
  
  Dim DatabaseType.s(5)
  DatabaseType(0) = "BLOB"
  DatabaseType(1) = "INTEGER"
  DatabaseType(2) = "TEXT"
  DatabaseType(3) = "REAL"
  DatabaseType(4) = "REAL"
  DatabaseType(5) = "INTEGER"
  
  Dim Result.s(0)
  If ExtractRegularExpression(0, pSQL, Result())
    TableName = Result(0)
  EndIf
  
  Protected QueryDump.s = ""
  
  QueryDump + "CREATE TABLE " + Trim(TableName) + " ("
  
  Protected NbColumns.l = DatabaseColumns(db)
  Protected i.l
  For i = 0 To NbColumns-1
    QueryDump + "[" + DatabaseColumnName(db, i) + "] " + DatabaseType(DatabaseColumnType(db, i))
    If i<NbColumns-1
      QueryDump + ","
    EndIf
  Next
  QueryDump + ");"
  
  While NextDatabaseRow(db)
    QueryDump + "INSERT INTO " + Trim(TableName) + " VALUES ("
    For i = 0 To NbColumns-1
      If DatabaseColumnType(db, i) = 2 ; text
        QueryDump + "'" + ReplaceString(GetDatabaseString(db, i), "'", "''") + "'"
      Else
        QueryDump + GetDatabaseString(db, i)
      EndIf
      If i<NbColumns-1
        QueryDump + ","
      EndIf
    Next
    QueryDump + ");"
  Wend
  
  CloseDatabase(db)
  ProcedureReturn QueryDump
EndProcedure

Procedure.s List_ApplicationMenu()
  ; this could be used on a server as all the SQLResultsDump
  ; does is create the SQL to recreate the results
  ProcedureReturn SQLResultsDump("test.db", "select [Menu ID] [id], Description d from Menu")
EndProcedure

; Example Call
UseSQLiteDatabase() ; note this is example is using an SQLite file for data retrieval, as well as a memory db

db.l = 0
If ToDataSet(List_ApplicationMenu(), @db)
  If Not DatabaseQuery(db, "select id, d from [Menu]")
    MessageRequester("Error", DatabaseError())
    End
  EndIf
  
  While NextDatabaseRow(db)
    Debug DatabaseColumnName(db, 0) + Str(GetDatabaseLong(db, 0)) + "     " + GetDatabaseString(db, 1)
  Wend
  CloseDatabase(db)
EndIf