Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Nov 14, 2018 11:50 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
 Post subject: Prepared SQL Statement Question
PostPosted: Mon Mar 25, 2013 12:45 pm 
Offline

Joined: Tue Mar 05, 2013 2:33 pm
Posts: 64
Location: Greensboro, North Carolina
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 R2.1 Enterprise, Mac OS, Windows, Ubuntu Linux


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Mon Mar 25, 2013 1:30 pm 
Offline
Real Software Engineer

Joined: Sat Dec 24, 2005 8:18 pm
Posts: 7858
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)


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Mon Mar 25, 2013 1:50 pm 
Offline

Joined: Tue Mar 05, 2013 2:33 pm
Posts: 64
Location: Greensboro, North Carolina
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 R2.1 Enterprise, Mac OS, Windows, Ubuntu Linux


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Mon Mar 25, 2013 1:52 pm 
Offline

Joined: Tue Mar 05, 2013 2:33 pm
Posts: 64
Location: Greensboro, North Carolina
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 R2.1 Enterprise, Mac OS, Windows, Ubuntu Linux


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Mon Mar 25, 2013 2:26 pm 
Offline

Joined: Tue Mar 05, 2013 2:33 pm
Posts: 64
Location: Greensboro, North Carolina
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 R2.1 Enterprise, Mac OS, Windows, Ubuntu Linux


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Tue Mar 26, 2013 11:23 pm 
Offline
Site Admin
User avatar

Joined: Tue May 06, 2008 1:07 pm
Posts: 1464
Location: NotEvenOnTheMap, CT
By the way, this really isn't critical, but I saw a small tweak I'd make to the code and figured I'd share.

There is no reason to repeatedly call BindType. You can do that at the beginning of the loop. Next, you can call SQLExecute with the value(s) to be bound, so you can eliminate the Bind call as well. You should also explicitly start a transaction, so the framework does not have to guess. Lastly, you're probably committing too frequently, wrapping all the inserts in one transaction should yield you better performance. Like this:
prepInsert.BindType(0,REALSQLPreparedStatement.SQLITE_TEXT)
mydb.SQLExecute("BEGIN TRANSACTION")
for i = UBound(MatchedSearch)
prepInsert.SQLExecute(MatchedSearch(i))
next
mydb.commit

_________________
Thom McGrath - @tekcor
Web Framework Architect, Real Software, Inc.


Top
 Profile  
Reply with quote  
 Post subject: Re: Prepared SQL Statement Question
PostPosted: Thu Mar 28, 2013 9:54 pm 
Offline

Joined: Tue Mar 05, 2013 2:33 pm
Posts: 64
Location: Greensboro, North Carolina
Thom McGrath wrote:
By the way, this really isn't critical, but I saw a small tweak I'd make to the code and figured I'd share.

There is no reason to repeatedly call BindType. You can do that at the beginning of the loop. Next, you can call SQLExecute with the value(s) to be bound, so you can eliminate the Bind call as well. You should also explicitly start a transaction, so the framework does not have to guess. Lastly, you're probably committing too frequently, wrapping all the inserts in one transaction should yield you better performance. Like this:
prepInsert.BindType(0,REALSQLPreparedStatement.SQLITE_TEXT)
mydb.SQLExecute("BEGIN TRANSACTION")
for i = UBound(MatchedSearch)
prepInsert.SQLExecute(MatchedSearch(i))
next
mydb.commit


Thom thank you much buddy.

_________________
Mike Cotrone - CCIE #8411 R&S, CCIE #8411 Voice
Chief Technology Officer
Onepath Systems, LLC


Real Studio 2012 R2.1 Enterprise, Mac OS, Windows, Ubuntu Linux


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group