SQL.GetFieldAsText

Returns field's value as text value; converts if needed.

Parameters

Parameter	Description	Example
Command	The command reference number gained with SQL.NewCommand.	$Command
Field	The name or one based index of the field.	$Field

Result

Returns text or error message.

Description

Examples

Read field value of field "FirstName" into variable:

$firstname = MBS("SQL.GetFieldAsText"; $command; "FirstName")

Run a stored procedure:

# new command for calling our test procedure on a Microsoft SQL Server
Set Variable [$command; MBS( "SQL.NewCommand"; $Connection; "dbo.Test" ) ]
# set a parameter
Set Variable [$r; MBS( "SQL.SetParamAsNumber"; $Command; "Param1"; 7 ) ]
# execute query
Set Variable [$r; MBS( "SQL.Execute"; $Command ) ]
# fetch first record
Set Variable [$r; MBS( "SQL.FetchNext"; $Command ) ]
# get result field
Set Variable [$result; MBS( "SQL.GetFieldAsText"; $Command; 1 ) ]

Read null value as empty, otherwise get text:

If(
      MBS("SQL.GetFieldIsNull"; $command; "FieldName");
      "";
      MBS("SQL.GetFieldAsText"; $command; "FieldName"))

Extract BLOBs from a Oracle database and store in files:

Extract BLOBs from a Oracle database and store in files:

Set Variable [ $Connection ; Value: MBS("SQL.NewConnection") ]
Set Variable [ $result ; Value: MBS("SQL.SetConnectionOption";$Connection;"OCI8.LIBS";"/Users/test/Downloads/instantclient_19_8/libclntsh.dylib") ]

Set Variable [ $result ; Value: MBS("SQL.Connect";$Connection;"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.128.12.150)(PORT=1521))(CONNECT_DATA=(SID=STRMTL)))";"User”;"Password";"Oracle") ]
If [ MBS("ISError") ]
    Show Custom Dialog [ "Failed to connect"; $result ]
Else
    Set Variable [ $sql ; Value: "SELECT FILE_NAME, FILE_DATA, FILE_DESCRIPTION FROM UPLOADED_FILE OFFSET 1452000 ROWS FETCH NEXT 10 ROWS ONLY" ]
    Set Variable [ $cmd ; Value: MBS("SQL.NewCommand"; $Connection; $sql) ]
    If [ MBS("ISError") ]
        Show Custom Dialog [ "Failed to parse SQL"; $cmd ]
    Else
        Set Variable [ $r ; Value: MBS("SQL.Execute"; $cmd) ]
        If [ MBS("ISError") ]
            Show Custom Dialog [ "Failed to run query"; $r ]
        Else
            Set Variable [ $folderPath ; Value: "/Users/test/Desktop/PDF/folder" ]
            #
            Set Variable [ $r ; Value: MBS("SQL.FetchNext"; $cmd) ]
            If [ MBS("ISError") ]
                # Failed to get record?
                Show Custom Dialog [ $r ]
            Else If [ $r = 0 ]
                # no records found
            Else
                Loop
                    # get file name
                    Set Variable [ $filename ; Value: MBS("SQL.GetFieldAsText"; $cmd; "FILE_NAME") ]
                    # get as BLOB with PDF
                    Set Variable [ $compressed ; Value: MBS("SQL.GetFieldAsContainer"; $cmd; "FILE_DATA"; $fileName) ]
                    #
                    # store in field
                    // New Record/Request
                    // Set Field [ SQL_API::PDF ; $PDF ]
                    // Commit Records/Requests [ With dialog: On ]
                    #
                    # write to file
                    Set Variable [ $filePath ; Value: MBS("Path.AddPathComponent"; $folderPath; $filename) ]
                    Set Variable [ $r ; Value: MBS("Container.WriteFile"; $PDF; $filePath) ]
                    #
                    # loop ends after last field
                    Set Variable [ $r ; Value: MBS("SQL.FetchNext"; $cmd) ]
                    Exit Loop If [ $r ≠ 1 ]
                End Loop
            End If
        End If
        Set Variable [ $r ; Value: MBS("SQL.FreeCommand"; $cmd) ]
    End If
End If
Set Variable [ $r ; Value: MBS("SQL.FreeConnection"; $Connection) ]

See also

• SQL.FreeConnection
• SQL.GetFieldAsContainer
• SQL.GetFieldAsDate
• SQL.GetFieldAsFloat
• SQL.GetFieldIsNull
• SQL.GetFieldsAsJSON
• SQL.GetParamAsText
• SQL.NewCommand
• SQL.NewConnection
• SQL.SetParamAsNumber

Example Databases

• SQL to other databases/Firebird Query
• SQL to other databases/Microsoft SQL Execute
• SQL to other databases/Microsoft SQL Server Connect
• SQL to other databases/Microsoft SQL Server Query
• SQL to other databases/MySQL example
• SQL to other databases/MySQL Query
• SQL to other databases/ODBC Query
• SQL to other databases/PostgreSQL Query
• SQL to other databases/SQLite blob
• SQL to other databases/SQLite fun

Blog Entries

• Tip of the day: Connect to MySQL and run a query

This function is free to use.

Created 18th August 2014, last changed 9th May 2022

SQL.GetFieldAsPNG - SQL.GetFieldAsTime