Difference between revisions of "DbWrite"
(DbConnection) |
|||
| Line 2: | Line 2: | ||
[[Category:Doc Status C]] <!-- For Lumina use, do not change --> | [[Category:Doc Status C]] <!-- For Lumina use, do not change --> | ||
| − | + | The [[DbWrite]] function writes data and sends other SQL to access or modify data to an external ODBC database. | |
| − | + | ''Requires [[Analyica Enterprise]] or above.'' | |
== DbWrite(connection, sql) == | == 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. | + | Connects to a data source «connection» 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. | 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 | + | == Processing multiple queries == |
| − | Some databases don't accept | + | Some databases don't accept multiple operations (insertions, deletions, etc.) in a single query. In these cases, you can separate your queries in the same «sql» text with double semicolons (<code>;;</code>). When [[DbWrite]] (or [[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 === | === Parameters === | ||
| Line 22: | Line 22: | ||
* «sql»: The SQL query text. | * «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. | * «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»: | + | * «datesAsText»: 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, | + | The «key» and «datesAsText» parameters are only meaningful when «sql» returns a result set, when you should normally be using [[DbQuery]] rather than [[DbWrite]]. Sothey are rarely used for [DbWrite]]. |
== Examples == | == Examples == | ||
Latest revision as of 17:58, 10 February 2024
The DbWrite function writes data and sends other SQL to access or modify data to an external ODBC database.
Requires Analyica Enterprise or above.
DbWrite(connection, sql)
Connects to a data source «connection» 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 multiple queries
Some databases don't accept multiple operations (insertions, deletions, etc.) in a single query. In these cases, you can separate your queries in the same «sql» text with double semicolons (;;). When DbWrite (or 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»: 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, when you should normally be using DbQuery rather than DbWrite. Sothey are rarely used for [DbWrite]].
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')")
Enable comment auto-refresher