Very good. What is the rough ETA for 8?

Regards

Beatrix Willius

Ivan have fix it.

FIX will go into 8.0 build.

Confirmed, it was another bug with string values.

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

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 )

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:



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

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)

I think - you should make sure that thePrepared statement filled with expected values first.
Just try to print out values before call BindText.

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:


After:


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

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

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.

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

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()

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

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

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.

Any ideas? Bug?

Mit freundlichen Grüßen/Regards

Beatrix Willius

http://www.mothsoftware.com
Mail Archiver X: The email archiving solution for professionals

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