Struggling with SQLite

I'm trying to read in data from a database, I have a table "HighScores" with the columns "PlayerName" and "PlayerScore", the code I'm currently using is this..

Code (CSharp):

1. while (SQLReader.Read())
2. {
3.        
4.      Debug.Log(SQLReader.GetString(1)); //THIS IS THE PROBLEM, IT RETURNS COLUMN NAME. NOT DATA
5.      PlayerInformation nextClass = new PlayerInformation(SQLReader.GetString(1), SQLReader.GetInt32(2)); //Makes a new class and adds the score and values
6.      PlayerInformationList.Add(nextClass); //adds this class to the list
7.        
8. }

The problem is SQLReader.GetString(1) returns the name of the name of column 1 not it's contents. I'm not too familiar with SQL so this has me kind of stumped.

Also, I want to be able to read in data from every row, I though putting it into a while SQLReader.Read loop would do that, but now I'm not too sure..

Any thoughts?

Thanks,
Fred T.A

 

Your naming convention is confusing, since it's typical to have class names capitalized and local variables lower case. I would include the code above that to help with the context. It'd be good to show your SQL too. FYI, when you do GetString() or whatever, the first column starts with zero, in case you didn't know.

That said, I use Simple SQL, which makes working with SQLite very easy:
https://www.assetstore.unity3d.com/en/#!/content/3845

 

Ok, here's the entire method...

Code (CSharp):

1. PlayerInformation[] QueryDatabase()
2.     {
3.  
4.         SQLConnection.Open();
5.         IDbCommand SQLCommand = SQLConnection.CreateCommand();
6.  
7.         SQLCommand.CommandText = "SELECT 'PlayerName', 'Score' FROM HighScores";
8.         IDataReader SQLReader = SQLCommand.ExecuteReader();
9.  
10.         List<PlayerInformation> PlayerInformationList = new List<PlayerInformation>();
11.  
12.         while (SQLReader.Read())
13.         {
14.            
15.                 Debug.Log(SQLReader.GetString(1)); //THIS IS THE PROBLEM, IT RETURNS COLUMN NAME. NOT DATA
16.                 PlayerInformation nextClass = new PlayerInformation(SQLReader.GetString(1), SQLReader.GetInt32(2)); //Makes a new class and adds the score and values
17.                 PlayerInformationList.Add(nextClass); //adds this class to the list
18.            
19.         }
20.  
21.         SQLReader.Close();
22.         SQLConnection.Close();
23.         SQLCommand.Dispose();
24.  
25.          PlayerInformation[] PlayerInformationArray = new PlayerInformation[PlayerInformationList.Count];
26.         return PlayerInformationArray;
27.  
28.     }

And this is the PlayerInformation Class:

Code (CSharp):

1. public class PlayerInformation
2. {
3.     public string PlayerName;
4.     public int PlayerScore;
5.  
6.     public PlayerInformation(string playerNameToSet, int playerScoreToSet)
7.     {
8.         PlayerName = playerNameToSet;
9.         PlayerScore = playerScoreToSet;
10.     }
11. }

So what your saying is, the problem is that "0" refers to the title of the column, how can I miss out 0?

 

He's saying that when using GetString and the other Get* methods, the index starts with 0, not 1. GetString(1) will return the second column, not the first. You may very well already know this, but it's not clear and it's a common mistake so was worth mentioning. Still, that is unlikely to be the problem.

Your problem actually is the SQL:

Code (CSharp):

1. "SELECT 'PlayerName', 'Score' FROM HighScores";

You're passing quotes around PlayerName and Score. This makes them strings, so the result set will contain "PlayerName" and "Score" as values rather than the actual data in the columns. If you want to select the actual values then remove the quotes:

Code (CSharp):

1. "SELECT PlayerName, Score FROM HighScores";

And actually your GetString(1) and GetInt32(2) are incorrect with that SQL. Your first column would be GetString(0), and the second GetInt32(1).


Kudos for not doing "select * from...".

 

Yep, got it working now, thanks for the help