replace xbasic with sql

[richard2007]

I have finally taken the plunge and am converting my application to ms sql 2012. ive created the tables and exported the data and have even got as far as creating my first stored procedure (although, for another post, im unsure which is better, SP or SQL Select).

in my pages i like to get data from 3 tables which counts the number of records based on a filter for the logged in user. this is how i currently achieve it;

head section of the .a5w page

dim nu_notifications as N
nu_notifications = tablecount("[PathAlias.ADB_Path]\notifications.dbf","n_viewed=.f..AND.n_user="+quote(session.employeename))

in the body

<%a5 ?str(nu_notifications, 8,0)%>

i would like to know the best way to get the data from the sql tables without slowing the page load and how would i write the script. is it something like this

SELECT N_TYPE, N_DATETIME, N_BODY, N_CODE, N_VIEWED 
FROM dbo.notifications 
WHERE CONTACTID = :contactid  AND  N_VIEWED = 0

appreciate the help.

[Steve Workings]

Re: replace xbasic with sql

For starters:

SELECT count(*) 
FROM Notifications
WHERE ContactID = :ContactID and N_Viewed = 0

And, if you haven't found it already, this can be very helpful:

https://www.alphasoftware.com/docume...L%20Tables.xml

And, SQL 2016 is out - something to consider if you can.

[richard2007]

Re: replace xbasic with sql

For starters:

SELECT count(*) 
FROM Notifications
WHERE ContactID = :ContactID and N_Viewed = 0

And, if you haven't found it already, this can be very helpful:

https://www.alphasoftware.com/docume...L%20Tables.xml

And, SQL 2016 is out - something to consider if you can.

thank you steve, all helpful. I had started a course on ms sql on udemy and select count * is something i had covered. im just working out how to work with sql in Alpha. I have started reading through the link you have given and it does make sense to use the latest ms sql version.

in my .a5w page, do i simply enclose the sql statement in <%a5 %> tags?

[Steve Workings]

Re: replace xbasic with sql

Well, it's a little more involved than that. And coming from the DBF world this seems like a lot of work, but it's worth it and gets to be very routine after a little while. I hope the below helps.

<%A5

' (caution - untested)

' DIM standard variables
' -----------------------
dim cn as sql::Connection
dim rs as sql::ResultSet
dim args as sql::Arguments
dim MyOutputValue as c = ""

' Here's the Query:
' --------------------
sql = <<%a%
	SELECT count(*) 
	FROM Notifications
	WHERE ContactID = :ContactID and N_Viewed = 0
%a%

' set argument value. This assumes you have a session variable of this name.
' It further assumes the field type is numeric
' ------------------------------------------
args.set("ContactID", val(session.ContactID))

' Assuming your connection string is named "conn", open the connection
' (which is a really good idea for portability to other projects)
' ---------------------
cn.open("::name::conn")

' Execute the query - don't forget the args:
' ------------------------------------------
flag = cn.execute(sql, args)

' If the execute was good, get the resultset:
' -------------------------------------------
if flag
	rs = cn.ResultSet
	' Check for a value in the resultset:
	if flag = rs.nextRow()
		' Assign the value:
		MyOutputValue = convert_type(rs.data(1), "C")
	end if
end if

' Free the resultset and close the connection:
' --------------------
cn.FreeResult()
cn.close()

' Emit the ouput string:
' ----------------------
if MyOutputValue <> ""
	txt = "You have " + MyOutputValue + " Notifications.<br>"
else
	txt = "Could not find Notifications.<br>"
end if

?txt

%>