Tech Tip: No Data Returned in SQL EXECUTE from SQL Server

PRODUCT: 4D | VERSION: 18 | PLATFORM: Mac & Win

Published On: March 22, 2021

4D can retreive data from a SQL server by setting up a connection using the SQL server's ODBC driver and performing a select statement with SQL EXECUTE from 4D. Remember to call the command SQL LOAD RECORD after the SQL EXECUTE call to retrieve the data. Failure to include the command SQL LOAD RECORD may result in 0 records being retrieved.

Below shows an example of retreiving customer ID's less than 10 from the customer table in the SQL server. The longint array $cusID_a retrieves the data. If the SQL LOAD RECORD line is removed, no data is returned.

SQL LOGIN("SQL_Server";"username";"password")
ARRAY LONGINT($cusID_a;0)

$statement:="SELECT customer_ID FROM customer_table WHERE customer_ID<10"
SQL EXECUTE($statement;$cusID_a)
SQL LOAD RECORD(SQL all records)

SQL LOGOUT