Problems with simple xbasic script

[coleresources]

I am working on becoming more proficient in xbasic. I wrote a simple script below, it's not complete but it wont hurt anything to run this script against my database as things stand. I know my syntax must be wrong (The syntax check tool is not reporting errors but I know that doesn't mean there isn't something wrong). Can someone point out to me what the problem may be? Thanks for any feedback.

dim conn as SQL::Connection
dim sql_update as C
if conn.Open("::name::CS") then
sql_update = "Update Users Set LastLoggedIn = now()"
if conn.Open()==.f. then
alert("Error", conn.callresult.text)
conn.execute(sql_update)
if conn.execute()==.t. then
alert("Successfully Executed")
end if
end if
end if

[drgarytraub]

Re: Problems with simple xbasic script

You are mixing Xbasic with javascript.

Alerts shgould be changed to ?
e.g. ?"Successfuully executed"

and change the double equal signs (==) to single(=)

[coleresources]

Re: Problems with simple xbasic script

Should this work?

FUNCTION LastLoggedIn AS C ( )
dim conn as SQL::Connection
dim sql_update as C
conn.Open("::name::CS")
sql_update = "Update Users Set LastLoggedIn = now()"
conn.execute(sql_update)
END FUNCTION

[coleresources]

Re: Problems with simple xbasic script

Trying to run this in the interactive window.

[lgrupido]

Re: Problems with simple xbasic script

It looks like it should work, but I don't usually use the interactive window.

Upload the page this is in locally and run it from there.

The first thing to do, if it's not working: Take the sql statement and run it directly in SQL, to make sure it works.

However, even if it does work, I don't think it's doing what you want it to do. That statement will update ALL the users. Don't you want to just update the current user?

[coleresources]

Re: Problems with simple xbasic script

I plan on including arguments to make sure only the one record will be updated. For now, it wouldn't hurt to update that particular column...there are only two users and I am not using this information inside of my app yet.

Thanks

[Steve Workings]

Re: Problems with simple xbasic script

Try this:

FUNCTION LastLoggedIn AS F ( )

dim conn as SQL::Connection
dim args as sql::arguments
dim sql_update as C

sql_update = "Update Users Set LastLoggedIn = :now"
args.set("now", now())

conn.Open("::name::CS")
conn.execute(sql_update, args)

conn.close()

END FUNCTION

Comments:

1. Alpha's PortableSQL may not be managing the SQL now() function. So, an argument will do the job, and you may also succeed if you set conn.PortableSQLEnabled = .f. after the conn.open()

2. Don't forget to close your connection as above

3. The function isn't returning anything, so it's not really a character type -- it's type "F" for "Function"

[coleresources]

Re: Problems with simple xbasic script

Thanks Steve. Makes sense

[GGandhi]

Re: Problems with simple xbasic script

hello

while steve's code is excellent, it does not take into consideration that connection may not have succeeded or the table may not be updated.
i have updated the code to return a logical value.
if returns true then the connection and the data update were successful if false then either one of those fail and you can trap the action and proceed accordingly.
( i am not know return type function, that is why i have updated the code to return type logical)

FUNCTION lastLoggedIn AS L ( user AS C )
	
	dim conn as sql::Connection
	dim result as l
	result = conn.open("::Name::your_connection_name")
	if result = .t.
		dim sql_update as c
		sql_update = "UPDATE your_table SET your_field = now() WHERE name_of_user = :newUser"
		dim args as sql::Arguments
		args.add("newUser", user)
		dim sql_result as l
		sql_result = conn.Execute( sql_update, args)
			if sql_result = .t. then
				lastLoggedIn = .t.
                                conn.close()
				else
				lastLoggedIn = .f.
				conn.Close()
				
			end if
		else
		
		lastLoggedIn = .f.
		
		end if
	
	
END FUNCTION

[aburningflame]

Re: Problems with simple xbasic script

hello

while steve's code is excellent, it does not take into consideration that connection may not have succeeded or the table may not be updated.
i have updated the code to return a logical value.
if returns true then the connection and the data update were successful if false then either one of those fail and you can trap the action and proceed accordingly.
( i am not know return type function, that is why i have updated the code to return type logical)

FUNCTION lastLoggedIn AS L ( user AS C )
	
	dim conn as sql::Connection
	dim result as l
	result = conn.open("::Name::your_connection_name")
	if result = .t.
		dim sql_update as c
		sql_update = "UPDATE your_table SET your_field = now() WHERE name_of_user = :newUser"
		dim args as sql::Arguments
		args.add("newUser", user)
		dim sql_result as l
		sql_result = conn.Execute( sql_update, args)
			if sql_result = .t. then
				lastLoggedIn = .t.
                                conn.close()
				else
				lastLoggedIn = .f.
				conn.Close()
				
			end if
		else
		
		lastLoggedIn = .f.
		
		end if
	
	
END FUNCTION

Why create all these extra variables?
THis is probably the minimal amount of code needed

FUNCTION lastLoggedIn AS L ( user AS C )
	dim conn as sql::Connection
        dim success as L=.f. 'set to fail for default
	if conn.open("::Name::your_connection_name") then
              dim sql as C=""
              dim args as SQL::Arguments
              sql="UPDATE your_table SET your_field = now() WHERE name_of_user = :newUser"
              args.set("newUser",user)
              if conn.execute(sql,args) then
                    success=.t. 'successful
              end if
              conn.close()
        end if
        lastLoggedIn=success
END FUNCTION

[GGandhi]

Re: Problems with simple xbasic script

thanks.

[coleresources]

Re: Problems with simple xbasic script

Thank you all for your feedback. The function Steve provided works. I am trying to modify it now to suit my needs. I have an a5w page that I use to collect the session variables I use throughout my application. In that page I am trying to call this function. It's not working, I've made some changes so please point out my mistakes. The error I receive is
"Error:Script:" /codepage.a5w" line:51
LastLoggedIn(vUserID)
LastLoggedIn(vUserID): Too many parameters"

The function:

FUNCTION LastLoggedIn AS F ( )

dim conn as SQL::Connection
dim args as sql::arguments
dim sql_update as C

if vUserID = ""
end
else
sql_update = "Update Users Set LastLoggedIn = :now where users_id = :userid"
args.set("now", now())
args.set("userid", vUserID)

conn.Open("::name::CS")
conn.execute(sql_update, args)

conn.close()
end if
END FUNCTION

The a5w page:
vUserID = session.userid

LastLoggedIn(vUserID)

[GGandhi]

Re: Problems with simple xbasic script

hello
as i indicated earlier, i am not familiar with return type function but..

FUNCTION LastLoggedIn AS F ( )

here you are declaring without a parameter.

LastLoggedIn(vUserID)

here you are calling the function with a parameter.
how about
FUNCTION LastLoggedIn AS F ( user as c)
then modify the code to reflect the user.

[GGandhi]

Re: Problems with simple xbasic script

FUNCTION LastLoggedIn AS F ( user as c )

dim conn as SQL::Connection
dim args as sql::arguments
dim sql_update as C


sql_update = "Update Users Set LastLoggedIn = :now where users_id = :userid"
args.set("now", now())
args.set("userid", user)

conn.Open("::name::CS")
conn.execute(sql_update, args)

conn.close()

END FUNCTION

The a5w page:
vUserID = session.userid

LastLoggedIn(vUserID)

possible correction to your code.