DbWrite

Revision as of 23:55, 6 September 2023 by Lchrisman (talk | contribs) (DbConnection)


(Requires Analyica Enterprise or Power Player)

Use the DbWrite function to write data to an external ODBC database.

DbWrite(connection, sql)

Connects to a data source and performs an SQL query in read-write mode. This function is identical to DbQuery() except that the SQL query is processed in read-write mode, making it possible to store data in the data source from within Analytica. If you aren't changing the data base, use DbQuery instead, to minimize the changes of making a mistake that messes up your data in the data base.

In most use cases, the «sql» statement serves to change the database rather than to retrieve data, and hence, you'll usually ignore the return value. However, if you issue an SQL statement that does return a record set, then like DbQuery, the result is a list that you will used to index the result set.

Processing as separate queries

Some databases don't accept sequential operations (insertions, deletions, etc.) is a single query. In these cases, you can separate your queries in the same «sql» text with double semicolons (;;). When DbWrite (on DbQuery) sees a double semicolon, it splits the «sql» text and sends each part as a separate query, but using the same sql context.

Parameters

All parameters are the same as for DbQuery.

  • «connection»: the data source that you want to query. It is either a textual connection string or a «Database connection» obtained from a previous call to DbConnection.
  • «sql»: The SQL query text.
  • «key»: In the case where «sql» returns a meaningful result set, use this if you want to use one of the result columns and the index row labels. Provide the column name from the result set.
  • «datesAsText»: In a result set, set this to true if you want dates (and times and date-times) to be returned as text instead as date-time numbers.

The «key» and «datesAsText» parameters are only meaningful when «sql» returns a result set, which is not the prototypical use case for DbWrite. Hence, «key» and «datesAsText» are usually not used.

Examples

DbWrite is often used to execute SQL statements like INSERT INTO, CREATE TABLE, UPDATE, etc. -- statements that make changes.

DbWrite("DSN=CrmDb", "INSERT INTO CustomerTable(Name,City,State,Country) VALUES ('James L. White','Carlsbad','NM','USA')")

See Also

Comments


You are not allowed to post comments.