xbasic or javascript and Jquery

[Berskyboy]

Hi everyone,

I'm in the process of moving all my MS Access ADO code over to Alpha Five. What is the best method to port over code that runs at a button click that does lots of database updates and/or insert? Would Xbasic be the best, or javascript? I have put a lot of code in triggers and stored procedures.

for example, I have a button that freezes our financial forecasts into an outlook (another table). So it iterates on all the contracts in one table, gets the current forecast and makes updates to another table.

In Access I just did a recordset and run thru a loop. How would I do the same thing in Alpha Five? Javascript and JQueries or Xbasic?

thanks

Mark

[NicholasWieland]

Re: xbasic or javascript and Jquery

In Access I just did a recordset and run thru a loop. How would I do the same thing in Alpha Five? Javascript and JQueries or Xbasic?

You can run a loop in Xbasic would be fine.

[KYBiggs]

Re: xbasic or javascript and Jquery

Recordset example

reading excel creating record set and looping thru

'Date Created: 23-Apr-2011 01:49:10 PM
'Last Updated: 23-Apr-2011 01:51:34 PM
'Created By : BIGKX001
'Updated By : BIGKX001
'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
debug(1)
flagResult = cn.open("::Name::Weekly")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t.
dim sqlStatement as c
sqlStatement = <<%sql%
SELECT * FROM [COMP$] COMP_ WHERE KEY IS NOT NULL
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
'This is sample code that shows you how to loop over the rows in the ResultSet
dim flagLoop as l
flagLoop = .t.
while flagLoop
'to read column values from the ResultSet, use then rs.Data() method, passing in either the column number or column name
'e.g. var = rs.Data(1)
' var = rs.Data("customerId")

'if we are on the last row, rs.NextRow() will return .f. which will break out of this while loop
flagLoop = rs.NextRow()

end While


'Now, close the connection
cn.close()

[aburningflame]

Re: xbasic or javascript and Jquery

Recordset example

reading excel creating record set and looping thru

'Date Created: 23-Apr-2011 01:49:10 PM
'Last Updated: 23-Apr-2011 01:51:34 PM
'Created By : BIGKX001
'Updated By : BIGKX001
'Execute a query on a SQL database
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
debug(1)
flagResult = cn.open("::Name::Weekly")
if flagResult = .f. then
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
end if

'Specify if you are using Portable SQL syntax, or not
cn.PortableSQLEnabled = .t.
dim sqlStatement as c
sqlStatement = <<%sql%
SELECT * FROM [COMP$] COMP_ WHERE KEY IS NOT NULL
%sql%
'Execute the Query
flagResult = cn.Execute(sqlStatement)
if flagResult = .f. then
ui_msg_box("Error",cn.CallResult.text)
cn.close()
end
end if
dim rs as sql::resultset
rs = cn.ResultSet
'This is sample code that shows you how to loop over the rows in the ResultSet
dim flagLoop as l
flagLoop = .t.
while flagLoop
'to read column values from the ResultSet, use then rs.Data() method, passing in either the column number or column name
'e.g. var = rs.Data(1)
' var = rs.Data("customerId")

'if we are on the last row, rs.NextRow() will return .f. which will break out of this while loop
flagLoop = rs.NextRow()

end While


'Now, close the connection
cn.close()

You can get rid of this:
flagLoop = rs.NextRow()
dim flagLoop as l
flagLoop = .t.

By changing while flagLoop to while rs.NextRow()

Also, the code above imports record by record - and if a record fails - the procedure stops. You might want to change this to something like this: (if any record fails - the entire thing gets rolled back)

dim x as N=0
if cn.begintransaction() then
     while rs.NextRow()
         'execute statements - insert stuff, etc etc'
         if failing_condition then
             flagerror=.t.
             exit while
         else 
             x=x+1
         end if
     end while

     if flagerror then
        cn.rollbacktransaction()
     else
        if cn.committransaction()==.f. then
           ui_msg_box("could not commit")
        else
           ui_msg_box("imported "+x+" records")
        end if
     end if 
else
  ui_msg_box("could not start transaction")
end if