500 Error caused by sql_lookup putting a null value in a session variable

[lilmofo812]

So before upgrading to v4 of alpha anywhere I wasn't having any issues with my login processing page (this is where I set all my session variables). After updating I started getting 500 errors which always said something to the effect of "property not found" and pertaining to code of the following nature:

session.variable_name = sql_lookup(cn,table,filter,result)

where cn, table, filter, and result were all set as previous variables. After fixing the first issue I then got the same error but pertaining to a different session variable. At this point the only culprit I could find is when I use the sql_lookup function and it returned a null value to a session variable the error would be thrown (if I took off the session portion there was no error or if I added an ifnull() wrapper to the result variable the error went away), is this something that is no longer allowed (i.e. session variables can't have null values)? Or was it something that shouldn't have worked originally but just happened to get through?

There are certain session variables that may get set to null values because there aren't values in the particular field for that particular user. For now I have been going through each look up that caused an issue (essentially fix one and test the page to see if a different error would get thrown) and added an ifnull() portion to the result portion of the sql_lookup. Just want to know if I need to continue this when I add new session variable or if this could potentially be a bug. Thanks in advance!

[ChristopherG]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

We are having the same issues with session variables unable to receive null returns. Where the field is a boolean then we are making repairs to ensure the return is 0. However we still have a problem where where it should receive null or a char. The Alpha documentation says that the function will return null and its always worked up to now.

When I looked in the WAS logs, I note an error caught on startup which states that non-character session variables will not be supported in later versions. That does not seem to be the case yet but perhaps NULLS are not permitted already.

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

I feel like NULL is an important one to allow as I have very different types of users essentially using the same system which means many session variables aren't set but I guess I'll just use an if statement on every session variable moving forward.

[ChristopherG]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Same here Josh. As a matter of interest, are you using an x-basic ifnull function or the sql function? If the sql one, are you using it like this?

result = "grp_name"

becomes

result = "ifnull(grp_name,'')"

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Result = "ifnull(column)" did not always work for me so I actually went with xbasic if statement like below:

Session.variable_name = if(sql_lookup(parameters) = null_value,blank value or 0 if numeric,sql_lookup(parameters))

[ChristopherG]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Thank you Josh, I'll try that. I would still suggest asking Alpha as it may be a bug in the new build.

[Lenny Forziati]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

This is a bug and will be fixed in the next prerelease.

In the future, please do not hesitate to report a change in behavior such as this to [email protected]. If it is not listed in the release notes as a change, it is probably not an intentional change and the sooner we know about it, the sooner we can fix it.

[ChristopherG]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Thanks Lenny!

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

So I can't believe I was in this thread in april and didn't think to make the change then, but I need this question answered as it could potentially shut down my entire web application when the change comes.

I was getting completely different error which led me to the xbasic logs where I was reminded of the session variables only being characters moving forward. I have over 150 components in my web application, all of which more than likely use session variable somewhere (whether it be an argument in a list, show hide expression, or setting the default value of a field). Now for my concerns about what will happen when the switch to only character session variables will occur:

1) I'm least worried about setting the default value of a field as it will still just be putting a value in the text box and since the textbook is saving to a numerical sql field that shouldn't matter (correct me if I am wrong).

2) I'm also not too worried about when I use a session variable as an argument value since I am setting the argument to be numerical I am assuming alpha is going to make sure that my session variable is properly converted so that when I run numerical funcitons on the argument (say greater than) it will operate correctly (again correct me if I am wrong).

3) I do use session variable from time to time in drop down boxes where most of the time it is an equal sign but I will be comparing a numerical sql field to a session variable that has a character value, is this going to run me into issues?

4) Now this one is a doozy, I often use session variables in client side show hide expressions with mathematical functions such as greater than and less than, and my concern is since the session variable will be a character field does that mean that all these client side expressions will fail (essentially meaning I will have to go through thousands of controls on hundreds of components to try and figure out how my entire application will break).

I also don't know how I would go about making the change as I would assume if I convert all my session variables to character fields in the login processing page that every user hits after logging in, that would mean I couldn't publish those changes until I went through every component to see where issues might be as I could cause my application to stop working for thousands of users correct, or is alpha doing something on the backend to make sure that where session variables may be used they'll still work as numerical values even though they are set as character fields.

[CharlesParker]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Pretty sure you would have to create the session var as a char then convert it to do the math - darned "progress" Along the same lines of progress I just saw that in the latest release google maps super control will no longer work in the desktop side - I wonder how long till it's killed on the web side - ---- THAT would be a MAJOR snag for me. I use the maps to track several things in my app!

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Oh the headaches I just got from the thought of preparing for this transition!

[JerryBrightbill]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Google maps on the desktop are handled very differently than maps in a component in a browser.

Google has changed the preferred method to get maps in a browser to the use of an API with a special key, which is how the web maps are configured. The map controls also expect a certain level of browser support and the current Google maps may not work in older browsers such as IE8. This is also true of other online map products.

The problem on the desktop is because the Google Map super control uses an older method with just a URL and a search query. While this still works to some extent, (you will get a map), some of the map features do not work as Google sends back multiple responses to fill certain features. The desktop browser control does not currently support all of the features needed to display all these map elements on a desktop form.

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

So I was running some preliminary tests on changing numerical session variables to character variables and ran into some things that I see as very big potential problems that I want to lay out and get some feedback on. Below is an example of my code how it is now, and what I tried to do to see if it would work. The image is the error I got after making this change.

This code currently works fine, CurrIdUser is set to a numerical type and pulls an ID field from the registeredusers table that is auto incremented integer field.

dim session.CurrUserName as c
dim session.CurrIdUser as n

myargs.add("user_name",session.CurrUserName)
sqlStatement = <<%longstring%
SELECT ID as user_id
FROM registeredusers WHERE UserName = :user_name
%longstring%
cnn.Execute(sqlStatement,myargs)
rs = cnn.ResultSet
session.CurrIdUser = rs.Data("user_id")

When I try both bits of code below I get the error that is in the image I attached. It seems odd that I would get this error considering I have my returned value from the sql wrapped in a str() function in number one and in number two I have the sql column converting to a string, but I guess this might be the case because it knows the value in the sql table is numerical anyways? However, if that is the case then Alpha switching over to only character values in session variables would mean I would have to convert all my numerical columns in the tables that are used as session variables to character types which seems like a ton of work considering I'm not sure what alpha is gaining by switching to numerical session variables only...? This is by no means my expertise but I want to get ahead of this problem before my web application becomes unusable.

dim session.CurrUserName as c
dim session.CurrIdUser as c

myargs.add("user_name",session.CurrUserName)
sqlStatement = <<%longstring%
SELECT ID as user_id
FROM registeredusers WHERE UserName = :user_name
%longstring%
cnn.Execute(sqlStatement,myargs)
rs = cnn.ResultSet
session.CurrIdUser = str(rs.Data("user_id"))

dim session.CurrUserName as c
dim session.CurrIdUser as c

myargs.add("user_name",session.CurrUserName)
sqlStatement = <<%longstring%
SELECT CONVERT(ID,CHAR(50)) as user_id
FROM registeredusers WHERE UserName = :user_name
%longstring%
cnn.Execute(sqlStatement,myargs)
rs = cnn.ResultSet
session.CurrIdUser = rs.Data("user_id")

Screen Shot 2016-11-17 at 1.03.28 AM.png

[Pat Bremkamp]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

First of all, don't dim session variables. Just assign them some value.

Second, I assume you are assigning session.CurrUserName a value, but are just not showing the code. Otherwise, you are creating an argument out of a variable with no value.

Third, instead of using str, try convert_type(rs.data("user_id"),"C")

That should get you started.

Also, when I converted my session variables, it was a big help to use the "Search in all web project files" under edit in the menu. I searched on "session." That will show you where and how it is used.

[lilmofo812]

Re: 500 Error caused by sql_lookup putting a null value in a session variable

Yes CurrUserName is being set It just happened to be much higher up in the code so I didn't worry about adding the code being used to set it and just declared it so it was there for reference.

Looks like the convert_type and un-dimming the variable took care of that issue, so thank you!

I'll definitely look into that search feature, I'm assuming that searches xbasic, JavaScript, show/hide expressions, dropdown box queries, and the likes or is it just going to search the properties like show/hide expressions?

Final questions, how large is your project in terms of components, how long did the search take, and what type of load did it put on your server (trying to figure out if I need to switch my sleep schedule for a few days so I can do this in the middle of the night to limit server load to when users aren't active). I've got about 650 components in my project (a good portion are older ones that I've just not had a chance to purge and would need their session variables dealt with), but I want to take every precaution I can when trying to do this update.

Thanks for the feedback!