On Saturday, January 20, 2007, 11:29:51 PM, RB Smissaert<sqlite-users@sqlite.org> wrote: > As said you can run
> pragma table_info(table) > in C as a query against the database table and it will give you all the info > you need without any awkward parsing. > RBS > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] > Sent: 20 January 2007 20:05 > To: RB Smissaert > Subject: [sqlite] Re: Table Schema > On Saturday, January 20, 2007, 8:31:09 PM, RB > Smissaert<sqlite-users@sqlite.org> wrote: >> 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<sqlite-users@sqlite.org> 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] >> > ---------------------------------------------------------------------------- > - > As you probably suspect, I have NO IDEA of that VB syntax and sadly I > have no intention of digging into it. My question is: is there > equivalent in C API or any way of porting this to C? Or else if > someone suggests another way of easily acquiring the schema of a > table... > Thanks in advance, > Ivailo Karamanolev > ---------------------------------------------------------------------------- > - > To unsubscribe, send email to [EMAIL PROTECTED] > ---------------------------------------------------------------------------- > - > ----------------------------------------------------------------------------- > To unsubscribe, send email to [EMAIL PROTECTED] > ----------------------------------------------------------------------------- Thanks to all. That did a very good job. ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
