Re: [sqlite] Suppressing column (field) headers?
> >Subject: Re: [sqlite] Suppressing column (field) headers? > From: [EMAIL PROTECTED] > Date: Mon, 22 Nov 2004 10:23:37 +1000 > To: [EMAIL PROTECTED] >[EMAIL PROTECTED] >21/11/2004 04:56 PM >Please respond to sqlite-users > > >To: [EMAIL PROTECTED] > cc: > Subject: [sqlite] Suppressing column (field) headers? > > > >> 1. How can I get the dll to return JUST the desired data? >>When I send the following SQL, for example, >> select colEntry from tblEntry where colNum = 3 >> the textbox shows >> colEntry >> "The text in the column." >> > >This is being done by your delphi wrapper. I beat around doing traces until I began to get that picture. The code is doing things with TStrings and TSringlists. >Please consult its documentation. I wish. Heh. >The "C" interface of sqlite (for both 2.8 series and 3.0 >series) returns the column names and column values separately, and doesn't >return any extra "quote" characters. Makes sense. >Clay Dowling wrote: >> You can get just the data that you want by using the prepared statements >> feature of the 3.0.x dll. It sounds like you're using the table output >> method for getting your results. This is fine, but it means that you >need >> it ignore the first row of data, which in the table interface is the >> column labels. > >This statement makes you sound like you're taking crazy pills, Clay >(prepared statements and non-prepared statements aren't functionally >different in sqlite)... but I'm interested to know if you have a grain of >truth hidden in there. Are you familiar with the delphi wrapper Mswarm is >using? Which delphi wrapper are you using, Mswarm? It says: "Simple class interface for SQLite. Hacked in by Ben Hochstrasser ([EMAIL PROTECTED]) Thanks to Roger Reghin ([EMAIL PROTECTED]) for his idea to ValueList." >What does the code that issues your query look like? Benjamin, so far as I can tell, the relevant code is as follows: fTable: TStrings; fLstName: TStringList; fLstVal: TStringList; SQLite_Exec: function(db: Pointer; SQLStatement: PChar; CallbackPtr: Pointer; Sender: TObject; var ErrMsg: PChar): integer; cdecl; function ExecCallback(Sender: TObject; Columns: Integer; ColumnValues: Pointer; ColumnNames: Pointer): integer; cdecl; var PVal, PName: ^PChar; n: integer; sVal, sName: String; begin Result := 0; with Sender as TSQLite do begin if (Assigned(fOnData) or Assigned(fTable)) then begin fLstName.Clear; fLstVal.Clear; if Columns > 0 then begin PName := ColumnNames; PVal := ColumnValues; for n := 0 to Columns - 1 do begin fLstName.Append(PName^); fLstVal.Append(PVal^); inc(PName); inc(PVal); end; end; sVal := fLstVal.CommaText; sName := fLstName.CommaText; if Assigned(fOnData) then fOnData(Sender, Columns, sName, sVal); if Assigned(fTable) then begin if fTable.Count = 0 then fTable.Append(sName); fTable.Append(sVal); end; end; end; end; function TSQLite.Query(Sql: String; Table: TStrings = nil): boolean; begin fError := SQLITE_ERROR; if fIsOpen then begin fPMsg := nil; fBusy := True; fTable := Table; if fTable <> nil then fTable.Clear; fError := SQLite_Exec(fSQLite, PChar(Sql), @ExecCallback, Self, fPMsg); SQLite_FreeMem(fPMsg); fChangeCount := SQLite_Changes(fSQLite); fTable := nil; fBusy := False; if Assigned(fOnQueryComplete) then fOnQueryComplete(Self); end; fMsg := ErrorMessage(fError); Result := (fError <> SQLITE_OK); end; I haven't used TStrings much, but I'm guessing that appending to TStrings creates the newlines, and that the .commatext method creates the commas in the TStringlists. I still can't figure out which code would add the "double quote" marks around returned text, though, if the DLL doesn't return a string that way. I've search the code for the " mark and the #value, but nothing turns up. Thanks
Re: [sqlite] Suppressing column (field) headers?
> >Subject: Re: [sqlite] Suppressing column (field) headers? > From: Clay Dowling <[EMAIL PROTECTED]> > Date: Sun, 21 Nov 2004 19:19:11 -0500 > To: [EMAIL PROTECTED] > >[EMAIL PROTECTED] wrote: > >>I'm using the 2.8 dll. You seem to be suggesting that I have to >>move up to the 3.0 dll to get just the data without the headers. >> >>Will I still have to strip quotes off text, and an appended newline? >> >>I'm not sure what "prepared statements" means. Googling the subject >>gets me lots of 'xxSQLxxx will do prepared statements' but little >>explanation of what that is. >> >> >Prepared statements let you write your SQL like this: SELECT name, qty, >location FROM inventory WHERE id=? >You then get to substitute any value for ? that you want. It's great >for avoiding data escaping problems, and speeds up your SQL processing. OK. As I said in my original post, I send such through a Delphi procedure. Quoting: --- 1. How can I get the dll to return JUST the desired data? When I send the following SQL, for example, select colEntry from tblEntry where colNum = 3 the textbox shows colEntry "The text in the column." What I really want is just: The text in the column. --- >The part that's relevant for you is that you get your result set back >one row of data at a time, getting one field for each function call. >What you get is the raw data without any quotes or trailing newlines or >labels, in the data type that you asked for. > >>some insight, especially the comments. I'm forced to rely on someone >>else's Delphi wrapper, not all of which I understand. >> >>Was this last comment your way of answering my "Can I drop in the >>3.0x dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect >>it to work the same?" question? >> >> >Well, it was a pretty second rate answer in response to your question. >If you look in the Delphi documentation about external libraries you'll >see that Delphi can call them directly without really needing a >wrapper. The sqlite3 dll won't be a drop in replacement, but it will >let you get the data back in a way that's likely to be more useful to >you. I tried. It didn't work right. Don't know enough yet about calling DLLs to know why. It claims the library isn't loaded. >All you need is a single pascal unit that imports the functions >defined in the DLL (or at least the functions you need). The Delphi >documentation will have examples of how to write that module. Then you >make Delphi calls to access the data in exactly the same way that you >would access it from a C program. This means that the C documentation >now also works for you writing in Delphi. I presume you mean the structure in the TSQLite code I'm using, which begins: SQLite_Open: function(dbname: PChar; mode: Integer; var ErrMsg: PChar): Pointer; cdecl; SQLite_Close: procedure(db: Pointer); cdecl; SQLite_Exec: function(db: Pointer; SQLStatement: PChar; CallbackPtr: Pointer; Sender: TObject; var ErrMsg: PChar): integer; cdecl; etc. >If you can wait a little while, I actually need to write a Delphi >interface to SQLite myself, and I can send you the Pascal unit and a >short example off list. All info a welcome part of the puzzle, I'm sure. Off list is fine, but please use the [sqlite] thingie in the header, if you can think of it, as I get 100 pieces of spam a day to this account. Thanks
Re: [sqlite] Suppressing column (field) headers?
Hello, [EMAIL PROTECTED] 21/11/2004 04:56 PM Please respond to sqlite-users To: [EMAIL PROTECTED] cc: Subject:[sqlite] Suppressing column (field) headers? > 1. How can I get the dll to return JUST the desired data? >When I send the following SQL, for example, > select colEntry from tblEntry where colNum = 3 > the textbox shows > colEntry > "The text in the column." > This is being done by your delphi wrapper. Please consult its documentation. The "C" interface of sqlite (for both 2.8 series and 3.0 series) returns the column names and column values separately, and doesn't return any extra "quote" characters. Clay Dowling wrote: > You can get just the data that you want by using the prepared statements > feature of the 3.0.x dll. It sounds like you're using the table output > method for getting your results. This is fine, but it means that you need > it ignore the first row of data, which in the table interface is the > column labels. This statement makes you sound like you're taking crazy pills, Clay (prepared statements and non-prepared statements aren't functionally different in sqlite)... but I'm interested to know if you have a grain of truth hidden in there. Are you familiar with the delphi wrapper Mswarm is using? Which delphi wrapper are you using, Mswarm? What does the code that issues your query look like? Benjamin.
Re: [sqlite] Suppressing column (field) headers?
[EMAIL PROTECTED] wrote: I'm using the 2.8 dll. You seem to be suggesting that I have to move up to the 3.0 dll to get just the data without the headers. Will I still have to strip quotes off text, and an appended newline? I'm not sure what "prepared statements" means. Googling the subject gets me lots of 'xxSQLxxx will do prepared statements' but little explanation of what that is. Prepared statements let you write your SQL like this: SELECT name, qty, location FROM inventory WHERE id=? You then get to substitute any value for ? that you want. It's great for avoiding data escaping problems, and speeds up your SQL processing. The part that's relevant for you is that you get your result set back one row of data at a time, getting one field for each function call. What you get is the raw data without any quotes or trailing newlines or labels, in the data type that you asked for. some insight, especially the comments. I'm forced to rely on someone else's Delphi wrapper, not all of which I understand. Was this last comment your way of answering my "Can I drop in the 3.0x dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect it to work the same?" question? Well, it was a pretty second rate answer in response to your question. If you look in the Delphi documentation about external libraries you'll see that Delphi can call them directly without really needing a wrapper. The sqlite3 dll won't be a drop in replacement, but it will let you get the data back in a way that's likely to be more useful to you. All you need is a single pascal unit that imports the functions defined in the DLL (or at least the functions you need). The Delphi documentation will have examples of how to write that module. Then you make Delphi calls to access the data in exactly the same way that you would access it from a C program. This means that the C documentation now also works for you writing in Delphi. If you can wait a little while, I actually need to write a Delphi interface to SQLite myself, and I can send you the Pascal unit and a short example off list. Clay Dowling
Re: [sqlite] Suppressing column (field) headers?
> >Subject: Re: [sqlite] Suppressing column (field) headers? > From: "Clay Dowling" <[EMAIL PROTECTED]> > Date: Sat, 20 Nov 2004 13:03:33 -0500 (EST) > To: [EMAIL PROTECTED] > > >[EMAIL PROTECTED] said: > >> 1. How can I get the dll to return JUST the desired data? >>When I send the following SQL, for example, > >You can get just the data that you want by using the prepared statements >feature of the 3.0.x dll. It sounds like you're using the table output >method for getting your results. This is fine, but it means that you need >it ignore the first row of data, which in the table interface is the >column labels. I'm using the 2.8 dll. You seem to be suggesting that I have to move up to the 3.0 dll to get just the data without the headers. Will I still have to strip quotes off text, and an appended newline? I'm not sure what "prepared statements" means. Googling the subject gets me lots of 'xxSQLxxx will do prepared statements' but little explanation of what that is. >Because sqlite is implemented as a C library, you can use it pretty much >directly from Delphi in exactly the way you would use the C interface. It >might be worth your while to do that, since you'd have a lot more control >over your interaction. I'm not a C person, but though going through the C code has given me some insight, especially the comments. I'm forced to rely on someone else's Delphi wrapper, not all of which I understand. Was this last comment your way of answering my "Can I drop in the 3.0x dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect it to work the same?" question?
Re: [sqlite] Suppressing column (field) headers?
[EMAIL PROTECTED] said: > 1. How can I get the dll to return JUST the desired data? >When I send the following SQL, for example, You can get just the data that you want by using the prepared statements feature of the 3.0.x dll. It sounds like you're using the table output method for getting your results. This is fine, but it means that you need it ignore the first row of data, which in the table interface is the column labels. Because sqlite is implemented as a C library, you can use it pretty much directly from Delphi in exactly the way you would use the C interface. It might be worth your while to do that, since you'd have a lot more control over your interaction. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
[sqlite] Suppressing column (field) headers?
New to SQL, SQLite, and the Delphi wrapper I'm using to talk to sqlite.dll. A couple of questions: 1. How can I get the dll to return JUST the desired data? When I send the following SQL, for example, select colEntry from tblEntry where colNum = 3 the textbox shows colEntry "The text in the column." What I really want is just: The text in the column. No header, no quotes, and no newline. Just data. I get the impression the dll is sending all this back, not that the Delphi code is adding it. If so, any way to supress, or must I strip the unneeded stuff off? 2. The Delphi setup is using the 2.something dll. Can I drop in the 3.something dll and change the 'sqlite.dll' line to 'sqlite3.dll' and expect it to work the same? If so, would there be any advantages? Thanks
