Fast SqLite data dump

[Fangbeast]

Thanks for sharing this great code.
I have never use database file
Can you put a link to a database who works with your code.?? :roll:

If you have never used a database file, then this code would not be useful to you as you might not understand how to fix it if I have left bugs or add to it if you need it or improve it and teach me ( and I need lots of teaching):)

As for a link to a database, I don't know of any public SQLite database for you to play with as I always make my own as everyone else does (I think).

Do you actually have a use for this?

[Kwai chang caine]

Like more and more use database, and especially SQlite.
I begin to say to me, that it's been good to see what is really the database and how she run. :roll:

It's just curiosity

Usually, the code put in the "Trick's and tips" works fine, or obviously have a little bug, but not very important.
This code is usually offered at everybody for use like he wants
It's the reason why, i like to take a look in this section, for learning

Furthermore you say :

In this version, you don't need to know any of the tables names

Then i say to me : "Good with this code it's surely more easy to use any database"

It's just that, excuse me to have disturb you
If you don't want, it's not important, you are the homeowner of this code, and it's your right

I wish you a good day

[Fangbeast]

You didn't disturb me mate, i'm disturbed enough already by the ton of kiwi sheep rooters infesting this forum lately (Very evil grin) and that srod who couldn't drive a sheep straight out of a barn if it had a steering wheel inserted up its' clavnak!!!

It's just not my job to find you databases to use with my code!!! I put out the free code as I needed it for something else I was doing, the rest is up to you:):)

/me wonders which kiwi will kill me first.

[srod]

... and that srod who couldn't drive a sheep straight out of a barn if it had a steering wheel inserted up its' clavnak!!!

Not my fault that the sheep around these parts have faulty steering and dodgy brakes now is it? They do tend to lack tread as well and so are useless for driving around in the snow!

[Fangbeast]

... and that srod who couldn't drive a sheep straight out of a barn if it had a steering wheel inserted up its' clavnak!!!

Not my fault that the sheep around these parts have faulty steering and dodgy brakes now is it? They do tend to lack tread as well and so are useless for driving around in the snow!

I blame that kiwi rooter idle for that. He's such an idle bastard that he didn't fixe the brakes and I know I sent him that big bucket of free sheep lotion for his own personal use as payment damnit!!

Maybe that other sheep fondler pdwyer was treading on the sheep and rotted the brakes again!!!

/me grins evilly.

[bobobo]

a little improvement (extracts ColumNames as well)

(blobs causes probs even without my improvement, the pb-sqlite
is'nt able to handle with such kind of data)

Code: Select all

Structure ProgramData
  DbHandle.i
EndStructure

Global NewList TableName.s(), Program.ProgramData

UseSQLiteDatabase()

DbName.s = OpenFileRequester("Open Database", "", "SQLite database file (*.db)|*.db|All files (*.*)|*.*", 0)

If DbName.s
  Program\DbHandle = OpenDatabase(#PB_Any, DbName.s, "", "", #PB_Database_SQLite)
  If Program\DbHandle
    If DatabaseQuery(Program\DbHandle, "Select name FROM sqlite_master WHERE type='table' ORDER BY name")
      While NextDatabaseRow(Program\DbHandle)
        CurrentTable.s = GetDatabaseString(Program\DbHandle, 0)    ; Table name
        If TableName.s <> "sqlite_sequence"
          AddElement(TableName.s())
          TableName.s() = CurrentTable.s
        EndIf
      Wend
      FinishDatabaseQuery(Program\DbHandle)
    EndIf
    If ListSize(TableName.s())
      ForEach TableName.s()
        If TableName.s() <> "sqlite_sequence"
          FileId  = CreateFile(#PB_Any,  TableName.s() + "_Export.Csv")
          If FileId
            If DatabaseQuery(Program\DbHandle, "SELECT * FROM '" + TableName.s() + "'")
              NumColumns = DatabaseColumns(Program\DbHandle)
              If NumColumns
                For NumStart = 0 To NumColumns
                  If NumStart <> NumColumns
                    CurrentLine.s + Chr(34) + DatabaseColumnName(Program\DbHandle, NumStart) + Chr(34) + ","
                  Else
                    CurrentLine.s + Chr(34) + DatabaseColumnName(Program\DbHandle, NumStart) + Chr(34)
                  EndIf
                Next
                WriteStringN(FileId, CurrentLine.s)
                CurrentLine.s = ""
                
                While NextDatabaseRow(Program\DbHandle)
                  For NumStart = 0 To NumColumns
                    If NumStart <> NumColumns
                      CurrentLine.s + Chr(34) + GetDatabaseString(Program\DbHandle, NumStart) + Chr(34) + ","
                    Else
                      CurrentLine.s + Chr(34) + GetDatabaseString(Program\DbHandle, NumStart) + Chr(34)
                    EndIf
                  Next
                  WriteStringN(FileId, CurrentLine.s)
                  CurrentLine.s = ""
                Wend
                FinishDatabaseQuery(Program\DbHandle)
              Else
                Debug "No columns found inside this table to process"
              EndIf
            Else
              Debug "Couldn't get the data from the current table"
            EndIf
            CloseFile(FileId)
          Else
            Debug "Could not open the export file to write to"
          EndIf
        Else
          Debug "We can't be bothered dumping the SQLite sequence file"
        EndIf
      Next
    Else
      Debug "There were no tables inside the opened database"
    EndIf
  Else
    Debug "The database was not able to be opened"
  EndIf
Else
  Debug "No database name supplied or user cancelled"
EndIf
End

[Fangbeast]

a little improvement (extracts ColumNames as well)

I didn't see the need for it in the example so I didn't put it it. But in my MultiBase program (that I use this in), I needed to have the column names when I exported the tables so it is in that one.

(blobs causes probs even without my improvement, the pb-sqlite
is'nt able to handle with such kind of data)

At least, not without statically importing the functions needed from the linked dll and either using the wrapper functions provided by TS-SOFT or rolling your own.

I had totally forgotten about blobs as I no longer use them or worry about them. What else have I forgotten???

Improvements are great, keep them coming please:):):)