New topic: Prepared SQL Statement Question
<http://forums.realsoftware.com/viewtopic.php?t=47399> Page 1 of 1 [ 5 posts ] Previous topic | Next topic Author Message mikecotrone Post subject: Prepared SQL Statement QuestionPosted: Mon Mar 25, 2013 12:45 pm Joined: Tue Mar 05, 2013 2:33 pm Posts: 19 Hi, This is my first stab at a prepared SQL statement. I was hoping you could lead me into the right direction. Basically i am parsing a file using a regex and populating an array. Then I want to insert each element array into my DB. Everything is working up to the DB Insert in which I am using a Prepared SQL Statement. I am not 100% sure I need to use the Prepared SQL statement so if I am not using it right please tell me The problem I have is that no data is ever written to my table, however the correct data is written to my Textarea. Sub SQLInsertCDPdb mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if // Set SQL String dim sql as string = "insert into MyDBName (DeviceName) values (?)" dim prepInsert as REALSQLPreparedStatement = mydb.Prepare(sql) // Set Bind Mappings for DB fields const DeviceName = 0 // const Platform = 1 // const IPAddress = 2 // const LocalIntf = 3 // const NeighborIntf = 4 // const Capabilities = 5 // Look into the Array and write each element to the DeviceName Field for i = 0 to UBound(MatchedSearch) Debugging.TextArea1.AppendText "Array Element #: " + str(i) + " " + MatchedSearch(i) + EndOfLine prepInsert.BindType(0, REALSQLPreparedStatement.SQLITE_TEXT) prepInsert.Bind(0, MatchedSearch(i)) prepInsert.SQLExecute mydb.commit next Debugging.TextArea1.AppendText "Data Has been Written to DB" + EndOfLine End Sub The above code does not enter anything into my DB Table. The following is a previous Method that establishes my Table. Sub CreateDBSchema // Create Schema mydb.SQLExecute("create table CDPInfo (DeviceName varchar, idfield integer NOT NULL PRIMARY KEY AUTOINCREMENT)") if Not IsError then mydb.Commit Return True else SQLInsertCDPdb Return False end if End Sub _________________ Mike Cotrone - CCIE #8411 R&S, CCIE #8411 Voice Chief Technology Officer Onepath Systems, LLC Real Studio 2012 Mac OS, Windows, Ubuntu Linux Top npalardy Post subject: Re: Prepared SQL Statement QuestionPosted: Mon Mar 25, 2013 1:30 pm Real Software Engineer Joined: Sat Dec 24, 2005 8:18 pm Posts: 7770 Location: Canada, Alberta, Near Red Deer mikecotrone wrote:Sub SQLInsertCDPdb mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if This happens to create a NEW in memory db every time I suspect what you really want is to create it once and reuse the same one after that Sub SQLInsertCDPdb if myDB = nil then mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if end if _________________ Norman Palardy (Real Software) ââââââââââââââââââââââââââââââââââââââââââââââââââ Real Studio Developer Conference - April 23 - 26, 2013 http://www.realsoftware.com/community/realworld.php Top mikecotrone Post subject: Re: Prepared SQL Statement QuestionPosted: Mon Mar 25, 2013 1:50 pm Joined: Tue Mar 05, 2013 2:33 pm Posts: 19 npalardy wrote:mikecotrone wrote:Sub SQLInsertCDPdb mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if This happens to create a NEW in memory db every time I suspect what you really want is to create it once and reuse the same one after that Sub SQLInsertCDPdb if myDB = nil then mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if end if Thanks! I will give that a try. BTW I used to Live in Olds, AB a long time ago We spent alot of time in Red Deer _________________ Mike Cotrone - CCIE #8411 R&S, CCIE #8411 Voice Chief Technology Officer Onepath Systems, LLC Real Studio 2012 Mac OS, Windows, Ubuntu Linux Top mikecotrone Post subject: Re: Prepared SQL Statement QuestionPosted: Mon Mar 25, 2013 1:52 pm Joined: Tue Mar 05, 2013 2:33 pm Posts: 19 npalardy wrote:mikecotrone wrote:Sub SQLInsertCDPdb mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if This happens to create a NEW in memory db every time I suspect what you really want is to create it once and reuse the same one after that Sub SQLInsertCDPdb if myDB = nil then mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if end if I modified my code to the following, but it is still not inserting to the Database Table. However after debugging it I am receiveing this error message from the debugger "ErrorMessage : Unable to prepare statement" when it hits the mydb.commit statement. I am also getting "Can not commit - no transaction is active" error. I am sure how I am trying to use my Array is wrong, but I am still not sure the correct syntax. Thanks if myDB = nil then mydb = new REALSQLDatabase if not mydb.connect() then msgbox "The example could not create an in memory database and cannot be used without it. This software will exit gracefully." quit end if end if // Set SQL String dim sql as string = "insert into MyTable (DeviceName) values (?)" dim prepInsert as REALSQLPreparedStatement = mydb.Prepare(sql) // Set Bind Mappings for DB fields const DeviceName = 0 //const Platform = 1 //const IPAddress = 2 //const LocalIntf = 3 //const NeighborIntf = 4 //const Capabilities = 5 // Look into the Array and write each element to the DeviceName Field for i = 0 to UBound(MatchedSearch) Debugging.TextArea1.AppendText "Array Element #: " + str(i) + " " + MatchedSearch(i) + EndOfLine prepInsert.BindType(0, REALSQLPreparedStatement.SQLITE_TEXT) prepInsert.Bind(0, MatchedSearch(i)) prepInsert.SQLExecute mydb.commit next Debugging.TextArea1.AppendText "Data Has been Written to DB" + EndOfLine _________________ Mike Cotrone - CCIE #8411 R&S, CCIE #8411 Voice Chief Technology Officer Onepath Systems, LLC Real Studio 2012 Mac OS, Windows, Ubuntu Linux Top mikecotrone Post subject: Re: Prepared SQL Statement QuestionPosted: Mon Mar 25, 2013 2:26 pm Joined: Tue Mar 05, 2013 2:33 pm Posts: 19 Thanks again for all of your help. This ended up me having a really bad decision by trying to place a variable of my db table (mytable) in the insert string. I didn't realize it until now that the string is passed literally as a SQL statement All is good now that I put the name of my actual table in. Thank you again! _________________ Mike Cotrone - CCIE #8411 R&S, CCIE #8411 Voice Chief Technology Officer Onepath Systems, LLC Real Studio 2012 Mac OS, Windows, Ubuntu Linux Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 5 posts ]
-- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
