can't figure out a simple query in C sharp

[westingtyler]

In a c sharp function I basically want to give the string for a character name and a word the player typed, and find (if it exists, if not null) the cell in the database, and save it to a string.
The manual and examples don't have a simple query like this, but instead have examples of gathering lots of things at once (Weapons), so I've tried to put it together but can't figure out what is wrong. I got this working in playmaker back in the day but can't figure it out in C sharp.


=======

public void QueryTheRequestedResponse()

{

bool recordExists;

CurrentResponseSTR = dbManagerU6.Query("SELECT Response FROM UltimaVIDialogue WHERE CharaName = '" + CurrentConversantNameSTR + "' AND Keyword = '" + CurrentQuerySTR + "'");

if (recordExists)

Debug.Log("record found." + CurrentResponseSTR);

else

//print the text "No record found." to the screen.

Debug.Log("No record found.");

}

----

but it gives the error: Assets\ConversationProcessor.cs(98,42): error CS0411: The type arguments for method 'SimpleSQLManager.Query<T>(string, params object[])' cannot be inferred from the usage. Try specifying the type arguments explicitly.


I don't really know coding and am coming from using Playmaker, so I cannot figure it out.

I wish the manual had just a thousand slightly different examples because someone like me, I can't figure out how to take a query for a list of objects, and convert it to a query for a single object, etc.

It seems like it wants me to make a class to put in the <> part, but I never had to make a custom class to use SimpleSQL with playmaker. I could just say, "query this database and save the results as a string. how can I do that?

[echo17]

Check out this FAQ: link

[westingtyler]

that's great! I scanned the FAQ but missed that one. it almost works. I keep getting an error.


SimpleSQL.SimpleDataTable dt = dbManagerU6.QueryGeneric("SELECT Response FROM UltimaVIDialogue WHERE CharaName = '" + CurrentConversantNameSTR + "' AND Keyword = '" + CurrentQuerySTR);









if (dt.rows.Count > 0)

{

if (dt.columns.Count > 0)

{

Debug.Log("Response found! : " + dt.rows[0][0].ToString());

}

else

{

Debug.Log("No columns found");

}

}

else

{

Debug.Log("Data table has no rows.");

}


keeps giving me the error

SQLiteException: unrecognized token: "'"
SimpleSQL.SQLite3.Prepare3 (System.IntPtr db, System.String query) (at <ea3ef99bdc20419285707375656552c3>:0)
SimpleSQL.SQLiteCommand.Prepare3 () (at <ea3ef99bdc20419285707375656552c3>:0)
SimpleSQL.SQLiteCommand.ExecuteQueryGeneric () (at <ea3ef99bdc20419285707375656552c3>:0)
SimpleSQL.SimpleSQLManager.QueryGeneric (System.String query, System.Object[] args) (at <ea3ef99bdc20419285707375656552c3>:0)
ConversationProcessor.QueryTheRequestedResponse () (at Assets/ConversationProcessor.cs:89)
ConversationProcessor.BuildTheQuery () (at Assets/ConversationProcessor.cs:77)
UnityEngine.Events.InvokableCall.Invoke () (at <5db060c2e1a7406683b7d65d782d6ade>:0)
UnityEngine.Events.UnityEvent.Invoke () (at <5db060c2e1a7406683b7d65d782d6ade>:0)
UnityEngine.UI.Button.Press () (at Library/PackageCache/com.unity.ugui@1.0.0/Runtime/UI/Core/Button.cs:70)
UnityEngine.UI.Button.OnPointerClick (UnityEngine.EventSystems.PointerEventData eventData) (at Library/PackageCache/com.unity.ugui@1.0.0/Runtime/UI/Core/Button.cs:114)
UnityEngine.EventSystems.ExecuteEvents.Execute (UnityEngine.EventSystems.IPointerClickHandler handler, UnityEngine.EventSystems.BaseEventData eventData) (at Library/PackageCache/com.unity.ugui@1.0.0/Runtime/EventSystem/ExecuteEvents.cs:57)
UnityEngine.EventSystems.ExecuteEvents.Execute[T] (UnityEngine.GameObject target, UnityEngine.EventSystems.BaseEventData eventData, UnityEngine.EventSystems.ExecuteEvents+EventFunction`1[T1] functor) (at Library/PackageCache/com.unity.ugui@1.0.0/Runtime/EventSystem/ExecuteEvents.cs:272)
UnityEngine.EventSystems.EventSystem:Update() (at Library/PackageCache/com.unity.ugui@1.0.0/Runtime/EventSystem/EventSystem.cs:501)


I also want to make sure this has an IFNULL condition. and I'm not sure how to get the final string, but I think FoundResponseSTR = dt.rows[0][0].ToString(); should do it, right?

[echo17]

You were close, just missing the final single quotation mark:

SimpleSQL.SimpleDataTable dt = dbManagerU6.QueryGeneric("SELECT Response FROM UltimaVIDialogue WHERE CharaName = '" + CurrentConversantNameSTR + "' AND Keyword = '" + CurrentQuerySTR + "'");

[echo17]

Dec 6, 2022 16:53:49 GMT westingtyler said:

I also want to make sure this has an IFNULL condition. and I'm not sure how to get the final string, but I think FoundResponseSTR = dt.rows[0][0].ToString(); should do it, right?

That should work since you are checking to make sure the rows and columns exist first. It might turn null values into a blank string, not sure about the C# ToString() method and how it handles null objects.

[westingtyler]

ah thanks it is starting to work. can you explain the purpose of the '" and the "'" things? Also, now I get the error that there is no column called John:

SimpleSQL.SimpleDataTable dt = dbManagerU6.QueryGeneric("SELECT Response FROM UltimaVIDialogue WHERE CharaName = " + CurrentConversantNameSTR + " AND Keyword = " + CurrentQuerySTR+ "'");

But the thing is, I am trying to get the COLUMN named "Response" WHERE the COLUMN named CharaName has "John" AND the COLUMN named KEYWORD has "introduction". I'm sure there's some format thing I'm missing? So the sql table actually looks like
CharaName |  Keyword | Response
John | introduction | Hello and welcome to my store!

And also, what is the best way to prevent an error if it asks for a column or row that does not exist? Since the user will be able to edit these sqls, I don't want their game to crash each time they make an error.

[echo17]

Dec 6, 2022 20:54:53 GMT westingtyler said:

ah thanks it is starting to work. can you explain the purpose of the '" and the "'" things?

Inside a sql statement, you have to wrap your strings with single quotes. In your statement, the final concatenation once the variables are processed by the compiler will look like:


SELECT Response FROM UltimaVIDialogue WHERE CharaName = 'John' AND Keyword = 'Some keyword'

Dec 6, 2022 20:54:53 GMT westingtyler said:

Also, now I get the error that there is no column called John:

SimpleSQL.SimpleDataTable dt = dbManagerU6.QueryGeneric("SELECT Response FROM UltimaVIDialogue WHERE CharaName = " + CurrentConversantNameSTR + " AND Keyword = " + CurrentQuerySTR+ "'");

You left out the other single quotes that you had in there before. They all need to be there for the statement to be valid:

SimpleSQL.SimpleDataTable dt = dbManagerU6.QueryGeneric("SELECT Response FROM UltimaVIDialogue WHERE CharaName = '" + CurrentConversantNameSTR + "' AND Keyword = '" + CurrentQuerySTR + "'");

Dec 6, 2022 20:54:53 GMT westingtyler said:

And also, what is the best way to prevent an error if it asks for a column or row that does not exist? Since the user will be able to edit these sqls, I don't want their game to crash each time they make an error.

You can handle these exceptions in your logic, something like (the section you are interested in is where the comment "do something here if no records are found"):


if (dt.rows.Count > 0)
{
   if (dt.columns.Count > 0)
   {
       Debug.Log("Response found! : " + dt.rows[0][0].ToString());
   }
   else
   {
       Debug.Log("No columns found")
   }
}
else
{
   // do something here if no records were found
}



What you do there is entirely up to you and your project design. It might be an alert or an image that tells the customer that no data was found for the given parameters.

[westingtyler]

Everything is working well now. But now I cannot get my retrieval function to work:

public void RetrieveDiscoveredKeywords()

{

//do a sql query to find all FullKeyword cells in the DiscoveredResponses table, where CharaName = CurrentConversantNameSTR, and add these into the DiscoveredEntityKeywordsLIST:

DiscoveredEntityKeywordsLIST.Clear();

SimpleSQL.SimpleDataTable dt = DBManagerU6sqlM.QueryGeneric("SELECT FullKeyword FROM DiscoveredResponses WHERE CharaName = '" + CurrentConversantNameSTR + "'");

foreach (var row in dt.Rows)

{

DiscoveredEntityKeywordsLIST.Add(row["FullKeyword"].ToString());

}



RefreshKeywords();

}

===

In Unity the error says "Assets\ConversationProcessor.cs(860,51): error CS1061: 'SimpleDataTable' does not contain a definition for 'Rows' and no accessible extension method 'Rows' accepting a first argument of type 'SimpleDataTable' could be found (are you missing a using directive or an assembly reference?)


"

but how can that be? how can it not understand what a row is? I'm sure I'm just using some weird syntax error, but I can't figure it out. (I couldn't find anywhere in the FAQ or guide, an example of how to get all rows with specific parameters, like this.

[echo17]

the rows member of SimpleDataTable is lowercase (you are using upper case), so your line should read:


foreach (var row in dt.rows)


Please check out the code for more information