ExecuteSQL pk grab

[Chris Scalzi]

I have a question which might just come down to improper syntax. I'm giving my first attempt at using the ExecuteSQL command and I've gone through some of the primers (specifically the one by Beverly) but I am having trouble with what seems like a basic query. 

 

I'm attempting to find the primary key of my record in a table using two fields and their data. Here's what I got:

Let (
    [ 
    x = 1;
    y = 2013;

    $query = " SELECT _pkLotteryRecordID
    FROM Lottery
    WHERE LotteryGrade = ? 
    AND LotterYearOnly = ? "

    ; $result = ExecuteSQL ( "$query"; "" ; "" ; x ; y )

    ]; $result
    
    )

[dansmith65]

Field names that start with/contain certain characters must be quoted, so change _pkLotteryRecordID to "_pkLotteryRecordID". Or use the SQLField custom function, which is the method I prefer: http://www.fmfunctions.com/members_display_record?memberId=375

[Chris Scalzi]

Thanks Dan! I ended up adding your suggestion as well as quoting the values of my variables and double checking my white space. My final code ended up being: 

Let (
[
x = "1";
y = "2013";

$query = " SELECT "_pkLotteryRecordID" FROM Lottery WHERE LotteryGrade = ? AND LotteryYearOnly = ? ";

$result = ExecuteSQL ( $query ; "" ; "" ; x ; y )

]; $result
)

 

 

Field names that start with/contain certain characters must be quoted, so change _pkLotteryRecordID to "_pkLotteryRecordID". Or use the SQLField custom function, which is the method I prefer: http://www.fmfunctions.com/members_display_record?memberId=375

[Wim Decorte]

one side note: try to avoid declaring $ variables in your let function.  $ variables live past the scope of the let function (their scope is the script) so you may run into issues when you use the same variable in another script step.

[dansmith65]

one side note: try to avoid declaring $ variables in your let function.  $ variables live past the scope of the let function (their scope is the script) so you may run into issues when you use the same variable in another script step.

 

I wouldn't say to avoid using $ variables in a let function, but I would say to use them appropriately, keeping in mind that they persist past the calculation they are used in.

 

I think the use of $query and $result in an SQL calculation like this is appropriate because it allows you to step through a script with the script debugger and see the query and result, even if you do post-processing of the result before returning it. This is very handy when debugging scripts that use SQL queries. Personally, I namespace the variables like so: $sqlQuery and $sqlResult  https://gist.github.com/dansmith65/4684647