[RESOLVED] Beginner trying to learn SQLite database

[jumper77]

Ok, the idea is what I thought you meant. After the changes, I can see that the column headers no longer has spaces.

Is there a way to keep the database field a name without spaces, but still keep the column name 'with' spaces? Or is that bad too?

thanks

[GaryMazzone]

When you write the select statement you can alias the field as I showed in my Select example

Select fieldname AS 'Some other name' FROM tablename

[Sitten Spynne]

If I'm wrong, just tell me. When I create a database table, I define one column as Primary Key. Primary Key will turn into an Auto-Increment automatically if you don't reference it anymore.

This is probably close enough. Some of this is specific to SQLite, so let's make sure we air that part out because if you do try out another database you need to know this. I'm going to mark each paragraph with a kind of attribute. "[General]" means I'd be surprised if you find a database where it isn't true.

[General]
Every table can have one 'primary key'. It can be of any type supported by the database. When a column is a primary key column, every value in that column must be unique. Trying to add a row with a non-unique value in a primary key column will result in an error in any sane database. It is not illegal to set a value for a primary key column, and generally you are required to set a value because obviously only one null value can exist. ([i]The technical motivation: the database sorts its internal data structures in a way that makes searching/sorting by primary keys very, very fast, as opposed to 'very fast'.)

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is usually not legal to set the value yourself. Sometimes you can do so, but you should be very careful if you do. Auto-increment columns are often primary keys, but they are not required to be primary keys.

[SQLite]
SQLite makes some decisions to try and streamline common tasks. The developers noticed it is so common to want an auto-incrementing integer primary key column, they'd shorten the syntax. So instead of "INTEGER AUTOINCREMENT PRIMARY KEY", you can type "INTEGER PRIMARY KEY" and get the same thing. That means the column definition "INTEGER PRIMARY KEY" in SQLite has both behaviors: it must be unique because it is a primary key, and you shouldn't set it yourself because it is auto-incrementing.

It's a syntax sugar. I think it makes people mad because it hides that auto-incrmenting is happening, and many other database engines are very explicit about that. While that is a valid concern, I think there are worse things to worry about.

[SQLite]
If you need a column that is an integer primary key but does not autoincrement, you must not type the exact string "INTEGER PRIMARY KEY". It is sufficient to say "INT PRIMARY KEY". That column will only have the "must be unique" restriction of being a primary key column, but not the "you can't set it" restriction of auto-incrementing because it doesn't auto-increment.

That's another place that tends to make people used to explicit database engines angry. Type assignment in SQLite is a little strange, so if you have very strict type definition needs it's not the database for you.

It is true that this means moving from SQLite to another SQL database will require some extra thought, as most others don't make assumptions for you. The more I mull it over, the more I want to make a "What's different in SQLExpress" series, just to learn myself. But now's not the time, and I'd be pleased if someone else wrote that part for me before I got there.

[jumper77]

When you write the select statement you can alias the field as I showed in my Select example

Select fieldname AS 'Some other name' FROM tablename

Ok, thanks. I'm going to learn how to do this and will post some code when I "think" I got it.

[GaryMazzone]

Sitten Spynne: Be careful with general statements like this:

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is not legal to set the value yourself. These columns should never be part of an UPDATE or INSERT. Auto-increment columns are often primary keys, but they are not required to be primary keys.


There are way to insert value into an Auto-incrementing field in different database systems. In MS SQL you issue the statement SET IDENTITY_INSERT ON. Do you insert statement including the value you want for that Auto increment field then issue the statement SET IDENTITY_INSERT OFF

[jumper77]

Good Morning.

[SQLite]
If you need a column that is an integer primary key but does not autoincrement, you must not type the exact string "INTEGER PRIMARY KEY". It is sufficient to say "INT PRIMARY KEY". That column will only have the "must be unique" restriction of being a primary key column, but not the "you can't set it" restriction of auto-incrementing because it doesn't auto-increment.

I'm familiar with using INT instead of INTEGER. That was back when I was using Sybase. And that's before MS bought them and mysteriously came out with SQL Server not long after that (remember, this is MY memory and may not be correct).

That's another place that tends to make people used to explicit database engines angry. Type assignment in SQLite is a little strange, so if you have very strict type definition needs it's not the database for you.

This is true. SQLite will let you put a string in a field that was defined as INT (again, I think this is right). But I never do any of that. If I define a field of a certain type, I always use that type.

[Sitten Spynne]

Sitten Spynne: Be careful with general statements like this:

[General]
Auto-increment is a different topic. It's very common to want a unique integer column (whether or not it's a primary key). Auto-increment columns keep track of internal state and always assign an incrementing value to the column. Because the database assigns this value, it is not legal to set the value yourself. These columns should never be part of an UPDATE or INSERT. Auto-increment columns are often primary keys, but they are not required to be primary keys.


There are way to insert value into an Auto-incrementing field in different database systems. In MS SQL you issue the statement SET IDENTITY_INSERT ON. Do you insert statement including the value you want for that Auto increment field then issue the statement SET IDENTITY_INSERT OFF

Huh. Good to know. I'm going to go back and update it to be more of an "It's USUALLY illegal". I bet it's also USUALLY a bad idea, but I also bet if I spent a year doing large DB projects I'd find myself needing it.

*edit*: I updated it to read more like "this is a bad idea, and sometimes you have to jump through hoops to do it."

Totally agree on spaces in column names. It's legal. But to most DB developers it's 'not right'. Everyone's used to column names being one word. I don't know what the consensus is on camelCasing vs. PascalCasing, I tend to prefer camelCasing and try to stick with it.

[jumper77]

Here's an update Gary. In the CreateDatabase sub, I was inserting data just so I could see some values when the form/DataGrid came up. I changed it to only defining the database table. In another form, the user can add new customers. In that section, I'm using the value of textboxes when I insert a customer field.

For that, it's working like it should. But I think I need to find another place where I can use the actual database field names. I'm going to look into that now too.

Here's the code I am talking about. And FYI: I'm going to stop trying to format the code blocks so you don't have to scroll to the right. It's a pain. Plus, it's less readable that way.

CreateDatabase sub:

    Public Sub CreateDatabase()
        Using objConn As New SQLiteConnection(connectionStringCreateDatabase)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()

                        .CommandText = "Create TABLE customer('Row Count' INTEGER PRIMARY KEY,'First Name' Text Not NULL, 'Last Name' Text Not NULL, Address Text Not NULL, 'Phone Number' Text Not Null);"
                        .ExecuteNonQuery()

                    End With
                Catch ex As Exception
                    'Do something here.
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

And here's the sub that calls Add Customer:

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
        Dim custmr As New Customer
        If txtFirstName.Text = "" Then
            MsgBox("First Name field cannot be blank")
            Return
        ElseIf txtLastName.Text = "" Then
            MsgBox("Last Name cannot be blank")
            Return
        ElseIf txtAddress.Text = "" Then
            MsgBox("Address cannot be blank")
            Return
        Else
            If txtPhoneNumber.Text = "" Then
                MsgBox("Phone number cannot be blank")
                Return
            End If
        End If

        custmr.addNewCustomer(txtFirstName.Text, txtLastName.Text, txtAddress.Text, txtPhoneNumber.Text)
    End Sub

And finally, the Add Customer code itself:

    Public Sub addNewCustomer(firstName As String, lastName As String, address As String, phoneNumber As String)
        Using objConn As New SQLiteConnection(connectionString)
            Using objCommand As SQLiteCommand = objConn.CreateCommand()
                Try
                    With objCommand
                        objConn.Open()
                        .CommandText = "INSERT INTO customer ('First Name', 'Last Name', 'Address', 'Phone Number')
                               VALUES('" & firstName & "','" & lastName & "','" & address & "','" & phoneNumber & "');"
                        .ExecuteNonQuery()
                    End With
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
            End Using
        End Using
    End Sub

[jumper77]

You win Gary Since I named things with a space in it, the rest of the code is really a pain! So much so, I'm going back to using: FirstName, LastName, etc.

I created a test sub just to see how it worked with spaces, and it doesn't. I tried for a bit to make it work, but as I said, it's too much of a pain.

So..... here I am with one word field names

[Sitten Spynne]

Table of Contents

• Lesson 0: SQL/SQLite introduction. CREATE TABLE, SELECT, INSERT, UPDATE, DELETE. Link to the syntax pages/SQLite documentation.
• Lesson 1: NuGet, setup, IDbConnection.
• Lesson 2: IDbCommand, focus on ExecuteNonQuery(), ExecuteReader(), ExecuteScalar(). Attached example of a working application with a layer of abstraction.

Lesson 3 - Parameters
There are a lot of problems that can happen when you take user input and add it to a SQL query. Suppose I have a search box for looking up customers by last name. It'd make sense to see the query built this way:

String.Format("SELECT id, firstName, lastName FROM customers WHERE lastName LIKE '{0}%'", input)

Bad things happen if the user knows a little SQL and wants to do bad things. For example, suppose they input the last name "'; DROP TABLE contacts; --" That's going to make our query look like:

SELECT id, firstName, lastName FROM customers
    WHERE lastName LIKE ''; DROP TABLE customers; --%'

The user's input tells SQL the SELECT is finished. The semicolon says "Let's do another query." DROP TABLE is a command I didn't mention, it completely deletes the table. Ouch. The '--' is a SQL comment, and stops the rest of the original SELECT query from being parsed (and likely causing an error that prevents the DROP TABLE from running.) You've probably heard of a 'SQL injection' attack. This is how they work. It's worse when they're used to steal sensitive data, but it's never good to allow this to happen.

So how do you prevent them? The best thing to do is called 'sanitizing' user input. This means checking it before putting it in the query. If you find any symbols that mean something special to the database, you escape them. This is sort of like how if you want a " inside a string in VB, you have to use "" instead.

But it is tedious to write your own sanitizer, and programmers often forget to call the sanitize routine. Making just one mistake is all the attacker needs. So it is far, FAR better to write your code in such a way that you can never forget to sanitize it. SQL parameters are one way to do this.

Let's look at String.Format() again. It's like good old printf(). You give it a string with special placeholders inside that might have formatting information. You also give it a list of data items. It looks for each placeholder, then picks a data item that lines up with it, converts that data item to a string, and replaces the placeholder with the string. So this code produces the result beneath it:

String.Format("{0} '{1}' {2}", 25, "hi", New Button())

"25 'hi' {System.Windows.Forms.Button}"

It would be nice if we had a String.Format() that knew how to sanitize SQL for us, wouldn't it? That's how working with parameters behaves.

Frustratingly, every database I've encountered uses slightly different syntax for parameters in the SQL query. Some use "?". Some use "{0}" like String.Format(). Most have a couple of different ways, I like the ones that let me give a name to my parameters. SQLite's syntax for a parameter looks like ':name'. It looks like MSSQL/SQLExpress uses '@name'.

So the first thing we do is take a SQL statement that doesn't use parameters and convert it to one that does. This is the reason I've been using String.Format(), all of my placeholder values are where parameters would go. I do this as a reminder to myself that I should really be using parameters. So the top line in this snippet is wrong, and the bottom line is right:

query = String.Format("SELECT id, firstName FROM customers WHERE lastName LIKE '{0}%'", input)

' sqlite
query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"
' MSSQL
query = "SELECT id, firstName FROM customers WHERE lastName LIKE @lastName"

Notice I didn't put the '' around the parameter. The parameter engine should be smart enough to do this on its own. So there's another benefit: you can't forget to close a ' in your query if you use parameters. I can't count how many times I've done that.

We obviously have to do some work on the code end. There's a lot of moving parts, and I wish it didn't feel so clunky. Let me try and guide you through.

IDbCommand has a Parameters collection. If parameters are being used, this collection must contain one parameter object per parameter in the query. If you don't provide one per query parameter, you will get a SQL error when the command is executed.

What type are parameters? Well, there is an IDbParameter, and it is implemented by an abstract class DbParameter. Your ADO .NET library will come with a specific class like SqlParameter or SqliteParameter. There are a few important properties and methods of parameters:

• ParameterName is the name of the placeholder the parameter object represents. Some databases/syntaxes don't use a name, those are bad but you don't use ParameterName with them.
• Value is an Object that represents the thing you want to go in place of the parameter. It's usually an Integer, Double, String, or DateTime. Check your documentation to see what types are supported before trying much else.

There's some others, like DbType and IsNullable, that do seem important. But it turns out we can get away without setting them ourselves. We might find a use for them, I'll explain them when and if we get there.

Sadly, parameters are not auto-generated by most IDbCommand types. So every time you make an IDbCommand, you also have to initialize its Parameters collection, setting the names and values for each parameter. And curiously, there aren't often good syntax sugars for doing it all in one go. The API is optimized for creating parameters without a value, and for this stage of the tutorial we want to set both name and value at the same time. It gets tedious. But here is the example query from before, top to bottom, using a parameter:

Using connection As SQLiteConnection = CreateConnection()
    Using command As SQLiteCommand = connection.CreateCommand()
        Dim lastNameParameter As New SQLiteParameter("lastName", input)
        command.Parameters.Add(lastNameParameter)

        Using reader As IDataReader = command.ExecuteReader()
            ' ...
        End Using
    End Using
End Using

It's often useful to prepare a helper method to set up the parameters for a command. More on that later. Notice the ":" or "@" isn't part of the parameter's REAL name, it's just a sign to the SQL engine that the token that follows is a parameter name.

If you're making a lot of INSERT statements, you'll be setting up a lot of parameters. One trick is to reuse the same command object, so you only have to initialize the parameters once. I wrote some code that did that in a previous project, it ended up looking something like this:

Sub InsertAll(ByVal data As ???())
    Using connection As SQLiteConnection = CreateConnection()
        Using command As SQLiteCommand = connection.CreateCommand()
            InitializeInsertParameters(command)
            For Each dataItem In data
                UpdateParameters(command, dataItem)
                command.ExecuteNonQuery()
            Next
        End Using
    End Using
End Sub

Private Sub InitializeInsertParameters(ByVal command As SqliteCommand)
    command.Parameters.Add(New SqliteParameter("first"))
    ...
End Sub

Private Sub UpdateParameters(ByVal command As SqliteCommand, ByVal item As ???)
    command.Parameters("first").Value = item.First
    ...
End Sub

This way I didn't clutter up my main block of code with lots of lines devoted to parameter management.

Parameters work in INSERT, UPDATE, DELETE, and SELECT queries. You should get in the habit of using them for EVERY place where you need to provide something variable, especially when you take user input.

The attachment for this lesson started based on the Lesson 2 file. I updated every query to use parameters, and for illustrative purposes added a loop to the end that lets you do a search against the database. Try typing a harmful string, or anything that might break the query like "'''''". This meant losing most of the benefits of my ExecuteNonQuery() helper method from Lesson2, and as a result Lesson3.vb is a bit more verbose. I know some ways to cut down on this, but they seemed 'too complex for tutorials' so I'm going to put them aside for a future lesson about building a baby ORM.

Don't be scared off by this extra 'complexity' in the form of having to repeat the Using statements. It cannot be understated how important it is to use parameters. I will go so far as to say code that doesn't is stupid code, not just bad code. That said, when I'm in a hurry and writing something JUST for myself, sometimes I don't use them. I'd never write a real program without them.

Other databases like MSSQL have other ways to deal with security in addition to parameters, such as user 'roles' that don't allow DELETE/DROP TABLE commands, etc. Stored procedures also help, I believe you can't make one take data WITHOUT using parameters. Those are good things to learn about if you are administrating one of those databases. If you're using SQLite, parameters are the safety net. Don't omit them.

Next up: I'm not sure.

At this point, if you master parameters, you know enough to write a full-scale CRUD application backed by a database. Your code will be clunky, because there are mechanisms that can take some tedium out of working with ADO .NET. And we haven't talked about GUI at all, which has some additional neat features.

So the natural path is probably to discuss DataSet/DataTable/DataAdapter next. From there, the relationship between those and DataGridView can be explored. That's a great segue into Windows Forms data binding. After that, some non-DataAdapter techniques for WinForms data binding can be investigated, though I think they're not as often used. I think, after that, the remaining topics are all "do your own research" because they'll have to be tailored to your specific database engine and application requirements.

[jumper77]

Thank you S for the file. I was not thinking about those kind of things because I was just trying to get the basics to work. But the file really points out a much better way of doing things. I'm going to integrate the code into what I have now.

One other thing... I will be gone for a few hours. Need to take care of some business.

And thanks for the "Whack on the side of the head". (did you read the book)

[jumper77]

I have a question about properties (yet again). Are they used only to protect private data or is there something else too?

[szlamany]

You can put logic in the property - right? That makes it more then just protecting private data, on just that point alone.

[Sitten Spynne]

That's an odd question for this thread, but OK.

The major advantage properties have over fields is they give you a getter and setter where you can do something when the value is changed or retrieved. The most common thing to do is implement INotifyPropertyChanged. But there are other things.

[jumper77]

Ok, thanks guys. I guess it IS off topic. I guess I was wondering out loud. But I'm glad I did. I think I just added a little more to my understanding of what they can do

[jumper77]

S, I looked up INotifyPropertyChanged and I think that's something really neat. I'm sure sometime (not yet) we can use it in the project.

edit: have to leave again <sigh>

[jumper77]

Hi everyone, the code below comes from the file SS attached. He's done something in a way that I don't know about. Can anyone explain this to me? I don't know if you need more code or not.

thanks

Dim updateQuery As String = "UPDATE customers SET " &
            "firstName = :firstName, lastName = :lastName address = :address, phoneNumber = :phoneNumber" &
            "WHERE id = :id"

If it's just another way to do a query, then I'm all for it. No messing with the weird string quotes and single quotes.

edit: I'm guessing that it has to do with combining a SQL Query with properties

[Sitten Spynne]

That is an UPDATE query using parameters. I didn't go through every kind of query in the lesson, because it'd have been redundant. The example I used was a SELECT:

query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

[Carlos Rocha]

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

For the sake of completeness in SQLite one can use any of ':', '@' or '$' to identify named parameters.

[FunkyDexter]

Sitten, a couple of other slightly pedantic but important corrections to your post 83. Both are to do with the first general point about primary keys:-

1. You say there can only be one null value. There can't be any nulls on a primary key. You can have a single null on a unique index but the primary key has "No Nulls" as an additional constraint.
2. You say the records are ordered according to the primary key. They're actually ordered according to the clustered index (unless the table is a "heap") which can differ from the primary key. Typically you'll put the clustered index on the primary key but the querying pattern can dictate that the clustered index is more valuable on a different field. E.g. Date fields and "ordering" fields are often better candidates.

Sorry to be pedantic but those are two classic gotcha questions at interviews and the like.

Totally agree on spaces in column names. It's legal. But to most DB developers it's 'not right'. Everyone's used to column names being one word. I don't know what the consensus is on camelCasing vs. PascalCasing, I tend to prefer camelCasing and try to stick with it.

I'd say that the most common convention is to Use_Under_Scores. People also tend to favour EVERYTHING_IN_UPPER_CASE. The conventions in DB world seem to be a lot more relaxed than in Dev world though and different DBAs seem to have different standards.

[jumper77]

That is an UPDATE query using parameters. I didn't go through every kind of query in the lesson, because it'd have been redundant. The example I used was a SELECT:

query = "SELECT id, firstName FROM customers WHERE lastName LIKE :lastName"

In SQLite, named parameters begin with a ':' symbol, then the name follows. It's an '@' symbol in MSSQL.

Thanks for the answer. I need to study up on parameters because it's a new thing to me. Also, I just checked the thread and didn't know I had replies. I think my mail program has lost it's mind

And Dex, thank you for the info too. I've never liked the use of all caps for a variable. This goes all the way back to my C coding days with things like MAX_PATH. I know it's standard in the database world though. I guess I should probably start doing it that way.

And hi Carlos, it's nice to meet you. I thank you as well for the info. I've seen the dollar sign used before, but I have to admit that I like the way ':' looks, so I will probably stick with that. Thanks for joining in on the thread.

[szlamany]

jumper77

Since SQLite will allow "@" for parameters it is better for you to select that character for parameters.

Otherwise once again you are marrying yourself to this very low profile database engine.

Forget that ":" and "$" are allowed - use "@".

As for column names (which are part of the table object) - I always use proper casing without spaces. MasName, PerName, MasId, SSN, DOB, Birth, Gender, DateEntered, DatePaid, AmountPaid - stuff like that.

And I am 100% consistent - for 20 years of doing SQL now. That is the most important.

All UPPERCASE looks terrible in any form given to the user. Tables in database can be reviewed using EXCEL - I would never want have ALL UPPERCASE column headings in EXCEL.

My personal preference - but remember it's not just you seeing these field names. They creep up everywhere.

And really important - they are not VARIABLE names like you kind of implied in your last post.

[FunkyDexter]

<sigh>I'm obviously in pedantic mode today. I hope I don't put anyone's nose out of joint.</sigh>

Forget that ":" and "$" are allowed - use "@".

This is a bit a Microsoft centric view of the world, the @ is hardly universal. Postgres, for example, uses $ and doesn't recognise the @ as a parameter. I don't think Oracle uses any symbol at all - it just requires that the name in the query matches the name of the parameter. Since we're a Microsoft orientated forum I think you're advice for Jumper to favour the @ is good but I'd temper it by saying, don't get wedded to any particular syntax for parameter handling. It's one of the things that isn't in the ANSII standard so will change from platform to platform.

edit>

I've never liked the use of all caps for a variable

Me neither, I think it looks ugly. I'm not keen on the underscores either and tend to use Pascal case in my own projects. I recognise that this is a bit maverick, though, and has upset more than one DBA I've worked with. Mostly I just tow the line and follow whatever culture the company I'm contracted to favours, which is SHOUTY_UNDERSCORES 9 times out of 10.

[jumper77]

Thanks for the advice Sz. You've been working with SQL quite a while. After working with it for that many years, you must be pretty good at it too. I think you're right about be constancy. Although I'm not great at it (but working on it).

And Dex, I smiled when I saw SHOUTY_UNDERSCORES . It does look awful. But you're right about one thing. Follow the ways of the company where you work. Something I always did because it seemed like every company has their own coding standards.

[jumper77]

And SS, thanks again for the file, but in order to help me understand how to do things, I'm trying to write my own class. I need to understand and implement past lessons into this project. That will take more time, but well worth it.

[Sitten Spynne]

Wow. Even doing the small version of DataAdapter is a big topic. It's not going to show up today, and the introduction is growing so big I worry it'll take multiple posts. Curse me for saying, "Oh, I'll show how to do it without FillSchema() then build up to that." Ugh.

Jumper: your #101 sort of... confuses me. Lesson 3 is entirely about parameters! I sort of expect the thread to be read in order. This is also the 3rd or 4th question about things directly inside the lessons. I know they're big, but there are lots of little gotchas worth knowing. Odds are if there's something weird in the .vb file, I explained it!

RE: "What symbol should I use for parameters?"

I'm not going to switch to '@' in this thread. I don't want to spend time editing posts AND updating code attachments, and I mentioned when I introduced parameters that each db engine might have different syntax. But in the future, I think I'm going to use '@', for the silly reason "it's easier to see than ':'". I'm comfortable expecting readers to be able to follow along.

RE: casing:

Being 100% consistent is what really matters. I'm actually breaking my rules, and feel sad now. For now, I'm going to continue to camelCase everything becasue it is what I've done so far. What I'd have done if I was smart is follow something similar to how we case things in .NET: 'important' things like tables and database names would be PascalCased. 'Less important' things like columns would be camelCased. I dropped the ball on this, so will stick with "everything is camelCased". The one thing I've noticed: we're batting 1.000 on "no one likes ALL_CAPS".

I do want to call this out:

Otherwise once again you are marrying yourself to this very low profile database engine.

SQLite is used by quite a few big names. It ships with iOS and Android, and is the only database I know that runs native on those phones. It's in the flight software for the Airbus A350. It's used by both Firefox and Chrome. It's bundled with PHP, Python, and REALbasic. All of this accounts for millions of users depending on it as a critical piece of their software.

Please let's not continue to waste time on this. SQLite shines in terms of no-network data storage with simple relational schemas. This is a tutorial about CRUD with one table and ten rows. And you're worrying about what happens when autoincrement overflows a 64-bit integer, or whether the SQL is exactly portable to a database designed to support thousands of simultaneous requests. These are issues I never consider when writing applications with SQLite.

Or, in a nutshell, you're telling me that VB is a "stupid toy language" and I should be using C. It's the same pattern, the same line of thought, and it's really not a healthy conversation.

I'd really lke it if you could prepare a post about when you wouldn't use SQLite. I think you know a lot more than I do about databases, and you could probably write a very good guide. And I think you have far more experience with large-scale databases than I do. But I suspect your answer is "I'd never use it, not even in the cases where it's appropriate", and I don't think that makes me the one married to a technology.

RE: FunkyDexters's #100:

I'm going to edit those facts back in, but my feelings are:

1) I sort of guessed this, since values have to be unique it seemed intuitive that null would be a bad idea. Also boolean columns.

2) I guess "sorting" was a bad word, I meant this in the sense that primary keys are typically an index. And, treating the db engine as a balck bocx

And SS, thanks again for the file, but in order to help me understand how to do things, I'm trying to write my own class. I need to understand and implement past lessons into this project. That will take more time, but well worth it.

I don't know what this means. What I read out of it is something like, "Thanks for writing a big post and a large code example, but I'd rather figure this out on my own, asking one question at a time."

I mean this with no anger, and no threat: if that is the case, please let me know. I'm sitting here planning something like 10 hours of work on the first couple of DataAdapter lessons, and it would be better for me to not write them if you find my large posts too hard to process. I still want to write them, but perhaps the best place to publish them would be a Code Bank series or a The Code Project series.

[szlamany]

I said low profile - you worked yourself up a whole lot over a simple statement.

It is a low profile database engine - intentionally.

I do not care where it runs - that doesn't change what it's intended to be.

All I said was use "@" since it's allowed and aligns with the MS world. That alignment is not a bad thing.

Chill out dude

[Sitten Spynne]

I've had about three cups of coffee now, is that chilling? I get it now.

[jumper77]

Jumper: your #101 sort of... confuses me. Lesson 3 is entirely about parameters! I sort of expect the thread to be read in order. This is also the 3rd or 4th question about things directly inside the lessons. I know they're big, but there are lots of little gotchas worth knowing. Odds are if there's something weird in the .vb file, I explained it!

I apologize for the extra questions. With this project I have not been going back to your earlier posts, but I will change that.

I mean this with no anger, and no threat: if that is the case, please let me know. I'm sitting here planning something like 10 hours of work on the first couple of DataAdapter lessons, and it would be better for me to not write them if you find my large posts too hard to process. I still want to write them, but perhaps the best place to publish them would be a Code Bank series or a The Code Project series.

I know you put in a lot of work into this, and I'm grateful. I also don't want to give you the idea that I'm not taking this seriously, because I am. So I believe that I should present myself in a more professional manner. And I will start doing this NOW.

Again, I do thank you for your help. I've learned enough things from you that would take years otherwise.

[Sitten Spynne]

I kind of need an answer. Big posts, or little posts?

[jumper77]

Either would be fine. You choice. Large posts concerning lessons will take longer to assimilate. But that will shorten with I stop making off topic and sidetracked posts. Everyone here knows a lot more than I and I'm running as fast as I can to keep up. This is not a comment about the length of your messages, it's about my lack of knowledge in these areas.

[Sitten Spynne]

OK, cool. I think I'll percolate a bit on the DataAdapter stuff and decide how big or small I want the posts to be. That'll give you some time to let parameters sink in.

Shortest possible version:

In a lot of queries, I was using String.Format to make "blanks" that I'd fill in with data. Something like this:

UPDATE customers SET firstName = _______ WHERE id = ________

When I use String.Format(), the 'blanks' are going to be {0} and {1}, and I have to remember whether to use '' or not.

When you use parameters, you put a symbol with your database's syntax for parameters where the blanks go. SQLite and MSSQL agree on '@', I think maybe I'm going to just switch to that and throw away what I said about consistency.

UPDATE customers SET firstName = @firstName WHERE id = @firstName

You can't use that string with a command as-is. You have to tell it what values to assign to which parameter. To do that, you add parameters (SqLiteParameter/SqlParameter) to the command's Parameters collection. Each parameter in the query should have a parameter with the same name in the collection. (Of note: the "@" sign or ":" sign only belongs in the SQL query to tell SQL where the parameters are. When you set your parameter object's name, don't include the symbol.)

When you do this, the code handles deciding if it needs '' around the value, or how it needs to be formatted, and even goes so far as to escape dangerous characters. Using parameters is a little more complex because of how you have to set them up, but they eliminate an entire universe of dangerous mistakes. So they're well worth it.

[jumper77]

You have to tell it what values to assign to which parameter. To do that, you add parameters (SqLiteParameter/SqlParameter) to the command's Parameters collection. Each parameter in the query should have a parameter with the same name in the collection.

Good information. I didn't know that Parameters are collections. This gives me another thing to look up and learn.

[jumper77]

Post edited for content

[Sitten Spynne]

Not quite. Part of what makes this hard is you have to use your imagination to visualize things, but it's hard to visualize something you can't see.

A "query" is the string of SQL that tells the database to do something. Imagine it is like a punch card.

A "parameter" is like a big hole in that query where there should be some data. Imagine if the punch card had a stencil-like blank space in it. Parameters have a name, to tell you which hole they represent, and a value, which is next.

A "value" is the data that goes in the parameter. It's like a bit of card stock you carefully punch holes into then tape to the hole in the punch card where a parameter is. This way, you can use the same punch card for a lot of different data.

A "command" is the object (like SqlCommand) that represents the punch card. A command:

• HAS A query, in the form of the CommandText property. That query might have "holes" that represent parameters.
• HAS A collection of parameters, one for each parameter in the query.

A "parameter" in this sense is an object (like SqlParameter) that represents the taped bit of punch card. It HAS A name and the value it represents.

Part of what is confusing is the word "parameter" is used to refer to both: the 'hole' in the query and the object the Command uses to map that hole to a value.

So, for illustration, suppose we have this SELECT query:

SELECT id, firstName, lastName FROM customers WHERE lastName = @lastName

This is a SQL query that says: "I will select rows from the table named 'customers'. Each row will have three columns, and my result set will only contain rows where the column 'lastName' is equal to a parameter 'lastName' that will be supplied when I am executed."

To use it in code, you have to create a command object with that query text. Then, you have to create a parameter object with the name 'lastName' and give it a value. Then, you add the parameter object to the command object. When you execute the command, the DB puts the value in the parameter object where the '@lastName' placeholder is in the query, taking care to format it correctly.

It would be SUPER COOL if I could demonstrate this by showing you the SQL generated by the command. Alas, I have yet to find a way to do that. I don't think it actually works that way, I think the process of assigning values to the parameters happens via calls to the SQLite C libraries. Don't quote me on that.

[jumper77]

Update: The project is now a console app. No need to make the learning curve any longer than it should be. I'm not good at UIs anyway.

[jumper77]

A "parameter" is like a big hole in that query where there should be some data. Imagine if the punch card had a stencil-like blank space in it. Parameters have a name, to tell you which hole they represent, and a value, which is next.

Good way to look at it. Makes it easier to understand and remember.

A "value" is the data that goes in the parameter. It's like a bit of card stock you carefully punch holes into then tape to the hole in the punch card where a parameter is. This way, you can use the same punch card for a lot of different data.

Same here where understanding is concerned. I'm all for punch cards that allow reuse.

Part of what is confusing is the word "parameter" is used to refer to both: the 'hole' in the query and the object the Command uses to map that hole to a value.

So, for illustration, suppose we have this SELECT query:

SELECT id, firstName, lastName FROM customers WHERE lastName = @lastName

This is a SQL query that says: "I will select rows from the table named 'customers'. Each row will have three columns, and my result set will only contain rows where the column 'lastName' is equal to a parameter 'lastName' that will be supplied when I am executed."

Thanks, parameters make a little more sense now. "To me", I'm now thinking of parameters as values to be used. And notice, I put To Me in quotes. Meaning it might be a bad way to think about it, but it does help clear up things for me.

To use it in code, you have to create a command object with that query text. Then, you have to create a parameter object with the name 'lastName' and give it a value. Then, you add the parameter object to the command object. When you execute the command, the DB puts the value in the parameter object where the '@lastName' placeholder is in the query, taking care to format it correctly.

Nice explanation. I hate to repeat myself, but this is making things more concrete. Understanding how things match up gives a clearer picture of what's going on.

Of course I'll still have to study your code some more, but the explanations in this post will make it much easier on me.

[Sitten Spynne]

This is what I've been trying to explain with way too many words. You're making me want to retry my DataAdapter lesson. It's at... 300 paragraphs? I don't expect you'd come out of that with much, now that I think about it.

[wes4dbt]

Update: The project is now a console app. No need to make the learning curve any longer than it should be. I'm not good at UIs anyway.

I don't think that's a good idea. Earlier you posted a form with a datagridview and a couple of button. Why take a step backwards. Your already able to display data in a dgv. I think it would be easier for you and us to review your program results that way.

I mean,

        Try
            Using con As New OleDbConnection(My.Settings.ContactsConnectionString), da As New OleDbDataAdapter("select * from contacts", con), dt As New DataTable
                da.Fill(dt)
                Me.DataGridView2.DataSource = dt
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try

Boom.... Done

[jumper77]

Well I glad that I got it finally. Plus, I think 'you' just got the idea yourself. If you write a post trying to explain things and I can hit the down-page button 3 or 4 times before I get to the end... It's probably a bit long and much harder to understand. I just thought of a great way of saying it... You always tell me to take 'baby steps', why don't you take them with me?

Seriously though, it's so much easier for me to understand if you take things one point at a time. Plus, it sticks with me a whole lot more. But when I read one of your big posts about things, I end up saying to myself: Ok, I got to learn this, and that, and that, etccccccccccc. Plus, I do hope you know that I'm not fussing at you or anything. But I just realized that if we could meet in the middle somewhere, it would be so much better for the both of us. And PLEASE... not 300 paragraphs

So what do you think? Sounds like a plan to me.

edit: Maybe this has something to do with people saying I try to fly before I can walk? Not sure. Just guessing.