2. PHPRunner General questions
  3. SQL Variables in SQL
This topic is locked

SQL Variables in SQL

 		9/19/2017 4:22:08 PM
PHPRunner General questions
G
 GregJ author

Updating Members table with 'After Record Added' event on another table.
This works just fine:

$sql = "update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=".$values["MemberId"];




Using new SQL variable method (9.8) doesn't work though. (No errors, just doesn't work):

$sql = "update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=':MemberId'";
Do I have the syntax wrong or misunderstanding the use of the variable?
C
 		copper21 9/19/2017

Syntax appears to incorrect. It should be something like:
$sql = "update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=".$values['MemberID']."";
This is assuming that "MemberID" is an integer.
Brian
G
 		GregJ author 9/20/2017



Syntax appears to incorrect. It should be something like:
$sql = "update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=".$values['MemberId']."";
This is assuming that "MemberID" is an integer.
Brian


The first example I gave works fine. It's the second example using PHPR 9.8 SQL variable :MemberId that doesn't work.
C
 		copper21 9/20/2017

I see what you are saying....that is a new feature in 9.8 that I was unaware of. I think that those variables may only work in the lookup wizard area, not in the events area.
Admin 9/20/2017

Try this:

$sql = DB::prepareSQL("update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=':MemberId'");

DB:Exec( $sql );


We are still working on updating the manual and this info will make this there soon.
G
 		GregJ author 9/20/2017



Try this:

$sql = DB::prepareSQL("update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=':MemberId'");

DB:Exec( $sql );


We are still working on updating the manual and this info will make this there soon.


Still doesn't work, no errors.

Don't worry about it, I can stick with the old way for now, which is working. I was just trying to figure out some of the new features in 9.8. The manual will help with that. Thanks Sergey.
Admin 9/20/2017

You need to print your SQL Query on the web page instead of executing it so what went wrong.
G
 		GregJ author 9/20/2017



You need to print your SQL Query on the web page instead of executing it so what went wrong.


Debug On.
Old Method:

select GroupID, UserName from physiorunnerv2_ugmembers where UserName='9999'

select TableName, AccessMask from physiorunnerv2_ugrights where GroupID in (2)

insert into ConcurrentRegistrations (ProvinceCode, MemberId) values (935, 608)

INSERT INTO physioadmin_audit (datetime,ip,user,table,action,description) VALUES ('2017-09-20 14:42:00','149.56.97.155','9999','R_Concurrent','add','---Keys\r\nId : 101\r\n---Fields\r\nProvinceCode [new]: 935\r\nMemberId [new]: 608\r\n')
New Method:

select GroupID, UserName from physiorunnerv2_ugmembers where UserName='9999'

select TableName, AccessMask from physiorunnerv2_ugrights where GroupID in (2)

insert into ConcurrentRegistrations (ProvinceCode, MemberId) values (947, 608)

INSERT INTO physioadmin_audit (datetime,ip,user,table,action,description) VALUES ('2017-09-20 14:45:37','149.56.97.155','9999','R_Concurrent','add','---Keys\r\nId : 102\r\n---Fields\r\nProvinceCode [new]: 947\r\nMemberId [new]: 608\r\n')

[color="#ff0000"]New method query not showing here at all.
This is my 'After Record Added' event:

// old method

// $sql = "update Members set regreview=1, renewalNotes=concat(renewalNotes,' | Conc') where MemberID=".$values["MemberId"];

// CustomQuery($sql);
// ver 9.8 new way (currently not working)

$sql = DB::prepareSQL("update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=':MemberId'");

DB:Exec( $sql );
UPDATE:

Adding a double colon in the second line of the script above (DB::Exec($sql) enables the script but now it seems it can't figure out what to do with the [size="2"]':MemberId' field name. Tried a :: there too but no good...I'm just guessing now. (see debug info below)[/size]
[color="#2C3E50"]

update Members set regreview=1, renewalNotes=concat(renewalNotes,' | ConcReg') where MemberID=