Insert Records With SQL

Collect! enables you to insert records into your database from an external application with an ODBC connection. Records from any Collect! SQL record type may be added. This includes Clients, Debtors, Contacts, Transactions, Attachments, Cosigners and more. Basically, all record types contained in the "collect" databases (d00_collect, d01_collect, d02_collect) may be inserted from external queries.

You can use the CV11 Admin Tool to view the complete list of SQL record types in the "collect" database. Please refer to Help topic, CV11 Admin Tool for details.

Inserting records into Collect! with ODBC and SQL is available in Collect! Version 11.4.1.1 and newer.

SQL Insert

You can create new records in Collect! using ODBC and SQL. Collect! creates a unique identifier whenever a record is created. This ensures the integrity of your database. The result is the same as if you entered the record manually or through an import routine.

One important difference is that when you insert a record through ODBC and SQL, all numeric fields in the new record must be properly initialized by your SQL query. If you do not initialize the fields, several fields in your new record may display "garbage" data.

SQL templates for each record are available for download in the Collect! Member Center Widgets area. You can use these as examples for creating properly initialized field data for each record type.

Download SQL Templates for SQL Insert

SQL Update

You can update records in Collect! using ODBC and SQL. For updated records, you only need to update the fields you want to change.

Testing SQL Queries

The CV11 Admin Tool SQL Browser can be used for testing your SQL inserts and updates. Please refer to Help topic, CV11 Admin Tool for details.

You must end your queries with a commit; command to finalize the insertion of your record into the Collect! database.

ODBC Writing To Notes

When writing to Notes using SQL, you may encounter an Error 22001 - File too big.

Changing the value of MAXSTRING in the catalog\rdmserver.ini in the "SQL PARAMETERS" section should correct this.

[SQL]
MaxString=256
;
; Specifies the maximum length of a string literal.