Bug? Custom Ajax button working on local, but giving errors in production?

Scriptcase 9 General
#1

Hello, I have an issue, I have a custom Button of type Ajax, and it works correctly in local, but as soon as I put the project in production (MS SQL server on Apache environment), whenever I click this Ajax button I get this error:

“Error while accessing the database
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server] incorrect syntax near ‘)’”

Then I try to click on “View SQL” but nothing happens.

The code is simple, it’s an Insert (sc_exec_sql) followed by the sc_lookup and finally a sc_redir.

The insert happens even if I get this error, so what doesn’t happen is the sc_redir.

This is the code:


$sql_insert = "INSERT INTO dbo.TblItem(ItemTyp, ItemName) VALUES ( '{ItemTyp}','{ItemName}')";

sc_exec_sql($sql_insert);

sc_lookup(id,"SELECT SCOPE_IDENTITY()");

$new_itemID=$id[0][0];

sc_redir('item_details_newly_added', selectedItemID=$new_itemID, '_parent');

so if the INSERT is executed (I see the new record being added in my grid), this means something happens after the INSERT to get the error.

The only ‘)’ that I can see (the error says there is a syntax error near ‘)’,
is or in the sc_lookup or in the sc_redir.

It might be the SCOPE_IDENTITY() ?

But that would be strange because on local it works, and my local scriptcase is connected to the same exact DB that is connected to production!

What this could be?

#2

If you rem all lines past the sc_exec_sql () command, do you still get the error?

If not, it should defintely be sc_lookup() that is generating the error.

If you’re sure there are no DB permissions issues, the only suggestion I can come up with is to terminate the sql command with a ‘;’.

I.e.

sc_lookup(id,“SELECT SCOPE_IDENTITY();”);

But I won’t bet on this solving your issue :slight_smile:
Especially since the trailing ‘;’ is the syntax for terminating SQL statements in MySql, but I dont’ know if it’s standard SQL syntex and so if it’s usable with MSSQL.

#3

I would try

$new_itemID = {id[0][0]};
#4

thank you for the answer,
first of all, can’t test right now changes on production, as I would need to redeploy and until monday I won’t be able.

But I’m pretty sure that up to the sc_exec_sql() command everything works because the INSERT gets executed and even if I get the error after pushing the button, I can see in the DB that the record has been INSERTED.

And for the thing about terminating the commands with ‘;’, as you can see in the code I posted, sadly I already put ‘;’ at the end of every command :frowning:

#5

hello, thank you for the answer, can’t test this right now because I should redeploy the project to test it, but why do you think this is the error? is there difference in using $id or {id} ?

And the error says near ‘)’ so, wouldn’t that mean it must be a syntax error right next to a parenthesis?

#6

You didn’t put a ‘;’ as part of the SQL statement.
My suggestion was to add it there:

sc_lookup(id,“SELECT SCOPE_IDENTITY()[SIZE=14px];[/SIZE]”);

But as I wrote, that’s a syntax that works with mySql, but I don’t know with MSSQL. Also, I have lilttle hope it will solve anything anyway

And the error is generated by the SQL Server driver, so it should be related to something that is parsed by the SQL server and not by SC or by PHP.
At least in theory…