Insert Records With SQL

With Collect!, you have the ability 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, and is not limited to Clients, Debtors, Contacts, Transactions, Attachments, and Cosigners. All record types contained in the databases (i.e. 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" databases. See the 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, your results may display gibberish data rather than the expected values.

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

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" is located by the menu path \System\ Network and Environment\Launch the CV11 Admin Tool. Once the Admin Tool is launched, you click on RDS and log in with your administrator credentials. The SQL Browser is the last option in the visual icons to choose from in the icon hierarchy which will open under the RDS blue catalog of icon options. This browser can be used for testing your SQL inserts and updates. See the Help topic, CV11 Admin Tool for more info on this subject.

You must end your queries with a commit; command to finalize the insertion of your record into a 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.