Funciones de Base de Datos

Las funciones de base de datos brindan acceso a las actividades básicas de la base de datos.

CacheLookup

Declaración

string CacheLookup(string databaseId, string sql)

Sintaxis

CacheLookup(<databaseId>, <sql>)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual
• sql: El comando SQL que se ejecutará en la base de datos

Descripción

Esta función es la misma que DBLookup, excepto que la primera búsqueda almacena en caché la información y las búsquedas posteriores utilizan este caché en lugar de consultar repetidamente la base de datos. (Una alternativa al almacenamiento en caché es utilizar las funciones Set y Get.)

Si no hay filas devueltas para la consultar especificada en sql, la función devuelve nulo.

La variable global Jitterbit $jitterbit.scripting.db.rows_affected no se establece con este método.

La base de datos utilizada en esta llamada de función debe definirse como origen o destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

// Looking up in a database using a SQL string
CacheLookup("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

CallStoredProcedure

Declaración

type CallStoredProcedure(string databaseId, string spName, type resultSet[, string inputOutputVariable,...])

Sintaxis

CallStoredProcedure(<databaseId>, <spName>, <resultSet>[, <inputOutputVariable>,...])

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual
• spName: El procedimiento almacenado que se ejecutará en el servidor de base de datos
• resultSet: Una variable global que contiene el conjunto de resultados devuelto por el servidor de base de datos, si corresponde. (Ver notas a continuación).

Parámetros Opcionales

• inputOutputVariable: Un parámetro de entrada o salida que se pasará al procedimiento almacenado; estos parámetros se agregan según lo requiera la firma del procedimiento almacenado

Descripción

Llama al procedimiento almacenado spName utilizando la información de conexión especificada por la fuente/destino identificado por databaseId.

Si corresponde, el material devuelto resultSet es una matriz bidimensional de cadenas. Si el procedimiento almacenado no devuelve ningún valor resultSet o si se utiliza un controlador ODBC, este argumento se ignora.

Los parámetros opcionales restantes se utilizan para pasar argumentos de entrada y salida al procedimiento almacenado. La cantidad de argumentos necesarios depende de la firma del procedimiento almacenado.

Los argumentos de entrada pueden ser un valor codificado, el valor de una fuente o el valor de un cálculo o fórmula. Los argumentos de salida (incluidos los resultSet) se especifican por referencia como "$name", dónde "name" es el nombre de la variable global que contendrá el valor de salida. El valor de retorno y el tipo de la función son el valor de retorno y el tipo del procedimiento almacenado.

La base de datos utilizada en esta llamada de función debe definirse como una fuente o un destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

Ejemplo 1: Llamar a un Procedimiento Almacenado Sin Ningún Conjunto de Resultados

// Calls a stored procedure "MyStoredProcedure",
// which takes one input variable, one output variable,
// and ignores the result set.
// "Input" is the name of the source global variable
// that provides the input and
// "output" is the name of the global variable
// used to store the output:

CallStoredProcedure("<TAG>Sources/myDBTarget</TAG>",
  "MyStoredProcedure", 0, Input, $output);

// The value of the output parameter can be accessed
// by using either $output or Get("output")

Ejemplo 2: Llamar a un Procedimiento Almacenado con un Conjunto de Resultados

// Calls a stored procedure "GetValues",
// which takes two input variables and returns a result set.
// The result set is returned as the two-dimensional array $result.
// The result can be accessed by using either $result or Get("result"):

CallStoredProcedure("<TAG>Sources/myDBTarget</TAG>", "GetValues", $result, Input1, Input2);

Ejemplo 3: Llamar a un Procedimiento Almacenado Que Accede a un Tipo de Objeto de Oracle

Uso de Tipos de Objetos y Registros de Oracle

Jitterbit admite tipos de objetos de Oracle para trabajar con bases de datos Oracle cuando se utiliza el controlador JDBC de Oracle. Los tipos de objetos de Oracle son similares a los tipos de registros de Oracle, que no son compatibles con Jitterbit debido a la falta de compatibilidad por parte de Oracle.

Para acceder a los tipos de registros de Oracle mediante el controlador JDBC de Oracle, puede crear un procedimiento almacenado "envoltorio" en su base de datos Oracle que pueda acceder a un tipo de registro de Oracle y convertirlo. Luego, utilice el CallStoredProcedure función en Jitterbit para llamar al procedimiento contenedor y hacer que realice la conversión hacia y desde un tipo de objeto Oracle.

El ejemplo que sigue describe cómo puede utilizar Oracle Objects en un CallStoredProcedure funcionar de manera simplificada.

Definiciones de Tipos de Oracle

Una definición de tipo de objeto de Oracle sigue este patrón:

CREATE OR REPLACE TYPE example_customer_details AS OBJECT
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);

Una definición de tipo de registro de Oracle sigue este patrón:

CREATE TYPE example_customer_details IS RECORD
(status NUMBER
,party_id NUMBER
,account_id VARCHAR
);

Pasos de Ejemplo

Paso 1: Crear el objeto
Para utilizar tipos de objetos de Oracle, primero cree el objeto en la base de datos de Oracle:

CREATE OR REPLACE TYPE example_customer_details AS OBJECT
(status                    NUMBER
,party_id                  NUMBER
,account_id                VARCHAR
);

Paso 2: Crear el paquete
A continuación, cree el paquete como una función en la base de datos de Oracle:

CREATE OR REPLACE PACKAGE example AS
  FUNCTION processcustomer(custin IN example_customer_details, new_account_number IN VARCHAR) RETURN example_customer_details;
END example;

Paso 3: Crear el cuerpo del paquete
A continuación, cree el cuerpo del paquete como una función en la base de datos de Oracle:

CREATE OR REPLACE PACKAGE BODY example AS
FUNCTION processcustomer(custin IN example_customer_details, new_account_number IN varchar) RETURN example_customer_details
  IS
  custout example_customer_details;
  BEGIN
     custout := example_customer_details(
     custin.status + 1,
     custin.party_id,
     new_account_number
     );
     return custout;
END;
END example;

Paso 4: Llamar al procedimiento almacenado en Jitterbit
Ahora está listo para llamar al procedimiento almacenado processcustomer desde Jitterbit usando el CallStoredProcedure función. Este secuencia de comandos de ejemplo muestra cómo pasar un objeto a la CallStoredProcedure función. También puede pasar objetos desde un procedimiento almacenado como parámetros de retorno o salida de manera similar.

<trans>
$cust = dict();
$cust["status"] = 1;
$cust["party_id"] = 10;
$cust["account_id"] = "2341";

$custout = CallStoredProcedure("<TAG>Sources/OracleDatabase</TAG>",
    "EXAMPLE.PROCESSCUSTOMER", "", $cust,"NA0233");

$result = "Status: " + $custout["STATUS"] +
  "  Party ID: " + $custout["PARTY_ID"] +
  "  Account ID:  " + $custout["ACCOUNT_ID"];

WriteToOperationLog("Resulting object: " + $result);
</trans>

DBCloseConnection

Declaración

void DBCloseConnection(string databaseId)

Sintaxis

DBCloseConnection(<databaseId>)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual

Descripción

Confirma la transacción actual y cierra la conexión a la base de datos.

La base de datos utilizada en esta llamada de función debe definirse como origen o destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

// Closing a database connection
DBCloseConnection("<TAG>Sources/myDBTarget</TAG>");

DBExecute

Declaración

array DBExecute(string databaseId, string sql)

int DBExecute(string databaseId, string sql, string outputVariable,...)

Sintaxis

DBExecute(<databaseId>, <sql>)

DBExecute(<databaseId>, <sql>, <outputVariable>,...)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual
• sql: El comando SQL que se ejecutará contra la base de datos
• outputVariable: (Segunda forma) Un parámetro de salida que coincide con los campos devueltos en el comando SQL. Se pueden especificar argumentos adicionales según sea necesario.

Descripción

Ejecuta una sentencia SQL en una base de datos y devuelve los resultados.

Si la sentencia SQL produce un conjunto de resultados, hay dos formas de recuperar los datos:

• Si especifica solo los dos parámetros requeridos (primer formulario), la función devolverá el conjunto de registros completo como una matriz de filas.

  Luego puede usar una While() bucle para iterar sobre las filas y usar Get() para recuperar los datos. Si no se devuelven filas, el método devuelve una matriz vacía (Length($arr) == 0).

• Si especifica variables de salida además de los dos parámetros requeridos (segunda forma), se devuelven los valores de los campos de la primera fila.

  Pase los nombres de las variables globales entre comillas como parámetros después de los dos primeros parámetros. El valor del primer campo de la primera fila se escribirá en la variable global pasada como tercer parámetro, el segundo campo de la primera fila en el cuarto parámetro, y así sucesivamente. Alternativamente, las variables globales se pueden pasar por referencia precediéndolas con un signo $, como $output.

  El valor de retorno en este caso es el número de registros devueltos; 1(si se encontraron registros) o 0(si no se devolvió ninguno).

Los valores de datos devueltos siempre son cadenas. Los datos binarios se devuelven como su representación en cadena hexadecimal.

La base de datos utilizada en esta llamada de función debe definirse como una fuente o un destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Variables de Jitterbit relacionadas

• Si este método se completa correctamente, $jitterbit.scripting.db.rows_affected contendrá el número de filas afectadas por la consultar.
• Si utiliza un controlador JDBC para conectarse a una base de datos, configure jitterbit.scripting.db.search.rowset a true arriba en la cadena de la función para realizar cualquier llamada a un procedimiento almacenado que devuelve múltiples resultados para devolver el primer conjunto de registros no vacío en lugar de devolver un conjunto vacío.
• Para ejecutar la declaración en una transacción, configure las variables $jitterbit.scripting.db.auto_commit=false y $jitterbit.scripting.db.transaction=true en un secuencia de comandos antes de la llamada. La transacción se confirmará al final de una transformación exitosa. Al configurar ambas variables (auto_commit y transaction) a true resultará en un error.
• Establecer $jitterbit.scripting.db.max_rows para limitar la cantidad de registros que se devolverán. El valor predeterminado es 10 000 filas.

Ejemplos

Ejemplo 1: Ejecución y Recuperación de Valores en una Matriz

// Results of the SQL select as an array
rows = DBExecute("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE, ORDER_AMOUNT FROM PO_HEADER WHERE PO_NUMBER = 1");

// The value of the database column ORDER_TYPE can then be accessed with
// Get($rows, $i, 0) where $i is the 0-based count of the row you want retrieved.

Ejemplo 2: Ejecución y Recuperación de Valores en Variables Globales Referenciadas Pasadas

// Results of the SQL select will be in the $custName and $custAddr global variables:
DBExecute("<TAG>Sources/myDBTarget</TAG>",
    "SELECT CustomerName, CustomerAddress FROM Customers WHERE CustomerId = " + $custId,
    $custName,
    $custAddr);

// The value of the database column CustomerName can then
// be accessed with either Get("custName") or $custName.

Ejemplo 3: Ejecución y Recuperación de Valores en Variables Globales Nombradas Pasadas

// Results of the SQL select will be in the OrderType and OrderAmount global variables:
DBExecute("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE, ORDER_AMOUNT FROM PO_HEADER WHERE PO_NUMBER = 1",
    "OrderType", "OrderAmount");

// The value of the database column ORDER_TYPE can then
// be accessed with either Get("OrderType") or $OrderType.

DBLoad

Declaración

void DBLoad(string source, string target, int mode, string tablename, string columnNames[, string columnKeynames, int skipLines, string dateFormat, string datetimeFormat])

Sintaxis

DBLoad(<source>, <target>, <mode>, <tablename>, <columnNames>[, <columnKeynames>, <skipLines>, <dateFormat>, <datetimeFormat>])

Parámetros Necesarios

• source: Una fuente de cadena en el proyecto actual que es un único archivo en formato CSV
• target: Una cadena de destino de la base de datos en el proyecto actual
• mode: Un entero; uno de 1 (insertar), 2 (insertar), o 3 (actualizar)
• tablename: La tabla en la base de datos de destino
• columnNames: Una lista de nombres de columnas delimitada por comas
• columnKeynames: Una lista delimitada por comas de nombres de columnas que forman la clave de actualización. Obligatorio si el modo no es 2.

Parámetros Opcionales

• skipLines: Número de líneas que se ignoran al comienzo del archivo (se utilizan para omitir encabezados)
• dateFormat: Especifica el formato de los campos de fecha, como "Date" en bases de datos Oracle
• datetimeFormat: Especifica el formato de los campos de fecha y hora, como "TimeStamp" en bases de datos Oracle

Descripción

Toma una fuente (un único archivo en formato CSV) y carga los datos en una tabla específica en una base de datos de destino.

El parámetro columnKeynames no se utiliza solo cuando se inserta (mode=2) y puede omitirse en ese caso.

Fuente

La fuente utilizada en esta llamada de función debe definirse como una fuente de archivo del proyecto actual. Se utilizará el primer archivo devuelto desde esa fuente. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

// Using the file returned from the source "MyCSVData",
// this example upserts (mode=1) into the table "MyTable"
// on the Database target "SQL Server JDBC". "MyCSVData"
// is expected to be a CSV file that contains data for
// the columns "ID,Col1,Col2,Col3". The update key (used
// to decide whether to update or insert) will be on the
// column "ID". The first line of the CSV file will be
// ignored as it is a header:

DBLoad("<TAG>Sources/MyCSVData</TAG>",
    "<TAG>Targets/SQL Server JDBC</TAG>", 1,
    "MyTable", "ID,Col1,Col2,Col3", "ID", 1);

DBLookup

Declaración

string DBLookup(string databaseId, string sql)

Sintaxis

DBLookup(<databaseId>, <sql>)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual
• sql: El comando SQL que se ejecutará en la base de datos

Descripción

Ejecuta una declaración SQL en una base de datos y devuelve el primer campo del primer resultado que coincide con los criterios especificados.

El valor de los datos devueltos siempre es una cadena. Los datos binarios se devuelven como su representación en cadena hexadecimal. Si no se devuelven filas para la consultar especificada, la función devuelve un valor nulo.

La variable global Jitterbit $jitterbit.scripting.db.rows_affected no se establece con este método.

Para consultas más avanzadas, donde desea recuperar más de un valor o fila, use las funciones DBLookupAll o DBExecute.

Identificación de la Base de Datos

La base de datos utilizada en esta llamada de función debe definirse como origen o destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

// Returns the first field of the first result from
// running the SQL query
result = DBLookup("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

DBLookupAll

Declaración

array DBLookupAll(string databaseId, string sql)

Sintaxis

DBLookupAll(<databaseId>, <sql>)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual
• sql: El comando SQL que se ejecutará en la base de datos

Descripción

Ejecuta una declaración SQL en una base de datos y devuelve los resultados que coinciden con los criterios especificados.

Los datos devueltos siempre se devuelven como una matriz bidimensional de cadenas. Los datos binarios se devuelven como su representación en cadena hexadecimal. Si no se devuelven filas para la consultar especificada, la función devuelve una matriz vacía.

La variable global Jitterbit $jitterbit.scripting.db.rows_affected no se establece con este método.

La base de datos utilizada en esta llamada de función debe definirse como origen o destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Para consultas más avanzadas, donde desea recuperar directamente variables globales, use la función DBExecute.

Ejemplos

// Returns the result from running the SQL query
result = DBLookupAll("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_NUMBER=1");

DBRollbackTransaction

Declaración

void DBRollbackTransaction(string databaseId)

Sintaxis

DBRollbackTransaction(<databaseId>)

Parámetros Necesarios

• databaseId: Una fuente de base de datos de cadena en el proyecto actual

Descripción

Revierte la transacción actual y cierra la conexión a la base de datos.

La base de datos utilizada en esta llamada de función debe estar definida como origen o destino del proyecto actual. Consulte las instrucciones sobre insertar elementos del proyecto.

Ejemplos

// Rolls back the current transaction
DBRollbackTransaction("<TAG>Sources/myDBTarget</TAG>");

DBWrite

Declaración

void DBWrite(string source, string target, int mode, string tablename, string columnNames[, string columnKeynames, int skipLines, string dateFormat, string datetimeFormat])

Sintaxis

DBWrite(<source>, <target>, <mode>, <tablename>, <columnNames>[, <columnKeynames>, <skipLines>, <dateFormat>, <datetimeFormat>])

Descripción

Un alias para la función DBLoad. Ver DBLoad para más detalles.

SetDBInsert

Declaración

void SetDBInsert()

Sintaxis

SetDBInsert()

Descripción

Anula la configuración actual del modo de inserción/actualización a "insertar" para el registro actual. El valor de retorno es nulo.

Ejemplos

// Sets the insert/update mode to "insert"
// for the current record
SetDBInsert();

SetDBUpdate

Declaración

void SetDBUpdate()

Sintaxis

SetDBUpdate()

Descripción

Anula la configuración actual del modo de inserción/actualización para "actualizar" el registro actual. El valor de retorno es nulo.

Ejemplos

// Sets the insert/update mode to "update"
// for the current record
SetDBUpdate();

SQLEscape

Declaración

string SQLEscape(string unescapedSQL[, bool escapeBackslash])

Sintaxis

SQLEscape(<unescapedSQL>[, <escapeBackslash>])

Parámetros Necesarios

• unescapedSQL: Una cadena de SQL que se debe escapar

Parámetros Opcionales

• escapeBackslash: Bandera booleana que indica si las barras invertidas ("\") debe escaparse duplicándose; el valor predeterminado es false

Descripción

Realiza el escape necesario de cadenas literales utilizadas en una declaración SQL.

Las cadenas utilizadas como constantes de caracteres en una declaración SQL utilizan una comilla simple (') como delimitador; si los datos reales contienen comillas simples, es necesario evitarlas especificándolas dos veces. Este método evita las comillas simples siguiendo el estándar SQL reemplazando cada comilla simple (') con dos comillas simples (''). Si los caracteres de barra invertida también deben escaparse, proporcione y configure el segundo parámetro en true.

Ejemplos

// In this example, the variable GUID needs to have
// any single quotes in it escaped (doubled); the
// resulting string is then enclosed in single quotes
// by the Quote function before being used in a
// DBLookup function:


DBLookup("<TAG>Sources/myDBTarget</TAG>",
    "SELECT ORDER_TYPE FROM PO_HEADER WHERE PO_ID=" +
    Quote(SQLEscape(GUID)));

Unmap

Declaración

void Unmap()

Sintaxis

Unmap()

Descripción

Para su uso en asignaciones, esta función establece un campo de destino de la base de datos que se tratará como no asignado. El valor de retorno es nulo.

Ejemplos

valueToInsert = DBLookup(....);
// If valueToInsert returned by a DBLookup is null, we want to treat
// this field as unmapped and we do not want to include it in the INSERT statement
// that is being generated for the DB target for this record:
If (valueToInsert == Null(), Unmap(), valueToInsert);

<SEQUENCE>

Declaración

<SEQUENCE>

Sintaxis

<SEQUENCE>

Descripción

Para su uso en asignaciones con bases de datos Oracle, esta función se utiliza cuando el destino contiene tablas vinculadas con una relación de clave principal/clave externa. En ese caso, asigne esta función a las claves principales generadas por la base de datos Oracle.

Para bases de datos distintas de Oracle, utilice la función <SQLIDENTITY> en cambio.

Ejemplos

Si <trans> Las etiquetas están presentes, <SEQUENCE>Deben colocarse fuera de ellos de la siguiente manera:

<trans>
</trans>
<SEQUENCE>

<SQLIDENTITY>

Declaración

<SQLIDENTITY>

Sintaxis

<SQLIDENTITY>

Descripción

Para su uso en asignaciones con bases de datos que no sean de Oracle, esta función se utiliza cuando el destino contiene tablas vinculadas con una relación de clave principal/clave externa. En ese caso, asigne esto a las claves principales que genera la base de datos, como Identity en SQL Server o Serial en PostgreSQL. Para bases de datos Oracle, utilice la función <SEQUENCE> en cambio.

Ejemplos

Si <trans> Las etiquetas están presentes, <SQLIDENTITY>Deben colocarse fuera de ellos de la siguiente manera:

<trans>
</trans>
<SQLIDENTITY>

<UDF>

Declaración

<UDF>string userDefinedFunction

Sintaxis

<UDF><userDefinedFunction>

Parámetros Necesarios

• userDefinedFunction: Una cadena que define una llamada a una función definida por el usuario

Descripción

Agrega una función de base de datos definida por el usuario al comienzo de una fórmula. <UDF>El prefijo se elimina de la expresión antes de pasarlo. Tenga en cuenta que la apertura y el cierre <trans> Las etiquetas se pueden utilizar para indicar partes de la llamada de función que Jitterbit debe evaluar antes de que la expresión se pase a una base de datos.

Ejemplos

// The user-defined function geography::Point()
// is being called with parameters created by evaluating
// the Jitterbit Script enclosed by <trans> tags:

<UDF>geography::Point(<trans>json$Incidents$item.Latitude$ + ","
  + json$Incidents$item.Longitude$ + ",4326";</trans>)