Announcement

Collapse
No announcement yet.

SQLite Plugin Question

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite Plugin Question

    I am trying to find out if a field exist in a table. Is there a simple way to do this ?

    I am using reteset's plugin v3.7.2
    The Ultimate Campground Log, My AMS8 Program
    http://www.theultimatecampgroundlog.com/

  • #2
    here is a helper function to check whether a column exist in a table

    Code:
    function DoesColumnExist(db,strTable,strColumn)
    
            local q = string.format("SELECT %s FROM %s",strColumn,strTable)
    		local stm = db:prepare(q)
    		local bret = false
    		
    		if stm then
                
                if(stm:step() ) then
    			   bret = true
    		    end
    		    
    			stm:finalize()
    	   end
    	   return bret
    end
    and this is the usage of function :

    Code:
    local db = sqlite3.open_memory()
    
    db:exec[[
      CREATE TABLE test (id INTEGER PRIMARY KEY, content TEXT);
      INSERT IGNORE INTO test VALUES (NULL, 'Hello World');
    ]]
    
    
    local tblName = "test"
    local clmnName = "content"
    
    if (DoesColumnExist(db,tblName,clmnName)) then   
         Dialog.Message("Notice", "The Column : "..clmnName.." Does Exist In Table : "..tblName);
    else
       Dialog.Message("Notice", "The Column : "..clmnName.." Does Not Exist In Table : "..tblName);
    end
    
    db:close()
    you can also do similar queries over a special table sqlite_master that contains all tables and column names in a database , but it may be complex for you

    so creating a statement with a desired query and checking for the error would do the job

    for example "SELECT columnname-to-check-existance FROM table-to-be-looked-in"
    if this query returns an error , it is probably there is no such column or table
    amsplugins.com Is Closed.

    Facebook Page

    Comment


    • #3
      Thanks, reteset.

      I was busting my head trying to figure that out. Your code worked very well.

      I now check for the field and if not exist I creat it. I did figure out adding a field to a table,
      just had a time checking if it existed or not.

      Thanks again. I really appreciate the help.
      Last edited by Jerryab; 12-05-2010, 02:42 PM.
      The Ultimate Campground Log, My AMS8 Program
      http://www.theultimatecampgroundlog.com/

      Comment


      • #4
        Hi.. anyone can help me using SQLite3 to add a new column in existing Table?

        Comment


        • #5
          Hi telco ,
          Don't know why you would want to add a col to existing table in DB as all inputs would just be NULL, unless you recode to get new values to and from new column
          Easier to create new table

          But here you go with this DEMO , check my avatar to get code
          Customise DB Sqlite3.zip
          Cheers

          Comment


          • #6
            can you share the code my friend?

            my main problem is i already created the db and thinking what if later i would like to add new column, i dont want to re create the database.. so creating new column is the solution i am thinking.. t

            thank you

            Comment


            • #7
              I did

              But here you go with this DEMO , check my avatar to get code
              Click image for larger version

Name:	Instruction help.png
Views:	62
Size:	143.0 KB
ID:	308551
              Cheers

              Comment


              • #8
                Originally posted by telco View Post
                can you share the code my friend?

                my main problem is i already created the db and thinking what if later i would like to add new column, i dont want to re create the database.. so creating new column is the solution i am thinking.. t

                thank you
                this is very useful for altering databases.

                https://sqlitebrowser.org/

                Comment

                Working...
                X