Anyone have any good Xbasic LOOP code?

[Fulltimer]

I'm trying to find someone who has some really good Xbasic LOOP code that traverses through a recordset. I've mostly worked with grids etc. but I have a special need to use raw Xbasic code to do a check on data contained in each record. I'm not up to speed with Xbasic and I'm betting that one of you sharper guys/gals have already written code that you use over and over again and are willing to share.

I come from other languages and can fully understand working code when I see it. However trying to start from scratch introduces all kinds of errors that could be avoided.

My needs are simple.
- Code should use A5 convention for using a named connection
- Code should use SQL command stored into var
- Code should use ARGS
- Code should use Execute command
- Code should create a Recordset
- A loop is needed to traverse from record 1 to record X (eof)
- While inside loop - test field for value ie. city = "New York" - this condition is NOT part of SQL command

It's important that this code is actually working code ( please rename any private stuff ) and not psuedo code.

I'm trying to build a library of code that I can use over and over with simple minor changes. Your help would be
greatly appreciated.

[Steve Workings]

Re: Anyone have any good Xbasic LOOP code?

You'll find this useful:

http://www.downloads.alphasoftware.c...SQLTables.html

[Peter.Greulich]

Re: Anyone have any good Xbasic LOOP code?

Here is some sample code:

Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments
Dim rs as SQL::ResultSet	

args.add("SomeValue",vSomeVar)

SQL = "SELECT * FROM table WHERE field = :SomeValue ORDER BY field"

conn.open("::Name::XXXXXXX")							
flag = conn.execute(SQL, args)
rs = conn.ResultSet
conn.close()

rowFlag = rs.nextRow()		'The first row
while rowFlag 
    txt = txt + "Lastname: " + rs.data("Lastname") + crlf()
 

   rowFlag = rs.nextRow()
end while

vText = conn.callResult.text
vTitle1 = "FINI" 
vMsg1 =  vText
ui_msg_box(vTitle1, vMsg1,UI_INFORMATION_SYMBOL)

Here is a more complex real world example:

function xbSelect as c (e as p)
'debug(1)

Dim vCount as n
vCount = val(e.checkboxRows.countChecked)
IF vCount < 1
	xbSelect = "alert('No rows were selected.');"
	EXIT FUNCTION
END IF


Dim vFilter as c
Dim vItems as c
For i = 1 to vCount
	vFilter = vFilter  + "TimexDesc_Id = " + alltrim(str(eval("e.checkedRows.key" + i + "[1]"))) + crlf()
next i

vFilter = alltrim(vFilter)
vFilter = stritran(vFilter,crlf()," OR ")
''=========================
Dim SQL as c
Dim conn as SQL::connection
Dim args as SQL::Arguments
Dim rs as SQL::ResultSet	

'args.add("PartNum",vBomPartNum)
'args.add("Part",vBomPart)
'args.add("Qty",vQty)

SQL = "SELECT Item FROM bill_timexdesc WHERE " + vFilter
	
conn.open("::Name::XXXXXXX")							
flag = conn.execute(SQL, args)
rs = conn.ResultSet
conn.close()
vText = conn.callResult.text

rowFlag = rs.nextRow()		'The first row
while rowFlag 
	vItems = vItems + alltrim(rs.data("Item")) + crlf()
    rowFlag = rs.nextRow()
end while

vItems = stritran(vItems,"'","")	''remove JS UN-friendly ' (apostrophe)
vItems = stritran(vItems,crlf(),"\n")	''replace paragraph breaks w. JS friendly \n

vCode= <<%longstring%
var po = {grid.Object}.getParentObject();
var vDesc = po.getValue('D','INV_DESC');

if (vDesc == '')
  {po.setValue('D','INV_DESC','xxxxx');}
else
  {po.setValue('D','INV_DESC',vDesc + '\n \n' + '---New Entries--- \n' + 'xxxxx');}

{grid.Object}.closeParentContainer();
%longstring%

vCode = stritran(vCode,"xxxxx",vItems)
xbSelect = vCode
end function

[Steve Workings]

Re: Anyone have any good Xbasic LOOP code?

Couple things to add to Peter's example:

1. Use args.set(), not args.add(). The former will re-use an existing arg or create a new one as necessary.

2. Don't forget to close the connection.

3. I had one customer using MySQL who was constantly running out of connections. Though the documentation says it's optional, I always use a conn.freeresult() now, which solved that problem.

4. Comment, comment, comment. It may look plain, simple and "everyone can understand that" now but 6 months from now, esp. if it's evolved, you'll be glad you commented.

[Peter.Greulich]

Re: Anyone have any good Xbasic LOOP code?

Hi Steve,

Actually these days I do use args.set. Also I think conn.close() is in both examples. I like to close as soon as possible - after I grab the result set. "conn.freeresult()" - thanks for the tip!

[Fulltimer]

Re: Anyone have any good Xbasic LOOP code?

Hi Steve,

Actually these days I do use args.set. Also I think conn.close() is in both examples. I like to close as soon as possible - after I grab the result set. "conn.freeresult()" - thanks for the tip!

To Peter and Steve,

Just to be clear!!!! Are your saying that in Peter's example :

args.add("SomeValue",vSomeVar) should be changed to
args.set("SomeValue",vSomeVar)

If so can we then stack these like :

args.set("SomeValue2",vSomeVar2)
args.set("SomeValue3",vSomeVar3)
args.set("SomeValue4",vSomeVar4)

Has args.add() been deprecated and should I go back into my code and change existing syntax?

Also, I am not clear as to the diff between conn.close() and conn.freeresult() and WHEN they should both be used in the code.
In other words "use conn.close() right after ..." and/or "use conn.result() right after ..."

Thanks

Tom W.

[Peter.Greulich]

Re: Anyone have any good Xbasic LOOP code?

Tom,

I used to use args.add() until I discovered args.set(), but frankly I can't tell the difference in practice. I don't believe args.add() is deprecated.

See the WiKI on conn.freeresult():
http://wiki.alphasoftware.com/SQL+Co...esult+Function'
Sounds like it's not something you ordinarily need to call.

I always close my connection after completing an operation. Once you assign the result-set to a variable you can close the connection if you no longer need it open for other purposes.

[Steve Workings]

Re: Anyone have any good Xbasic LOOP code?

args.add() is not deprecated, as Peter points out. But there's no need for it -- just use args.set().

When I was first learning, I always used args.add() when I was first setting a particular arg, and then args.set() any successive time. But it's easier and works fine to just use args.set() all the time. I am replacing my old args.add() whenever I run across it, but it's only because I'm there.

My order of call is:
cn.freeresult()
cn.close()

I'm sure I decided a couple years ago to do it in that order but can't tell you why right now.

[Fulltimer]

Re: Anyone have any good Xbasic LOOP code?

Thanks Steve and Peter,

Your replies have brought me a long way towards a clearer understanding. I have already implemented your suggestions and they work just fine.

Tom W.

[Foodtester]

Re: Anyone have any good Xbasic LOOP code?

I've found this very helpful, thank you all above.

I have a loop where I am:

1. Opening the connection
2. Gathering info / arguments with various if / then criteria
3. Running the SQL statement
4 Loop back to 2
5. Close the connection

I'm also using Args.Add, so I'll switch to Args.set


My callback takes a long while, is it be more efficient to open and close the connection at the end of each loop?

Robin

[Sparticuz]

Re: Anyone have any good Xbasic LOOP code?

Just wanted to make a note about what I do. I was looking through the code above and noticed there was no error checking in any of it. (Or none that was inline in the code)
If I'm running more sql commands after that. I'll do a .freeresult() instead of a close, so I can re-use the connection.

dim cn as SQL::Connection
if cn.open("::name::XXXX") then
	if cn.Execute(sql,args) then
		dim result as sql::ResultSet
		result = cn.ResultSet
		cn.close()
	else
		error = "Error: SQL Command not executed"
		cn.close()
		end 
	end if
else
	error = "Error: Can't connect to Database"
	end
end if

while result.nextRow()
	'Do things for each row
end while

3. I had one customer using MySQL who was constantly running out of connections. Though the documentation says it's optional, I always use a conn.freeresult() now, which solved that problem.

I also had this problem, about 2 years ago (in v10), but I recently went through a major refactoring (in v11) and using .Close() seems to actually close the connection now. Must have been a bug fixed somewhere along the line...

[aburningflame]

Re: Anyone have any good Xbasic LOOP code?

'I personally prefer keeping an error/error_msg variable in all my scripts/functions instead of killing the script on error, 
dim conn as SQL::Connection
dim error as L=.f.
dim error_msg as C=""

if conn.open("::name::conn") then
    dim sql as C="SELECT * FROM table WHERE id=:id"
    dim args as SQL::Arguments
    args.set("id",idVariableValue)
    if conn.execute(sql,args) then
          'you can store the resultset by doing   rs=conn.ResultSet , then below you would use while rs.NextRow() and rs.data("countryField")
          'this is only necessary if you need to execute other sql statements in the loop because conn.ResultSet will be overwritten when you execute another sql statement
          while conn.ResultSet.NextRow()
              dim email_sent as L=.f.
               if upper(conn.ResultSet.data("countryField"))=="CANADA" then 'case insensitive search 
                        'do some processing , send an email, whatever
                        email_sent=sendSomeRandomEmail()

                         'stop processing if any email fails
                         if email_sent==.f. then
                                   
                                  error=.t. 
                                  error_msg="Could not send email to: "+conn.ResultSet.data("customer_id") 
                                  exit while
                          end if
               end if
               
              
          
          end while
          conn.FreeResult()
    else
       error=.t.
       error_msg="Could not load records"
    end if
    conn.close()
else
     error=.t.
     error_msg="Could not open connection"
end if

if error==.f. then
 ?"Process Complete"
else
 ?"Error Occurred: "+error_msg
'handle error, send error email to admin, log the error in the database - whatever you want to do
end if

[aburningflame]

Re: Anyone have any good Xbasic LOOP code?

ARGS.ADD() vs ARGS.SET()

args.add("myarg",1) <<will add myarg1
args.set("myarg2",2) <<will add myarg2
args.add("myarg",3) << I believe this will fail because myarg has already been added
args.set("myarg',3) << will set the value of :myarg to 3