Database input datetime

Question

I'm building an app and ran into an issue. In all the other tables I've used strings. In this one, I'm using datetime for id. string GigID=System.DateTime.Now.ToString("MM/dd/yyyy"); I'm using the SQLIter asset pack from the assetstore. Thanks in advance!

While it seemed to work, I cannot enter anything into the SQLite database(was able to get one in, not sure how) and when I try to display them, I get an error Format Exception on the DATETIME. Table was set up thusly mSQLString = "CREATE TABLE IF NOT EXISTS " + SQL_TABLE_NAME + " (" + COL_TN_ID + " DATE PRIMARY KEY, " + COL_TN_NAME + " TEXT," + COL_TN_ADDRESS + " TEXT," + COL_TN_DATE + " TEXT," + COL_TN_TIME + " TEXT)";

Form: public void Form() { string ID=System.DateTime.Now.ToString("MM/dd/yyyy"); GUI.Label (new Rect (Screen.width/2, 10, 700, 50), "Add-a-Tron!"); GUI.Label (new Rect (10, 30, 700, 50), "Enter the name of the event" ); Name=GUI.TextField(new Rect(155, 50, 100, 25), Name); GUI.Label (new Rect (10, 70, 700, 50), "Enter the Address."); Address=GUI.TextField(new Rect(155, 90, 100, 25), Address); GUI.Label (new Rect (10, 110, 700, 50), "Enter the date of the event" ); Date=GUI.TextField(new Rect(155, 120, 100, 25), Date); GUI.Label (new Rect (10, 130, 700, 50), "Enter the time of the event" ); Time=GUI.TextField(new Rect(155, 140, 110, 25), Time); if(GUILayout.Button( "Add Gig")) { Add(ID, Name, Address, Date, Time);

             }
         }

Add to the Database: (All other functions have worked to put stuff in the database) public void Add(string ID, string Name, string Address, string Date, string Time) { Debug.Log(ID + " "+ Name + " " + Address + " " + Date + " " +Time);

             mSQLString = "INSERT OR REPLACE INTO " + SQL_TABLE_NAME
                 + " ("
                     + COL_TN_ID + ","
                     + COL_TN_NAME + ","
                     + COL_TN_ADDRESS + ","
                     + COL_TN_DATE + ","
                     + COL_TN_TIME
                     + ") VALUES ("
                     + "'" + ID + "',"  // note that string values need quote or double-quote delimiters
                     + "'" + Name + "',"
                     + "'" + Address + "',"
                     + "'" + Date + "',"
                     + "'" + Time +"');";
             
             Debug.Log(mSQLString + "Added!");
             ExecuteNonQuery(mSQLString);
         }

To display them onscreen, I made a prefab and hooked it up. Here are the functions that handle that. I did put in a test entry using the newest DB Browser for SQLite:

 public void GetAll()
     {
         Listname.Clear();
         mConnection.Open();
         mSQLString = "SELECT * FROM " + SQL_TABLE_NAME; 
         mCommand.CommandText = mSQLString;
         mCommand.ExecuteNonQuery();
         mReader = mCommand.ExecuteReader();
         
         while (mReader.Read())
         {
             listname.Add(new listname(mReader.GetString(0), 
                               mReader.GetString(1), 
                               mReader.GetString(2), 
                               mReader.GetString(3), 
                               mReader.GetString(4)));
 
             //Debug.Log(mReader.GetDateTime(0) + mReader.GetString(1) +mReader.GetString(2) + mReader.GetString(3) + mReader.GetDateTime(4) );
         }

Finally...

 public void ShowAll()
     {    Debug.Log(listname.Count);
         for(int i = 0; i< listname.Count; i++)
         {
             GameObject giObj = Instantiate(thePrefab);
             listname tmpVar = listname[i];
             giObj.GetComponent<theScript>().Displaylistnames(tmplistname.ID, tmplistname.Name, tmplistname.Address, tmplistname.Date, tmplistname.TIme);
             giObj.transform.SetParent(Equipparent); 
             
         }
 
     }

Answer 1

Best Answer

Answer by phil_me_up · Jan 07, 2016 at 09:52 PM

I've not read all your code, but I expect the problem is that you're trying to use a DateTime as a primary key. In SQL terms, the primary key must be unique and a datetime (especially one restricted to just the year, month and day) will only be unique if you only expect one entry per day. I guess this is how you managed to enter one row.

You might be able to get away with including hours, minutes and seconds to form the id but it's probably not a good idea as two rows within the same second will cause issues. Also, you generally shouldn't be using a string for your ID as it won't be efficient when querying the DB.

So, in short, make sure your Primary key is an ID or something that is guaranteed to be unique. Either a simple integer or if supported, a GUID. This can be auto generated for you by your database when setup properly and inserting a new row.

Add comment · Share

Answer 2

Answer by Gilead7 · Jan 09, 2016 at 05:48 PM

I suddenly realized after posting this that I had moved the project to another drive and had been looking at the database from the original! D'OH! Things had been saved after all! I think I will switch over to an integer id instead. Should I use AUTOINCREMENT after primary key specificity? There had been some controversy about using it.

If anyone else has ideas or suggestions, please let me know. Thanks!

Add comment · Show 1 · Share

phil_me_up · Jan 09, 2016 at 06:07 PM 0

Share

The autoincrement issue is a divisive one.

If you can use another, useful ID that you know to be unique (like a user-id from some other service), then it's ok and there is no need to use AUTOINCRE$$anonymous$$ENT. However, if you don't have such a key, or if there is any chance you'll be expecting duplicate entries then just use an autoincremented id (or an auto-created GUID if available).

Remember that it's often advantageous to mark other columns as indexes to help with queries etc too (i.e if you have row_id as a primary id, a user_id could be an index if you expect to see this user id more than once in the table). This will help you do queries like 'select x, y, z where user_id = 123'

Database input datetime

2 Replies

Your answer

Follow this Question

Related Questions