locked
how to use parameters with this statement? RRS feed

  • Question

  • User-1780802465 posted

    Hi guys

    I'm not a sql guru and wondered how would i use parameters with this statement? I'm selecting the data from another table to put in a virtual one. Problem is that someone might use a ? or special character.

    sql = "Insert INTO tblHALv2001(question,answer,action,keywordPriority,keyword,keyword2,attach) SELECT question,answer,action,keywordPriority,keyword,keyword2,attach FROM tblHAL";
    cmd = new SQLiteCommand(sql, m_dbConnection);
    cmd.ExecuteNonQuery();

    thanks

    Wednesday, September 27, 2017 3:00 PM

Answers

  • User753101303 posted

    Yes it happens at the reader so this is actually :

    "Select * from tblHALv2001 WHERE tblHALv2001 MATCH '" + question + " * '"

    Here the question mark is not supposed to be a parameter and it must conform to whatever syntax is accepted by the MATCH operand. It seems they tell that ? is ignored anyway for a full text search so you could likely just remove this character (and possibly others).

    Check the doc to see what MATCH expects.

    Edit: try perhaps https://www.sqlite.org/fts5.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 3, 2017 8:18 AM

All replies

  • User753101303 posted

    Hi,

    And the parameter will be used for doing what? It is not a problem. Having a ? in a literal value or in a column is not the same than having a ? placeholder directly in a statement.

    With:

    Insert INTO tblHALv2001(question,answer,action,keywordPriority,keyword,keyword2,attach,Comment)
    SELECT question,answer,action,keywordPriority,keyword,keyword2,attach,'will it work ?'
    FROM tblHAL
    WHERE MyColumn=?
    

    you have a single parameter at the very end. The other ? character is just part of a literal string.

    The basic idea is that using a ? placeholder doesn't work with just a search/replace. It is really a parameter and in particular it can be used only where you could have a literal value (for exemple you can't use a parameter to change the table name in a FROM clause).

    Edit: no real experience with SQLite but parameters should work roughly the same in all databases though the syntax can vary, the same rules applies...

    Wednesday, September 27, 2017 3:19 PM
  • User-1780802465 posted

    when i do the insert it fails because the value being added had a ? mark in it. For example the value was where is my car?

    I used parameters to add the value to the main database which is fine but when adding it to the virtual database using the statement above it falls over. I was not sure how to insert the data using a select statement with parameters.

    Here is how i do an insert into the main database (Without the select statement)

    protected void InsertRecordSQLDB(String question)
        {
            String ConnString = GetConnectSQLite();
            String requestDate = DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss");
            try
            {
            
    
                using (SQLiteConnection m_dbConnection = new SQLiteConnection(ConnString))
                {
                    //sql statement bound to a string which will be used next
                    String sql = "INSERT INTO tblHAL(question) VALUES (?)";
    
    
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, m_dbConnection))
                    {
                        //Parameters for the database to write
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("question", question);
    
                        //Open the DB connection and execute the query
                        m_dbConnection.Open();
                        cmd.ExecuteNonQuery();
    
                    }
                }
            }

    Wednesday, September 27, 2017 4:23 PM
  • User753101303 posted

    Hi,

    So you mean that

    cmd.Parameters.AddWithValue("question", "Where is my car?"); would fail because a "?" character is passed as part of the literal string? If would find this very surprising.

    Which error message do you have?

    Wednesday, September 27, 2017 4:33 PM
  • User-1780802465 posted

    SQL logic error or missing database
    fts5: syntax error near "?"

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SQLite.SQLiteException: SQL logic error or missing database
    fts5: syntax error near "?"

    if i use any punctuation it fails with the error above.

    This is why I am wondering if I need to use parameters.. I suppose I could remove any punctuation before inserting any record into the database. Not ideal though.

    Wednesday, September 27, 2017 4:37 PM
  • User753101303 posted

    Sure not. It would be just stupid if really working this way. Does  a SELECT work ? I tried :

      cmd.CommandText="INSERT INTO Test(Data) VALUES(?)";
      cmd.Parameters.AddWithValue("Data", "Does it work?");
      cmd.ExecuteNonQuery();

    and it works without any problem. I suspect your issue is caused by something else (is the connection string correct ? This is why I'm asking to try a SELECT).

    Wednesday, September 27, 2017 4:51 PM
  • User-1780802465 posted

    i know its stupid!

    it seems to be the statement which works fine as long as I don't use punctuation. It's quite annoying and I can't figure out how to fix it.

    Wednesday, September 27, 2017 4:57 PM
  • User-1780802465 posted

    code here

    //Insert record into HALs Memory
        protected void InsertRecordSQLDB(String question)
        {
            String ConnString = GetConnectSQLite();
            String requestDate = DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss");
            try
            {
                
    
                using (SQLiteConnection m_dbConnection = new SQLiteConnection(ConnString))
                {
                    //sql statement bound to a string which will be used next
                    String sql = "INSERT INTO tblHAL(question) VALUES (?)";
    
    
                    using (SQLiteCommand cmd = new SQLiteCommand(sql, m_dbConnection))
                    {
                        //Parameters for the database to write
                        cmd.CommandType = CommandType.Text;
                        cmd.Parameters.AddWithValue("question", question);
    
                        //Open the DB connection and execute the query
                        m_dbConnection.Open();
                        cmd.ExecuteNonQuery();
    
                        //Close the DB connection
                        m_dbConnection.Close();
                    }
                }
            }
    
            catch (Exception ex)
            {
                lblError1.Text = "There was a problem with the database connection somewhere! If that helps :)";
            }
    
            finally
            {
                //Replicate changes to HALs main database to the Virtual database.
                replicateVirtualDB();
            }
        }
    
    
    
     //This method will drop the existing virtual table and update it with the latest information from HALs Database.
        protected void replicateVirtualDB()
        {
            String ConnString = GetConnectSQLite();
            String requestDate = DateTime.Now.ToString("yyyy'-'MM'-'dd HH:mm:ss");
            String sql = "";
    
            try
            {
                using (SQLiteConnection m_dbConnection = new SQLiteConnection(ConnString))
                {
                    SQLiteCommand cmd = new SQLiteCommand(sql, m_dbConnection);
    
                    //Open the database connection
                    m_dbConnection.Open();
    
                    //Enable advanced extensions for sqlite
                    m_dbConnection.EnableExtensions(true);
                    m_dbConnection.LoadExtension("System.Data.SQLite.dll", "sqlite3_fts5_init");
    
                    //Drop the table --- Might need to change this in the future.
                    sql = "DROP TABLE tblHALv2001";
                    cmd = new SQLiteCommand(sql, m_dbConnection);
                    cmd.ExecuteNonQuery();  //Execute Query
    
                    //Create a new Virtual table --- Might need to change this in the future.
                    sql = "create VIRTUAL TABLE tblHALv2001 USING fts5(question,answer,action,keywordPriority,keyword,keyword2,attach)";
                    cmd = new SQLiteCommand(sql, m_dbConnection);
                    cmd.ExecuteNonQuery();
    
                    //Insert the updated data from HALs main database into the Virtual database --- Might need to look at Update instead of insert.
                    sql = "Insert INTO tblHALv2001(question,answer,action,keywordPriority,keyword,keyword2,attach) SELECT question,answer,action,keywordPriority,keyword,keyword2,attach FROM tblHAL";
                    cmd = new SQLiteCommand(sql, m_dbConnection);
                    cmd.ExecuteNonQuery();
                }
            }
    
            finally
            {
                //Do something here after record has been added 
            }
        }

    Wednesday, September 27, 2017 4:58 PM
  • User753101303 posted

    Is this REALLY the code that fails and on which line exactly does it fail? It works fine here.

    If not please show the code that actually fails (and mention on which exact line please).

    Wednesday, September 27, 2017 5:46 PM
  • User-1780802465 posted

    SQL logic error or missing database
    fts5: syntax error near "?"

    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

    Exception Details: System.Data.SQLite.SQLiteException: SQL logic error or missing database
    fts5: syntax error near "?"

    Source Error: 

    Line 222:            using (SQLiteCommand cmd = new SQLiteCommand(sql, m_dbConnection))
    Line 223:            {
    Line 224:                using (SQLiteDataReader dr = cmd.ExecuteReader())
    Line 225:                {
    Line 226:                    while (dr.Read())


    Source File: c:\BA_Intranet\Net\ProjectHal2000\hal.aspx.cs    Line: 224 

    Stack Trace: 

    [SQLiteException (0x1): SQL logic error or missing database
    fts5: syntax error near "?"]
       System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt) +476
       System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt) +232
       System.Data.SQLite.SQLiteDataReader.NextResult() +448
       System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave) +321
       System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior) +60
       _HAL.SQLreader(String question) in c:\BA_Intranet\Net\ProjectHal2000\hal.aspx.cs:224
       _HAL.submitRequest(Object sender, EventArgs e) in c:\BA_Intranet\Net\ProjectHal2000\hal.aspx.cs:86
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +11773973
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +150
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5062
    Monday, October 2, 2017 8:45 AM
  • User753101303 posted

    Ah. It happens at ExecuteReader and this is the first time ever we see that statement in the code you posted. So could it be that for now you shown the wrong code?

    Which value do you you have in the sql variable when it happens ? I believe it happens in a SQL statement you never yet shown to us...

    Monday, October 2, 2017 11:02 AM
  • User-1780802465 posted

    I believe it happens here:

    "Insert INTO tblHALv2001(question,answer,action,keywordPriority,keyword,keyword2,attach) SELECT question,answer,action,keywordPriority,keyword,keyword2,attach FROM tblHAL";
    Monday, October 2, 2017 11:52 AM
  • User-1780802465 posted

    here is the reader:

     //This method will search the virtual database for a match to the question asked.
        protected void SQLreader(String question)
        {
    
            //Set up required variables
            String ConnString = GetConnectSQLite();
            String issue = "";
            String location = "";
            String notes = "";
            String email = "";
            String answer1 = "";
            String answer2 = "";
            String answer3 = "";
            String answer4 = "";
            String action1 = "";
            String action2 = "";
            String action3 = "";
            String action4 = "";
            
            //i is used to check if any results were returned. If there were no results, i will stay at 0.
            int i = 0;
    
            using (SQLiteConnection m_dbConnection = new SQLiteConnection(ConnString))
            {
                String sql = "Select * from tblHALv2001 WHERE tblHALv2001 MATCH '" + question + " * '";
                m_dbConnection.Open();
    
                //Enable sqlite advanced extensions.
                m_dbConnection.EnableExtensions(true);
                m_dbConnection.LoadExtension("System.Data.SQLite.dll", "sqlite3_fts5_init");
    
                using (SQLiteCommand cmd = new SQLiteCommand(sql, m_dbConnection))
                {
                    using (SQLiteDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            issue = Convert.ToString(dr["action"]);
                            i++;
                        }
    
                    }
                }
            }
    
            //If HAL does not have an answer, record the question in HALs memory bank for processing.
            if (i == 0)
            {
                lblHAL.Text = "I'm sorry Dave I do not have the answer to that question. Ask the same question again in a week and I might have an answer for you";
                InsertRecordSQLDB(question);
            }
    
    
        }



    Monday, October 2, 2017 11:54 AM
  • User-1780802465 posted

    so the sqlreader takes the question "where is my car?"

    and then it falls over in the reader (only if I have a ? or any other punctuation in the question variable)

    any idea?

    Monday, October 2, 2017 12:00 PM
  • User-1780802465 posted

    I asked at Stack overflow and I think the answer they gave is probably correct.

    https://stackoverflow.com/questions/46525854/sqlite3-fts5-error-when-using-punctuation/46529832#46529832

    Tuesday, October 3, 2017 7:56 AM
  • User753101303 posted

    Yes it happens at the reader so this is actually :

    "Select * from tblHALv2001 WHERE tblHALv2001 MATCH '" + question + " * '"

    Here the question mark is not supposed to be a parameter and it must conform to whatever syntax is accepted by the MATCH operand. It seems they tell that ? is ignored anyway for a full text search so you could likely just remove this character (and possibly others).

    Check the doc to see what MATCH expects.

    Edit: try perhaps https://www.sqlite.org/fts5.html

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, October 3, 2017 8:18 AM