1. Beatrix Willius
  2. Valentina Database ADK
  3. Samstag, Dezember 30 2017, 02:13 PM
  4.  Abonnieren via E-Mail
Hi,

I'm converting all my SQL statements to use the VPreparedStatement. I don't understand how the Init/AddRow combo is supposed to go together.

The code below should get the child rows of the currently selected row in a hierarchical listbox and then delete them. However, only the first child row is deleted and not the rest:


dim SelectedRow as AppleListboxRow = getSelectedRow
dim theChildRows(-1) as AppleListboxRow = SelectedRow.GetAllChildren
theSQL = "Delete from Mailbox where MailboxPath = ?"
thePrepared = theDatabase.Prepare(theSQL)
'for each theMailbox as AppleListboxRow in theChildRows
for currentBox as Integer = 0 to UBound(theChildRows)
if currentBox = 0 then
thePrepared.Init
else
thePrepared.AddRow
end if
thePrepared.BindText(0, theChildRows(currentBox).Tag)
call thePrepared.SqlExecute
next


And as usual the documentation is a bit special:
VSQLPreparedStatement is called VSQLStatement. AddRow is AddBindRow.

Do I need to understand why there are both Init and AddRow?

A happy new year to you! I've got other SQL to fix so this isn't urgent.

Valentina 7.5.6 I think. Xojo 2017r1, El Capitan.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Hm....

This makes odd errors. The original SQL for one of the places where I need to use the prepared statements is:


dim theSQL as string = "update Mailbox set MailboxPath = replace(MailboxPath, '" + OldPath + "', '" + NewPath + "') where " + "left(Mailbox.MailboxPath, length('" + OldPath + globals.MailboxDivider + Utility.EscapeString(rowDragged.text) + "')) = '" + OldPath + globals.MailboxDivider + Utility.EscapeString(rowDragged.text) + "'"


Trying to replace this with a prepared statement:


dim theSQL as string = "update Mailbox set MailboxPath = replace(MailboxPath, ?, ?) where " + "left(Mailbox.MailboxPath, length('" + OldPath + globals.MailboxDivider + Utility.EscapeString(rowDragged.text) + "')) = '" + _
OldPath + globals.MailboxDivider + Utility.EscapeString(rowDragged.text) + "'"
dim thePrepared as VPreparedStatement = MainWindow(self.Window).theDatabase.Prepare(theSQL)
thePrepared.BindText(0, OldPath)
thePrepared.BindText(0, NewPath)

call thePrepared.SqlExecute


gives a VException: "Using clause does not match parameter specs". Goggle knows about this error but wasn't able to tell me what this means. The prepared statement should have 4 parameters and not 2. I got the error and started adding the BindTexts one by one. With only one the SQL works, with the second I get the error.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Any ideas? Bug?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Beatrix,

1) You can check Examples, it should have examples of VPreparedStatement, at least in the Test project.

2) Init/AddRow should be used only if you going bind MANY rows to a single query. but ok, you try to do exactly this.

3) Should be:

func( params )

stmt = db.Prepare( "DELETE FROM T WHERE RecID = ?" )

for( i = 1; i <= 10; ++i )
{
RecID = ...
stmt.Bind_int(0, RecID )

if( i < 10 )
stmt.AddBindRow()
}

stmt.SqlExecute() // Delete 10 records at once.

end func


Init() in this case even is not needed, because stmt object will be destroyed at the end of the function.
Init() is required only if you want re-use stmt object for next group of records.
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Hi Ruslan,

thanks for the example. I had missed that the SQLExecute is not in the loop. There is no example of the Init statement, though, not even in the examples folder.

And I need to solve the problem with error I had.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Next problem: how do I use prepared statements for Regex? The original SQL is:

dim theSQL as String = "select mailid from attachment where attachment.attachmentname REGEX '(?i)" + Utility.EscapeString(SearchString) + "' group by MailID"

The ' around the Regex means that this is a literal which isn't recognized by the prepared statement. Both of the following variations don't work:

select mailid from attachment where attachment.attachmentname REGEX '(?i) :1' group by MailID
select mailid from attachment where attachment.attachmentname REGEX (?i) :1 group by MailID

Any ideas for this one?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Beatrix,

You should replace the whole VALUE by :1


query = "select mailid from attachment where attachment.attachmentname REGEX :1 group by MailID"

stmt = db.Prepare( query )

strToBind = "(?i)" + SearchString // NO NEED to Escape string, when you use binding! Cool? :)
stmt.bind_text( 0, strToBind )

curs = stmt.Select()
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Thanks, Ruslan!

What about the error message "Using clause does not match parameter specs"?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
In your code

thePrepared.BindText(0, OldPath)
thePrepared.BindText(0, NewPath)

But should be

thePrepared.BindText(0, OldPath)
thePrepared.BindText(1, NewPath)

You have in query TWO placeholders ? ?

When you use bind_xxxx() function you should specify to which placeholder this will go, starting from zero.


In this wrong code:

thePrepared.BindText(0, OldPath)
thePrepared.BindText(0, NewPath)

You have twice assign only the first parameter.
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Thanks! You don't want to know how long I stared at this code. Off to the next problem.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
I can't get this to work. This is my current code:

'get the children and delete these
dim SelectedRow as AppleListboxRow = getSelectedRow
dim theChildRows(-1) as AppleListboxRow = SelectedRow.GetAllChildren
dim theSQL as string = "Delete from Mailbox where MailboxPath = ?"
dim thePrepared as VPreparedStatement = theDatabase.Prepare(theSQL)

if theChildRows <> Nil then
for currentBox as Integer = UBound(theChildRows) DownTo 0
thePrepared.BindText(0, theChildRows(currentBox).Tag)
thePrepared.AddRow
next
end if

'delete the parent
thePrepared.BindText(0, currentMailbox)
dim theResult as Integer = thePrepared.SqlExecute

In the debugger I can see that the child mailboxes are correctly added.

Before:
https://imgur.com/7AS6SXd.png

After:
https://imgur.com/99H7dIV.png

Some rows were deleted but not all. Any ideas what is going on? I know I could write a simple SQL to delete all child rows at once but I want to learn more about prepared statements.

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
I think - you should make sure that thePrepared statement filled with expected values first.
Just try to print out values before call BindText.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Hi Beatrix,

Questions to better understand code:

1)

thePrepared.BindText(0, theChildRows(currentBox).Tag)

.Tag is a string? Path to that folder?


2)

for currentBox as Integer = UBound(theChildRows) DownTo 0
...
thePrepared.BindText(0, currentMailbox)


CurrentBox - is integer.
CurrentMailbox -- is string? By sense should be string.

Both variables have similar names, but they have different types it seems.

May be better to use names as:

for currentChildBox as Integer = UBound(theChildRows) DownTo 0

thePrepared.BindText(0, currentMailboxPath)
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
This is the full table BEFORE deleting:

"MailboxPath","WhereClause","MailboxPathName"
"Views","","Views"
"Views:::All Mails","1","All Mails"
"Trash","","Trash"
"Macsend","","Macsend"
"Macsend:::@read","","@read"
"Macsend:::@read:::Ihr Interesse an Daylite 5","","Ihr Interesse an Daylite 5"
"Macsend:::@read:::mehrtest","","mehrtest"
"Macsend:::@read:::neue mailbox","","neue mailbox"
"Macsend:::mimemails","","mimemails"
"Macsend:::mimemails:::andere mailbox","","andere mailbox"
"Macsend:::zum löschen","","zum löschen"
"Macsend:::[Airmail]","","[Airmail]"

This is the table AFTER deleting:

"MailboxPath","WhereClause","MailboxPathName"
"Views","","Views"
"Views:::All Mails","1","All Mails"
"Trash","","Trash"
"Macsend:::@read:::Ihr Interesse an Daylite 5","","Ihr Interesse an Daylite 5"
"Macsend:::@read:::mehrtest","","mehrtest"
"Macsend:::@read:::neue mailbox","","neue mailbox"
"Macsend:::mimemails:::andere mailbox","","andere mailbox"

I verified that the values of the bound values in the loop are correct:

https://imgur.com/oUffbae.png

If I have a look at the number of rows with


dim theResult as Integer = thePrepared.SqlExecute


I get 5. Only every second row of data is deleted. I'm still confused...

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Hi Trixi,

Yes, it was a bug with multi-row-bind statement. It will be fixed in the next hotfix.

Anyway, this is an example of correct code:


dim cmdStr as string = "INSERT INTO T1(fldBool, fldLong, fldDouble, fldVarChar) VALUES( :1, :2, :3, :4)"
...

// Another LOOP (multirow statement) with 10 records:
for i = 1 to 10

vstmt.BindBool( 0, true )
vstmt.BindInt32( 1, i )
vstmt.BindDouble( 2, i * 2.2 )
vstmt.BindText( 3, "Hello World" + str(i) )
if i < 10 then
vstmt.AddRow
end if
next

rowsAffected = vstmt.SqlExecute()
DO_TEST_EQ( rowsAffected, 10 )
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
This is my current code:

'get the children and delete these
dim SelectedRow as AppleListboxRow = getSelectedRow
dim theChildRows(-1) as AppleListboxRow = SelectedRow.GetAllChildren
dim theSQL as string = "Delete from Mailbox where MailboxPath = ?"
dim thePrepared as VPreparedStatement = theDatabase.Prepare(theSQL)

if theChildRows <> Nil then
for currentBox as Integer = UBound(theChildRows) DownTo 0
thePrepared.BindText(0, theChildRows(currentBox).Tag)
thePrepared.AddRow
next
end if

'delete the parent
thePrepared.BindText(0, currentMailbox)
dim theResult as Integer = thePrepared.SqlExecute
Return True

The array theChildRows has the correct entries. Only 5 rows are deleted with the same data as posted above..

Regards

Beatrix Willius
Kommentar
There are no comments made yet.
Ivan Smahin Akzeptierte Antwort
Confirmed, it was another bug with string values.
Kommentar
There are no comments made yet.
Ruslan Zasukhin Akzeptierte Antwort
Ivan have fix it.

FIX will go into 8.0 build.
Kommentar
There are no comments made yet.
Beatrix Willius Akzeptierte Antwort
Very good. What is the rough ETA for 8?

Regards

Beatrix Willius
Kommentar
There are no comments made yet.
  • Seite :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Categories

Announcements & News
  1. 0 subcategories
Valentina Studio
  1. 2 subcategories
Valentina Server
  1. 4 subcategories
Valentina Database ADK
  1. 0 subcategories
Valentina Reports ADK
  1. 0 subcategories
Other Discussions
  1. 0 subcategories
BETA Testing
  1. 0 subcategories
Education & Research
  1. 0 subcategories
Omegabundle
  1. 0 subcategories