Something I learned about .nextrow() and debug while converting to mysql

[lisaanderson]

The situation that I uncovered may be common knowledge to more experienced alpha users, but I thought I'd document this for others who may be converting to sql. The documentation about looping through a resultset states the following:

When you first get a pointer to a ResultSet, the current row pointer in the ResultSet is positioned BEFORE the first records in the ResultSet. So, the first time you execute .NextRow(), it will position the pointer on the FIRST records in the ResultSet.
NOTE: If you read data (using the ResultSet's .Data(), or .DataIsNull() method) from a ResultSet - WITHOUT FIRST USING .NextRow() to position the pointer on the first row in the Resultset - then Alpha Five automatically does a .NextRow() to position the pointer on the first record in the Resultset. Therefore, the following two sets of commands are equivalent:

cn.execute("Select * from customer")
rs = cn.resultset
rs.nextRow()
?rs.data("Lastname")
= "Graham"

And

cn.execute("Select * from customer")
rs = cn.resultset
?rs.data("Lastname")
= "Graham"

Through testing my complicated function for the first time, I wanted to step through the code with debug to make sure things were as I intended them to be. I happened to be "watching" one of the resultset variables in the watch window below my code. I couldn't figure out why my code (as in the first example above) was actually moving to the second record or EOF when there was only one record. Then it dawned on me that I was causing the pointer to look at the first record as soon as the resultset was opened (before the nextrow()) since I was watching rs.data("myvariable"). My next line in code was to do a nextrow() which then moved me past the record I wanted. As soon as I closed out those watch variables, it all worked properly.

Hopefully this will help someone save some time!
Lisa

[christappan]

Re: Something I learned about .nextrow() and debug while converting to mysql

Some of us do know that Lisa, but many of us learned in similar fashion. As a bonus, I'll add the other part I learned too late which is in the same line of thinking. To loop through a result set you should do this:

cn.Execute("SELECT Field1 FROM Table1")
rs = cn.resultSet

rsFlag = rs.NextRow()
while rsFlag
    'do something
    Field1 = rs.data("Field1")
    rsFlag = rs.NextRow()
end while

[rvanderbes]

Re: Something I learned about .nextrow() and debug while converting to mysql

THANKS so much for sharing!

I was struggling to figure out why my code was executing differently in the (de)bugger.