Results 1 to 4 of 4
  1. DrewPre's Avatar
    Posts
    818 Posts
    Global Posts
    829 Global Posts
       #1  
    Below is the sql statements that I use in my app to create a database....

    snippet#1) Have I correctly defined the field 'job_no' in the Create table statement. I need/want it to be an integer so I can sort by those integers.

    snippet#2.) Is my query statement formatted properly to return results in proper numerical [not alphabetical] order? [e.g. 1, 2, 3, ...10 - NOT - 1, 10, 2, 3, ....]

    snippet#1
    Code:
    CREATE TABLE IF NOT EXISTS "backup" 
          ( 
                 job_name TEXT NOT NULL DEFAULT "nothing" 
                 job_no INTEGER NOT NULL DEFAULT "nothing", '
                 date TEXT NOT NULL DEFAULT "nothing"
                 flags TEXT NOT NULL DEFAULT "nothing"
                 file_name TEXT NOT NULL DEFAULT "nothing" 
          ); 
          GO;
    snippet#2
    Code:
    SELECT * from backup ORDER BY job_no;

    Palm Pre Backup Utility...done!
    Locate Pre....done!
  2. DrewPre's Avatar
    Posts
    818 Posts
    Global Posts
    829 Global Posts
       #2  
    think I figured it out.

    I use NUMERIC not INTEGER

    That seems to work... I think.

    Palm Pre Backup Utility...done!
    Locate Pre....done!
  3. #3  
    That is correct, I'm unsure what that floating symbol is past job_no, and you might get an error with the "nothing" on the int field.

    My sqlite is a little rusty, but with all sql there is some recommendations and best practices:

    You should rarely use a Text datatype, instead use a varchar for variable length fields under 100 characters. Additionally, "nothing" is not a good value for null, Null should be used instead, and currently defeats the purpose of having not null fields.

    Additionally your database appears to not be normalized, for instance your flags field, do you envision something along the lines of 'Y,N,N,N,Y' or 'Completed=Y,Finalized=N', in which case instead of doing a parse of the values it would be more beneficial to create a second or third table such as JobsFlag(flagID, job_no, value), and Flag(flagID, description), or possible just Flag(job_no, name, value) *though this could cause duplicate names to appear

    Of course any improvement in the database might not matter for a small project, but it allows an easy way to add fields and values as needed if a table is fully naturalized and allows a project to grow as additional features are required or desired.

    To keep it simpey though I would suggest you to consider the table instead:
    Job(jobId "int, primary key" , jobName "varchar(30) not null", date "Date not Null", flags "not sure about this one probably nullable", fileName varchar(40) not null)

    It describes what your doing, has a reference id, uses reasonable types, and all fields are required, as it doesn't make sense for there to be a custom null on every type (except maybe flags and date if sqlite allows date.now)

    From this point you'd be able to order it as your second snippet.

    One thing I do remember about Sqlite is it is very forgiving and simply stores everything as text (or used to until the latest updates) so it allows mistakes, the problem is when you attempt to work with them. For instance, sorting on a date field when it thinks it is a text field will not provide to the results you were hoping for.
  4. DrewPre's Avatar
    Posts
    818 Posts
    Global Posts
    829 Global Posts
       #4  
    'flags' is just going to be a single character or at most a four character text field having some permutation of the letters P B A & M. a possible value might be P, PB, or PBAM without quotes.

    my application references the field names extensively so it is important that the field names remain the same. when it comes to sql statements, I am a novice. I don't know how to take what you've presented [conceptually] and apply it to my app's sql statement. Without changing the field names how would you 'normalize' my OP's SQL statement?

    I am extremely clueless when it comes to SQL and it's statements.

    PS - yes, that floating symbol has been dealt with by a couple of guys named guido and nick!

    Palm Pre Backup Utility...done!
    Locate Pre....done!

Posting Permissions