I need table info all the time and I used to parse the table sqlite_master,
but as you say that is a bit of a hassle and I have now switched to using
pragma table_info('" & strTable & "')
This is a VB function, but you will get the idea:
Function GetSQLiteTableInfo(strDB As String, _
strTable As String, _
Optional strFields As String, _
Optional strSelect As String, _
Optional strDataTypes As String, _
Optional bCurrentConnection As Boolean) _
As Variant
'will produce the table fields as an 0-based 1-D array
'and make the strings:
'field1,field2,field3 etc.
'field1, field2, field3 etc.
'datatype1,datatype2,datatype3 etc.
'------------------------------------------------------
Dim r As Long
Dim c As Long
Dim strSQL As String
Dim rs2 As SQLiteDb.Recordset
Dim arr
Dim arr2
On Error GoTo ERROROUT
If bCurrentConnection = False Then
SetSQLiteConnection True, strDB
End If
strSQL = "pragma table_info('" & strTable & "')"
Set rs2 = SQLiteConn.Execute(strSQL)
arr = rs2.GetRows
ReDim arr2(0 To UBound(arr, 2))
For c = 0 To UBound(arr, 2)
arr2(c) = arr(1, c)
If c = 0 Then
strFields = arr2(c)
strSelect = arr2(c)
strDataTypes = arr(2, c)
Else
strFields = strFields & "," & arr2(c)
strSelect = strSelect & ", " & arr2(c)
strDataTypes = strDataTypes & "," & arr(2, c)
End If
Next
GetSQLiteTableInfo = arr2
If bCurrentConnection = False Then
CloseSQLiteConnection
End If
Exit Function
ERROROUT:
If bCurrentConnection = False Then
CloseSQLiteConnection
End If
MsgBox Err.Description, , Erl
End Function
RBS
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: 20 January 2007 17:16
To: Thomas Fjellstrom
Subject: [sqlite] Re[2]: Table Schema; 2. Using this list
On Saturday, January 20, 2007, 6:54:00 PM, Thomas
Fjellstrom<[email protected]> wrote:
> On Saturday 20 January 2007 6:29 am, [EMAIL PROTECTED] wrote:
>> Mark Richards wrote:
>> > [EMAIL PROTECTED] wrote:
>> > > What is the easiest way to retrieve the structure of a table? The
only
>> > > thing i have found so far is by parsing the `sqlite_master`.`sql`
>> > > which seems to be too much coding. I want them as (char**
>> > > column_names), (char **column_types) or something similar.
>> > > thanks in advance
>> > > Ivailo Karamanolev
>> >
>> > .schema {tablename}
>> >
>> > sqlite3
>> > .help
>>
>> 1. Isn't that supposed to return the SQL syntax for the table
>> creation?
>> 1.1. I would like to do this from the C API
> exec ".schema tablename" from the C api's sql query function.
>>
>> 2. How am i supposed to write replies to someone's post in this mailing
>> list?
> Its email, select a message and press "reply".
>> Thanks in advance.
>> Ivailo Karamanolev
>>
>>
>>
---------------------------------------------------------------------------
>>-- To unsubscribe, send email to [EMAIL PROTECTED]
>>
---------------------------------------------------------------------------
>>--
Am I stupid, or you assume something that I don't know for obvious?
I wrote sqlite3_exec(...., ".shema test", ....). The response was:
> Near ".": Syntax Error
I wrote that in the sqlite3 command line utility, except that it
returned nothing (just wrote sqlite3> again) which is offtopic, I
assume that it must print something similar to
CREATE TABLE `test` (...... )
which I already achieved by accessing `sqlite_master`.`sql` field.
However, that is not precisely what I want, because I still must parse
the "CREATE TABLE" SQL syntax myself, which is way too much coding. Is
there another, simpler way, or I must satisfy myself with the need to
parse SQL?
PS. To all developers of SQLite - I am surprised to see so good piece
of C code :)
Thanks in advance,
Ivailo Karamanolev.
----------------------------------------------------------------------------
-
To unsubscribe, send email to [EMAIL PROTECTED]
----------------------------------------------------------------------------
-
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
