DATABASE Issues

[sezeala]

I need help i want to check if a database exist or not. if it doesn't exist, the database should be created otherwise it should perform some other operations based on other conditions. this is my code below.

<%a5
firstSelect = "select * from orders where session ="+ sessionc
	flag = conn.Execute(firstSelect) ' check if user have initiated an order before
	frs = conn.ResultSet
	if ((flag = frs.NextRow()) = null_value()) '  if no create a temporary table with the username or session variable
		newTable = "CREATE TABLE"+ sessionc + "(id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, session VARCHAR(50) NOT NULL,username VARCHAR(100) NOT NULL,item VARCHAR(60),quantity INT(255),price FLOAT(20),total FLOAT(20),transaction VARCHAR(30))"
			conn.Execute(newTable) 'after creating, insert the data into the new table
		
		
	end if
%>

This is the error report i get

500 Internal Server Error

Script ErrorError:Script:" /restuarant/ajaxCallback.ajax.a5w" line:41

	if ((flag = frs.NextRow()) = null_value()) '  if no create a temporary table wi

Variable does not contain a valid result set while getting result set next row.'

[Steve Workings]

Re: DATABASE Issues

I'm not real sure whether you have other problems here, but on is your use of the flag variable. It returns a true or false. So in Xbasic terms you would want:

if (flag = frs.nextRow()) = .t.)....

or more simply:

if frs.nextRow() ....

Your code doesn't show it but I assume much of the variables are DIMmed elsewhere, e.g. DIM frs as sql::resultset

[TheSmitchell]

Re: DATABASE Issues

I need help i want to check if a database exist or not. if it doesn't exist, the database should be created otherwise it should perform some other operations based on other conditions. this is my code below.

<%a5
firstSelect = "select * from orders where session ="+ sessionc
	flag = conn.Execute(firstSelect) ' check if user have initiated an order before
	frs = conn.ResultSet
	if ((flag = frs.NextRow()) = null_value()) '  if no create a temporary table with the username or session variable
		newTable = "CREATE TABLE"+ sessionc + "(id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, session VARCHAR(50) NOT NULL,username VARCHAR(100) NOT NULL,item VARCHAR(60),quantity INT(255),price FLOAT(20),total FLOAT(20),transaction VARCHAR(30))"
			conn.Execute(newTable) 'after creating, insert the data into the new table
		
		
	end if
%>

This is the error report i get

500 Internal Server Error

Script ErrorError:Script:" /restuarant/ajaxCallback.ajax.a5w" line:41

	if ((flag = frs.NextRow()) = null_value()) '  if no create a temporary table wi

Variable does not contain a valid result set while getting result set next row.'

I think Alpha is having issues with the = null_value(). The NextRow method returns a true or false value. You can simplify the check by eliminating the = null_value() (which I don't think is helpful here at all) and the assignment:

firstSelect = "select * from orders where session ="+sessionc
flag = conn.Execute(firstSelect) ' check if user have initiated an order before
if (flag = .t.) then
    frs = conn.ResultSet
    if (frs.NextRow() <> .t.) then
        ' if no create a temporary table with the username or session variable
        newTable = "CREATE TABLE"+ sessionc + "(id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, session VARCHAR(50) NOT NULL,username VARCHAR(100) NOT NULL,item VARCHAR(60),quantity INT(255),price FLOAT(20),total FLOAT(20),transaction VARCHAR(30))"
        conn.Execute(newTable) 'after creating, insert the data into the new table
    end if
else
    'first sql statement failed; handle here
end if

Also - I would advise using SQL::Arguments instead of concatenating your sessionc variable to the statement. It makes your SQL easier to read and helps protect from SQL injection/malformed constants. EG:

dim args as sql::arguments
args.set("SESSIONC",sessionc)
firstSelect = "SELECT * FROM orders WHERE session = :SESSIONC"
flag = conn.execute(firstSelect,args)

Good luck!

[mikeallenbrown]

Re: DATABASE Issues

Try using count instead...

<%a5
firstSelect = "select [COLOR="#FF0000"]count(*) as count[/COLOR] from orders where session ="+ sessionc
	flag = conn.Execute(firstSelect) ' check if user have initiated an order before
	frs = conn.ResultSet
	if [COLOR="#FF0000"]conn.ResultSet.data("count") = 0[/COLOR] '  if no create a temporary table with the username or session variable
		newTable = "CREATE TABLE"+ sessionc + "(id INT(20) UNSIGNED AUTO_INCREMENT PRIMARY KEY, session VARCHAR(50) NOT NULL,username VARCHAR(100) NOT NULL,item VARCHAR(60),quantity INT(255),price FLOAT(20),total FLOAT(20),transaction VARCHAR(30))"
			conn.Execute(newTable) 'after creating, insert the data into the new table
		
		
	end if
%>

[sezeala]

Re: DATABASE Issues

Thank you all got it figured out through our help. keep up the good work. cheers