SQL statement

[Atta]

I am trying to understand this but I cannot, for some reason. Could someone point me to a website or give me some help?

In a UX component I have 2 fields
One field asks about how many How_Many_IP do you need?
The other field ask from which lablocation, lab examples are : '34-1650-DELL' or '34-1650-HP'

How_Many_IP is a numeric field
lablocation is a drop down character field

I am working with an Xbasic statement

After the person clicks on the button, I want this to happen
I want to run a SQL statement that checks if there are enough IP's, see SQL statement below.

SELECT COUNT(IpAddress) as totalavailable FROM AddressTable WHERE Lab= @lablocation AND IsAvailable=1

If totalavailable > How_ManyIP then go to the Excel import
If totalavailable < How_Many_Ip pop up box with not enough ip's etc.etc.

This should be simple enough, but I cannot figure it out.

Some help would really be appreciated.

[mumfie]

Re: SQL statement

After the person clicks on the button, I want this to happen
I want to run a SQL statement that checks if there are enough IP's, see SQL statement below.

SELECT COUNT(IpAddress) as totalavailable FROM AddressTable WHERE Lab= @lablocation AND IsAvailable=1

If totalavailable > How_ManyIP then go to the Excel import
If totalavailable < How_Many_Ip pop up box with not enough ip's etc.etc.

This should be simple enough, but I cannot figure it out.

You could add code to the UX dialogValidate serverside event. That will execute when the user submits the UX.

Your XBasic code will need to setup the SQL argument @lablocation, execute the query and assign the result to totalavailable

More#1

More#2

[Atta]

Re: SQL statement

So this is what I have, can I put this code in a button when people click on it as a Xbasic function, or does it need to be in the server side onDialogExcute?


dim cn as sql::Connection
if cn.Open("::Name::LE-SQL40")
dim sqlcommand as c
sqlcommand = "SELECT COUNT(IpAddress) FROM AddressTable WHERE LabLocation= '34-1659-DELL' AND IsAvailable=1"

flag = cn.Execute(sqlcommand)
dim rs as sql::ResultSet
rs = cn.ResultSet
if rs > 0
open up a grid
else
say in a popup window that there are not enough IP's
end if
else
end if

How do I check what is in rs with the if statement?

[Atta]

Re: SQL statement

So this is what I have to find out if I have data but nothing get's returned.

I have a button and on the button click event I have a Ajax callback see below.

function CheckAvailableAddresses as c (e as p)
'This function will handle the Ajax callback.

dim cn as sql::Connection
dim sqlcommand as c
sqlcommand = "SELECT COUNT(IpAddress) FROM AddressTable WHERE LabLocation= '34-1659-DELL' AND IsAvailable=1"
cn.Open("::Name::LE-SQL40")
cn.Execute(sqlcommand)
dim rs as sql::ResultSet
rs = cn.ResultSet
if rs.NextRow() = .T.
dim error_msg as c="Data is there"
e.javascript="alert('"+js_escape(error_msg)+"');"
else
error_msg = "Data is not there"
e.javascript="alert('"+js_escape(error_msg)+"');"

end if
end function

Why does not not work?

[Steve Wood]

Re: SQL statement

Test your SQL statement as a query against the database outside of Alpha, in whatever tool you are using to manage the database.

Or, at least get your Alpha script to return any error produced by the database, like this:

cn.Execute(sqlcommand)
save_to_file(cn.callresult.text,"c:\errors.txt") ' writes "Success" or an error message if it failed.

[Steve Workings]

Re: SQL statement

Point #1:
---------------
An AJAX callback doesn't use e.javascript as you've written.

Instead, the function itself can return javascript, e.g.

function CheckAvailableAddresses as c (e as p)
'This function will handle the Ajax callback.

dim cn as sql::Connection
dim sqlcommand as c
sqlcommand = "SELECT COUNT(IpAddress) FROM AddressTable WHERE LabLocation= '34-1659-DELL' AND IsAvailable=1"
cn.Open("::Name::LE-SQL40")
cn.Execute(sqlcommand)
dim rs as sql::ResultSet
rs = cn.ResultSet
if rs.NextRow() = .T.
 dim error_msg as c="Data is there"
 [COLOR="#0000FF"]CheckAvailableAddresses = "alert('"+js_escape(error_msg)+"');"[/COLOR]
else
 error_msg = "Data is not there"
 [COLOR="#0000FF"]CheckAvailableAddresses = "alert('"+js_escape(error_msg)+"');"[/COLOR]
end if
end function

Point #2
----------
What Steve Wood says:
What do you get if you run your sqlCommand in the A5 SQL command window? Or, as a query in MS Management Studio?

[mumfie]

Re: SQL statement

Why does not not work?

You need to access the returned result and test for sql errors.
Heres a simplified example that works with MSQL

FUNCTION SQ_GetCount AS p (location AS C )
option strict
dim ptr as p
dim ptr.errMsg as c =""
dim ptr.totalAvailable as n=0
dim dataItems[0] as p
dim rs as SQL::ResultSet
dim sqlSelect as C
Dim sqlParams as sql::arguments

sqlSelect = <<%txt%
SELECT count(ipaddress) as totalavailable from DemoAddressTable where labLocation=:location

%txt%
'create db connection
dim conn as SQL::Connection

conn.Open("::name::healthcare")
sqlParams.set("location",location)
IF conn.Execute(sqlSelect,sqlParams)
rs=conn.ResultSet
rs.ToPropertyArray(dataItems)
IF dataitems.size(1)=1
ptr.totalAvailable = dataItems[1].totalAvailable
END IF
ELSE
ptr.errMsg = conn.CallResult.Text
END IF
conn.close()

SQ_GetCount=ptr
END FUNCTION