[sqlite] Question about views.

[John Newby]

Hi, I was just wondering when creating a view, if you could declare a column
as primary key on the view or add in an extra column like viewID?

The reason being is that I am working with the finisar ADO.Net wrapper which
is unable to build commands on tables/views that do not contain primary
keys.

Any help would be greatfully appreciated.

Many thanks

John

Re: [sqlite] Re: Foreign Keys

[John Newby]

Hi all, thanks for the infor on foreign keys, helps a lot.

Thanks again

John

On 10/08/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote:


* John Newby <[EMAIL PROTECTED]> [2006-08-10 00:25]:
> it recognises them if you put them in your create table
> statement but it does not enforce them, is this correct?

Yes. But note that you can retrofit enforcement yourself. Take a
look at

Enforce Foreign Key Integrity in SQLite with Triggers

http://www.justatheory.com/computers/databases/sqlite/foreign_key_triggers.html

Regards,
--
Aristotle Pagaltzis // <http://plasmasturm.org/>

[sqlite] Foreign Keys

[John Newby]

FOREIGN KEY constraints are parsed but are not enforced.

Hi, what does the above statement on the SQLite website mean?

Call me stupid but I do not understand the word "parsed", I was thinking it
meant, it recognises them if you put them in your create table statement but
it does not enforce them, is this correct?

I looked it up in a dictionary but it confused me more, this is what it said
for the word parsed :-

1. To break (a sentence) down into its component parts of speech with an
explanation of the form, function, and syntactical relationship of each
part.
2. To describe (a word) by stating its part of speech, form, and syntactical
relationships in a sentence.
3.
  a. To examine closely or subject to detailed analysis, especially by
breaking up into components: "What are we missing by parsing the behavior of
chimpanzees into the conventional categories recognized largely from our own
behavior?" (Stephen Jay Gould).
  b. To make sense of; comprehend: I simply couldn't parse what you just
said.
4. Computer Science. To analyze or separate (input, for example) into more
easily processed components.

Re: [sqlite] what tier architecture?

[John Newby]

Hi Jay,  I will be writing about the technical details within the
implementation stage of my report, but I wanted to write about the
architecture in the design part of the report.

Thanks for your advice.

John

On 07/08/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


On 8/7/06, John Newby <[EMAIL PROTECTED]> wrote:
> Hi, this is probably a stupid question and it has nothing to do with
SQlite
> per se so I apologise in advance for hijacking the emails withmy
question
> plus I don't even know if this is the right place to ask the question.
>
> I have created a GUI to SQLite using VB.Net for my University project
and it
> interfaces with the SQLite .dll file.
>
> I have to now write about the architecture of my product but I am unsure
as
> what it would be, after reading this website I am even more confused :-
>
> http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

Why is this particular guys method of dividing things up of particular
interest?
You can arbitrarily diving things up any way you like.

IMHO  if the paper is about the technical details of the implementation
I would break it up into functional groupings of interest to the reader of
the paper. Major functionality and/or which part comes from which library
and what you wrote yourself to glue it together.

As per usual, when people ask "What's the best ?"
I have to ask "For what?"
If you're trying to plow snow a Porsche isn't useful.
If you're trying to win a race a snow plow won't work too well.

Re: [sqlite] what tier architecture?

[John Newby]

Hi Michael, thanks for your help, it is much appreciated.

Thanks again

John

On 07/08/06, Michael Ruck <[EMAIL PROTECTED]> wrote:


From your description I would consider this a one-tier architecture.
Ussually the tiers are defined as follows:

- Presentation

GUI, all user interaction.

- Business Logic

Logic, which can not be expressed by constraints in the database.
Interaction with other (software) systems and some more logic.

- Data (access) layer

This one is hard IMHO. In most cases this resembles a mapper, which maps
from relational tables to objects used in the business logic. Additionally
you have a set of predefined functions, which perform queries upon the
data
sources and return the results as objects. In most cases these are only
access layers, which use a database server and stored procedures.

HTH,
Mike

-Ursprüngliche Nachricht-
Von: John Newby [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 7. August 2006 16:43
An: sqlite-users@sqlite.org
Betreff: [sqlite] what tier architecture?

Hi, this is probably a stupid question and it has nothing to do with
SQlite
per se so I apologise in advance for hijacking the emails withmy question
plus I don't even know if this is the right place to ask the question.

I have created a GUI to SQLite using VB.Net for my University project and
it
interfaces with the SQLite .dll file.

I have to now write about the architecture of my product but I am unsure
as
what it would be, after reading this website I am even more confused :-

http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

Could it be one tier as both the DBMS and GUI are as one

or could it be two tier as the GUI could be the client and the DBMS the
server

or could it be three tier as the GUI could be the client and the DBMS the
server and database?

I'm really confused, so any help or information on this subject would be
really appreciated.

Many thanks

John

[sqlite] what tier architecture?

[John Newby]

Hi, this is probably a stupid question and it has nothing to do with SQlite
per se so I apologise in advance for hijacking the emails withmy question
plus I don't even know if this is the right place to ask the question.

I have created a GUI to SQLite using VB.Net for my University project and it
interfaces with the SQLite .dll file.

I have to now write about the architecture of my product but I am unsure as
what it would be, after reading this website I am even more confused :-

http://www.javaworld.com/javaworld/jw-01-2000/jw-01-ssj-tiers.html

Could it be one tier as both the DBMS and GUI are as one

or could it be two tier as the GUI could be the client and the DBMS the
server

or could it be three tier as the GUI could be the client and the DBMS the
server and database?

I'm really confused, so any help or information on this subject would be
really appreciated.

Many thanks

John

Re: [sqlite] SQLite_master information on website

[John Newby]

Hi Gerry, thanks for this info, much appreciated.

John.

On 07/08/06, Gerry Snyder <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi, can anyone direct me to the right page relating to the sqlite_master
> table please, I am unable to find it on the website.
I do not know of a single complete description (other than the source
code).

The table is discussed near the bottom of the CREATE TABLE page in the
syntax  section.

Much information is available from the email archive and in the
searchable wiki.


HTH,

Gerry

[sqlite] SQLite_master information on website

[John Newby]

Hi, can anyone direct me to the right page relating to the sqlite_master
table please, I am unable to find it on the website.

Many thanks

John

Re: [sqlite] Re: Help on triggers

[John Newby]

Hi Igor, thanks for your help.

Is the 'WHEN' clause used very often when creating triggers or do you think
I could get away with leaving it out?

I am unsure as what to put in the expression part of the WHEN clause in
order to test it thoroughly.

Also does anyone have a test database I could use to test out my triggers
thoroughly as the one I created doesn't really lend itself to logical
updates of other tables.

Here is the one I created for testing purposes :-

CREATE TABLE owner (ownerID number(2), surname varchar2(10), forename
varchar2(10), address varchar2(20), primary key(ownerID));
CREATE TABLE vet (vetID number(3), name varchar2(10), primary key (vetID));
CREATE TABLE pettype (pettypeID number(2), pettype varchar2(10), legcount
number(2), primary key(pettypeID));
CREATE TABLE pet (petID number(2), ownerID number(2), pettypeID number(2),
petname varchar2(10), primary key (petID));
CREATE TABLE food (foodID number(2), food varchar2(20), primary
key(foodID));
CREATE TABLE diet (pettypeIDnumber(2), foodID number(2), primary key
(pettypeID,foodID));
CREATE TABLE visit (visitID number(5), petID number(2), vetID number(3),
treatment varchar2(20),cost number(4,2), primary key (visitID));
INSERT INTO owner values (1,'Abbott','Andy','1 the Avenue');
NSERT INTO owner values (2,'Abbott','Zak','2 the Ambles');
INSERT INTO owner values (3,'Bailey','Bill','3 the Boulavard');
INSERT INTO owner values (4,'Charles','Cynthia','4 the Court');
INSERT INTO owner values (5,'Duck','Howard','5 the Downs');
INSERT INTO owner values (6,'Evans','Ethel','6 the Elms');
INSERT INTO owner values(7,'Flintstone','Fred','7 the Fells');
INSERT INTO vet values (100,'Williams');
INSERT INTO vet values (200,'Xavier');
INSERT INTO vet values (300,'Young');
INSERT INTO vet values (400,'Zorro');
INSERT INTO pettype values (1,'Dog',4);
INSERT INTO pettype values (2,'Cat',4);
INSERT INTO pettype values (3,'Mouse',4);
INSERT INTO pettype values (4,'Fish',0);
INSERT INTO pettype values (5,'Ant',6);
INSERT INTO pettype values (6,'Spider',8);
INSERT INTO pettype values (7,'Starfish',5);
INSERT INTO pettype values (8,'Snake',0);
INSERT INTO pettype values (9,'Duck',2);
INSERT INTO food values (1,'dog food');
INSERT INTO food values (2,'cat food');
INSERT INTO food values (3,'biscuits');
INSERT INTO food values (4,'cheese');
INSERT INTO food values (5,'worms');
INSERT INTO food values (6,'flies');
INSERT INTO food values (7,'amoeba');
INSERT INTO food values (8,'grass');
INSERT INTO food values (9,'pond weed');
INSERT INTO food values (10,'wood');
INSERT INTO food values (11,'nectar');
INSERT INTO food values (12,'chocolate drops')
INSERT INTO diet values (1,3);
INSERT INTO diet values (1,12);
INSERT INTO diet values (2,2);
INSERT INTO diet values (2,12);
INSERT INTO diet values (3,4);
INSERT INTO diet values (4,5);
INSERT INTO diet values (4,6);
INSERT INTO diet values (4,7);
INSERT INTO diet values (4,9);
INSERT INTO diet values (5,10);
INSERT INTO diet values (5,11);
INSERT INTO diet values (6,6);
INSERT INTO diet values (7,7);
INSERT INTO diet values (8,5);
INSERT INTO diet values (8,6);
INSERT INTO diet values (9,8);
INSERT INTO diet values (9,9);
INSERT INTO diet values (1,1);
INSERT INTO pet values (1,1,1,'Fido');
INSERT INTO pet values (2,1,1,'Gnasher');
INSERT INTO pet values (3,1,1,'Wolfie');
INSERT INTO pet values (4,2,2,'Fluff');
INSERT INTO pet values (5,2,2,'Felix');
INSERT INTO pet values (6,2,1,'Poochie');
INSERT INTO pet values (7,3,4,'Nemo');
INSERT INTO pet values (8,3,4,'Wanda');
INSERT INTO pet values (9,3,4,'Fred');
INSERT INTO pet values (10,3,4,'Titus');
INSERT INTO pet values (11,3,8,'Hiss');
INSERT INTO pet values (12,3,8,'Sid');
INSERT INTO pet values (13,4,2,'Tom');
INSERT INTO pet values (14,4,2,'Garfield');
INSERT INTO pet values (15,4,3,'Jerry');
INSERT INTO pet values (16,4,3,'Mickey');
INSERT INTO pet values (17,5,6,'Incy');
INSERT INTO pet values (18,5,6,'Wincy');
INSERT INTO pet values (19,5,9,'Daffy');
INSERT INTO pet values (20,6,1,'Woof');
INSERT INTO pet values (21,6,3,'Squeek');
INSERT INTO pet values (22,6,5,'Tony');
INSERT INTO pet values (23,6,7,'Spider');
INSERT INTO pet values (24,6,9,'Jenny');
INSERT INTO pet values (25,7,2,'Jinx');
INSERT INTO visit values (1,1,100,'Broken leg',20.00);
INSERT INTO visit values (2,1,100,'Remove cast',5.50);
INSERT INTO visit values (3,2,200,'Worming',15.25);
INSERT INTO visit values (4,4,100,'Checkup',10.00);
INSERT INTO visit values (5,5,100,'checkup',10.00);
INSERT INTO visit values (6,6,200,'Worming',15.25);
INSERT INTO visit values (7,6,100,'Virus infection',14.60);
INSERT INTO visit values (8,6,300,'Eye drops',5.00);
INSERT INTO visit values (9,6,400,'Cut eye',12.40);
INSERT INTO visit values (10,7,300,'Fin rot',3.00);
INSERT INTO visit values (11,7,300,'Fin rot',3.00);
INSERT INTO visit values (12,11,200,'checkup',13.00);
INSERT INTO visit values (13,11,100,'scale infection',7.00);
INSERT INTO visit values (14,12,300,'Eye drops',5.00);
INSERT INTO visit v

[sqlite] Help on triggers

[John Newby]

Hi, I am creating a GUI to SQLite for my final year projectat University so
that the user need not know SQL in order to create a database.

I am having trouble when trying to create triggers.  The example shown on th
SQLite website only shows one example for "UPDATE OF" which seems to be the
easist of all triggers to create.

I am unsure of the following things and would like some help on the answers
please:-

do you only need the column name when "UPDATE OF" is chosen and not the
other 3?

do you need either "BEFORE" or "AFTER" only when "INSERT", "DELETE", or
"UPDATE" are chosen, but not when "UPDATE OF" is chosen?

when would the "WHEN" option be required and what is the "expression" part
of the WHEN clause?

Many thanks

John

Re: [sqlite] how to use the sqlite command-line specific commands with the dll version

[John Newby]

Hi Nuno and John, thanks for the tips, I'll look into it.

John

On 27/07/06, John Stanton <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi, I was wondering how I could use the commands specific to the
> command-line interface with the dll version.
>
> The commands I am wanting to use are as follows :-
>
> .dump
> .import
> .output
> .read
>
> Any help would be greatly appreciated
>
> Many thanks
>
> John
>
These are part of the Sqlite3.exe program.  Lift code out of that and
place it in your application.

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[John Newby]

Hi Richard, I noticed 'ADD' and 'COLUMN' aren't allowed but aren't on the
list but I haven't came accross any others at the moment.

I have taken your advice and am now putting all identifiers between double
quotes.

Many thanks for your help

John

On 26/07/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Dennis Cote <[EMAIL PROTECTED]> wrote:
> John Newby wrote:
> >
> > Is there any other names I need to look out for other than the
> > "sqlite_" and
> > "table" that SQLite doesn't like as being a table name that anyone
> > knows of?
> >
> John,
>
> All keywords need to be quoted to use them as identifiers. There is a
> comprehensive list of keywords at
http://www.sqlite.org/lang_keywords.html
>
> I would also suggest that you stick with the SQL standard method of
> quoting identifiers using double quotes (rather than the other
> extensions that SQLite accepts for compatibility with other non-standard
> database systems).
>

The keyword list has not been scrubbed lately and might contain
omissions.  Also, new keywords are added from time to time.
To be safe, it is best to quote all table and column names, or
else use a prefix on every name that is unlikely to ever be used
as a keyword.
--
D. Richard Hipp   <[EMAIL PROTECTED]>

[sqlite] how to use the sqlite command-line specific commands with the dll version

[John Newby]

Hi, I was wondering how I could use the commands specific to the
command-line interface with the dll version.

The commands I am wanting to use are as follows :-

.dump
.import
.output
.read

Any help would be greatly appreciated

Many thanks

John

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[John Newby]

Hi Dennis, thanks for this, I've just spent the last 30 mins typing in every
word I could think of that might have come up with a conflict, it was
nowhere near as big as the list in the link.

Thanks for the tips.

John

On 26/07/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


John Newby wrote:
>
> Is there any other names I need to look out for other than the
> "sqlite_" and
> "table" that SQLite doesn't like as being a table name that anyone
> knows of?
>
John,

All keywords need to be quoted to use them as identifiers. There is a
comprehensive list of keywords at http://www.sqlite.org/lang_keywords.html

I would also suggest that you stick with the SQL standard method of
quoting identifiers using double quotes (rather than the other
extensions that SQLite accepts for compatibility with other non-standard
database systems).

HTH
Dennis Cote

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[John Newby]

Hi Jonathan, yes you are right, thanks for this, I'll just put a check for
them all as you never know what a user is going to do its better to try and
fix it beforehand.

thanks again

John

On 26/07/06, Jonathan Ballet <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi Jonathan, Derrel, thanks for the info.
>
> I just wanted to know as I am creating an application that interacts
with
> the SQLite.dll and if a user was to try to create a table beginning with
> "sqlite_" the user would get the very detailed erro message, but if for
> some
> reason the user wanted to call the table "Table" they would just get a
> simple SQL syntax error and may become stuck in my application.
>
> I'll just put some check on the input from the user and if on the off
> chance
> they decide to call it table, it will change it to "Table".
>
> Is there any other names I need to look out for other than the "sqlite_"
> and
> "table" that SQLite doesn't like as being a table name that anyone knows
> of?
>
> Thanks again
>
> John
>

I think that all SQL commands or operators (create, alter, update, where,
from,
select, in, as, etc...) cannot be used.

Just check it by yourself ;)

Jonathan Ballet

Re: [sqlite] "SQL Error: near 'Table': Syntax error"

[John Newby]

Hi Jonathan, Derrel, thanks for the info.

I just wanted to know as I am creating an application that interacts with
the SQLite.dll and if a user was to try to create a table beginning with
"sqlite_" the user would get the very detailed erro message, but if for some
reason the user wanted to call the table "Table" they would just get a
simple SQL syntax error and may become stuck in my application.

I'll just put some check on the input from the user and if on the off chance
they decide to call it table, it will change it to "Table".

Is there any other names I need to look out for other than the "sqlite_" and
"table" that SQLite doesn't like as being a table name that anyone knows of?

Thanks again

John

On 26/07/06, [EMAIL PROTECTED] <
[EMAIL PROTECTED]> wrote:


"John Newby" <[EMAIL PROTECTED]> writes:

> Does anyone know any reason why SQLite doesnt like tables called "Table"
or
> is this a standard SQL thing?

It's a reserved word, so if you really, Really, REALLY want to create a
table
of that name (you're making it confusing to read, so you really
shouldn't),
you can do it using either quotes or square brackets around the table
name, as
shown here:

SQLite version 3.2.1
Enter ".help" for instructions
sqlite> create table "TABLE" (i integer);
sqlite> .schema
CREATE TABLE "TABLE" (i integer);
sqlite> .mode line
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE "TABLE" (i integer)
sqlite> drop table "table";
sqlite> create table [TABLE] (i integer);
sqlite> .schema
CREATE TABLE [TABLE] (i integer);
sqlite> select * from sqlite_master;
type = table
name = TABLE
tbl_name = TABLE
rootpage = 2
 sql = CREATE TABLE [TABLE] (i integer)
sqlite>



Derrell

[sqlite] "SQL Error: near 'Table': Syntax error"

[John Newby]

Hi, I've took the following quote from the SQLite website stating that
"Tables names that begin with "*sqlite_*" are reserved for use by the
engine"  and if you try to create a table beginning with this it lets you
know about it, but what about tables called "TABLE", this throws back an
error stating "SQL Error: near 'Table': Syntax error"

Does anyone know any reason why SQLite doesnt like tables called "Table" or
is this a standard SQL thing?

Many thanks

John

Re: [sqlite] Altering a table when field(column) names are unknown

[John Newby]

Hi Martin, Jens and Clark, thank you all for your comments and suggestions,
they all work perfectly.

What I actually meant to ask was how would I delete a field(column) when the
name of that column wasnt known using the create temp function, but I worded
my orignial question wrong.  But from all your help and suggestions I think
I will be able to work it out using the pragma table_info functions.

Thanks again to all

John

On 24/07/06, Clark Christensen <[EMAIL PROTECTED]> wrote:


I have had success using:

create temp table my_temp as select * from my_table;

Of course, if you don't know the column names, it might be a challenge
getting the data back into the new (altered) table.

If all you need is to add a column, ALTER TABLE does a good job in later
releases.  I'm on 3.2.7, and it's been very useful.

ALTER TABLE also supports renaming a table, so when I have a more
significant change to make, I've found it more useful to rename the original
table, create the new table, select data from old to new, and drop the
old.  But that requires knowing both the old and new schemas.

-Clark


- Original Message 
From: John Newby <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Monday, July 24, 2006 5:29:16 AM
Subject: [sqlite] Altering a table when field(column) names are unknown

http://www.sqlite.org/faq.html#q13

Hi, the above link goes to an FAQ on the SQLite website stating that the
ALTER TABLE command has limited functionality and recommends creating a
temp
table and copying everything there, dropping the original table,
re-creating
it with the desired changes and copying everything back from the temp
table
and then dropping that.

This is all good when the fields(columns) are known in advance, but how
would I attempt doing this without knowing the name of the fields?

Any ideas?

Many thanks

John

[sqlite] Altering a table when field(column) names are unknown

[John Newby]

http://www.sqlite.org/faq.html#q13

Hi, the above link goes to an FAQ on the SQLite website stating that the
ALTER TABLE command has limited functionality and recommends creating a temp
table and copying everything there, dropping the original table, re-creating
it with the desired changes and copying everything back from the temp table
and then dropping that.

This is all good when the fields(columns) are known in advance, but how
would I attempt doing this without knowing the name of the fields?

Any ideas?

Many thanks

John

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi all, I would like to thank you all for helping me with my problems.  I
have managed to obtain the details from the SQLite DBMS that I required.
The problems I am having now are predominantly VB.Net(and my lack of
understanding of the language) related so I will leave you all now as this
thread has taken you all off the topic of the excellent SQLite DBMS.

I'd like to thank you all again and no doubt you'll be hearing from me again
soon.

Many thanks

John

On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:


I can't see anything obviously wrong with that code, but that's probably
because it's the only bit of VB I've ever looked at. :-/

You need to divide and conquer - does the combo box get filled properly
if you hard code the values in fieldNames in selectFieldNames()? If so,
print out the value of fieldNames after the sqlite_datareader.Read()loop
or put a MessageBox.Show() in your While sqlite_datareader.Read() loop.
That should give you a good idea of where the problem is.


Martin

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Mike, I am using vb.net 2002.  I looked at the scripting.dictionary class
as suggested earlier in the thread, but I could not understand it.  I would
think a count of the rows would be needed in order to know how many times
the while loop would need to be executed to add the name of each field.

On 13/07/06, Michael Ruck <[EMAIL PROTECTED]> wrote:


If you're using VB6 your choices are VBs native Collection or the
Scripting.Dictionary class mentioned by Craig. Look them up in VBs online
help, there are examples on using them. I'd suggest get more familiar with
VB and its Container classes...

Mike

-Ursprüngliche Nachricht-
Von: John Newby [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 13. Juli 2006 18:10
An: sqlite-users@sqlite.org
Betreff: Re: [sqlite] How do you find out the names of the fields within a
table?

Hi Craig, thanks for your comments, but I can't seem to get that to work,
it
says that add and count aren't part of the dictionaryBase or
dictionaryEntry
(those being the only 2 dictionary things i could find in vb)

I think I'm nearly there with the array.

Thanks again for your help

John

On 13/07/06, Craig Morrison <[EMAIL PROTECTED]> wrote:
>
> John Newby wrote:
> > Hi Craig, I have looked into the dictionary that you suggested, but
> > wouldn't this be the same as the array as I would still need to know
> > how many
> times
> > to go through the while loop to add the names of the fields?  Also I
> > need to get the names of the fields back out to populate a comboBox,
> > which I already know how to do with an array.
>
> item = myDict("hash")
>
> Its pretty simple.
>
> Use your favorite search engine to look for "scripting.dictionary" and
> you will find a wealth of information on VBScript's dictionary object.
>
> From an earlier message of yours:
>
> >' select all field names from the selected table
> >sqlite_cmd.CommandText = (strSQL)
> >' Now the SQLiteCommand object can give us a DataReader-Object:
> >sqlite_datareader = sqlite_cmd.ExecuteReader()
> >i = 0
> >While sqlite_datareader.Read()
> >'Try
> >fieldNames(i) = sqlite_datareader("name")
> >'atch es As Exception
> >'  MessageBox.Show(es.Message)
> >End Try
> >i += 1
> >End While
> >
> >sqlite_datareader.Close()
>
> You are already getting the field names on the fly, why do you insist on
>   knowing how many fields there are?
>
> After you populate the dictionary, you can get the count of items
> (fields) by doing:
>
> itemCount = myDict.Count
>
> To attempt to stave off any more questions:
>
> set fieldNames = CreateObject("Scripting.Dictionary")
> sqlite_cmd.CommandText = (strSQL)
> sqlite_datareader = sqlite_cmd.ExecuteReader() i = 0 While
> sqlite_datareader.Read()
>  fieldNames.Add i, sqlite_datareader("name")
>  i += 1
> End While
> sqlite_datareader.Close()
> fieldCount = fieldNames.Count
>
> Your problem is *not* sqlite, but rather a command of VB itself. You
> might want to spend some time getting up to speed on VB.
>
> Or better yet, why don't you just pass a reference to the list box and
> add them directly to it?
>
> I'm not going to comment further on this topic as it is now QUITE far
> off-topic.
>
> --
> Craig Morrison
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> http://pse.2cah.com
>Controlling pseudoephedrine purchases.
>
> http://www.mtsprofessional.com/
>A Win32 email server that works for You.
>

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Fred, yes I am using vb.net.  I have searched online for vb.net dynamic
arrays, and one site I came accross states the new features of the
2.0framework allows for dynamic arrays but sadly I am using the
1.1 version.
http://www.ondotnet.com/pub/a/dotnet/2004/05/25/whidbey_vbnet_pt2.html
I am going to try and give it a go though.

Many thanks

John



On 13/07/06, Fred Williams <[EMAIL PROTECTED]> wrote:


Did not realize he was using VB when I sent my last message.  I'd bet VB
still can't do anything dynamic.  That's only one of the reasons I
walked away from VB's "Daddy" (Quick Basic) years ago.

> -Original Message-
> From: Martin Jenkins [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 13, 2006 8:08 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How do you find out the names of the
> fields within
> a table?
>
>
> John Newby wrote:
> > Yeah I can get the names, but I need to put them in an
> array, and to put
> > them in an array I need to know the size of the array to
> store them in,
> > so I
> > need to get a count first, then store this number as the
> size of the array
> > before I store the values into the array.
>
> Are you sure there no dynamic container objects in VB that support an
> "append" method? Lists?
>
> If not (and I find that hard to believe) you could hack around it by
> appending the names to a string, then parsing the string and then
> dimensioning your array, or you could build a linked list but
> ... surely
> VB has more intelligent containers than statically sized arrays?
>
> Martin

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Fred, I am using vb.net 2002.  I am not sure about dynamic arrays, I am
looking into it now

Many thanks

John

On 13/07/06, Fred Williams <[EMAIL PROTECTED]> wrote:


What language are you using?  Can't you do a dynamic array?

> -Original Message-
> From: John Newby [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 13, 2006 7:37 AM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] How do you find out the names of the
> fields within
> a table?
>
>
> Yeah I can get the names, but I need to put them in an array,
> and to put
> them in an array I need to know the size of the array to
> store them in, so I
> need to get a count first, then store this number as the size
> of the array
> before I store the values into the array.
>
> Many thanks
>
> John
>
> On 13/07/06, Christian Smith <[EMAIL PROTECTED]> wrote:
> >
> > John Newby uttered:
> >
> > > Hi, how can I find out the names of the fields within a
> given table?
> > >
> > > I've tried "pragma table_info(test);"
> > >
> > > but this brings back too much info, I just require the
> names as I'll be
> > > storing them in an array within my application.
> >
> >
> > Then just cherry pick the information you require. You get
> a result set
> > from the pragma, and the column name is the second field.
> Not difficult.
> >
> >
> > >
> > > Many thanks
> > >
> > > John
> > >
> >
> > --
> >  /"\
> >  \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
> >   X   - AGAINST MS ATTACHMENTS
> >  / \
> >
>

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Martin, I'm not sure about the wrapper, and because it is an old wrapper,
there is no help pages for it and it no longer gets updated as there is now
a version for 2.0

My deadline is the end of the month for the project, but I also have to make
time to do my dissertation.

I think I am nearly there, I can get the combo box filled with the first
field name, it just doesnt fill the rest in, as it doesnt know how many
times to go through the loop.

My code is as follows :-

Private Sub frmCreateIndex_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
   Dim i As Integer
Dim a As Integer
   Dim tableSelected As String
   tableSelected = cmbTableIndex.SelectedItem
   Try
   'dbConn.selectFieldNames("pragma table_info(" & tableSelected &
")")
   dbConn.selectFieldNames("pragma table_info(test)")
   For a = 0 To UBound(dbConn.fieldNames)
   cmbFieldIndex.Items.Add(dbConn.fieldNames(a))
   Next a
   Catch es As Exception
   MessageBox.Show(es.Message)
End Try
End Sub

Public Function selectFieldNames(ByVal strSQL As String) As Array
   Dim fields As Integer
   ' select all total of all tables from the sqlite master table
   sqlite_cmd.CommandText = (strSQL)
   fields = sqlite_cmd.ExecuteScalar()

   ReDim fieldNames(fields)
   ' select all field names from the selected table
   sqlite_cmd.CommandText = (strSQL)
   ' Now the SQLiteCommand object can give us a DataReader-Object:
   sqlite_datareader = sqlite_cmd.ExecuteReader()
   i = 0
   While sqlite_datareader.Read()
   Try
   fieldNames(i) = sqlite_datareader("name")
   Catch es As Exception
   MessageBox.Show(es.Message)
   End Try
   i += 1
   End While
   sqlite_datareader.Close()
  Return fieldNames
   End Function

On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> I would still need to know how many times to go through the while
> loop to add the names of the fields?

John,

I haven't been following this thread that closely, but (based on other
wrappers I've used) I would have expected your sqlite wrapper to do one
of two things:

a) let you request all results returned in an array or list
b) let you request results one at a time and raise an error or exception
when you've run off the end.

In a) you don't need to know the number of results or allocate an array
to hold them - the wrapper does it for you. If you use the right query
you get what you want with no work.

In b) you start with an empty list or array and loop through the result
set appending each result to the end of the list. Dictionaries solve the
know-the-size-in-advance problem but aren't really appropriate as you
lose the order of the fields and you have to extract the keys/names (and
store them...?) before you can use them.

You only need a while loop for b) because if you know the number of
items in advance a for-loop would be more appropriate. a) is the better
solution here because it's less work on your part.

It's a pity you're stuck with VB - what you're trying to do is a one
liner in Python with either of apsw or pysqlite and I imagine most other
languages and wrappers are similar. :-/

JOOI, when is your deadline?

Martin

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Craig, thanks for your comments, but I can't seem to get that to work, it
says that add and count aren't part of the dictionaryBase or dictionaryEntry
(those being the only 2 dictionary things i could find in vb)

I think I'm nearly there with the array.

Thanks again for your help

John

On 13/07/06, Craig Morrison <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi Craig, I have looked into the dictionary that you suggested, but
> wouldn't
> this be the same as the array as I would still need to know how many
times
> to go through the while loop to add the names of the fields?  Also I
> need to
> get the names of the fields back out to populate a comboBox, which I
> already
> know how to do with an array.

item = myDict("hash")

Its pretty simple.

Use your favorite search engine to look for "scripting.dictionary" and
you will find a wealth of information on VBScript's dictionary object.

From an earlier message of yours:

>' select all field names from the selected table
>sqlite_cmd.CommandText = (strSQL)
>' Now the SQLiteCommand object can give us a DataReader-Object:
>sqlite_datareader = sqlite_cmd.ExecuteReader()
>i = 0
>While sqlite_datareader.Read()
>'Try
>fieldNames(i) = sqlite_datareader("name")
>'atch es As Exception
>'  MessageBox.Show(es.Message)
>End Try
>i += 1
>End While
>
>sqlite_datareader.Close()

You are already getting the field names on the fly, why do you insist on
  knowing how many fields there are?

After you populate the dictionary, you can get the count of items
(fields) by doing:

itemCount = myDict.Count

To attempt to stave off any more questions:

set fieldNames = CreateObject("Scripting.Dictionary")
sqlite_cmd.CommandText = (strSQL)
sqlite_datareader = sqlite_cmd.ExecuteReader()
i = 0
While sqlite_datareader.Read()
 fieldNames.Add i, sqlite_datareader("name")
 i += 1
End While
sqlite_datareader.Close()
fieldCount = fieldNames.Count

Your problem is *not* sqlite, but rather a command of VB itself. You
might want to spend some time getting up to speed on VB.

Or better yet, why don't you just pass a reference to the list box and
add them directly to it?

I'm not going to comment further on this topic as it is now QUITE far
off-topic.

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
   Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
   A Win32 email server that works for You.

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Christian,

Nope VB is the best they can come up with, and it's only version 2002, they
can't even get the latest edition so I have to work with an outdated wrapper
aswell.

I was reading your reply regarding the Max(id) thread, I was wondering if I
could use this for my query.

The pragma table_info(test) command returns 6 columns, the second being the
name column which is what I want.

The first being cid column which numbers the fields, if I could somehow
select the max from the cid column and then add 1 that wuold give me the
total fields in my table and I could use this in my array.

Do you know of a way I can do a select on the information brought back with
the pragma command?

Many thanks

John

On 13/07/06, Christian Smith <[EMAIL PROTECTED]> wrote:


John Newby uttered:

> Hi Martin, I'm not sure, I don't use VB that often, I just need to use
it
> for my Uni project at the moment.


With all due respect to your University, but VB sucks as a teaching
language IMO. Doesn't your Uni have better development tools? Any
professor that advocates VB is not worthy of the title.


>
> Many thanks
>
> John.
>
> On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:
>>
>> John Newby wrote:
>> > Yeah I can get the names, but I need to put them in an array, and to
put
>> > them in an array I need to know the size of the array to store them
in,
>> > so I
>> > need to get a count first, then store this number as the size of the
>> array
>> > before I store the values into the array.
>>
>> Are you sure there no dynamic container objects in VB that support an
>> "append" method? Lists?
>>
>> If not (and I find that hard to believe) you could hack around it by
>> appending the names to a string, then parsing the string and then
>> dimensioning your array, or you could build a linked list but ...
surely
>> VB has more intelligent containers than statically sized arrays?
>>
>> Martin
>>
>

--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Nuno, I tried the select statement you recommended, it does not return
any value!!

No I dont think my wrapper returns the column names, it is too late to
change wrappers now, I am too close to my deadline to start again.

Many thanks

John

On 13/07/06, Nuno Lucas <[EMAIL PROTECTED]> wrote:


On 7/13/06, John Newby <[EMAIL PROTECTED]> wrote:
> Hi Nuno, I'm using VB.Net 2002 so I don't think I can use these
commands.

And what about doing a "SELECT * FROM your_table_name LIMIT 0" ?

Does your sqlite wrapper returns the column names?

If it doesn't, maybe you should think in using another sqlite wrapper
that allows it...

Regards,
~Nuno Lucas

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Craig, I have looked into the dictionary that you suggested, but wouldn't
this be the same as the array as I would still need to know how many times
to go through the while loop to add the names of the fields?  Also I need to
get the names of the fields back out to populate a comboBox, which I already
know how to do with an array.

Many thanks

John

On 13/07/06, Craig Morrison <[EMAIL PROTECTED]> wrote:


Martin Jenkins wrote:
> John Newby wrote:
>> Yeah I can get the names, but I need to put them in an array, and to
put
>> them in an array I need to know the size of the array to store them
>> in, so I
>> need to get a count first, then store this number as the size of the
>> array
>> before I store the values into the array.
>
> Are you sure there no dynamic container objects in VB that support an
> "append" method? Lists?
>
> If not (and I find that hard to believe) you could hack around it by
> appending the names to a string, then parsing the string and then
> dimensioning your array, or you could build a linked list but ... surely
> VB has more intelligent containers than statically sized arrays?
>
> Martin
>

Yup, they are called dictionaries..

set myDict = CreateObject("Scripting.Dictionary")

myDict.Add "Hash", "Data"


I'll leave the rest up to the OP as this is straying from the topic at
hand..

--
Craig Morrison
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
http://pse.2cah.com
   Controlling pseudoephedrine purchases.

http://www.mtsprofessional.com/
   A Win32 email server that works for You.

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Martin, I'm not sure, I don't use VB that often, I just need to use it
for my Uni project at the moment.  I just know this was worked when I need
to create an array of all tables in the database, I just did a select count
statement on the master table and added that number to the size of the array
then filled the array using a while loop till i reached the max array size,
so i thought i could amend it slightly for the field names, but without the
count of fields the array wont fill as the while loop doesnt end.

Many thanks

John.

On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Yeah I can get the names, but I need to put them in an array, and to put
> them in an array I need to know the size of the array to store them in,
> so I
> need to get a count first, then store this number as the size of the
array
> before I store the values into the array.

Are you sure there no dynamic container objects in VB that support an
"append" method? Lists?

If not (and I find that hard to believe) you could hack around it by
appending the names to a string, then parsing the string and then
dimensioning your array, or you could build a linked list but ... surely
VB has more intelligent containers than statically sized arrays?

Martin

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Yeah I can get the names, but I need to put them in an array, and to put
them in an array I need to know the size of the array to store them in, so I
need to get a count first, then store this number as the size of the array
before I store the values into the array.

Many thanks

John

On 13/07/06, Christian Smith <[EMAIL PROTECTED]> wrote:


John Newby uttered:

> Hi, how can I find out the names of the fields within a given table?
>
> I've tried "pragma table_info(test);"
>
> but this brings back too much info, I just require the names as I'll be
> storing them in an array within my application.


Then just cherry pick the information you require. You get a result set
from the pragma, and the column name is the second field. Not difficult.


>
> Many thanks
>
> John
>

--
 /"\
 \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
  X   - AGAINST MS ATTACHMENTS
 / \

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Nuno, I'm using VB.Net 2002 so I don't think I can use these commands.

Many thanks

John

On 12/07/06, Nuno Lucas <[EMAIL PROTECTED]> wrote:


On 7/12/06, John Newby <[EMAIL PROTECTED]> wrote:
> Hi, how can I find out the names of the fields within a given table?
>
> I've tried "pragma table_info(test);"
>
> but this brings back too much info, I just require the names as I'll be
> storing them in an array within my application.

When you use pre-compiled SQL statements you get that info for free,
that is, whitout actually needing to run the query. Look the
sqlite3_column_name function:

* http://sqlite.org/capi3ref.html#sqlite3_column_name

Regards,
~Nuno Lucas


>
> Many thanks
>
> John

Re: [sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi Kees, I'm writing it in VB.Net 2002.

I think I've got somethign nearly working now, but I need to somehow get a
count of how many fields is in the table, or how many rows the pragma
command brings back so that I can give the array a number before I can fill
it.  Does anyone know the name of the first column returned in the pragma
table_info(test) command, it looks as if its counting the rows(fields),
maybe I could use this somehow.

The code Ive got so far is as follows :

Public Function selectFieldNames(ByVal strSQL As String) As Array

   ' select all field names from the selected table
   sqlite_cmd.CommandText = (strSQL)
   ' Now the SQLiteCommand object can give us a DataReader-Object:
   sqlite_datareader = sqlite_cmd.ExecuteReader()
   i = 0
   While sqlite_datareader.Read()
   'Try
   fieldNames(i) = sqlite_datareader("name")
   'atch es As Exception
   '  MessageBox.Show(es.Message)
   End Try
   i += 1
   End While

   sqlite_datareader.Close()

   Return fieldNames
   End Function

On 12/07/06, Kees Nuyt <[EMAIL PROTECTED]> wrote:


On Wed, 12 Jul 2006 19:05:51 +0100, you wrote:

> Hi, how can I find out the names of the
> fields within a given table?
>
> I've tried "pragma table_info(test);"
> but this brings back too much info,
> I just require the names as I'll be
> storing them in an array within my application.

Your application could select the 'name' column in your result
set.
In what language is your application written?
PHP for example has a nice wrapper to achieve what you want:

sqlite_array_query()

Here is some sample PHP code (wrapped by mailclient):

if ($dbhandle = sqlite_open($dbfilename, 0666,
$sqliteerror)){
// query
$result = sqlite_array_query($dbhandle, 'SELECT * FROM
tablename', SQLITE_ASSOC);
// display
printf('');
$firsttime = TRUE;
foreach ($aResult as $aRow){
if ($firsttime){
printf('');
foreach ($aRow as $col => $val){
printf('%s',$col);
}
reset($aRow);
printf("\n");
$firsttime = FALSE;
}
printf('');
foreach ($aRow as $col => $val){
printf('%s',$val);
}
printf("\n");
}
printf('');
sqlite_close($dbhandle);
} else {
die($sqliteerror);
}


>Many thanks
>
>John

Hope this helps.
--
kees

[sqlite] How do you find out the names of the fields within a table?

[John Newby]

Hi, how can I find out the names of the fields within a given table?

I've tried "pragma table_info(test);"

but this brings back too much info, I just require the names as I'll be
storing them in an array within my application.

Many thanks

John

Re: [sqlite] create table command from listview in vb.net

[John Newby]

Hi Dennis,

Yeah I know, I have posted several VB forums regarding this matter, I was
just hoping there might have been an SQLite user that may have came accross
this in the past.  Sorry to have bothered you all.

Thanks again

John.

On 11/07/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


John Newby wrote:
>
> Do you know of a way I could get the details from the listview?
>
John,

This question is far more likely to be answered on a Visual Basic
mailing list rather than this one.

HTH
Dennis Cote

Re: [sqlite] create table command from listview in vb.net

[John Newby]

I don't normally but this is for a University project and I have to
demonstrate it at University so I have to use the software that is available
on the network.

Thanks for your comments though.

John

On 11/07/06, C.Peachment <[EMAIL PROTECTED]> wrote:


Sorry, but I don't use proprietary programming languages
and can not provide assistance with Visual Basic.

On Tue, 11 Jul 2006 12:00:34 +0100, John Newby wrote:

>Hi, thanks for your speedy reply,

>Yes, you have understood my code correctly, this is the create table
>statement, and at the time of execution all column names are known to the
>form, I am just unsure as how to get them from the listview to place into
a
>create table statement, I think I will need some sort of FOR loop to
bring
>back each line that is in the listview but I am unsure as how to do this.
>This is my first time working with listviews and I've searched the
internet
>and have 7 books from the library on vb.net but none explain how to get
>details from a listview, only how to populate it.

>I think I worded my original message wrong.

>Do you know of a way I could get the details from the listview?

>Many thanks for your help

>John

>On 11/07/06, C.Peachment <[EMAIL PROTECTED]> wrote:
>>
>> If  understand your code correctly, this line creates a table with a
>> single column.
>>
>> dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName &
" "
>> &
>> fldAttribute & ")")
>>
>> You describe a user interface to be used to create tables with multiple
>> columns.
>>
>> You must do one of two things:
>>
>> 1. delay the create table statement execution until all columns
>> are
>> known and can be included inside the parentheses, or
>>
>> 2. perform the create for the first column and then use the
alter
>> table
>> statement to append remaining columns, perhaps one at a time.
>>
>> ---
>>
>> On Tue, 11 Jul 2006 10:57:36 +0100, John Newby wrote:
>>
>> >Hi, I am creating a front-end to the sqlite DBMS using VB.Net 2002. I
>> have
>> >managed to get the name of the table, field names and types from user
>> input
>> >displayed into a listview but I can only get the Create table command
to
>> >accept the last input values, so if the table has more than one
>> field(which
>> >nealry every table has) it simply ignores the previously entered
fields.
>>
>> >How would I get it to create an SQL query with all the inputted field
>> >details?
>>
>> >I have inserted the code I have used so far.
>>
>> >Many thanks for your help.
>>
>> >This button opens up a new form for the user to input the field name
and
>> >select the field type then brings them back and displays them in a
>> listview.
>>
>>
>> >Public Sub btnAddColumn_Click(ByVal sender As System.Object, ByVal e
As
>> >System.EventArgs) Handles btnAddColumn.Click
>> >Dim frmAddColumns1 As New frmAddColumns()
>> >frmAddColumns1.ShowDialog(Me)
>> >fldName = frmAddColumns.ColumnNameTB.Text
>> >fldAttribute = frmAddColumns.ColumnTypeTB.SelectedItem
>> >'Create ListViewItem
>> >Dim item1 As New ListViewItem(fldName, 0)
>> >item1.SubItems.Add(fldAttribute)
>> >'Add the items to the ListView.
>> >listView1.Items.AddRange(New ListViewItem() {item1})
>> >Me.Controls.Add(listView1)
>> >End Sub
>>
>> >This button takes the input table name, field name and attributes and
>> >creates the SQL command to send to the database to create the table:
>>
>>
>> >Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal
e
>> As
>> >System.EventArgs) Handles btnCreateTable.Click
>> >Dim tblName As String
>> >tblName = txtTableName.Text.ToString()
>> >If Len(txtTableName.Text) < 1 Then
>> >MessageBox.Show("Please type a name for the table")
>> >ElseIf Len(txtTableName.Text) > 0 Then
>> >Try
>> >dbConn.openExistingDatabse("Data Source=" & getDBName() &
>> >";Version=3;New=False;Compress=True;")
>> >dbConn.createSQLCommand()
>> >dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
>> >fldAttribute & ")")
>> >MessageBox.Show("Table created successfully")
>> >Me.Close()
>> >Dim frmInsertData1 As frmInsertData = New frmInsertData()
>> >frmInsertData1.Show()
>> >Catch es As Exception
>> >MessageBox.Show(es.Message)
>> >End Try
>> >End If
>> >End Sub
>>
>>
>>
>>
>>

Re: [sqlite] create table command from listview in vb.net

[John Newby]

Hi, thanks for your speedy reply,

Yes, you have understood my code correctly, this is the create table
statement, and at the time of execution all column names are known to the
form, I am just unsure as how to get them from the listview to place into a
create table statement, I think I will need some sort of FOR loop to bring
back each line that is in the listview but I am unsure as how to do this.
This is my first time working with listviews and I've searched the internet
and have 7 books from the library on vb.net but none explain how to get
details from a listview, only how to populate it.

I think I worded my original message wrong.

Do you know of a way I could get the details from the listview?

Many thanks for your help

John

On 11/07/06, C.Peachment <[EMAIL PROTECTED]> wrote:


If  understand your code correctly, this line creates a table with a
single column.

dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " "
&
fldAttribute & ")")

You describe a user interface to be used to create tables with multiple
columns.

You must do one of two things:

1. delay the create table statement execution until all columns
are
known and can be included inside the parentheses, or

2. perform the create for the first column and then use the alter
table
statement to append remaining columns, perhaps one at a time.

---

On Tue, 11 Jul 2006 10:57:36 +0100, John Newby wrote:

>Hi, I am creating a front-end to the sqlite DBMS using VB.Net 2002. I
have
>managed to get the name of the table, field names and types from user
input
>displayed into a listview but I can only get the Create table command to
>accept the last input values, so if the table has more than one
field(which
>nealry every table has) it simply ignores the previously entered fields.

>How would I get it to create an SQL query with all the inputted field
>details?

>I have inserted the code I have used so far.

>Many thanks for your help.

>This button opens up a new form for the user to input the field name and
>select the field type then brings them back and displays them in a
listview.


>Public Sub btnAddColumn_Click(ByVal sender As System.Object, ByVal e As
>System.EventArgs) Handles btnAddColumn.Click
>Dim frmAddColumns1 As New frmAddColumns()
>frmAddColumns1.ShowDialog(Me)
>fldName = frmAddColumns.ColumnNameTB.Text
>fldAttribute = frmAddColumns.ColumnTypeTB.SelectedItem
>'Create ListViewItem
>Dim item1 As New ListViewItem(fldName, 0)
>item1.SubItems.Add(fldAttribute)
>'Add the items to the ListView.
>listView1.Items.AddRange(New ListViewItem() {item1})
>Me.Controls.Add(listView1)
>End Sub

>This button takes the input table name, field name and attributes and
>creates the SQL command to send to the database to create the table:


>Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e
As
>System.EventArgs) Handles btnCreateTable.Click
>Dim tblName As String
>tblName = txtTableName.Text.ToString()
>If Len(txtTableName.Text) < 1 Then
>MessageBox.Show("Please type a name for the table")
>ElseIf Len(txtTableName.Text) > 0 Then
>Try
>dbConn.openExistingDatabse("Data Source=" & getDBName() &
>";Version=3;New=False;Compress=True;")
>dbConn.createSQLCommand()
>dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
>fldAttribute & ")")
>MessageBox.Show("Table created successfully")
>Me.Close()
>Dim frmInsertData1 As frmInsertData = New frmInsertData()
>frmInsertData1.Show()
>Catch es As Exception
>MessageBox.Show(es.Message)
>End Try
>End If
>End Sub

[sqlite] create table command from listview in vb.net

[John Newby]

Hi, I am creating a front-end to the sqlite DBMS using VB.Net 2002. I have
managed to get the name of the table, field names and types from user input
displayed into a listview but I can only get the Create table command to
accept the last input values, so if the table has more than one field(which
nealry every table has) it simply ignores the previously entered fields.

How would I get it to create an SQL query with all the inputted field
details?

I have inserted the code I have used so far.

Many thanks for your help.

This button opens up a new form for the user to input the field name and
select the field type then brings them back and displays them in a listview.


Public Sub btnAddColumn_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnAddColumn.Click
Dim frmAddColumns1 As New frmAddColumns()
frmAddColumns1.ShowDialog(Me)
fldName = frmAddColumns.ColumnNameTB.Text
fldAttribute = frmAddColumns.ColumnTypeTB.SelectedItem
'Create ListViewItem
Dim item1 As New ListViewItem(fldName, 0)
item1.SubItems.Add(fldAttribute)
'Add the items to the ListView.
listView1.Items.AddRange(New ListViewItem() {item1})
Me.Controls.Add(listView1)
End Sub

This button takes the input table name, field name and attributes and
creates the SQL command to send to the database to create the table:


Private Sub btnCreateTable_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnCreateTable.Click
Dim tblName As String
tblName = txtTableName.Text.ToString()
If Len(txtTableName.Text) < 1 Then
MessageBox.Show("Please type a name for the table")
ElseIf Len(txtTableName.Text) > 0 Then
Try
dbConn.openExistingDatabse("Data Source=" & getDBName() &
";Version=3;New=False;Compress=True;")
dbConn.createSQLCommand()
dbConn.createTable("CREATE TABLE " & tblName & "(" & fldName & " " &
fldAttribute & ")")
MessageBox.Show("Table created successfully")
Me.Close()
Dim frmInsertData1 As frmInsertData = New frmInsertData()
frmInsertData1.Show()
Catch es As Exception
MessageBox.Show(es.Message)
End Try
End If
End Sub

Re: [sqlite] ALTER table command

[John Newby]

http://www.sqlite.org/faq.html#q13

Hi, SQLite FAQ recommends creating temp tables and copying the data from the
original table into it then deleting the old table then recreating the old
table (with the desired new column) then copying the data back and deleting
the temp table.

On 19/06/06, Anish Enos Mathew <[EMAIL PROTECTED]> wrote:



Hi all,
Any body knows  how to use ADD [COLUMN] in alter table command? I want
to add a new field to my table. Can "modify " be used with alter command
as in SQL?



The information contained in, or attached to, this e-mail, contains
confidential information and is intended solely for the use of the
individual or entity to whom they are addressed and is subject to legal
privilege. If you have received this e-mail in error you should notify the
sender immediately by reply e-mail, delete the message from your system and
notify your system manager. Please do not copy it for any purpose, or
disclose its contents to any other person. The views or opinions presented
in this e-mail are solely those of the author and do not necessarily
represent those of the company. The recipient should check this e-mail and
any attachments for the presence of viruses. The company accepts no
liability for any damage caused, directly or indirectly, by any virus
transmitted in this email.

www.aztecsoft.com

Re: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider

[John Newby]

Yes please, I am using vb.net but I should be able to work through and see
whats going on and transfer it to my project.

Many thanks for your help.

John

On 15/06/06, Noel Frankinet <[EMAIL PROTECTED]> wrote:


John Newby wrote:

> Hi, I don't know if you can remember but I was asking for advice on
> displaying info to the user using a dataset/datagrid, and you said you
> used
> a virtual listview.  I am currently having loads of trouble using the
> dataset/datagrid so I was wondering if you could let me know more
> about your
> virtual listview please.
>
> Also would the virtual listview allow for
> amendments/deletion/insertion of
> data and recognise the change and update the database accordingly.
>
> Many thanks
>
> John
>
> On 10/05/06, Noel Frankinet <[EMAIL PROTECTED]> wrote:
>
>>
>> John Newby wrote:
>>
>> >Hi, I am trying to access a SQLite database using the ADO.Net provider
>> and
>> >bring all the data back in a tabular form, I can bring back the data
>> using a
>> >datareader but I don't know if I can put the data into a datset or a
>> >datagrid.
>> >
>> >Does anyone know if you can do this?
>> >
>> >Many thanks.
>> >
>> >John.
>> >
>> >
>> >
>>
>
>>
>> >
>> >No virus found in this incoming message.
>> >Checked by AVG Free Edition.
>> >Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date:
>> 9/05/2006
>> >
>> >
>> Hello,
>> I use a virtual listview directly on top of sqlite3, its in c++ but it
>> works very well (even on PDA). It could easily be turned in an activeX
>> (its ATL/WTL, no MFC).
>>
>> Best wishes.
>>
>>
>> --
>> Noël Frankinet
>> Gistek Software SA
>> http://www.gistek.net
>>
>>
>
>
>
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.394 / Virus Database: 268.8.4/364 - Release Date: 14/06/2006
>
>
Hello,
My virtual list is WTL C++ code, it runs on top of a Cdataview class
that does all the work.
It works on PC and PDA, and yes, beiing virtual, it reflects insert and
deletes quite well (a trigger does the Window Updating).
I can send you that code (3 .h files) but it won't be free of dependency
from other part of my project.
I hope to one day make it into a separated project but I don't have the
time right now,
Are you interested ?

Best wishes

--
Noël Frankinet
Gistek Software SA
http://www.gistek.net

Re: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider

[John Newby]

Hi, I don't know if you can remember but I was asking for advice on
displaying info to the user using a dataset/datagrid, and you said you used
a virtual listview.  I am currently having loads of trouble using the
dataset/datagrid so I was wondering if you could let me know more about your
virtual listview please.

Also would the virtual listview allow for amendments/deletion/insertion of
data and recognise the change and update the database accordingly.

Many thanks

John

On 10/05/06, Noel Frankinet <[EMAIL PROTECTED]> wrote:


John Newby wrote:

>Hi, I am trying to access a SQLite database using the ADO.Net provider
and
>bring all the data back in a tabular form, I can bring back the data
using a
>datareader but I don't know if I can put the data into a datset or a
>datagrid.
>
>Does anyone know if you can do this?
>
>Many thanks.
>
>John.
>
>
>
>
>
>No virus found in this incoming message.
>Checked by AVG Free Edition.
>Version: 7.1.392 / Virus Database: 268.5.5/335 - Release Date: 9/05/2006
>
>
Hello,
I use a virtual listview directly on top of sqlite3, its in c++ but it
works very well (even on PDA). It could easily be turned in an activeX
(its ATL/WTL, no MFC).

Best wishes.


--
Noël Frankinet
Gistek Software SA
http://www.gistek.net

Re: [sqlite] Re: reasonable assumptions

[John Newby]

no

On 04/06/06, Dave Dyer <[EMAIL PROTECTED]> wrote:



>
>I think this is a very reasonable assumption.

It's a lot easier to drive if you assume you're the only
car on the road.

Re: [sqlite] [Video] An Introduction to SQLite

[John Newby]

works fine for me, obviously as it says, it is not yet available in your
country, I live in the UK and it works here.

On 04/06/06, imcs ee <[EMAIL PROTECTED]> wrote:


the url returns
"

Currently, the playback feature of Google Video isn't available in your
country.

We hope to make this feature available more widely in the future, and we
really appreciate your patience.
"

On 6/4/06, A. Pagaltzis <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> for those who haven't noticed, a video of a 45-min talk by
> Dr. Hipp about SQLite that he gave at Google has been posted on
> Google Video:
>
> http://video.google.com/videoplay?docid=-5160435487953918649
>
> Not much nitty-gritty, but a nice 20,000 ft view; there isn't
> much news to me in it, but there are interesting cues and bits
> in there. I like it.
>
> [Apologies if this has already been posted about in a more
> appropriate venue; I didn't see it on the list or the homepage.]
>
> Regards,
> --
> Aristotle Pagaltzis // 
>

Re: [sqlite] unsuscribe

[John Newby]

yeah didnt even notice that, glad i got it right, thank god for the spell
checker!! lol.

On 31/05/06, Bob Dankert <[EMAIL PROTECTED]> wrote:


Especially considering they are all spelling the word wrong with the
same mis-spelling.

-Original Message-
From: John Newby [mailto:[EMAIL PROTECTED]
Sent: Wednesday, May 31, 2006 1:25 PM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] unsuscribe

whats with all these unsubscribe messages, they're beginning to do my
head
in now!!!

On 31/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:
>
> unsuscribe
>
>
>
>
>
>

Re: [sqlite] unsuscribe

[John Newby]

whats with all these unsubscribe messages, they're beginning to do my head
in now!!!

On 31/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]>
wrote:


unsuscribe

[sqlite] Help creatign a database using Finisar SQLite ADO.Net provider and VB.Net

[John Newby]

Hi, I am trying to open a database, using the input database name given by
the user, if I run the testDB() function where the source of the database is
given, it works fine, but if I try to use the createNewdatabase() function,
passing back the string with the filename from the input and it doesn't
work, I was wondering if anyone could help me as to where I'm going wrong, I
have attached the code I am using below.

Many thanks.

John

This works fine
Public Function testDB()
   ' create a new database
   sqlite_conn = New SQLiteConnection("Data
Source=D:\test.db;Version=3;New=True;Compress=True;")
   End Function

This doesn't
   Public Function createNewdatabase(ByVal strSQL As String)
   ' create a new database
   sqlite_conn = New SQLiteConnection(strSQL)
   End Function


   Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnNew.Click
   Dim dbConn As dbConnection = New dbConnection()
   Dim FlName As String
   With SaveFileDialog1
   '.DefaultExt = ".db"
   .Filter = "SQLite Database Files|*.db|All Files|*.*"
   If .ShowDialog() = DialogResult.OK Then
   .FileName = FileName
   FlName = FileName
   ' create a new database with given name
   dbConn.createNewdatabase("Data Source=" & FlName &
";Version=3;New=True;Compress=True;")
   End If
   End With
   End Sub

Re: [sqlite] Can't access sqlite_master from VB6 via ODBC

[John Newby]

Hi Robin, have you looked here, there are many wrappers for SQLite
http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers
this one in particular mentions "any ActiveX language such Visual Basic"
http://vfornazin.ipdz.com/

hope this is of help to you

john



On 22/05/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:


Hi

I thought it wasn't permissions. The VB interface I'm using is ActiveX
Data
Objects, which works through ODBC and the SQLite ODBC driver. Might there
be some problem with this driver?

Robin

Original Message:
-
From: John Stanton [EMAIL PROTECTED]
Date: Mon, 22 May 2006 09:07:17 +1000
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Can't access sqlite_master from VB6 via ODBC


It is not permissions, more like a VB interface problem.

Robin Wilson wrote:
> Hi all,
>
> I've just started looking at SQLite - and think it is really impressive.
> It would be just the thing to use with my students for teaching them
> about RDBMS's (especially with some of the nice Windows UIs which are
> available for the students who can't cope with command line tools!).
>
> I have had a few problems though. At the moment I am accessing SQLite
> through ODBC from Visual Basic 6. This means the students can carry on
> using the ADO commands that they are familier with (from working with
> Access DBs). However, when working from VB the SQL query "SELECT * FROM
> sqlite_master;" does not return any records, but when I run that from
> the sqlite3 command line program with the same db file it returns 1 row.
>
> Is this some kind of permissions problem? If this happened elsewhere I
> would assume it was, but I remember reading on your website that
> permissions aren't implemented in SQLite.
>
> Does anyone have any ideas?
>
> Cheers,
>
> Robin
>
> P.S. I hope this is the right list, and that it is ok to just butt in
> etc...



mail2web - Check your email from the web at
http://mail2web.com/ .

Re: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider

[John Newby]

Hi, I have managed to display the data into the datagrid using a dataset,
many thanks for all your help.

John.

On 10/05/06, John Newby <[EMAIL PROTECTED]> wrote:
>
> Hi Michael, I am already using the Finisar.SQLite wrapper, I can
> successfully connect to the database, create a table, insert data and
> display it to the console or in a message box, but I am unsure as how to
> display it into a grid (like ms access)
>
>
>
>
> On 10/05/06, Michael B. Hansen <[EMAIL PROTECTED]> wrote:
> >
> > Hi John,
> >
> > There is a number of .NET-wrappers for SQLite
> > (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers).
> >
> > I personally use Finisar.SQLite
> > (http://adodotnetsqlite.sourceforge.net/) which performs acceptable
> > (although I currently have one single issue with it - look for the
> > subject "Transactions and 'library routine called out of sequence' locks
> >
> > the database" in this list).
> >
> >
> > /Michael
> >
> > -Original Message-
> > From: John Newby [mailto:[EMAIL PROTECTED]
> > Sent: 9. maj 2006 17:18
> > To: sqlite-users@sqlite.org
> > Subject: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider
> >
> > Hi, I am trying to access a SQLite database using the ADO.Net provider
> > and bring all the data back in a tabular form, I can bring back the data
> >
> > using a datareader but I don't know if I can put the data into a datset
> > or a datagrid.
> >
> > Does anyone know if you can do this?
> >
> > Many thanks.
> >
> > John.
> >
>
>

[sqlite] Creating a database

[John Newby]

Hi, I know how to create a databse from a DOS prompt by going to the
directory the .exe file is and by typing sqlite3 name.db which then creates
the database, but what I do not know is how to create a database from my
application I am building using VB.Net using the .dll file, I can
successfully connect to and create tables and insert data, but as soon as I
close the connection all the information is lost.

The sqlite3 name.db command does not work as I am not accessing the .exe
file.

Does anyone out there have any ideas?

Many thanks.

John.

Re: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider

[John Newby]

Hi Michael, I am already using the Finisar.SQLite wrapper, I can
successfully connect to the database, create a table, insert data and
display it to the console or in a message box, but I am unsure as how to
display it into a grid (like ms access)



On 10/05/06, Michael B. Hansen <[EMAIL PROTECTED]> wrote:
>
> Hi John,
>
> There is a number of .NET-wrappers for SQLite
> (http://www.sqlite.org/cvstrac/wiki?p=SqliteWrappers).
>
> I personally use Finisar.SQLite
> (http://adodotnetsqlite.sourceforge.net/) which performs acceptable
> (although I currently have one single issue with it - look for the
> subject "Transactions and 'library routine called out of sequence' locks
> the database" in this list).
>
>
> /Michael
>
> -Original Message-
> From: John Newby [mailto:[EMAIL PROTECTED]
> Sent: 9. maj 2006 17:18
> To: sqlite-users@sqlite.org
> Subject: [sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider
>
> Hi, I am trying to access a SQLite database using the ADO.Net provider
> and bring all the data back in a tabular form, I can bring back the data
> using a datareader but I don't know if I can put the data into a datset
> or a datagrid.
>
> Does anyone know if you can do this?
>
> Many thanks.
>
> John.
>

[sqlite] VB.Net DataSet & DataGrid with ADO.Net Provider

[John Newby]

Hi, I am trying to access a SQLite database using the ADO.Net provider and
bring all the data back in a tabular form, I can bring back the data using a
datareader but I don't know if I can put the data into a datset or a
datagrid.

Does anyone know if you can do this?

Many thanks.

John.

[sqlite] Displaying a SQLite databse in VB.Net

[John Newby]

Hi, I am having some trouble displaying the data from a database in my
VB.Net application, I am using the Finisar ADO.Net wrapper to connect to the
SQLite database.  I can display the data using messageboxes and textboxes so
I know it's working, I just can't figure out how to display it in something
like a datagrid.

Can anyone out there help me out with this?

Many thanks.

John.

Re: [sqlite] List of table names?

[John Newby]

Hi Dennis, thanks for this.

John.

On 05/05/06, Dennis Cote <[EMAIL PROTECTED]> wrote:


John Newby wrote:
> Hi, how do you get a list of all the table names within SQLite?
>
> Many thanks.
>
> John.
>
John,

From the sqlite shell use the .tables command.

From SQL code use:

  select name from sqlite_master where type = 'table';

HTH
Dennis Cote

[sqlite] List of table names?

[John Newby]

Hi, how do you get a list of all the table names within SQLite?

Many thanks.

John.

Re: [sqlite] Cannot load sqlite3.dll

[John Newby]

I have to use the University version at the moment as I'm creating my
University project and we have to demonstrate it at the University.

Thanks for letting me know, I would never have considered the version
differences.

John.

On 05/05/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


- Original Message -----
From: "John Newby" <[EMAIL PROTECTED]>


>Hi Robert, thanks for the advice.  I'm using either the 2002 or 2003
>version
>(not sure what the University registers to)  My supervisor recommended I
>use
>the finisar one instead of yours because of the .net framework, the
version
>on the universoty is 1 and i think yours is based on version 2 (correct
me
>if im wrong)
>
>So do you recommend I replace the older dll version?

If you're using VS2003, then you don't have a choice -- you have to use
the
Finisar library.  As for compatibility between the Finisar wrapper and the
new sqlite3.dll, your mileage may vary.  You'll have to test thoroughly
and
make sure everything functions properly.

If in the future you upgrade to VS2005, then you'll definitely want to
switch to my wrapper.  It's public domain, adds a lot of new features and
performance enhancements over Finisar, and integrates really well with
VS2005's design-time environment.

Robert

Re: [sqlite] Cannot load sqlite3.dll

[John Newby]

Hi Robert, thanks for the advice.  I'm using either the 2002 or 2003 version
(not sure what the University registers to)  My supervisor recommended I use
the finisar one instead of yours because of the .net framework, the version
on the universoty is 1 and i think yours is based on version 2 (correct me
if im wrong)

So do you recommend I replace the older dll version?

On 05/05/06, Robert Simpson <[EMAIL PROTECTED]> wrote:


- Original Message -----
From: "John Newby" <[EMAIL PROTECTED]>
>No sure with the one Robert recommended but the one I used has but it was
a
>different size and not as upto date as the one that I got from the SQLite
>site so I just replaced it with the newer version but I presume it would
>work with the older version also.

There have been some changes to SQLite since the last Finisar library was
released, and you'll have to test it thoroughly to make sure the newer
sqlite3.dll works with the older Finisar library.

Robert

Re: [sqlite] Cannot load sqlite3.dll

[John Newby]

No sure with the one Robert recommended but the one I used has but it was a
different size and not as upto date as the one that I got from the SQLite
site so I just replaced it with the newer version but I presume it would
work with the older version also.

John.

On 05/05/06, Paul Hunnisett <[EMAIL PROTECTED]> wrote:


Excellent - thanks for the recommendation.  I'm assuming the wrapper dll
doesn't contain the sqllite dll embedded in it?

John Newby wrote:
> I was having this problem also but I just copied the dll file into the
> directory, and it worked fine with the ado.net wrapper at
> http://adodotnetsqlite.sourceforge.net/
>
> John
>
> On 04/05/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:
>>
>> > I'm trying to add sqlite3.dll ot my application in VS 2005.  I
>> > downloaded the dll from the sqlite home page.  VS simply says that
>> it is
>> > not a valid assembly and can't be loaded.
>> >
>>
>> It's not an assembly so the error message is correct.
>> Copy it to the directory where you execute your program and see if it
>> will load it.
>> The wrapper should take care of that for you, you shouldn't need to do
>> anything
>> explicit in your code to load it.
>>
>



This email has been independently scanned for viruses and any virus
software has been removed using McAfee anti-virus software

Re: [sqlite] Cannot load sqlite3.dll

[John Newby]

I was having this problem also but I just copied the dll file into the
directory, and it worked fine with the ado.net wrapper at
http://adodotnetsqlite.sourceforge.net/

John

On 04/05/06, Jay Sprenkle <[EMAIL PROTECTED]> wrote:


> I'm trying to add sqlite3.dll ot my application in VS 2005.  I
> downloaded the dll from the sqlite home page.  VS simply says that it is
> not a valid assembly and can't be loaded.
>

It's not an assembly so the error message is correct.
Copy it to the directory where you execute your program and see if it
will load it.
The wrapper should take care of that for you, you shouldn't need to do
anything
explicit in your code to load it.

Re: [sqlite] Complile and connecting to SQLite

[John Newby]

There are wrappers here to connect to SQLite from loads of different
languages and toolkits :-

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

On 02/05/06, Aaron Jones <[EMAIL PROTECTED]> wrote:


Windows XP, don't know what compiler to use.

I am creating a GUI to SQLite, so need my interface to connect to SQLite,
and wanted to know what it connected to, the source code or the exe file.

On 01/05/06, Gerry Snyder <[EMAIL PROTECTED]> wrote:
>
> Aaron Jones wrote:
> > Hi, I have to questions I would like some help on please
> >
> > 1.  How do you compile SQLite from the source code
> What OS, what compiler, ...?
>
> And why do you need to compile? Why not use a precompiled executable?
> >
> > and
> >
> > 2.  How do I connect to SQLite?  Do I connect to the source code or
> > the exe
> > file?
> You don't. You just run the executable.
>
> If you want detailed answers, you have to ask detailed questions
> (and that requires reading the info on the web page and wiki first,
> and maybe even trying something and getting far enough to run into
> trouble).
>
> Gerry
>

Re: [sqlite] Rule of thumb for estimating index size

[John Newby]

Hi Christian, thanks for the explanation, when you explain it like that it's
so simple, the index in the book concept didnt even come to me. lol.

John.

On 21/04/06, Christian Smith <[EMAIL PROTECTED]> wrote:
>
> On Fri, 21 Apr 2006, Rusty Conover wrote:
>
> >Hi,
> >
> >Is there an easy way to get an estimate on the space used by an
> >existing index?  If not what is a good guess on how to estimate the
> >size?
> >
> >My guess would be (assuming text fields are being indexed):
> >
> >[total length of all index keys] + [number of rows]*8 (for the offset
> >location)
> >
> >Is that close? I realize disregards all space used by page allocation
> >overhead.
>
>
> The page overhead will be pretty constant per index entry.
>
> One underestimate from above is the effect of overflow pages and internal
> fragmentation. If the key doesn't fit in the btree node, overflow pages
> are used to store the rest of the key, and the pages are used in their
> entirety, and not shared with other entries. Thus, if your keys are quite
> long, the internal fragmentation must be taken into account in the total
> key length. If 1/4 of your rows cause overflow, the the extra overhead can
> be approximated as:
>
> pagesize/2 * [num of rows]/4
>
> This assumes that the last overflow page is on average half full.
>
>
> >
> >Thanks,
> >
> >Rusty
> >--
> >Rusty Conover
> >InfoGears Inc.
> >
> >
> >
> >
>
> --
> /"\
> \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
>  X   - AGAINST MS ATTACHMENTS
> / \
>

Re: [sqlite] Rule of thumb for estimating index size

[John Newby]

A little off subject I know, but what is the purpose of an index?

On 21/04/06, Rusty Conover <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> Is there an easy way to get an estimate on the space used by an
> existing index?  If not what is a good guess on how to estimate the
> size?
>
> My guess would be (assuming text fields are being indexed):
>
> [total length of all index keys] + [number of rows]*8 (for the offset
> location)
>
> Is that close? I realize disregards all space used by page allocation
> overhead.
>
> Thanks,
>
> Rusty
> --
> Rusty Conover
> InfoGears Inc.
>
>
>
>

Re: [sqlite] Structured or Object-Oriented?

[John Newby]

I said I was not sure if my answer was correct or not, so he probably just
wanted re-assuring that my answer was correct, give him a break, he'll be
under enough stress doing his project for uni without us getting on his
back.

On 13/04/06, Fred Williams <[EMAIL PROTECTED]> wrote:
>
> It would appear the first responder gave you your answer.  So where's
> the beef?
>
> If you don't like the answer, don't complain to us about service :-)
>
> Fred
>
> > -Original Message-
> > From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
> > Sent: Thursday, April 13, 2006 8:20 AM
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] Structured or Object-Oriented?
> >
> >
> > On 4/13/06, Aaron Jones <[EMAIL PROTECTED]> wrote:
> > > How come you all reply to other peoples emails but only 1
> > person relied to
> > > mine?
> >
> > I didn't reply because I don't know anything about mono.
> > I wouldn't take it personally.
>
>

Re: [sqlite] Structured or Object-Oriented?

[John Newby]

I think Object Oriented databases came in with the SQL-99 standard and
SQLite is the SQL-92 standard so it may be structured but I'm not sure at
all

On 06/04/06, Aaron Jones <[EMAIL PROTECTED]> wrote:
>
> Hi, I am doing a project for University where I am creating a
> cross-platform, open-source GUI to SQLite, I am going to be using mono to
> build it, do you think this will be ok or do you think I will encounter
> any
> problems along the way?  I've noticed there is a wrapper for mono, but
> where
> mono is unable to use windows.forms it uses GTK#, and I don't think there
> is
> a wrapper for GTK#, but I am not sure if this will be needed.
>
> Also, was SQLite created using a structured or object-oriented methodolgy
> so
> that I can design the interface in the same way.
>
> Any suggestions would be greatfully appreciated.
>
> Aaron
>
>

Re: [sqlite] Academic journals for SQLite

[John Newby]

OK thank you.

John

On 13/03/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> "john.newby" <[EMAIL PROTECTED]> wrote:
> > Hi, I sent this request yesterday from my other address but wasn't sure
> if
> > anyone had received it as my other email address didn't receive it but
> both
> > address are still receiving other peoples questions.
> >
> > I was wondering if anyone knew of any journals I could use for my
> University
> > dissertation on SQLite.
> >
> > Many thanks.
>
> Academic journals are generally only interested in new ideas.
> SQLite deliberately uses only ideas that are 20 years old or
> older in order to avoid patent problems. Hence
>
> --
> D. Richard Hipp   <[EMAIL PROTECTED]>
>
>

[sqlite] Academic Journals on SQLite

[John Newby]

Hi, does anyone know where I can get some academic journals on SQLite?  I
require them for my University project.

Many thanks.

John.

Re: [sqlite] Final Year Project/Dissertation help required!!!!

[John Newby]

P.S.  I can't start to build it until I have done the analysis of what I am
trying to build, that is why I am asking what people would like out of a
GUI.

John.

On 13/12/05, juan perez <[EMAIL PROTECTED]> wrote:
>
> So in a nutshell you are asking the community to do your homework. What
> a shame.
>
> Start by analyzing the sqlite3 command line interface, make a GUI that
> replicates that (and more, heck its a GUI), contribute it to the sqlite
> project, THEN talk about your dissertation.
>
> jp.
>
> John Stanton wrote:
> > john.newby wrote:
> >
> >> Hi, I am doing my final year project/dissertation on "Creating a GUI to
> >> SQLite"
> >>
> >> I am after help for my analysis phase and would like any comments/help
> >> from
> >> members or if you could put me in the right direction as to where I
> could
> >> find out this information.
> >>
> >> I would like information regarding SQLite for the following if at all
> >> possible:-
> >>
> >> Why users use SQLite instead of other SQL databases?
> >
> > SQLite has two fine features.  Firstly it is simple, lives in one file
> > and doesn't require a server process.  Secondly it implements all the
> > important elements of SQL.
> >
> >> Would a GUI detract users from using SQLite?
> >
> > There is a wagon load of GUIs to SQLite already.  They neither add nor
> > detract from the DBMS.
> >
> >>
> >> Regarding a GUI to SQLite:-
> >>
> >> What features a GUI MUST have?
> >> What features you would LIKE a GUI to have?
> >> What features you would NOT LIKE to have?
> >
> > This is like asking what features are needed in a coat without
> > specifying its intended usage.
> >
> >>
> >> Any help on these questions or any other information you may feel
> >> would help
> >> me on my journey would be greatfully appreciated.
> >>
> >> You can contact me at my University email address on
> [EMAIL PROTECTED]
> >>
> >> Many thanks for your help.
> >>
> >> John.
> >>
> >>
> >>
> >>
> >> 
> >> This e-mail is intended solely for the addressee. It may contain
> >> private and
> >> confidential information. If you are not the intended addressee,
> >> please take
> >> no action based on it nor show a copy to anyone. Please reply to this
> >> e-mail
> >> to highlight the error. You should also be aware that all electronic
> mail
> >> from, to, or within Northumbria University may be the subject of a
> >> request
> >> under the Freedom of Information Act 2000 and related legislation, and
> >> therefore may be required to be disclosed to third parties.
> >> This e-mail and attachments have been scanned for viruses prior to
> >> leaving
> >> Northumbria University. Northumbria University will not be liable for
> any
> >> losses as a result of any viruses being passed on.
> >>
> >>
> >
> >
> >
>
>

Re: [sqlite] Final Year Project/Dissertation help required!!!!

[John Newby]

No, I'm not asking the community to do my homework for me, I need to do a
user analysis phase for my dissertation, and as I don't know anyone who uses
SQLite this is the only place I can get my questions answered.

Many thanks.

John.

On 13/12/05, juan perez <[EMAIL PROTECTED]> wrote:
>
> So in a nutshell you are asking the community to do your homework. What
> a shame.
>
> Start by analyzing the sqlite3 command line interface, make a GUI that
> replicates that (and more, heck its a GUI), contribute it to the sqlite
> project, THEN talk about your dissertation.
>
> jp.
>
> John Stanton wrote:
> > john.newby wrote:
> >
> >> Hi, I am doing my final year project/dissertation on "Creating a GUI to
> >> SQLite"
> >>
> >> I am after help for my analysis phase and would like any comments/help
> >> from
> >> members or if you could put me in the right direction as to where I
> could
> >> find out this information.
> >>
> >> I would like information regarding SQLite for the following if at all
> >> possible:-
> >>
> >> Why users use SQLite instead of other SQL databases?
> >
> > SQLite has two fine features.  Firstly it is simple, lives in one file
> > and doesn't require a server process.  Secondly it implements all the
> > important elements of SQL.
> >
> >> Would a GUI detract users from using SQLite?
> >
> > There is a wagon load of GUIs to SQLite already.  They neither add nor
> > detract from the DBMS.
> >
> >>
> >> Regarding a GUI to SQLite:-
> >>
> >> What features a GUI MUST have?
> >> What features you would LIKE a GUI to have?
> >> What features you would NOT LIKE to have?
> >
> > This is like asking what features are needed in a coat without
> > specifying its intended usage.
> >
> >>
> >> Any help on these questions or any other information you may feel
> >> would help
> >> me on my journey would be greatfully appreciated.
> >>
> >> You can contact me at my University email address on
> [EMAIL PROTECTED]
> >>
> >> Many thanks for your help.
> >>
> >> John.
> >>
> >>
> >>
> >>
> >> 
> >> This e-mail is intended solely for the addressee. It may contain
> >> private and
> >> confidential information. If you are not the intended addressee,
> >> please take
> >> no action based on it nor show a copy to anyone. Please reply to this
> >> e-mail
> >> to highlight the error. You should also be aware that all electronic
> mail
> >> from, to, or within Northumbria University may be the subject of a
> >> request
> >> under the Freedom of Information Act 2000 and related legislation, and
> >> therefore may be required to be disclosed to third parties.
> >> This e-mail and attachments have been scanned for viruses prior to
> >> leaving
> >> Northumbria University. Northumbria University will not be liable for
> any
> >> losses as a result of any viruses being passed on.
> >>
> >>
> >
> >
> >
>
>

[sqlite] Final Year Project/Dissertation help required!!

[John Newby]

Hi, I am doing my final year project/dissertation on "Creating a GUI to
SQLite"

I am after help for my analysis phase and would like any comments/help from
anyone or if you could put me in the right direction as to where I could
find out this information.

I would like information regarding SQLite for the following if at all
possible:-

Why users use SQLite instead of other SQL databases?
Would a GUI detract users from using SQLite?

Regarding a GUI to SQLite:-

What features a GUI MUST have?
What features you would LIKE a GUI to have?
What features you would NOT LIKE to have?

Any help on these questions or any other information you may feel would help
me on my journey would be greatfully appreciated.

You can contact me at my University email address on [EMAIL PROTECTED]

Many thanks for your help.

John.