Exporting Single Record from found set. (Server Side Script)

[Tyra]

I am in the process of converting scripts from a robot client to run as server side scripts and this is a common one that I have. This works, but exits with a script error of (101). I know about the big in server side scripts that produces this bug with "Go Record Next (Exit after last). Which happens in other files, but this one actualy shows up in the Schedule Log as an error. Also was womdering if any one had any ideas on simplifying this script without invoking a new window. I know of the omit method, but I can't get that to work with a found set..

 

Go to Layout [“Client Export” (Company)]

Set Variable [$CTime; Value:Get ( CurrentTimeStamp )]

#Export last hour

Set Variable [$sdate; Value:Get ( CurrentTimeStamp ) - 3600]

Set Variable [$date; Value:" ≥ "&$sdate]

Show All Records

Set Error Capture [On]

Enter Find Mode []

#Find by Date (Normal)

Set Field [Company::ts_Mod; $date]

Perform Find []

If [Get ( FoundCount ) = 0]

Exit Script []

End If

##Export Records##

Go to Record/Request/Page [First]

Loop

New Window [Name: "Export Client"; Style: Document]

Set Variable [$rec; Value:Company::n_SNComp]

Set Variable [$name; Value:Company::n_SNComp&".client.xml"]

Enter Find Mode []

Set Field [Company::n_SNComp; $rec]

Perform Find []

#Remote Export

Export Records [No dialog; “$name”; "FMPDSORESULT"; Unicode (UTF-8)]

Close Window [Name: "Export Client"; Current file]

Go to Record/Request/Page [Next; Exit after last]

End Loop

 

 

[Wim Decorte]

A quick observation, this:

Enter Find Mode []
Set Field [Company::n_SNComp; $rec]
Perform Find []

is dangerous code, you may get more records than you bargain for.  Use:

Enter Find Mode []
Set Field [Company::n_SNComp; "==" & $rec]
Perform Find []

In general though, I would use an ExecuteSQL() call to find all the record ids that match your search request, store them in a variable and loop through those.  That will eliminate the need to spawn a new window to preserve your original found set.

[Tyra]

Thanks for the quick tip Wim. I have not used the ExecuteSQL() function much but in playing with, I am getting a wierd result.. I run:

 

Let ([

$query =

“SELECT ts_Mod

FROM COMPANY

WHERE ts_Mod < '2014-01-01 00:00:00'”;

$result = ExecuteSQL( $query ; “” ; “”)

];

$result)

 

I get all records. both before 2014 and after... (the "ts_Mod" field is a modification timestamp) 

 

If I change it to "WHERE ts_Mod > '2014-01-01 00:00:00'”;

 

I get none..

[Wim Decorte]

As an aside: don't declare local variables ($) as part of a let in situations like this.

Use calculation variables.  The scope is different and important:

 

http://www.soliantconsulting.com/blog/2014/01/all-variables-should-be-global-or-not

[Wim Decorte]

This query works for me:

 

Let (

[

_query =

"SELECT ts_Mod

FROM COMPANY

WHERE ts_Mod < ?";

_result = ExecuteSQL( _query ; "" ; "" ; Timestamp ( Date ( 1 ; 1 ; 2014 ) ; Time ( 0 ; 0 ; 0 ) ))

];

_result

)

 

Note that I let FM format the timestamp and pass it as a parameter to teh query, so that I don't have to remember to format it correctly and quote it properly

[Tyra]

Ok, I have been able to get the following to work:

 

Let (

[tt = Get ( CurrentTimeStamp )-7200;

_query =
"SELECT ts_Mod
FROM COMPANY
WHERE ts_Mod > ?";
_result = ExecuteSQL( _query ; "" ; "" ; tt)
];
_result
)

 

but would like to use something like:

 

Let (

[

_query =

"SELECT ts_Mod

FROM COMPANY

WHERE ts_Mod > DATE_SUB(NOW(), INTERVAL 2 HOUR)";

_result = ExecuteSQL( _query ; "" ; "" )

];

_result

)

 

but of course there is the time format difference..

[Wim Decorte]

AFAIK, DATE_SUB is a MySQL function, not a generic SQL function.

 

What would be different in the result from what you have working?

[Tyra]

Nothing, was just trying to reduce lines of code ;-) I really apprciate all your help. Will move forward with the working version above. 

[Tyra]

This is the script I ended up with. The list removal part seems a bit clunky, but it works. (I'm using "" instead of isempty for no psrticular reason).

 

Go to Layout [“Client Export” (Company)]

#Export last two days

//  Set Variable [$rec; Value:Let ( [tt = Get ( CurrentTimeStamp )-172800;  _query = "SELECT n_SNComp FROM COMPANY WHERE ts_Mod > ?"; _result = ExecuteSQL( _query ; "" ; "" ; tt) ]; _result )]

#Export last 2 hours

Set Variable [$rec; Value:Let ( [tt = Get ( CurrentTimeStamp )-7200;  _query = "SELECT n_SNComp FROM COMPANY WHERE ts_Mod > ?"; _result = ExecuteSQL( _query ; "" ; "" ; tt) ]; _result )]

Show All Records

If [$rec= ""]

Exit Script []

End If

##Export Records##

Loop

Set Variable [$name; Value:Left ($rec; Position ( $rec ; "¶" ; 1 ; 1 )-1)]

If [$name=""]

Set Variable [$name; Value:$rec]

End If

Enter Find Mode []

Set Field [Company::n_SNComp; $name]

Set Error Capture [On]

Perform Find []

Set Variable [$filename; Value:Company::n_SNComp&".client.xml"]

Export Records [No dialog; “$filename”; "FMPDSORESULT"; Unicode (UTF-8)]

If [WordCount ( $rec ) > 1]

Set Variable [$rec; Value:Substitute ( $rec; $name &"¶"; "" )]

Else

Set Variable [$rec; Value:Substitute ( $rec; $name; "" )]

End If

Exit Loop If [$rec=""]

End Loop