Executing SQL Code

[sprivitera]

I need to execute some static SQL code on our mysql database (cleanup tables, updating values etc).
How can i attach the SQL code to a button or a link in a web page using AlphaFive?

Thanks

[DaveF]

Re: Executing SQL Code

You need to invoke a server-side Xbasic function so you can execute your SQL code. Your button can initiate an AJAX callback which can be done using Action Javascript. In a grid or dialog component, select your button control and then double-click on the Javascript onclick event in the property list on the right side. A new window appears and you will click on "Add new action". Another window appears with a list of actions on the right side. Double click on "Ajax Callback". Then a dialog appears where you need to enter the name of your server-side Xbasic function. In the "Function name" field, enter a unique XBasic function name such as "CleanupTables". Click the hyperlink that says "Create function prototype" and then click the "Copy to clipboard" button to copy the prototype. Then close the prototype window and click OK to save your callback function definition. Then you have to click "Save" to save your Action Javascript definition and you will return back to the main IDE. Click "XBasic Functions" on the left side under "Code". Now paste your code in the XBasic Functions code editor and begin writing your SQL code.

[sprivitera]

Re: Executing SQL Code

Thanks a lot!

Your post was very useful.Can you post a simple xbasic do execute a simple SQL query?

Salvatore

[DaveF]

Re: Executing SQL Code

http://wiki.alphasoftware.com/Learni...ith+SQL+Tables

dim args as sql::Arguments
dim result as l
dim sqlCommand as c
dim rs as sql::ResultSet
dim ln as c
dim fn as c


result = cn.open("::Name::SQL Server 2008")
if result = .f.
     return  ' Error opening the database
end if


args.Add("id", "01564")


sqlCommand = "Select * from Customer where custid = :id"     ' The colon (:) tells A5 the following field is an argument
result = cn.Execute(sqlCommand, args)
if result = .f.
     return  ' Customer id not found
end if


rs = cn.ResultSet

while rs.NextRow() = .t.
     ln = rs.data("lastname")
     fn = rs.data("firstname")
   ' Do something with the data
end while


cn.close()