FM.SQL.Text

Queries text of SQL result.

Parameters

Parameter	Description	Example	Flags
SQLref	The reference number returned by FM.SQL.Execute function.	$SQLRef
firstRow	The index of first row. Default is 0.	0	Optional
lastRow	The index of last row. Default is FM.SQL.RowCount-1.	5	Optional
firstCol	The index of first column. Default is 0.	0	Optional
lastCol	The index of last column. Default is FM.SQL.FieldCount-1.	6	Optional
rowSeparator	The row separator. Default is CR. Can be more than one character.	Char(13)	Optional
colSeparator	The column separator. Default is tab character. Can be more than one character.	Char(9)	Optional

Result

Returns text or error.

Description

Examples

Query fields and export as text and CSV:

Set Variable [$sql; Value:MBS( "FM.SQL.Execute"; ""; "select * from Clients")]
If [MBS("IsError")]
    Show Custom Dialog ["SQL error"; $sql]
Else
    Show Custom Dialog ["SQL result"; "got " & MBS( "FM.SQL.RowCount"; $sql ) &" rows with each " & MBS( "FM.SQL.FieldCount"; $sql ) & " …"]
    # Write to text file
    Set Variable [$text; Value:MBS( "FM.SQL.Text"; $sql )]
    Set Variable [$desktopFolder; Value:MBS( "Folders.UserDesktop" )]
    Set Variable [$path; Value:MBS( "Path.AddPathComponent"; $desktopFolder; "fields.txt" )]
    Set Variable [$r; Value:MBS( "Text.WriteTextFile"; $text; $path; "UTF8")]
    # Write to text file
    Set Variable [$text; Value:MBS( "FM.SQL.CSV"; $sql )]
    Set Variable [$path; Value:MBS( "Path.AddPathComponent"; $desktopFolder; "fields.csv" )]
    Set Variable [$r; Value:MBS( "Text.WriteTextFile"; $text; $path; "UTF8")]
    # Cleanup
    Set Variable [$r; Value:MBS( "FM.SQL.Release"; $sql )]
End If

See also

• FM.SQL.Execute
• FM.SQL.FieldCount
• FM.SQL.Max
• FM.SQL.Release
• FM.SQL.RowCount
• FM.SQL.Sum
• IsError
• Path.AddPathComponent
• SQL.Execute
• Text.WriteTextFile

Release notes

• Version 13.0
  • Changed FM.SQL.Text and FM.SQL.CSV functions to return empty text if there are no rows and no row parameter.
• Version 7.0
  • Added FM.SQL.Text and FM.SQL.CSV.

Blog Entries

• MBS FileMaker Plugin, version 12.6pr2
• FileMaker SQL with Sum, Min, Max and Avg on results
• CSV functions in MBS FileMaker Plugin
• MBS FileMaker Plugin, version 7.0pr2

This function checks for a license.

Created 8th January 2017, last changed 31st March 2023

FM.SQL.Sum - FM.SQL.ToMatrix