www.webdeveloper.com
Results 1 to 6 of 6

Thread: SQLite conundrum

  1. #1
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,316

    SQLite conundrum

    I have a little SQLite database on a website, when I set up the tables using the CREATE directive, the ENTRY_ID was set up with the following.

    CREATE TABLE visitors(ENTRY_ID INTEGER PRIMARY KEY AUTOINCREMENT, REQUEST_TIME INTEGER, USER_IP... ... ...

    However... the table is not "Auto Incrementing" and the ENTRY_ID fields are empty...

    My query is how is it best to move forward with altering the primary key to actually auto increment keeping in mind that the table has nearly 34,000 entries in it.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  2. #2
    Join Date
    May 2013
    Location
    Kansas City
    Posts
    21
    That looks about right.
    Try "INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL"
    If that doesn't work, it's probably related to the way you're inserting. Every time I've ever had a problem like this with SQLite, it's been on the insert side. Make sure you're not inserting a null value into the ENTRY_ID field when you're dropping information into the table.

  3. #3
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,316
    I have this

    INSERT INTO visitors(REQUEST_TIME,REMOTE_ADDR, ...

    So it should autoincrement when a data set is pushed in to the table.

    Question is that now the table exists, any idea on how to make the table autoincrement with the proper int key value
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  4. #4
    Join Date
    May 2013
    Location
    Kansas City
    Posts
    21
    Does anything happen when you try to pull a field called row_id?
    Some versions of SQLite will create an extra column naturally.

    My gut feeling is that it's not auto incrementing, because the field can take nulls.
    You should be able to alter table, add NOT NULL to the field properties, which should fix your problem.

  5. #5
    Join Date
    Mar 2007
    Location
    localhost
    Posts
    2,316
    I get "No Results" issued by my script.

    SQLite seems to be a fickle beast, handy but required more coaxing than MySQL which I would have used but I needed this widget to be independent of reliance of MySQL hence the use of SQLite.

    After reading the SQLite ALTER TABLE directive, I think it would be easier to create a completely new table and copy across the entries one by one with the oldest first.
    Yes, I know I'm about as subtle as being hit by a bus..(\\.\ Aug08)
    Yep... I say it like I see it, even if it is like a baseball bat in the nutz... (\\.\ Aug08)
    I want to leave this world the same way I came into it, Screaming, Incontinent & No memory!
    I laughed that hard I burst my colostomy bag... (\\.\ May03)
    Life for some is like a car accident... Mine is like a motorway pile up...

    Problems with Vista? :: Getting Cryptic wid it. :: The 'C' word! :: Whois?

  6. #6
    Join Date
    Sep 2012
    Posts
    720
    Hi..Data (illustrative) in SQLite3. Have a few million rows, actually.

    a, aardvark
    a, anvil
    a, apple
    a, automaton
    b, apple
    b, peanut
    b, persimmon
    b, walnut
    Key for my data is actually multiple columns but here I've just used the first column.

    So, I'd like to find all the keys that are associated with apple, then use that to query all the other values associated with those keys.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles