SQLite Query Utility

[Chris Holbrook]

I would be interested to know whether this application runs on your PC! You will need XP SP2 or better. Actually I would be fascinated to know if it does not run, or goes wrong.

You will also need sqlite3.dll from www.sqlite.org - go to the downloads page and unpack the Windows zipped dll.

It creates its own little internal database in the executable directory on the first time it runs.

[Pedro Ramirez]

I´ve downloaded and ran it. No flaws it ran OK.
But since I don´t know SQL the only thing I did was to run it, let it create
the Database and show the main window.

Windows XP in Spanish / SP3

[Chris Holbrook]

I´ve downloaded and ran it. No flaws it ran OK.

Thanks Pedro!

But since I don´t know SQL ...

http://www.w3schools.com/sql/

[Don Dickinson]

works for me. the only trouble i have is that the label above the tree view gets cut-off (running windows vista).

thanks
don

[Chris Holbrook]

Thanks Don, that picture is very helpful!

[David L Morris]

I am also using Vista and have the same results as Don. Also, I think the listview should autosize the column widths for better presentation of the query results.

[Michael Mattias]

Also, I think the listview should autosize the column widths for better presentation of the query results.

I have a similar utility I Created for use with ODBC (nowhere near as pretty). Trying to size the columns ofthe listview drove me nuts, since some of the column data can be huge,and not to metntion when you have many columns it runs off the screen.

But I'm sure if you have algorithm to use Mr. Holbrook will look at it.

[David L Morris]

I think the CSV_Edit.bas example in PBForms does this pretty well. Test it out.

    'Automatically size columns based on data.
    CONTROL HANDLE hDlg, %LVW_SYSLISTVIEW32_1 TO hList
    FOR i = 0 TO UBOUND(asFlds)
        ListView_SetColumnWidth (hList, i, %LVSCW_AUTOSIZE)
    NEXT lCol

[Chris Holbrook]

Thanks both for the advice re column sizing, I suspect that I disabled it by mistake.

LVSCW_AUTOSIZE and ~USEHEADER is OK but both can produce ugly results. The trick is to calculate a size for each column which shows enough of the column title when the column does not contain data, and enough of the data when to display it all would be nonsensical. Unfortunately I have not yet mastered that trick. When you are displaying database tables of unknown structure, column titles can be interesting, especially when the column names are long and the distinguishing part starts at the 25th character!

[Michael Mattias]

Maybe set column width = MAX (MIN?) (whatisneededto show data, somevalue) ???

Somevalue could be some percentage of control width, or maybe some percentange of screen.control width based on the number of columns? Or maybe tied to the size required to show the column names without truncation?

(I have never been any good at these kinds of algorithms). (MAX and MIN in particular cause me 'issues').

I am going to be re-doing my own ODBC version to use ADO, and as long as I am in there I may as well clean up the display, so I'm eager to look at anything.

MCM

[Chris Holbrook]

Maybe set column width =

It would have to be the minimum width to deduplicate visible column titles, maybe with right-alignment until expanded?

so

%MinColTitleLength = 8 ' for example
%MaxUnexpandedColTitleLength = 15 ' for example
' calcluate shortest length required to show at least 2 non-matching 
' characters by inspecting each column title
 
' if greater than %MaxUnexpandedColTitleLength, right-align column title text 
' if this can't be done, truncate it, precede with a "~" and expand when 
' column header is dragged wider

Pure conjecture of course.

[Michael Mattias]

I'm just throwing out thoughts.

I know finding a "good" presentation with variable number of columns with variable data length takes someone really good with both 'visual things' and algorithms.

I'm just kind of hoping someone gets an idea from one of my ramblings and has the much-coveted "Eureka!" moment.

[jcfuller]

Would multi-line headers help? I've got code somewhere that produced attached.
James

[David L Morris]

I consider these query browsers as DBA tools only, to test SQL and connection strings with the aim to use the code in subsequent purpose built programs as required. When the results included large memo fields, the subsequent program might include a command button to to run a second query to select and display the memo field in a new screen with a large textbox. Even MSAccess does not provide anything much better than the autosize feature in the existing ListView.

[Brian Chirgwin]

SDB extension creates restore point...

I found the following on the bottom of the page

http://www.sqlite.org/cvstrac/wiki?p=PerformanceTuningWindows

Reworded:

SDB extension is associated with APPFIX package.
Simply put this means every change to the SDB file will generate a Restore point.

[Chris Holbrook]

Simply put this means every change to the SDB file will generate a Restore point.

The footnote on your link suggests that this is not the case, but I will change the default to DB for the next release (if I'm spared)

[Brian Chirgwin]

The footnote on your link suggests that this is not the case, but I will change the default to DB for the next release (if I'm spared)

Yes. I missed the footnote. Makes me less concerned. Of course, I'd probably be the one to get the client that installs the software on an unpatched OS and spend hours pulling out my hair as I forget this tidbit of knowledge. I better use a different extension name to avoid the issue altogether.