{ Analytica Model Database_library_demo, encoding="UTF-8" }
SoftwareVersion 6.4.6

{!-60000|Attribute AcpStyles}

AskAttribute Value,Variable,Yes
AskAttribute Domain,Variable,Yes
AskAttribute TableCellDefault,Variable,Yes
AskAttribute MetaOnly,Variable,Yes
AskAttribute OnChange,Variable,Yes
{!40700|AskAttribute CellFormatExpression,Variable,Yes}

LinkModule Database_library_demo
Title: Database library demo
Description: A demonstration of using the functions in the Database library to create, read, update, examine and delete database tables. The Database library is a stand-alone model that can be extracted from this demonstration model and embedded in other models.
Author: Ben
Date: Mon, Jun 12, 2023 10:15 PM
NodeSize: 104,24
NodeInfo: 1,0,0
DiagState: 2,365,10,558,718,17,10
FontStyle: Arial,16
FileInfo: 0,LinkModule Database_library_demo,2,2,0,0,C:\Users\filst\OneDrive\Documents\Repos\FacilitatingLibraries\FacilitatingLibraries\Database_library_demo.ana

Module Model_Details
Title: Model Details
Description: Interals of the model. Includes libraries used, additional resources, and works in progress.
Author: Ben Filstrup~
Lumina Decision Systems
Date: Mon, Nov 21, 2022 2:11 AM
NodeLocation: 440,680,1
NodeSize: 96,32
NodeInfo: 1,0,0,,,,1
DiagState: 2,1191,294,227,246,17,10
NodeFont: Century Gothic,19

Module Additional_Resources
Title: Additional Resources
Description: Contains additional resources pertaining to DSNs e.g. setting up your own custom connection string.
Author: Ben Filstrup~
Lumina Decision Systems
Date: Tue, Dec 13, 2022 9:40 PM
NodeLocation: 120,120,1
NodeSize: 96,32
DiagState: 2,528,95,485,239,17,10

Module Setting_up_a_DSN_con
Title: Setting up a DSN connection string
Author: Ben Filstrup~
Lumina Decision Systems
Date: Tue, Dec 13, 2022 9:40 PM
NodeLocation: 168,136,1
NodeSize: 96,32
DiagState: 2,0,0,879,770,17,10

Variable Advanced_Connection_
Title: Advanced Connection String (DSN)
Description: Depending on which information is required, this node will output your connection string in the correct format.
Definition: Local DSN := IF my_dsn <> '' THEN 'DSN='&my_dsn ELSE MsgBox('Please enter a DSN and try again.', 48, 'No DSN Found');~
Local UID := IF my_UID <> '' THEN 'UID='&my_UID;~
Local PWD := IF my_PWD <> '' THEN 'PWD='&my_PWD;~
Local Database := IF my_Database <> '' THEN 'Database='&my_Database;~
~
JoinText([DSN,UID,PWD,Database], separator: ';')
NodeLocation: 768,400,1
NodeSize: 80,32
NodeInfo: 1,0,0
WindState: 2,98,82,720,350
ValueState: 2,196,202,894,303,,MIDM

Decision my_DSN
Title: DSN
Description: Input your DSN.
Definition: 'analytica_db'
NodeLocation: 560,224,1
NodeSize: 80,32
Aliases: FormNode Fo1653274467

FormNode Fo1653274467
Title: Database to Connect to
Definition: 0
NodeLocation: 560,272,1
NodeSize: 72,16
NodeInfo: 1,,,0,,,,128,,,,,,0
Original: my_DSN

Decision my_UID
Title: UID
Description: Input your UID.
Definition: ''
NodeLocation: 560,336,1
NodeSize: 80,32
Aliases: FormNode Fo1664511843

Decision my_PWD
Title: PWD
Description: Input your PWD.
Definition: ''
NodeLocation: 560,448,1
NodeSize: 80,32
Aliases: FormNode Fo322334563

Decision my_Database
Title: Database
Description: Input your Database.
Definition: ''
NodeLocation: 560,560,1
NodeSize: 80,32
Aliases: FormNode Fo1396076387

FormNode Fo1664511843
Title: UID
Definition: 0
NodeLocation: 560,384,1
NodeSize: 72,16
NodeInfo: 1,,,0,,,,128,,,,,,0
Original: my_UID

FormNode Fo322334563
Title: PWD
Definition: 0
NodeLocation: 560,496,1
NodeSize: 72,16
NodeInfo: 1,,,0,,,,128,,,,,,0
Original: my_PWD

FormNode Fo1396076387
Title: Database
Definition: 0
NodeLocation: 560,608,1
NodeSize: 72,16
NodeInfo: 1,,,0,,,,128,,,,,,0
Original: my_Database

Text Te938965219
Title: Set up your ODBC Driver
Description: This allows us to access data in a DB via Analytica~
1. Press WIN key or open the Start menu~
2. Type ODBC~
3. Select "ODBC Data Sources (64-bit)"~
Note: It must be the 64-bit version~
4. Click the "System DSN" tab~
5. Click "Add.." on the righthand side~
6. Scroll down and select "SQL Server" & press "Finish"~
7. Enter in the following:~
Name: analytica_db~
Description:~
Server: TBD~
--- Next > ---~
--- Next > ---~
--- Next > ---~
--- Next > ---~
--- "Test Data Source..." ---~
~
8. ~
- If you see at the bottom of the test "TESTS COMPLETED SUCCESSFULLY!" then Press "OK" twice; you're done and should see your new System DSN.~
- If you don't see a success message, go back and double-check the information entered and try again. If this doesn't work, search any specific error messages you receive to resolve system specifc issues.
NodeLocation: 220,380,-1
NodeSize: 212,372
NodeInfo: 1,,,,1,1
NodeColor: 65535,65535,65535

Text Te1465106147
Description: Populate whichever information is relevant/required for your DSN and then view the variable node to get your DSN connection string.
NodeLocation: 652,380,-1
NodeSize: 212,372
NodeInfo: 1,,,,1,1
NodeColor: 65535,65535,65535

Button Clear_Entry_Fields
Title: Clear Entry Fields
Description: Sets all entry fields seen below back to blank.
NodeLocation: 768,704,1
NodeSize: 80,32
OnClick: my_DSN := '';~
my_UID := '';~
my_PWD := '';~
my_Database := ''

Close Setting_up_a_DSN_con

Text Te978566883
Title: Alternate Connection Strings: DSN
Description: ODBC instructions & nodes to help construct the DSN connection string.
NodeLocation: 172,108,-1
NodeSize: 156,92
NodeInfo: 1,,,,1,1
NodeColor: 65535,65535,65535

Close Additional_Resources

Module OnModelLaunch
Title: OnModelLaunch
Description: Contains buttons that run automatically when the model is loaded. The primary use behind this is to create the tables used in the "delete" DB function examples so the user is less likely to encounter an error.
Author: Ben Filstrup~
Lumina Decision Systems
Date: Tue, Jan 3, 2023 12:01 AM
NodeLocation: 120,48,1
NodeSize: 96,32
DiagState: 2,14,11,446,299,17,10
WindState: 2,589,36,720,326

Button Create_DEL_Table_1
Title: Create DEL Table 1
Description: OnModelLoad creates the first table for the delete examples so users can immediately delete table entries without error.
NodeLocation: 120,152,1
NodeSize: 80,32
WindState: 2,36,522,720,350
{!40300|ProactivelyEvaluate: 32}
OnClick: IF Sum(List_of_DB_Tables = Table_to_be_Deleted1) THEN 0 ELSE (~
DB_Create_Table(DB_connection, Table_to_be_Deleted1, DB_Fields, DB_Datatypes);~
DB_Add_Record(DB_connection, Table_to_be_Deleted1, DB_Fields, Data_to_Write)~
)

Button Create_DEL_Table_2
Title: Create DEL Table 2
Description: OnModelLoad creates the second table for the delete examples so users can immediately delete a table without error.
NodeLocation: 120,224,1
NodeSize: 80,32
{!40300|ProactivelyEvaluate: 32}
OnClick: IF Sum(List_of_DB_Tables = Table_to_be_Deleted2) THEN 0 ELSE (~
DB_Create_Table(DB_connection, Table_to_be_Deleted2, DB_Fields, DB_Datatypes);~
DB_Add_Record(DB_connection, Table_to_be_Deleted2, DB_Fields, Data_to_Write)~
)

Decision Table_to_be_Deleted1
Title: Table to be Deleted 1 Label
Description: Label for table deleted in DB_delete_record() example.
Definition: 'tableToBeDeleted1'
NodeLocation: 320,152,1
NodeSize: 80,32

Decision Table_to_be_Deleted2
Title: Table to be Deleted 2 Label
Description: Label for table deleted in DB_del_table() example.
Definition: 'tableToBeDeleted2'
NodeLocation: 320,224,1
NodeSize: 80,32

Button Delete_Default_Table
Title: Delete Default Table
Description: OnModelLoad deletes the default table so users can immediately create their own table without error should they choose to not change the default name.
NodeLocation: 120,80,1
NodeSize: 80,32
WindState: 2,863,119,720,350
{!40300|ProactivelyEvaluate: 32}
OnClick: IF Sum(List_of_DB_Tables = Table_to_be_Created_) THEN DB_Delete_Table(DB_connection, Table_to_be_Created_, False)

Decision Table_to_be_Created_
Title: Table to be Created Label
Description: Label for table used in most examples.
Definition: 'testTable123'
NodeLocation: 320,80,1
NodeSize: 80,32

Close OnModelLaunch

Module Details
Title: Details
Description: Model details pertaining to the 'Refresh' calls and SQL Server / MariaDB datatype indices.
Author: Ben Filstrup~
Lumina Decision Systems
Date: Mon, Oct 16, 2023 10:09 AM
NodeLocation: 120,192,1
NodeSize: 96,32
DiagState: 2,1244,77,254,248,17,10

Index SQL_Server_Dtypes
Title: SQL Server Dtypes
Description: List of datatypes for SQL Server.
Definition: ['char','varchar','varchar(max)','text','nchar','nvarchar','nvarchar(max)','ntext','binary(n)','varbinary','varbinary(max)','image','bit','tinyint','smallint','int','bigint','decimal','numeric','smallmoney','money','float','real','datetime','datetime2','smalldatetime','date','time','datetimeoffset','timestamp','sql_variant','uniqueidentifier','xml','cursor','table']
NodeLocation: 112,56,1
NodeSize: 80,32

Index MariaDB_Dtypes
Title: MariaDB Dtypes
Description: List of datatypes for MariaDB.
Definition: ['VARCHAR(255)','INT','TEXT','BOOLEAN','DOUBLE','BIT','TINYINT','SMALLINT','MEDIUMINT','BIGINT','DECIMAL','NUMBER','FLOAT','CHAR','ENUM','LONGTEXT','VARBINARY','DATE','TIME','DATETIME','TIMESTAMP']
NodeLocation: 112,136,1
NodeSize: 80,32

Close Details

Close Model_Details

Module Create_write_to_a_ta
Title: Create & Write to a Table
Description: Guided examples that show how to:~
- Structure your data for DB interactions~
- Create a DB table~
- Write data to a DB table~
- Read from a DB table~
~
Functions shown:~
- DB_Create_Table()~
- DB_Add_Record()~
- DB_Retrieve_Record()
Author: Ben Filstrup~
Lumina Decision Systems
Date: Wed, Jan 11, 2023 7:05 AM
NodeLocation: 440,248,1
NodeSize: 96,32
NodeInfo: 1,0,0,,,,1
DiagState: 2,22,8,1137,675,17,10
WindState: 2,1133,16,720,350
NodeFont: Century Gothic,19

Variable Data_to_Write
Title: Data to Write
Description: Generic table for a user to fill out data to then be written to a database.
Definition: Table(Array_Rows,DB_Fields)(~
'New York','New York',833.5897,~
'California','Los Angeles',382.2238,~
'Texas','Houston',230.2878,~
'Texas','San Antonio',147.2909,~
'California','San Diego',138.1162,~
'Texas','Dallas',129.9544,~
'Florida','Jacksonville',97.1319,~
'California','San Jose',97.1233,~
'Florida','Miami',44.9514,~
'Florida','Tampa',39.8173,~
'New York','Buffalo',27.6486,~
'New York','Rochester',20.9352)
NodeLocation: 296,456,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,98,82,720,350
DefnState: 2,78,353,416,303,0,DFNM
ValueState: 2,207,233,416,303,,MIDM
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19
ReformDef: [DB_Fields,Array_Rows]
{!40700|Att_CellFormat: CellSpan(DB_Fields,CellNumberFormat('Fixed Point',0,0,1,dateFormat:'ABBREV',fullPrecision:0,numbersAsDates:0,datesAsNumbers:0,digits_:4,zeroes_:0,showZeroImPart:0),3,header:0)}
{!50000|Att_ColumnWidths: [,DB_Fields,\([,127])]}

Index Array_Rows
Att_PrevIndexValue: [1,2,3,4,5,6,7,8,9,10,11,12]
Title: Array Rows
Description: Generic Row variable for a user to fill out data to then be written to a database.~
~
Note: This is PURPOSELY not named DB Rows because these rows only correspond to the Analytica Table that will be written to database. For example, if anything has already been written to the database and then these rows were used to write your own data, they'd already be misaligned if you viewed the database table.
Definition: 1..12
NodeLocation: 296,368,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,944,107,720,350
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19

Index DB_Fields
Att_PrevIndexValue: ['State','City','Population']
Title: DB Fields
Description: Name of the fields created in your database table. If fields are added/removed from the database table at a later point, know that this index will no longer represent all available tables in your database table.
Definition: ['State','City','Population']
NodeLocation: 128,368,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,98,82,715,350
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19

Variable Create_Table_in_Anal
Title: Create Table in Analytica DB
Description: Create your database table inside ANAGRAM's test database.
Definition: DB_Create_Table( DB_connection, DB_Table_Name, DB_Fields, DB_Datatypes)
NodeLocation: 560,240,1
NodeSize: 80,40
NodeInfo: 1,,,,,,1
WindState: 2,60,314,720,350
ValueState: 2,491,166,966,278,,MIDM
NodeFont: Century Gothic,19

Variable Retrieve_DB_Table
Title: Retrieve DB Table
Description: View the contents of specified database table.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name)
NodeLocation: 560,432,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,689,33,428,221,,MIDM
Aliases: Alias Al1457855203
NodeFont: Century Gothic,19

Decision DB_Table_Name
Title: DB Table Name
Description: Name of the database table to be created in ANAGRAM's test database.
Definition: 'populationData'
NodeLocation: 296,288,1
NodeSize: 80,32
NodeInfo: 1,,0,,,,1
WindState: 2,98,82,720,350
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19

Variable Write_Table_to_Analy
Title: Write Table to Analytica DB
Description: Write data to your database table inside ANAGRAM's test database.
Definition: DB_Add_Record(DB_connection, DB_Table_Name, DB_Fields, Data_to_Write)
NodeLocation: 904,192,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,116,122,1099,567,,MIDM
NodeFont: Century Gothic,19

{!50500|ArrowClass Arrow1233743075}
{!50500|Att_HeadNode: Write_Table_to_Analy}
{!50500|Att_TailNode: Data_to_Write}
{!50500|Att_ArrowWayPoints: [Null,174.9993503100232,0.6551724137931034,25,0.7586206896551724,0.84375]}
{!50500|Att_ArrowProperties: 1}

{!50500|ArrowClass Arrow898198755}
{!50500|Att_HeadNode: Retrieve_DB_Table}
{!50500|Att_TailNode: DB_Table_Name}
{!50500|Att_ArrowWayPoints: [Null,Null,0.4230769230769231,0.07407400343153211,0.5384615384615384,0.2592593299018012,0.5769230769230769,0.5,0.6153846153846154,0.7222222222222222]}
{!50500|Att_ArrowProperties: 1}

Text Te864644323
Title: Step 2:
Description: Use DB_Create_Table() to create a table in the Lumina Test DB. ~
~
Note: For this example a connection string is supplied.~
~
~
~
~
~
You can view your table by using DB_Retrieve_Record() in the node below.
NodeLocation: 564,276,-1
NodeSize: 172,228
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Text Te138025699
Title: Step 1:
Description: Customize your inputs, including the name of your database table, it's fields, field types, and the data you'll write to said table.~
~
For this model we'll denote editable variables as orange.
NodeLocation: 212,276,-1
NodeSize: 172,228
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Text Te449125091
Title: Create & Write to a Table
NodeLocation: 556,264,-10
NodeSize: 548,256
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Arial,19

Text Te1533352675
Title: Step 3:
Description: Use DB_Add_Record() to populate your table in the Lumina Test DB & then view your table.~
~
~
~
~
Hit the "Refresh data from DB" button to force "Retrieve DB Table" to send a fresh query to the database.
NodeLocation: 916,276,-1
NodeSize: 172,228
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Alias Al1457855203
Title: View DB Table
Definition: 1
NodeLocation: 904,456,1
NodeSize: 80,32
NodeFont: Century Gothic,19
Original: Retrieve_DB_Table

Decision DB_Datatypes
Att_PrevIndexValue: ['Field 1','Field 2','Field 3']
Title: DB Datatypes
Description: Data types of the fields created in your database table.
Definition: Table(DB_Fields)(Choice(SQL_Server_Dtypes,3,0),Choice(SQL_Server_Dtypes,3,0),Choice(SQL_Server_Dtypes,16,0))
IndexVals: ['item 1']
NodeLocation: 128,456,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,98,82,715,350
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19
TableCellDefault: Choice(SQL_Server_Dtypes,3,0)

{!50500|ArrowClass Arrow369516003}
{!50500|Att_HeadNode: Create_Table_in_Anal}
{!50500|Att_TailNode: DB_Datatypes}
{!50500|Att_ArrowWayPoints: [Null,176.4236633180243,0.16666666666666666,0.18518518518518517,0.4444444444444444,0.2222222222222222,0.6111111111111112,0.25925925925925924,0.6296296296296297,0.8888888888888888]}
{!50500|Att_ArrowProperties: 1}

Alias Al1868519507
Title: Rerun SQL
NodeLocation: 904,376,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
NodeFont: Century Gothic,19
Original: Refresh_data_from_DB

Text Te1943974355
Title: Common Error Troubleshooting
Description: When running 'Create Table in Analytica DB', you may get an error stating "There is already an object named 'populationData' in the database...' This either means you've created a table and tried to create another table with the same name OR that another user is currently using this demo and has already created a table with the default name 'populationData'. Simply change the name in 'DB Table Name' to continue on with the demo.
NodeLocation: 556,596,-1
NodeSize: 548,68
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Alias Al1888924115
Title: Connection String
NodeLocation: 128,288,1
NodeSize: 80,32
NodeInfo: 1,,0
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19
Original: DB_connection

Close Create_write_to_a_ta

Module Explore_More_DB_Func
Title: Explore More DB Functions
Description: Guided examples that show how to:~
- Seeing all tables in a DB~
- Seeing all columns in a DB table~
- Reading select data from a DB table~
- Deleting from a DB table~
~
Functions shown:~
- DB_List_Tables()~
- DB_List_Columns()~
- DB_Delete_Record()~
- DB_Retrieve_Record()~
- DB_Delete_Table()
Author: Ben Filstrup~
Lumina Decision Systems
Date: Wed, Jan 11, 2023 7:05 AM
NodeLocation: 440,376,1
NodeSize: 96,32
NodeInfo: 1,0,0,,,,1
DiagState: 2,5,7,1351,504,17,10
WindState: 2,1142,498,720,350
NodeFont: Century Gothic,19

Variable List_of_DB_Tables
Title: List of DB Tables
Description: Shows all tables in the database.
Definition: DB_List_Tables(DB_refreshed)
NodeLocation: 136,344,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
NodeFont: Century Gothic,19

Variable Delete_DB_Table_Defa
Title: Delete DB Table Default
Description: Delete your database table. Careful, this is irreversible.
Definition: DB_Delete_Table(DB_connection, DB_Table_Name)
NodeLocation: 1200,312,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,482,89,720,350
NodeFont: Century Gothic,19

Variable Delete_DB_Records
Title: Delete DB Records - Single 1
Description: Delete records specified by the fields and data specified.~
~
In this example the function will delete all records where the column 'City' has the value 'San Jose'. Since the City column is full of unique values we expect our table to lose 1 record.
Definition: DB_Delete_Record(DB_connection, DB_Table_Name, 'City', 'San Jose')
NodeLocation: 928,264,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,404,410,893,348,,MIDM
NodeFont: Century Gothic,19

Variable Retrieve_DB_Columns_
Title: Retrieve DB Columns - Single
Description: Grab columns from your database table.~
~
The 'retrieveField' parameter is specified so the function grabs just that column from your database table.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name, 'City')
NodeLocation: 408,344,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
NodeFont: Century Gothic,19
ReformVal: [Undefined,Sys_LocalIndex('colIndex')]

Variable Find_DB_Records_Mult
Title: Find DB Records - Multiple
Description: Grab records from your database table.~
~
In this example we're trying to find retrieve records from the 'City' and 'Population' columns by doing a lookup on the 'City' column where the value in the 'City' column is 'Buffalo'. Our expected result is to find the city and population data for the city of Buffalo.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name, ['City', 'Population'], 'City', 'Buffalo')
NodeLocation: 672,424,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,436,442,898,303,,MIDM
NodeFont: Century Gothic,19
Att_ResultSliceState: [Self,1,Sys_LocalIndex('colIndex'),1]

Text Te2049252067
Title: Explore More DB Functions (first, create a table and write to it step 1 to view these examples)
NodeLocation: 676,252,-11
NodeSize: 668,244
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Variable Retrieve_DB_Columns1
Title: Retrieve DB Columns - Multiple
Description: Grab columns from your database table.~
~
The 'retrieveField' parameter is specified, now with two columns, so the function grabs both specified columns from your database table.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name, ['City', 'Population'])
NodeLocation: 408,424,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
NodeFont: Century Gothic,19
ReformVal: [Undefined,Sys_LocalIndex('colIndex')]

Variable Retrieve_DB_Columns
Title: Retrieve DB Columns
Description: Grab columns from your database table.~
~
In this example no columns are specified to retrieve so the function follows default behavior and grabs all columns from your database table.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name)
NodeLocation: 408,264,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,0,48,409,363,,MIDM
NodeFont: Century Gothic,19
ReformVal: [Undefined,Sys_LocalIndex('colIndex')]

Text Te664476387
Description: Show all user tables in the database & columns in a table respectively.
NodeLocation: 148,268,-1
NodeSize: 124,212
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Text Te509287139
Description: Retrieve columns from a DB table. You can provide either: ~
- no columns (shows all columns)~
- one column (Text/Index)~
- multiple columns (Index)
NodeLocation: 408,268,-1
NodeSize: 128,212
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Variable Find_DB_Records_Sing
Title: Find DB Records - Single 1
Description: Grab records from your database table.~
~
In this example we're trying to find retrieve records from the 'Population' column by doing a lookup on the 'City' column where the value in the 'City' column is 'Miami'. Our expected result is to find the population data for the city of Miami.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name, 'Population', 'City', 'Miami', 0)
NodeLocation: 672,264,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,520,291,669,299,,MIDM
NodeFont: Century Gothic,19

Variable Find_DB_Records_Sin1
Title: Find DB Records - Single 2
Description: Grab records from your database table.~
~
In this example we're trying to find retrieve records from the 'City' column by doing a lookup on the 'State' column where the value in the 'State' column is 'Texas'. Our expected result is to find a few cities that reside within the state of texas.~
~
The main difference in this example from the previous is that indices are supplied instead of plain text values.
Definition: DB_Retrieve_Record(DB_refreshed, DB_Table_Name, ['City'], ['State'], 'Texas')
NodeLocation: 672,344,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,436,444,898,301,,MIDM
NodeFont: Century Gothic,19
Att_ResultSliceState: [Self,1,Sys_LocalIndex('colIndex'),1]

Text Te392108771
Description: Return records from a DB where a provided value exists in the columns specified. You can provide either: ~
- one column (Text/Index)~
- multiple columns (Index)
NodeLocation: 672,268,-1
NodeSize: 128,212
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Text Te1732765411
Description: Delete records from a DB where a provided value exists in the columns specified. You can provide either: ~
- one column (Text/Index)~
- multiple columns (Index)
NodeLocation: 936,268,-1
NodeSize: 128,212
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Text Te2003298019
Description: Delete table from a DB. Default behavior provides a warning message box which can be disabled with the ignoreWarning argument.
NodeLocation: 1200,268,-1
NodeSize: 128,212
NodeInfo: 1,,,,1,1,1
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,19

Variable Delete_DB_Table_No_W
Title: Delete DB Table No Warning
Description: Delete your database table. Careful, this is irreversible.~
~
In this version the 'ignoreWarning' parameter is specified to be True so this node will NOT give any kind of warning before deleting when evaluated.
Definition: DB_Delete_Table(DB_connection, Table_to_be_Deleted1, ignoreWarning: True)
NodeLocation: 1200,392,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,482,89,720,350
NodeFont: Century Gothic,19

Variable Delete_DB_Records_Mu
Title: Delete DB Records - Multiple
Description: Delete records specified by the fields and data specified.~
~
In this example the function will delete all records where the columns 'State' and 'City' both have the value 'New York'. In most situations this would lead to no deletions but because 'New York' is the name of both the State and the City we expect our table to lose 1 record.
Definition: DB_Delete_Record(DB_connection, DB_Table_Name, ['State', 'City'], 'New York')
NodeLocation: 928,424,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,98,82,720,350
ValueState: 2,404,410,893,348,,MIDM
NodeFont: Century Gothic,19

Variable Delete_DB_Records_Si
Title: Delete DB Records - Single 2
Description: Delete records specified by the fields and data specified.~
~
In this example the function will delete all records where the column 'City' has the value 'Tampa'. Since the City column is full of unique values we expect our table to lose 1 record.~
~
The main difference in this example from the previous is that indices are supplied instead of plain text values.
Definition: DB_Delete_Record(DB_connection, DB_Table_Name, ['City'], 'Tampa')
NodeLocation: 928,344,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
ValueState: 2,404,410,893,348,,MIDM
NodeFont: Century Gothic,19

Variable List_of_Columns
Title: List of Columns
Description: Shows all columns in the specified database table.
Definition: DB_List_Columns(DB_refreshed, DB_Table_Name)
NodeLocation: 136,424,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,865,91,720,350
NodeFont: Century Gothic,19

Alias Al163546835
Title: Rerun SQL
NodeLocation: 136,264,1
NodeSize: 80,32
NodeFont: Century Gothic,19
Original: Refresh_data_from_DB

Close Explore_More_DB_Func

Text Te1732452067
Title: How to use the Database library
Description: This model shows how to use the Database library for communicating with databases. ~
~
~
~
~
See <a href="https://docs.analytica.com/index.php/Database_library">Database library</a> for more or to download the latest version.
NodeLocation: 280,96,-1
NodeSize: 272,88
NodeInfo: 1,,,,1,1,1,,,,0
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,16

Text Te1422073571
Description: 1. To learn how to:~
- Structure your data for DB interactions~
- Create a DB table~
- Write data to a DB table~
- Read from a DB table
NodeLocation: 280,248,-1
NodeSize: 272,56
NodeInfo: 1,,,,1,1,1,,,,0
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,16

Text Te739098339
Description: 2. To learn about the remaining DB functions, including:~
- See all tables in a DB~
- See all columns in a DB table~
- Read select data from a DB table~
- Delete from a DB table~
- Delete a DB table
NodeLocation: 280,376,-1
NodeSize: 272,64
NodeInfo: 1,,,,1,1,1,,,,0
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,16

Module Database_connection_
Title: Database connection and refresh
Description: Shows database connection, and refresh method to make sure that data from a
Author: Ben Filstrup~
Lumina Decision Systems
Date: Tue, Feb 13, 2024 10:03 AM
NodeLocation: 440,496,1
NodeSize: 96,32
DiagState: 2,924,180,608,454,17,10
WindState: 2,788,525,720,350

Button Refresh_data_from_DB
Title: Refresh data from DB
Description: Changes the value of Refresh to invalidate the results of the nodes it is connected to. ~
~
For normal modeling Analytica will automatically invalidate results when a node's input changes. However, when working with SQL, we need to manually invalidate nodes that we want to speak to the database again.
NodeLocation: 176,392,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1
WindState: 2,735,89,720,350
Aliases: Alias Al1868519507, Alias Al163546835
NodeFont: Century Gothic,19
OnClick: Refresh := NOT Refresh

Variable Refresh
Title: Refresh
Description: The sole purpose of this global is to invalidate the results of the nodes it is connected to. ~
~
For normal modeling Analytica will automatically invalidate results when a node's input changes. However, when working with SQL, we need to manually invalidate nodes that we want to speak to the database again.
Definition: 0
NodeLocation: 366,295,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1,,,,0
NodeFont: Century Gothic,19

Variable DB_connection
Title: DB connection
Description: Connection string to access the database.~
~
Lumina provides the following connection string to allow users to test out the Databse library without having to first undergo the process of setting up a database. If you have your own database you'd like to work with, simply replace this connection string with your own.
Definition: Local db := 'DRIVER=SQL Server;SERVER=suan-stage.analytica.com,1433;Database=SQLLibrary;UID=SQLWriter;PWD=Ben$DB2022!';~
IF AnalyticaVersion < 60400 {Releases prior to 6.4 don't support DbConnection() to create a persistent connection}~
   THEN  db ~
   ELSE  DbConnection(db, readOnly:False)
NodeLocation: 176,296,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1,,,,0
WindState: 2,792,11,1082,433
Aliases: Alias Al1888924115
NodeColor: 65535,52425,39321
NodeFont: Century Gothic,19

Text Te199668179
Title: Connect to database and refreshing data
Description: Suppose variable DB_read uses DB_retrieve_record() to read data from a database. If you then modify the data in the database, the values in DB_read may then be out-of-date. Data in a database doesn't follow Analytica evaluation rules that invalidate a variable when its inputs change causing it to be recomputed when needed. To deal with this problem, we use button 'Refresh data from DB' that changes the value of the variable 'Refresh', which is used in Connection_refresh. So when you click 'Refresh data from DB', variables getting data using DB_retrieve_record(Connection_refresh, ..) will be invalidated, and the next time they are evaluated will get up-to-date data.
NodeLocation: 304,228,-1
NodeSize: 296,220
NodeInfo: 1,,,,1,1,1,,,,0
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,16

Variable DB_refreshed
Title: DB connection refreshed
Description: The database connection. This version will invalidate any variables using it after clicking "Refresh data from DB" button, so that they will recompute using the latest data from the database.
Definition: Refresh; DB_connection
NodeLocation: 366,391,1
NodeSize: 80,32
NodeInfo: 1,,,,,,1,,,,0
WindState: 2,331,270,719,506
NodeFont: Century Gothic,19

Close Database_connection_

Text Te347687379
Description: 3. See database connection ~
and data refresh to make sure changes ~
to the database propagate to variables ~
reading from it.~
~
This demo uses a temporary database provided by Lumina (via connection string) for your testing. Don't provide any sensitive data, or rely on it for storing your data! The database gets reinitialized each time anyone runs this demo.
NodeLocation: 280,540,-1
NodeSize: 272,92
NodeInfo: 1,,,,1,1,1,,,,0
NodeColor: 65535,65535,65535
NodeFont: Century Gothic,16

Library Database_library
Title: Database library
Description: A library of functions used to create, read, update, examine and delete database tables.
Author: James Milford~
Lumina Decision Systems
Date: Mon, Sep 25, 2023 10:44 AM
DefaultSize: 80,32
NodeLocation: 440,96,1
NodeSize: 96,32
NodeInfo: 1,,,,,,1
DiagState: 2,19,6,733,721,17,10
FontStyle: Arial,15
NodeFont: Century Gothic,19

Function DB_Create_Table(db; tbl: Text Atom; field, type: Text; constraint: Text=''; debug: Atom=0)
Title: DB_Create_Table(db, tbl, field, type)
Description: Creates a new database table having column names of «field», data formats of «type», and optional null or non-null designation of «constraint». ~
~
[db]: a connection string or variable calling DbConnection to access the database.~
[tbl]: the database table.~
[field]: field names for the table. This can be scalar or a list.~
[type]: the data type (eg, text, char, float, int, bit, etc). This can be scalar, a list with same length/ordering as field, or an array indexed by field.~
[constraint]: an optional constraint (eg, Null or Not Null) for entries in the new table. This can be scalar, a list with same length/ordering as field, or an array indexed by field.~
[debug]: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Create_DB_Table(DB_Connection, 'MyTable', 'Country', 'char', 'NULL') --> Executes a SQL statement like "CREATE TABLE MyTable ( "Country" char NULL );"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex field:=['UserID', 'UserEmail'];~
Local type:=Array(field, ['int', 'char']);~
Local constr:=Array(field, ['NOT NULL', 'NULL']);~
Create_DB_Table(db, tbl, field, type, constr) --> Executes a SQL statement like "CREATE TABLE [dbo].[UserData] ( [UserID] int NOT NULL, [UserEmail] char NULL );"
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(field, type, constraint);~
LocalIndex fieldList:=CopyIndex(#Slice(formatParam, 1));~
type:=(#Slice(formatParam, 2))[.fieldList=fieldList];~
constraint:=(#Slice(formatParam, 3))[.fieldList=fieldList];~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local beginStr:=f"{DB_Create_Table_Syn[@DB_Stmnt_Part_Short=1]}";~
Local repeatBody:=f"{DB_Create_Table_Syn[@DB_Stmnt_Part_Short=2]}";~
Local endStr:=f"{DB_Create_Table_Syn[@DB_Stmnt_Part_Short=3]}";~
~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local field:='{fieldList}';~
   Local type:='{type}';~
   Local constraint:='{constraint}';~
   f""{repeatBody}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
repeatBody:=TextReplace(TextReplace(JoinText(literals, fieldList, ', '), '"', '""', True), "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{Concatenate all parts and evaluate literals}~
expr:=f"Local tbl:='{tbl}';~
f""{beginStr} {repeatBody} {endStr}""";   {the formatted text literal}~
Local query:=Evaluate(expr);~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   DbWrite(db, query)~
)
NodeLocation: 216,400,1
NodeSize: 184,24
WindState: 2,434,10,1388,901

Function DB_Add_Record(db; tbl: Text Atom; field, value; debug: Atom=0)
Title: DB_Add_Record(db, tbl, field)
Description: Adds a new record to database table «tbl». The field and value pairs of the new record are specified by the «field» and «value» parameters, respectively.~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«field»: field names for the record. This can be scalar or a list.~
«value»: the values assigned to the record for the fields specified. This can be scalar, a list with same length/ordering as field, or an array indexed by field.~
«debug»: an optional integer specifying a debug mode (see modes below). ~
	0 = (the default) execute the database query command without displaying the underlying database query.~
	1 = return the database query command as the result, but don't execute it.~
	2 = execute the database query command and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Add_DB_Record(DB_Connection, 'MyTable', 'Country', 'USA') --> Executes a SQL statement like "INSERT INTO MyTable ( "Country" ) VALUES ( 'USA' );"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex field:=['UserID', 'UserEmail'];~
Local value:=Array(field, [22, 'lincoln@gmail.com']);~
Add_DB_Record(db, tbl, field, value) --> Executes a SQL statement like "INSERT INTO UserData ( "UserID", "UserEmail" ) VALUES ( 22, 'lincoln@gmail.com' );"
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(field, value);~
LocalIndex fieldList:=CopyIndex(#Slice(formatParam, 1));~
value:=(#Slice(formatParam, 2))[.fieldList=fieldList];~~~
value:=DB_Add_Literal_Quote(IF IsDateTime(value) THEN DB_Format_Date_Text(value) ELSE value);~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local beginStr:=f"{DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=1]}";~
Local repeatBody1:=f"{DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=2]}";~
Local body2:=f"{DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=3]}";~
Local repeatBody3:=IF IsDateTime(value)~
   THEN f"{TextReplace(DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=4], 'value', 'value:Dyyyy-MM-dd')}"~
   ELSE f"{TextReplace(DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=4], 'value', 'value:G')}";~
Local endStr:=f"{DB_Insert_Record_Syn[@DB_Stmnt_Part_Long=5]}";~
~
{Evaluate text literals and apply dimensionality to repeatable body #1}~
Local expr:=f"Local field:='{fieldList}';~
   f""{repeatBody1}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
repeatBody1:=TextReplace(TextReplace(JoinText(literals, fieldList, ', '), '"', '""', True), "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{Evaluate text literals and apply dimensionality to repeatable body #3}~
expr:=f"Local value:={value};~
   f""{repeatBody3}""";   {the formatted text literal}~
literals:=Evaluate(expr);~
repeatBody3:=TextReplace(TextReplace(JoinText(literals, fieldList, ', '), '"', '""', True), "''", "'", True);   {Join text and replace solitary double quote with two double quotes}~
~
{Concatenate all parts and evaluate literals}~
expr:=f"Local tbl:='{tbl}';~
   f""{beginStr} {repeatBody1} {body2} {repeatBody3} {endStr}""";   {the formatted text literal}~
Local query:=Evaluate(expr);~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query); ~
   DbWrite(db, query)~
)~

NodeLocation: 216,248,1
NodeSize: 184,24
WindState: 2,408,61,1453,834

Function DB_Update_Record(db; tbl: Text Atom; updatedField, updatedValue, lookupField, lookupValue; debug: Atom=0)
Title: DB_Update_Record(db, tbl, updatedField, updatedValue, lookupField, lookupValue)
Description: Syntax to update the values in an existing record in database table «tbl». The field and updated value pairs are specified by the «updatedField» and «updatedValue» parameters, respectively. The existing record to be updated is found by searching for the field and value pairs specified by the «lookupField» and «lookupValue» parameters, respectively.~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«updatedField»: field names for the updated field-value pairs, typically specified in the "SET" portion of a SQL statement. This can be scalar or a list.~
«updatedValue»: the values assigned to the fields being updated, typically specified in the "SET" portion of  a SQL statement. This can be scalar, a list with same length/ordering as updatedField, or an array indexed by updatedField.~
«lookupField»: field names used to find a matching record to update, typically specified in the "WHERE" portion of a SQL statement. This can be scalar or a list.~
«lookupValue»: the values to search for in the fields used to find a matching record to update, typically specified in the "WHERE" portion of  a SQL statement. This can be scalar, a list with same length/ordering as lookupField, or an array indexed by lookupField.~
«debug»: an optional integer specifying a debug mode (see modes below).~
	0 = (the default) execute the database query command without displaying the underlying database query.~
	1 = return the database query command as the result, but don't execute it.~
	2 = execute the database query command and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Update_DB_Record(DB_Connection, 'MyTable', 'Population', 3500, 'Country', 'USA') --> Executes a SQL statement like "UPDATE MyTable SET "Population" = 3500 WHERE "Country" = 'USA';"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex updatedField:=['UserID', 'UserEmail'];~
Local updatedValue:=Array(updatedField, [22, 'lincoln@gmail.com'];~
LocalIndex lookupField:=['FirstName', LastName'];~
Local lookupValue:=Array(lookupField, ['Abe', 'Lincoln']);~
Update_DB_Record(db, tbl, updatedField, updatedValue, lookupField, lookupValue) ~
	--> Executes a SQL statement like "UPDATE UserData SET [UserID] = 22, [UserEmail] = 'lincoln@gmail.com' WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln';"
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(updatedField, updatedValue);~
LocalIndex updatedFieldList:=CopyIndex(#Slice(formatParam, 1));~
updatedValue:=DB_Add_Literal_Quote((#Slice(formatParam, 2))[.fieldList=updatedFieldList]);~
formatParam:=DB_Format_Parameters(lookupField, lookupValue);~
LocalIndex lookupFieldList:=CopyIndex(#Slice(formatParam, 1));~
lookupValue:=(#Slice(formatParam, 2))[.fieldList=lookupFieldList];~
lookupValue:=DB_Add_Literal_Quote(IF IsDateTime(lookupValue) THEN DB_Format_Date_Text(lookupValue) ELSE lookupValue);~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local beginStr:=f"{DB_Update_Record_Syn[@DB_Stmnt_Part_Long=1]}";~
Local repeatBody1:=IF IsDateTime(updatedValue)~
   THEN f"{TextReplace(DB_Update_Record_Syn[@DB_Stmnt_Part_Long=2], 'value', 'value:Dyyyy-MM-dd')}"~
   ELSE f"{TextReplace(DB_Update_Record_Syn[@DB_Stmnt_Part_Long=2], 'value', 'value:G')}";~
Local body2:=f"{DB_Update_Record_Syn[@DB_Stmnt_Part_Long=3]}";~
Local repeatBody3:=IF IsDateTime(lookupValue)~
   THEN f"{TextReplace(DB_Update_Record_Syn[@DB_Stmnt_Part_Long=4], 'value', 'value:Dyyyy-MM-dd')}"~
   ELSE f"{TextReplace(DB_Update_Record_Syn[@DB_Stmnt_Part_Long=4], 'value', 'value:G')}";~
Local endStr:=f"{DB_Update_Record_Syn[@DB_Stmnt_Part_Long=5]}";~
~
{Evaluate text literals and apply dimensionality to repeatable body #1}~
Local expr:=f"Local field:='{updatedFieldList}';~
   Local value:={updatedValue};~
   f""{repeatBody1}""";   {the formatted text literal}~
ConsolePrint(expr);~
Local literals:=Evaluate(expr);~
repeatBody1:=TextReplace(TextReplace(JoinText(literals, updatedFieldList, ', '), '"', '""', True), "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{Evaluate text literals and apply dimensionality to repeatable body #3}~
expr:=f"Local field:='{lookupFieldList}';~
   Local value:={lookupValue};~
   f""{repeatBody3}""";   {the formatted text literal}~
ConsolePrint(expr);~
literals:=Evaluate(expr);~
repeatBody3:=TextReplace(TextReplace(JoinText(literals, lookupFieldList, ' AND '), '"', '""', True), "''", "'", True);   {Join text and replace solitary double quote with two double quotes}~
~
{Concatenate all parts and evaluate literals}~
expr:=f"Local tbl:='{tbl}';~
   f""{beginStr} {repeatBody1} {body2} {repeatBody3} {endStr}""";   {the formatted text literal}~
ConsolePrint(expr);~
Local query:=Evaluate(expr);~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query); ~
   DbWrite(db, query)~
)
NodeLocation: 216,192,1
NodeSize: 184,24
WindState: 2,98,105,1731,879

Function DB_Delete_Record(db; tbl: Text Atom; field, value; debug: Optional Atom=0)
Title: DB_Delete_Record(db, tbl, field)
Description: Deletes existing database records where column names «field» have entries of «value».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«field»: field names used to find a matching record to delete. This can be scalar or a list.~
«value»: the values to search for in the fields used to find a matching record to delete. This can be scalar, a list with same length/ordering as lookupField, or an array indexed by lookupField.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Delete_DB_Record(DB_Connection, 'MyTable', 'Country', 'USA') --> Executes a SQL statement like "DELETE FROM MyTable WHERE "Country" = 'USA';"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex field:=['FirstName', LastName'];~
Local value:=Array(lookupField, ['Abe', 'Lincoln']);~
Delete_DB_Record(db, tbl, field, value) --> Executes a SQL statement like "DELETE FROM UserData WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln';"
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(field, value);~
LocalIndex fieldList:=CopyIndex(#Slice(formatParam, 1));~
value:=(#Slice(formatParam, 2))[.fieldList=fieldList];~
value:=DB_Add_Literal_Quote(IF IsDateTime(value) THEN DB_Format_Date_Text(value) ELSE value);~
		~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local beginStr:=f"{DB_Delete_Record_Syn[@DB_Stmnt_Part_Short=1]}";~
Local repeatBody:=IF IsDateTime(value)~
   THEN f"{TextReplace(DB_Delete_Record_Syn[@DB_Stmnt_Part_Short=2], 'value', 'value:Dyyyy-MM-dd')}"~
   ELSE f"{TextReplace(DB_Delete_Record_Syn[@DB_Stmnt_Part_Short=2], 'value', 'value:G')}";~
Local endStr:=f"{DB_Delete_Record_Syn[@DB_Stmnt_Part_Short=3]}";~
		~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local field:='{fieldList}';~
   Local value:={value};~
   f""{repeatBody}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
repeatBody:=TextReplace(TextReplace(JoinText(literals, fieldList, ' AND '), '"', '""', True), "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
		~
{Concatenate all parts and evaluate literals}~
expr:=f"Local tbl:='{tbl}';~
   f""{beginStr} {repeatBody} {endStr}""";   {the formatted text literal}~
Local query:=Evaluate(expr);~
		~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query); ~
   DbWrite(db, query)~
)
NodeLocation: 216,304,1
NodeSize: 184,24
WindState: 2,366,90,1388,901

Function DB_Retrieve_Record(db; tbl: Text; retrieveField: Optional; lookupField, lookupValue: Optional; debug: Optional Atom=0)
Title: DB_Retrieve_Record(db, tbl, retrieveField, lookupField, lookupValue)
Description: Retrieves columns «retrieveField» from an existing database table «tbl» for records where column names «lookupField» have entries of «lookupValue». Parameters «retrieveField», «lookupField» and «lookupValue» are optional. Omitting «retrieveField» will return all columns in the table. Omitting either «lookupField» or «lookupValue» will return all records in the table. ~
~
The result will show records along a .Row index. If no «retrieveField» is specified, the results will also be indexed by a .Column index. If «retrieveField» is specified and scalar, no column index is used. If «retrieveField» is specified and an index, it will appear as the column index. ~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«retrieveField»: optional field names to retrieve from the table. This can be scalar or a list.~
«lookupField»: optional field names used to find a matching field-value pair for the retrieved record. This can be scalar or a list.~
«lookupValue»: optional values to search for in the fields used to find a matching field-value pair for the retrieved record. This can be scalar, an implicit list with same length/ordering as lookupField, or a one-dimensional array indexed by lookupField.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password');~
Retrieve_DB_Record(DB_Connection, 'MyTable') --> Executes a SQL statement like "SELECT * FROM MyTable"~
~
Example #2: ~
Local db:='Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx';~
Retrieve_DB_Record(db, 'MyTable', 'Population', 'Country', 'USA') --> Executes a SQL statement like "SELECT "Population" FROM MyTable WHERE "Country" = 'USA';"~
~
Example #3: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex retrieveField:=['UserID', 'UserEmail'];~
LocalIndex lookupField:=['FirstName', LastName'];~
Local lookupValue:=Array(lookupField, ['Abe', 'Lincoln']);~
Retrieve_DB_Record(db, tbl, retrieveField, lookupField, lookupValue) --> Executes a SQL statement like "SELECT [UserID], [UserEmail] FROM UserData WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln';"
Definition: {Determine and apply dimensionality to parameters}~
Local allFieldFlag:=IsNotSpecified(retrieveField);~
LocalIndex formatParam:=DB_Format_Parameters(retrieveField);~
LocalIndex retrieveFieldList:=CopyIndex(#Slice(formatParam, 1));~
Local allRecordFlag:=IsNotSpecified(lookupField) OR IsNotSpecified(lookupValue);~
IF allRecordFlag~
THEN (~
   lookupField:='';~
   lookupValue:='';~
);~
formatParam:=DB_Format_Parameters(lookupField, lookupValue);~
LocalIndex lookupFieldList:=CopyIndex(#Slice(formatParam, 1));~
lookupValue:= (#Slice(formatParam, 2))[.fieldList=lookupFieldList];~
lookupValue:= DB_Add_Literal_Quote(IF IsDateTime(lookupValue) THEN DB_Format_Date_Text(lookupValue) ELSE lookupValue);~
~
{Check for disallowed dimensionality in db}~
LocalIndex invalidIndexes:=IndexesOf(db);~
Local invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «db» that includes the following disallowed dimensionality: [' & JoinText(invalidIdent, invalidIndexes, ', ') & ']. Ensure that «db» has no dimensionality.', 'Invalid «db» Parameter');~
~
{Check for disallowed dimensionality in tbl}~
invalidIndexes:=IndexesOf(tbl);~
invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «tbl» that includes the following disallowed dimensionality: [' & JoinText(invalidIdent, invalidIndexes, ', ') & ']. Ensure that «tbl» has no dimensionality.', 'Invalid «tbl» Parameter');~
~
{Check for disallowed dimensionality in retrieveField}~
LocalIndex allIndexes:=IndexesOf(retrieveFieldList);~
invalidIndexes:=Subset(allIndexes <> Handle(retrieveFieldList));~
invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «retrieveField» that includes the following disallowed dimensionality: ' & JoinText(invalidIdent, invalidIndexes, ', ') & '. Ensure that «retrieveField» is either scalar or a list.', 'Invalid «retrieveField» Parameter');~
~
{Check for disallowed dimensionality in lookupField}~
allIndexes:=IndexesOf(lookupFieldList);~
invalidIndexes:=Subset(allIndexes <> Handle(lookupFieldList));~
invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «lookupField» that includes the following disallowed dimensionality: ' & JoinText(invalidIdent, invalidIndexes, ', ') & '. Ensure that «lookupField» is either scalar or a list.', 'Invalid «lookupField» Parameter');~
~
{Check for disallowed dimensionality in lookupValue}~
allIndexes:=IndexesOf(lookupValue);~
invalidIndexes:=Subset(allIndexes <> Handle(lookupFieldList));~
invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «lookupValue» that includes the following disallowed dimensionality: ' & JoinText(invalidIdent, invalidIndexes, ', ') & '. Ensure that «lookupValue» is either scalar, an implicit list with the same length and order as «lookupField», or a one-dimensional array indexed by «lookupField».', 'Invalid «lookupValue» Parameter');~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local beginStr:=f"{DB_Select_Record_Syn[@DB_Stmnt_Part_Long=1]}";~
Local repeatBody1:=f"{DB_Select_Record_Syn[@DB_Stmnt_Part_Long=2]}";~
Local body2:=f"{DB_Select_Record_Syn[@DB_Stmnt_Part_Long=3]}";~
Local repeatBody3:=IF IsDateTime(lookupValue)~
   THEN f"{TextReplace(DB_Delete_Record_Syn[@DB_Stmnt_Part_Short=2], 'value', 'value:Dyyyy-MM-dd')}"~
   ELSE f"{TextReplace(DB_Select_Record_Syn[@DB_Stmnt_Part_Long=4], 'value', 'value:G')}";~
Local endStr:=f"{DB_Select_Record_Syn[@DB_Stmnt_Part_Long=5]}";~
 ~
{Modify query parts in response to omitted parameters}~
IF allRecordFlag~
THEN (~
   body2:=TextReplace(body2, "WHERE", '', True);~
   repeatBody3:='';~
);~
IF allFieldFlag~
THEN repeatBody1:='*';~
~
{Evaluate text literals and apply dimensionality to repeatable body #1}~
Local expr:=f"Local field:='{retrieveFieldList}';~
   f""{repeatBody1}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
repeatBody1:=TextReplace(TextReplace(JoinText(literals, retrieveFieldList, ', '), '"', '""', True), "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{Evaluate text literals and apply dimensionality to repeatable body #3}~
expr:=f"Local field:='{lookupFieldList}';~
   Local value:={lookupValue};~
   f""{repeatBody3}""";   {the formatted text literal}~
literals:=Evaluate(expr);~
repeatBody3:=TextReplace(TextReplace(JoinText(literals, lookupFieldList, ' AND '), '"', '""', True), "''", "'", True);   {Join text and replace solitary double quote with two double quotes}~
~
{Concatenate all parts and evaluate literals}~
expr:=f"Local tbl:='{tbl}';~
   f""{beginStr} {repeatBody1} {body2} {repeatBody3} {endStr}""";   {the formatted text literal}~
Local query:=Evaluate(expr);~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   LocalIndex Row:=DbWrite(db, query);~
   LocalIndex Column:=DbLabels(Row);~
   Local data:=DbTable(Row, Column);~
   IF AllFieldFlag THEN data ELSE data[Column=retrieveField]~
)
NodeLocation: 216,136,1
NodeSize: 184,24
WindState: 2,191,-15,1648,898

Function DB_Add_Column(db; tbl: Text Atom; field, type: Text; debug: Atom=0)
Title: DB_Add_Column(db, tbl, field, type)
Description: Adds a new column «field» to an existing database table «tbl».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«field»: the column name for the new column. This can be scalar or a list if adding multiple columns. ~
«type»: the data type (eg, text, char, float, int, bit, etc). This can be scalar, a list with same length/ordering as field, or an array indexed by field.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Add_DB_Column(DB_Connection, 'MyTable', 'City', 'char') --> Executes a SQL statement like "ALTER TABLE MyTable ADD COLUMN "City" char;"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex field:=['UserPhone', 'UserCity'];~
Local type:=Array(field, ['int', 'char']);~
Add_DB_Column(db, tbl, field, type) --> Executes two SQL statements like:~
    ALTER TABLE UserData ADD UserPhone int;~
    ALTER TABLE UserData ADD UserCity int;
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(field, type);~
LocalIndex fieldList:=CopyIndex(#Slice(formatParam, 1));~
type:=(#Slice(formatParam, 2))[.fieldList=fieldList];~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local body:=f"{DB_Add_Column_Syn}";~
~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local tbl:='{tbl}';~
   Local field:='{fieldList}';~
   Local type:='{type}';~
   f""{body}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
Local query:=TextReplace(literals, "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query) ~
   ELSE DbWrite(db, query)~
)~

NodeLocation: 216,568,1
NodeSize: 184,24
WindState: 2,471,4,1388,856

Function DB_Delete_Table(db; tbl: Atom Text; debug: Atom=0; ignoreWarning: Boolean=0)
Title: DB_Delete_Table(db, tbl)
Description: Deletes an existing database table «tbl».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
«ignoreWarning»: an optional parameter to turn off warning messages about deleting the table. True = ignore warning, while False = show warning.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Delete_DB_Table(DB_Connection, 'MyTable') --> Executes a SQL statement like "DROP TABLE MyTable;"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
LocalIndex tbl:=['UserData', 'UserResponse'];~
Delete_DB_Table(db, tbl) --> Executes two SQL statements like:~
    DROP TABLE UserData;~
    DROP TABLE UserResponse;
Definition: {Provide warning message about deletion}~
IF NOT ignoreWarning ~
THEN MsgBox("Are you sure you want to delete database table '" & tbl & "'? This action is irreversible. Press 'OK' to delete the table, or 'Cancel' to keep the table.", 1 , "Delete table?");~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local body:=f"{DB_Delete_Table_Syn}";~
	~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local tbl:='{tbl}';~
   f""{body}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
Local query:=TextReplace(literals, "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   DbWrite(db, query)~
)
NodeLocation: 216,680,1
NodeSize: 184,24
WindState: 2,232,5,1388,846

Function DB_Delete_Column(db; tbl: Text Atom; field: Text; debug: Atom=0)
Title: DB_Delete_Column(db, tbl, field)
Description: Deletes an existing column «field» from an existing database table «tbl».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: the database table.~
«field»: the column name for the column that will be deleted. This can be scalar or a list if deleting multiple columns. ~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('Driver=Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb); ReadOnly=0;DBQ=C:\Users\BettyLou\Workbook.xlsx');~
Local tbl:='MyTable';~
Local field:='City';~
Delete_DB_Column(DB_Connection, tbl, field, type) --> Executes a SQL statement like "ALTER TABLE MyTable DROP COLUMN "City";"~
~
Example #2: ~
Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password';~
Local tbl:='UserData';~
LocalIndex field:=['UserPhone', 'UserCity'];~
Delete_DB_Column(db, tbl, field, type, constr) --> Executes two SQL statements like:~
    ALTER TABLE UserData DROP COLUMN UserPhone;~
    ALTER TABLE UserData DROP COLUMN UserCity;
Definition: {Determine and apply dimensionality to parameters}~
LocalIndex formatParam:=DB_Format_Parameters(field);~
LocalIndex fieldList:=CopyIndex(#Slice(formatParam, 1));~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local body:=f"{DB_Drop_Column_Syn}";~
~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local tbl:='{tbl}';~
Local field:='{fieldList}';~
   f""{body}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
Local query:=TextReplace(literals, "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   DbWrite(db, query)~
)~

NodeLocation: 216,624,1
NodeSize: 184,24
WindState: 2,273,110,1388,686

Module DB_Function_Examples
Title: DB Function Examples
Author: James Milford~
Lumina Decision Systems
Date: Thu, Oct 12, 2023 5:35 PM
NodeLocation: 568,88,1
NodeSize: 64,24
DiagState: 2,695,6,1086,762,17,10
NodeColor: 65535,40606,6682

Variable DB_Create_Scalar
Title: Create DB - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:='fieldName';~
Local type:='int';~
Local constr:='NULL';~
DB_Create_Table(db, tbl, field, type, constr, debug:1)
NodeLocation: 472,440,1
NodeSize: 64,24
WindState: 2,60,22,720,350
ValueState: 2,112,18,966,304,,MIDM

Variable DB_Add_Rec_Array
Title: Add Record - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
LocalIndex record:=1..3;~
LocalIndex third:1..2;~
Local value:=@field * @record * @third;~
DB_Add_Record(db, tbl, field, value, debug:1)
NodeLocation: 760,288,1
NodeSize: 64,24
WindState: 2,98,82,765,444
ValueState: 2,77,54,1647,304,,MIDM

Variable DB_Create_ScalarArr
Title: Create DB - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:=['fieldName1', 'fieldName2'];~
Local type:=['int', 'char'];~
Local constr:='NULL';~
DB_Create_Table(db, tbl, field, type, constr, debug:1)
NodeLocation: 616,440,1
NodeSize: 64,24
WindState: 2,58,100,720,350
ValueState: 2,31,44,966,304,,MIDM

Variable DB_Create_Array
Title: Create DB - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
Local type:=['int', 'char'];~
Local constr:=Array(field, ['NULL', 'NOT NULL']);~
DB_Create_Table(db, tbl, field, type, constr, debug:1);
NodeLocation: 760,440,1
NodeSize: 64,24
WindState: 2,54,414,1221,350
ValueState: 2,248,326,1475,577,,MIDM

Variable DB_Add_Rec_ScalArr
Title: Add Record - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
Local val:='3';~
DB_Add_Record(db, tbl, field, val, debug:1)
NodeLocation: 616,288,1
NodeSize: 64,24
ValueState: 2,64,542,1647,304,,MIDM

Variable DB_Add_Rec_Scalar
Title: Add Record - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:='fieldName1';~
Local val:=10-Nov-2023;~
DB_Add_Record(db, tbl, field, val, debug:1)
NodeLocation: 472,288,1
NodeSize: 64,24
ValueState: 2,27,12,1647,336,,MIDM

Variable DB_UpdateRec_Array
Title: Update Record - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex record:=1..3;~
LocalIndex updatedField:=['fieldName1', 'fieldName2'];~
Local updatedValue:=Array(updatedField, [NumberToText(record),record]);~
LocalIndex lookupField:=['fieldName3','fieldName4'];~
Local lookupValue:=Array(lookupField, [@record, @record*2]);~
DB_Update_Record(db, tbl, updatedField, updatedValue, lookupField, lookupValue, debug:1)
NodeLocation: 760,232,1
NodeSize: 64,24
WindState: 2,98,82,765,444
ValueState: 2,20,27,1647,304,,MIDM

Variable DB_UpdateRec_ScalArr
Title: Update Record - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex updatedField:=['fieldName1','fieldName2'];~
Local updatedValue:='1';~
LocalIndex lookupField:=['fieldName3','fieldName4'];~
Local lookupValue:=Array(lookupField, [1,2]);~
DB_Update_Record(db, tbl, updatedField, updatedValue, lookupField, lookupValue, debug:1)
NodeLocation: 616,232,1
NodeSize: 64,24
ValueState: 2,35,355,1647,304,,MIDM
ReformVal: [Sys_LocalIndex('lookupField'),DB_Platform]

Variable DB_Update_Rec_Scalar
Title: Update Record - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local updatedField:='fieldName1';~
Local updatedValue:='label';~
Local lookupField:='fieldName2';~
Local lookupValue:=1M;~
DB_Update_Record(db, tbl, updatedField, updatedValue, lookupField, lookupValue, debug:1)
NodeLocation: 472,232,1
NodeSize: 64,24
ValueState: 2,36,363,1647,304,,MIDM
{!50000|Att_ColumnWidths: [588]}

Variable DB_Delete_Rec_Scalar
Title: Delete Record - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:='fieldName';~
Local value:=1K;~
DB_Delete_Record(db, tbl, field, value, debug:1)
NodeLocation: 472,344,1
NodeSize: 64,24
ValueState: 2,407,9,966,304,,MIDM

Variable DB_DeleteRec_ScalArr
Title: Delete Record - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:=['fieldName1', 'fieldName2'];~
Local value:=[2, 'char'];~
DB_Delete_Record(db, tbl, field, value, debug:1)
NodeLocation: 616,344,1
NodeSize: 64,24
ValueState: 2,31,44,966,304,,MIDM

Variable DB_DeleteRec_Array
Title: Delete Record - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
LocalIndex record:=1..3;~
Local value:=Array(field, [@record, @record*2]);~
DB_Delete_Record(db, tbl, field, value, debug:1);
NodeLocation: 760,344,1
NodeSize: 64,24
ValueState: 2,1,267,1475,577,,MIDM

Variable DB_Retrv_Rec_Scalar
Title: Retrieve Record - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local retrieveField:='fieldName1';~
Local lookupField:='fieldName2';~
Local lookupValue:=1-Jan-2023;~
DB_Retrieve_Record(db, tbl, retrieveField, lookupField, lookupValue, debug:1)
NodeLocation: 472,176,1
NodeSize: 64,24
ValueState: 2,38,14,1309,304,,MIDM

Variable DB_Retrv_Rec_ScalArr
Title: Retrieve Record - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex retrieveField:=['fieldName1','fieldName2'];~
Local lookupField:=['fieldName3', 'fieldName4'];~
Local lookupValue:='x';~
DB_Retrieve_Record(db, tbl, retrieveField, lookupField, lookupValue, debug:1)
NodeLocation: 616,176,1
NodeSize: 64,24
ValueState: 2,25,30,966,304,,MIDM

Variable DB_Retrv_Rec_Array
Title: Retrieve Record - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex retrieveField:=['fieldName1','fieldName2'];~
LocalIndex lookupField:=['FirstName', 'LastName'];~
Local lookupValue:=Array(lookupField, ['Betty', 'Lou']);~
DB_Retrieve_Record(db, tbl, retrieveField, lookupField, lookupValue, debug:1)
NodeLocation: 760,176,1
NodeSize: 64,24
ValueState: 2,18,95,1475,577,,MIDM

Variable DB_Add_Column_Scalar
Title: Add Column - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:='fieldName';~
Local type:='int';~
DB_Add_Column(db, tbl, field, type, debug:1)
NodeLocation: 472,608,1
NodeSize: 64,24
ValueState: 2,407,9,966,304,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Add_Col_Scal_Arr
Title: Add Column - Scalar+Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:=['fieldName1', 'fieldName2'];~
Local type:=['int', 'char'];~
DB_Add_Column(db, tbl, field, type, debug:1)
NodeLocation: 616,608,1
NodeSize: 64,24
ValueState: 2,41,198,1323,561,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Add_Column_Array
Title: Add Column - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
Local type:=['int', 'char', 'bool'];~
DB_Add_Column(db, tbl, field, type, debug:1);
NodeLocation: 760,608,1
NodeSize: 64,24
ValueState: 2,1,267,1475,577,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Delete_Tbl_Scalar
Title: Delete Table - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
DB_Delete_Table(db, tbl, debug:1)
NodeLocation: 472,720,1
NodeSize: 64,24
ValueState: 2,407,9,966,304,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Delete_Col_Scalar
Title: Delete Column - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
Local field:='fieldName';~
DB_Delete_Column(db, tbl, field, debug:1)
NodeLocation: 472,664,1
NodeSize: 64,24
ValueState: 2,112,80,966,304,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Delete_Col_Array
Title: Delete Column - Array
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
LocalIndex field:=['fieldName1', 'fieldName2'];~
DB_Delete_Column(db, tbl, field, debug:1);
NodeLocation: 760,664,1
NodeSize: 64,24
ValueState: 2,1,267,1475,577,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Retrv_All_Rec
Title: Retrieve All Records
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
DB_Retrieve_Record(db, tbl, debug:1)
NodeLocation: 904,176,1
NodeSize: 64,24
ValueState: 2,38,14,1309,304,,MIDM

Variable DB_List_Col_Scalar
Title: List Columns - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
Local tbl:='tblName';~
DB_List_Columns(db, tbl, debug:1)
NodeLocation: 472,552,1
NodeSize: 64,24
ValueState: 2,407,9,1359,476,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_Delete_Tbl_Array
Title: Delete Table - Array
Definition: Local db:='DRIVER=SQL Server...';~
LocalIndex tbl:=['Table1', 'Table2'];~
DB_Delete_Table(db, tbl, debug:1)
NodeLocation: 760,720,1
NodeSize: 64,24
ValueState: 2,407,9,966,304,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Variable DB_List_Tbl_Scalar
Title: List Tables - Scalar
Definition: Local db:='DRIVER=SQL Server...';~
DB_List_Tables(db, debug:1)
NodeLocation: 472,496,1
NodeSize: 64,24
ValueState: 2,292,319,1300,461,,MIDM
ReformVal: [Sys_LocalIndex('fieldList'),DB_Platform]

Alias Al75172435
Title: Create DB Table
NodeLocation: 208,440,1
NodeSize: 184,24
Original: DB_Create_Table

Alias Al1148914259
Title: Add DB Record
NodeLocation: 208,288,1
NodeSize: 184,24
Original: DB_Add_Record

Alias Al612043347
Title: Update DB Record
NodeLocation: 208,232,1
NodeSize: 184,24
Original: DB_Update_Record

Alias Al1685785171
Title: Delete DB Record
NodeLocation: 208,344,1
NodeSize: 184,24
Original: DB_Delete_Record

Alias Al343607891
Title: Retrieve DB Record
NodeLocation: 208,176,1
NodeSize: 184,24
Original: DB_Retrieve_Record

Alias Al1417349715
Title: Add DB Column
NodeLocation: 208,608,1
NodeSize: 184,24
Original: DB_Add_Column

Alias Al880478803
Title: Delete DB Table
NodeLocation: 208,720,1
NodeSize: 184,24
Original: DB_Delete_Table

Alias Al1954220627
Title: Delete DB Column
NodeLocation: 208,664,1
NodeSize: 184,24
Original: DB_Delete_Column

Alias Al1385777491
Title: List DB Columns
NodeLocation: 208,552,1
NodeSize: 184,24
Original: DB_List_Columns

Alias Al1064913235
Title: List DB Columns
NodeLocation: 208,496,1
NodeSize: 184,24
Original: DB_List_Tables

Text DB_Te1305263827
Title: Functions to view or modify DB schema
NodeLocation: 524,568,-1
NodeSize: 516,184
NodeInfo: 1,,,,1,1

Text DB_Te644660947
Title: Functions to view or modify DB records
NodeLocation: 524,248,-1
NodeSize: 516,128
NodeInfo: 1,,,,1,1

Text DB_Te37104083
Title: Note on DbConnection function
Description: Analytica versions 4.6 and higher include a built-in <a href="https://docs.analytica.com/index.php/DbConnection">DbConnection</a> function that creates a «Database connection» object. ~
We recommend using a «Database connection» object to minimize database read/write times by maintaining an open connection to the database.~
Analytica versions prior to 4.6 must use a connection string to access the database, which is slightly slower.~
These examples use a connection string, instead of a «Database connection» object calling DbConnection, simply for backward compatibility.
NodeLocation: 524,60,-1
NodeSize: 516,52
NodeInfo: 1,,,,1,1
WindState: 2,157,305,720,350
NodeColor: 65535,65535,65535

Close DB_Function_Examples

Text DB_Te435882579
Title: Functions to view or modify DB schema
NodeLocation: 212,528,-1
NodeSize: 204,184
NodeInfo: 1,,,,1,1

Text DB_Te310839891
Title: Examples of query statements generated by DB functions
NodeLocation: 572,64,-1
NodeSize: 148,56
NodeInfo: 1,,,,1,1

Function DB_List_Columns(db; tbl: Text; debug: Atom=0)
Title: DB_List_Columns(db, tbl)
Description: Lists all the column names in an existing database table «tbl».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«tbl»: a database table.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection('DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password');~
Local tbl:='UserData';~
List_DB_Columns(DB_Connection, tbl) --> Executes a SQL statements like "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'UserData'; "
Definition: {Check for disallowed dimensionality in db}~
LocalIndex invalidIndexes:=IndexesOf(db);~
Local invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «db» that includes the following disallowed dimensionality: [' & JoinText(invalidIdent, invalidIndexes, ', ') & ']. Ensure that «db» has no dimensionality.', 'Invalid «db» Parameter');~
~
{Check for disallowed dimensionality in tbl}~
invalidIndexes:=IndexesOf(tbl);~
invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «tbl» that includes the following disallowed dimensionality: [' & JoinText(invalidIdent, invalidIndexes, ', ') & ']. Ensure that «tbl» has no dimensionality.', 'Invalid «tbl» Parameter');~
~
{Slice out query parts, uses literals to deal with two single or double quotes}~
Local body:=f"{DB_List_Columns_Syn}";~
	~
{Evaluate text literals and apply dimensionality to repeatable parts}~
Local expr:=f"Local tbl:='{tbl}';~
   f""{body}""";   {the formatted text literal}~
Local literals:=Evaluate(expr);~
Local query:=TextReplace(literals, "''", "'", True);   {Join text and replace solitary single/double quote with two single/double quotes}~
	~
	~
{If debug mode, then perform appropriate action}~
IF debug = 1~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   LocalIndex Row:=DbWrite(db, query);~
   LocalIndex Column:=DbLabels(Row);~
   DbTable(Row, Column);  ~
)
NodeLocation: 216,512,1
NodeSize: 184,24
WindState: 2,140,25,1388,846

Function DB_List_Tables(db; debug: Atom=0)
Title: DB_List_Tables(db)
Description: Lists all the table names from an existing database table referenced in the «db».~
~
«db»: a connection string or variable calling DbConnection to access the database.~
«debug»: an optional integer specifying a debug mode as follows: ~
	0 = (the default) execute the database query statement without displaying the query statement.~
	1 = return the database query statement as the result, but don't execute it.~
	2 = execute the database query statement and print it to typescript console, which can be opened by typing CTL+' (CTL + single quote). Warning: this will slow down execution, and should be turned off after debugging.~
~
Example #1: ~
DB_Connection:=DbConnection(Local db:='DRIVER=SQL Server;SERVER=data.server.com,1433;Database=MyDatabase;UID=UserName;PWD=Password');~
List_DB_Tables(DB_Connection) --> Executes a SQL statements like "SELECT name AS tableName FROM sys.tables;"
Definition: {Grab appropriate query}~
Local query:=DB_List_Tables_Syn;~
~
{Check for disallowed dimensionality in db}~
LocalIndex invalidIndexes:=IndexesOf(db);~
Local invalidIdent:=Identifier of IndexValue(invalidIndexes);~
IF Size(invalidIdent) > 0 ~
   THEN Error('You have specified a value for «db» that includes the following disallowed dimensionality: [' & JoinText(invalidIdent, invalidIndexes, ', ') & ']. Ensure that «db» has no dimensionality.', 'Invalid «db» Parameter');~
	~
{If debug mode, then perform appropriate action}~
IF debug~
THEN query~
ELSE (~
   IF debug = 2 THEN ConsolePrint(query);~
   LocalIndex Row:=DbWrite(db, query);~
   LocalIndex Column:=DbLabels(Row);~
   DbTable(Row, Column);  ~
)
NodeLocation: 216,456,1
NodeSize: 184,24
WindState: 2,387,-2,1388,846

Module DB_Syntax_Definition
Title: DB Syntax Definitions
Author: James Milford~
Lumina Decision Systems
Date: Sat, Feb 3, 2024 2:32 PM
NodeLocation: 568,184,1
NodeSize: 64,24
DiagState: 2,925,29,469,660,17,10
NodeColor: 0,31611,35466
NodeFontColor: 65535,65535,65535

Decision DB_Platform
Title: DB Platform
Description: Select the database platform you'll be using. If you don't see your platform here, you can add your platform to the DB_Platforms index and you'll need to update the syntax descriptions in the DB Syntax Definitions module.
Definition: Choice(Self,1,0)
NodeLocation: 136,184,1
NodeSize: 64,24
WindState: 2,62,170,720,350
Aliases: FormNode New39143763, FormNode Fo1368178387
{!40300|DomainExpr: CopyIndex(DB_Platforms)}

Decision DB_Update_Record_Syn
Title: Update Record Syntax
Description: Syntax for updating an existing record in a database table with name «tbl». The field-and-value pairs to be updated are included in "Repeatable Body 1". The records (eg, rows in the table) that will be updated are those that contain the field-and-value pairs in "Repeatable Body 3".~
~
Allowed parameter names: tbl, field, and value.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
Begin					Repeatable Body 1			Body 2 			Repeatable Body 3			End~
UPDATE {tbl} SET		""{field}""  = {value}			WHERE			""{field}""  = {value}			);~
~
Result #1:~
UPDATE MyTable SET "Population" = 350,000,000 WHERE "Country" = 'USA';~
	When «tbl» is MyTable, the «field» and «value» pair in Repeatable Body 1 is Population and 350,000,000, and the «field» and «value» pair in Repeatable Body 3 is Country and USA.~
~
Example #2:~
Begin						Repeatable Body 1			Body 2 			Repeatable Body 3			End~
UPDATE {tbl} SET		[{field}]  = {value}			WHERE			[{field}]  = {value}			);~
~
Result #2:~
UPDATE UserData SET [UserName] = 'Abe Lincoln', [UserEmail] = 'lincoln@gmail.com' WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln';~
	When «tbl» is UserData, ~
	Repeatable Body 1's «field» is a list ['UserName', 'UserEmail'] ~
	Repeatable Body 1's «value» is an array indexed by «field» (eg, Array(field, ['Abe Lincoln', 'lincoln@gmail.com']) )~
	Repeatable Body 3's «field» is a list ['FirstName', 'LastName'] ~
	Repeatable Body 3's «value» is an array indexed by «field» (eg, Array(field, ['Abe', 'Lincoln']) )
Definition: DetermTable(DB_Stmnt_Part_Long,DB_Platform)(~
'UPDATE {tbl} SET','UPDATE {tbl} SET','UPDATE {tbl} SET','UPDATE {tbl} SET',~
'{field} = {value}','{field} = {value}','""{field}"" = {value}','[{field}] = {value}',~
'WHERE','WHERE','WHERE','WHERE',~
'{field} = {value}','{field} = {value}','""{field}"" = {value}','[{field}] = {value}',~
';',';',';',';')
NodeLocation: 360,152,1
NodeSize: 64,24
WindState: 2,4,-1,1423,704
DefnState: 2,159,223,1076,303,0,DFNM
ValueState: 2,580,586,797,303,,MIDM
ReformDef: [DB_Stmnt_Part_Long,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}

Text DB_Te1722532947
Title: Syntax Definitions
Description: New database platforms can be added to the "DB Platforms" index. Then the platform-specific syntax can be added to each of the syntax variables.
NodeLocation: 236,328,-1
NodeSize: 220,320
NodeInfo: 1,,,,1,1

Index DB_Platforms
Title: DB Platforms
Definition: ['SQL Server','MariaDB','PostgreSQL','MS Access']
NodeLocation: 136,248,1
NodeSize: 64,24

Decision DB_Create_Table_Syn
Title: Create Table Syntax
Description: Syntax for creating a new database table with name «tbl», having columns of «field», data formatting of «type», and null or non-null specification of «constraint».~
~
Allowed parameter names: tbl, field, type, and constraint.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
Begin						Repeatable Body				End	~
CREATE TABLE {tbl} (		""{field}"" {type} {constraint}		);~
~
Result #1:~
CREATE TABLE MyTable ( "Country" INT NULL );~
	When «tbl» is MyTable, «field» is Country, «type» is INT, and «constraint» is NULL.~
~
Example #2:~
Begin								Repeatable Body				End	~
CREATE TABLE [.dbo].[{tbl}] (		[{field}] {type} {constraint}		);~
~
Result #2:~
CREATE TABLE [.dbo].[UserData] ( [UserID] int Not Null, [UserEmail] char Null );~
	When «tbl» is UserData, ~
	field» is a list ['UserID', 'UserEmail']~
	«type» is an array indexed by «field» (eg, Array(field, ['int', 'char']) )~
	«constraint» is a also an array (eg, Array(field, ['Not Null', 'Null']) ).
Definition: DetermTable(DB_Stmnt_Part_Short,DB_Platform)(~
'CREATE TABLE [dbo].[{tbl}] (','CREATE TABLE {tbl} (','CREATE TABLE {tbl} (','CREATE TABLE {tbl} (',~
'[{field}] {type} {constraint}','`{field}` {type} {constraint}','""{field}"" {type} {constraint}','[{field}] {type}',~
');',');',');',');')
NodeLocation: 360,96,1
NodeSize: 64,24
WindState: 2,296,11,1411,722
DefnState: 2,27,217,1232,445,0,DFNM
ValueState: 2,16,138,416,303,,MIDM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,\([,255])]}

Index DB_Stmnt_Part_Short
Att_PrevIndexValue: ['Begin','Repeatable Body','End']
Title: Statement Part - Short
Definition: ['Begin','Repeatable Body','End']
NodeLocation: 136,304,1
NodeSize: 64,24

Decision DB_Insert_Record_Syn
Title: Insert Record Syntax
Description: Syntax for inserting a new record to a database table with name «tbl». The field-and-value pairs to be inserted are included in "Repeatable Body 1" and "Repeatable Body 3".~
~
Allowed parameter names: tbl, field, and value.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
Begin					Repeatable Body 1			Body 2 				Repeatable Body 3			End~
INSERT INTO {tbl} (		""{field}"" 					) VALUES (		 	{value}						);~
~
Result #1:~
INSERT INTO MyTable ("Population") VALUES (350,000,000);~
	When «tbl» is MyTable, «field» is Population and «value» is 350,000,000.~
~
Example #2:~
Begin						Repeatable Body 1		Body 2 				Repeatable Body 3			End~
INSERT INTO {tbl} (			[{field}] 					) VALUES (		 	{value}						);~
~
Result #2:~
INSERT INTO UserData ([UserName], [UserEmail], [FirstName], [LastName]) VALUES ('Abe Lincoln', 'lincoln@gmail.com', 'Abe', 'Lincoln');~
	When «tbl» is UserData, ~
	«field» is a list ['UserName', 'UserEmail', 'FirstName', 'LastName'] ~
	«value» is an array indexed by «field» (eg, Array(field, ['Abe Lincoln', 'lincoln@gmail.com', 'Abe', 'Lincoln']) )
Definition: DetermTable(DB_Platform,DB_Stmnt_Part_Long)(~
'INSERT INTO {tbl} (','[{field}]',') VALUES (','{value}',');',~
'INSERT INTO {tbl} (','`{field}`',') VALUES (','{value}',');',~
'INSERT INTO {tbl} (','""{field}""',') VALUES (','{value}',');',~
'INSERT INTO {tbl} (','[{field}]',') VALUES (','{value}',');')
NodeLocation: 360,208,1
NodeSize: 64,24
WindState: 2,34,77,1313,734
DefnState: 2,28,89,866,303,0,DFNM
ReformDef: [DB_Stmnt_Part_Long,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}

Index DB_Stmnt_Part_Long
Att_PrevIndexValue: ['Begin','Repeatable Body 1','Body 2','Repeatable Body 3','End']
Title: Statement Part - Long
Definition: ['Begin','Repeatable Body 1','Body 2','Repeatable Body 3','End']
IndexVals: ['item 1']
NodeLocation: 136,360,1
NodeSize: 64,24

Decision DB_Select_Record_Syn
Title: Select Record Syntax
Description: Syntax for retrieving fields and records from an exisitng database table with name «tbl». The «field» in "Repeatable Body 1" are the typically the table columns that you wish to return. The «field» and «value» pairs in "Repeatable Body 3" are typically used as lookups to find specific records or rows within the table.~
~
Allowed parameter names: tbl, field, and value.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
Begin			Repeatable Body 1			Body 2 					Repeatable Body 3			End~
SELECT		""{field}"" 					FROM {tbl} WHERE		 ""{field}""  = {value}			;~
~
Result #1:~
SELECT "Population" FROM MyTable WHERE "Country" = 350,000,000;~
	When «tbl» is MyTable, Body 1's «field» is Population and Body 3's «field» and «value» pair is Country and 350,000,000.~
~
Example #2:~
Begin			Repeatable Body 1			Body 2 					Repeatable Body 3			End~
SELECT		[{field}] 						FROM {tbl} WHERE		 [{field}] = {value}			;~
~
Result #2:~
SELECT [UserName], [UserEmail] FROM UserData WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln';~
	When «tbl» is UserData, ~
	Body 1's «field» is a list ['UserName', 'UserEmail'], ~
	Body 3's «field» is a list ['FirstName', 'LastName'],~
	Body 3' «value» is an array indexed by «field» (eg, Array(field, ['Abe', 'Lincoln']) )
Definition: DetermTable(DB_Stmnt_Part_Long,DB_Platform)(~
'SELECT','SELECT','SELECT','SELECT',~
'[{field}]','`{field}`','""{field}""','[{field}]',~
'FROM {tbl} WHERE','FROM {tbl} WHERE','FROM {tbl} WHERE','FROM {tbl} WHERE',~
'[{field}] = {value}','`{field}` = {value}','""{field}"" = {value}','[{field}] = {value}',~
';',';',';',';')
NodeLocation: 360,264,1
NodeSize: 64,24
WindState: 2,25,130,1281,691
DefnState: 2,26,278,1135,303,0,DFNM
ValueState: 2,600,386,893,303,,MIDM
ReformDef: [DB_Stmnt_Part_Long,(Domain Of DB_Platform)]
ReformVal: [DB_Stmnt_Part_Short,DB_Platform]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}

Decision DB_Delete_Record_Syn
Title: Delete Record Syntax
Description: Syntax for deleting existing records from a database table with name «tbl». The «field» and «value» pairs in "Repeatable Body" are typically used as lookups to find specific records or rows within the table.~
~
Allowed parameter names: tbl, field, and value.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
Begin					Repeatable Body		End~
DELETE FROM {tbl}		""{field}""  = {value} 		;~
~
Result #1:~
DELETE FROM MyTable WHERE "Country" = 'USA'~
	When «tbl» is MyTable, «field» is Country, and «value» is USA.~
~
Example #2:~
Begin					Repeatable Body		End~
DELETE FROM {tbl}		[{field}]  = {value} 		;~
~
Result #2:~
DELETE FROM MyTable WHERE [FirstName] = 'Abe' AND [LastName] = 'Lincoln'~
	When «tbl» is UserData, ~
	«field» is a list ['FirstName', 'LastName'],~
	«value» is an array indexed by «field» (eg, Array(field, ['Abe', 'Lincoln']) )
Definition: DetermTable(DB_Stmnt_Part_Short,DB_Platform)(~
'DELETE FROM {tbl} WHERE','DELETE FROM {tbl} WHERE','DELETE FROM {tbl} WHERE','DELETE FROM {tbl} WHERE',~
'[{field}] = {value}','`{field}` = {value}','""{field}"" = {value}','[{field}] = {value}',~
';',';',';',';')
NodeLocation: 360,320,1
NodeSize: 64,24
WindState: 2,38,105,1462,606
DefnState: 2,14,192,866,303,0,DFNM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,\([197])]}

Decision DB_Delete_Table_Syn
Title: Delete Table Syntax
Description: Syntax for deleting an existing table «tbl» from a database. The «field» and «value» pairs in "Repeatable Body" are typically used as lookups to find specific records or rows within the table.~
~
Allowed parameter names: tbl.~
Use curly brackets around parameter names (eg, {tbl}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{tbl}'' will appear as 'MyTable' in the database query statement, when the value supplied for table is MyTable).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{tbl}"" will appear as "MyTable" in the database query statement, when the value supplied for field is MyTable).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example:~
DROP TABLE {tbl};~
~
Result:~
DROP TABLE MyTable~
  	When «tbl» is MyTable.
Definition: DetermTable(DB_Platform)('DROP TABLE {tbl};','DROP TABLE {tbl};','DROP TABLE {tbl};','')
NodeLocation: 360,376,1
NodeSize: 64,24
WindState: 2,33,55,1462,606
DefnState: 2,53,483,866,303,0,DFNM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,\([197])]}

Decision DB_Add_Column_Syn
Title: Add Column Syntax
Description: Syntax for adding a new column «field» to an existing database table «tbl». The data type of the new field is indicated by «type».~
~
Allowed parameter names: tbl, field, and type.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
ALTER TABLE {tbl} ADD COLUMN ""{field}"" {type};~
~
Result #1:~
ALTER TABLE MyTable ADD COLUMN "Country" INT;~
	When «tbl» is MyTable, «field» is Country, and «type» is INT.~
~
Example #2:~
ALTER TABLE {tbl} ADD {field} {type};~
~
Result #2:~
ALTER TABLE UserData ADD FirstName char;~
ALTER TABLE UserData ADD LastName char;~
	When «tbl» is UserData, ~
	«field» is a list ['FirstName', 'LastName'],~
	«type» is an array indexed by «field» (eg, Array(field, ['char', 'char']) )
Definition: DetermTable(DB_Platform)('ALTER TABLE {tbl} ADD {field} {type};','ALTER TABLE {tbl} ADD COLUMN {field} {type};','ALTER TABLE {tbl} ADD COLUMN ""{field}"" {type};','ALTER TABLE {tbl} ADD COLUMN {field} {type};')
NodeLocation: 360,432,1
NodeSize: 64,24
WindState: 2,330,230,1462,689
DefnState: 2,16,80,866,303,0,DFNM
ValueState: 2,388,394,597,303,,MIDM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,\([197])]}

Decision DB_Drop_Column_Syn
Title: Drop Column Syntax
Description: Syntax for deleting an existing column «field» from database table «tbl».~
~
Allowed parameter names: tbl and field.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
ALTER TABLE {tbl} DROP COLUMN ""{field}"";~
~
Result #1:~
ALTER TABLE MyTable DROP COLUMN "Country";~
	When «tbl» is MyTable, and «field» is Country.~
~
Example #2:~
ALTER TABLE {tbl} DROP COLUMN {field};~
~
Result #2:~
ALTER TABLE UserData DROP COLUMN FirstName;~
ALTER TABLE UserData DROP COLUMN LastName~
	When «tbl» is UserData and 	«field» is a list ['FirstName', 'LastName'].
Definition: DetermTable(DB_Platform)('ALTER TABLE {tbl} DROP COLUMN {field};','ALTER TABLE {tbl} DROP COLUMN {field};','ALTER TABLE {tbl} DROP COLUMN ""{field}"";','ALTER TABLE {tbl} DROP COLUMN {field};')
NodeLocation: 360,488,1
NodeSize: 64,24
WindState: 2,20,261,1462,606
DefnState: 2,24,27,866,303,0,DFNM
ValueState: 2,388,394,597,303,,MIDM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,[197]]}

Decision DB_List_Tables_Syn
Title: List Tables Syntax
Description: Syntax for listing all tables in a database. These SQL statements generally do not have any parameters to specify and rely only on the connection string or variable calling DbConnection to identify the database.
Definition: DetermTable(DB_Platform)('SELECT name AS tableName FROM sys.tables;','SHOW TABLES;','SELECT name AS "tableName" FROM pg_catalog.pg_tables WHERE schemaname != ''pg_catalog'' AND schemaname != ''information_schema'';','SELECT [Name] AS tableName FROM MSysObjects WHERE Type=1 AND Flags=0;')
NodeLocation: 360,544,1
NodeSize: 64,24
WindState: 2,43,12,1411,722
DefnState: 2,535,73,1232,445,0,DFNM
ValueState: 2,16,138,1493,540,,MIDM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [,DB_Stmnt_Part_Short,[,255]]}

Decision DB_List_Columns_Syn
Title: List Columns Syntax
Description: Syntax for listing all the column names for a given database table «tbl».~
~
Allowed parameter names: tbl.~
Use curly brackets around parameter names (eg, {field}).~
Use two repeated single quotes to represent a solitary single quote (eg, ''{field}'' will appear as 'Country' in the database query statement, when the value supplied for field is Country).~
Use two repeated double quotes to represent a solitary double quote (eg, ""{field}"" will appear as "Country" in the database query statement, when the value supplied for field is Country).~
If this operation cannot be performed via a SQL statement for a given database platform, then populate the syntax with two single quotes (eg, '') to create an empty text string.~
~
Example #1: ~
SHOW COLUMNS FROM {tbl};~
~
Result #1:~
SHOW COLUMNS FROM MyTable;~
	When «tbl» is MyTable.
Definition: DetermTable(DB_Platform)('SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = ''''{tbl}'''';','SHOW COLUMNS FROM {tbl};','SELECT column_name FROM information_schema.columns WHERE table_name = ''''{tbl}'''';','SELECT [Name] AS columnName FROM MSysObjects INNER JOIN MSysACEs ON MSysObjects.Id = MSysACEs.ObjectID WHERE MSysOBjects.Name=''''{tbl}'''' AND MSysObjects.Type=1;')
NodeLocation: 360,600,1
NodeSize: 64,24
WindState: 2,121,88,1411,722
DefnState: 2,57,250,1756,445,0,DFNM
ValueState: 2,20,51,1281,303,,MIDM
ReformDef: [DB_Stmnt_Part_Short,(Domain Of DB_Platform)]
{!40700|Att_CellFormat: CellEntry(0,0,1,0,0)}
{!50000|Att_ColumnWidths: [1306,DB_Stmnt_Part_Short,\([,255])]}

FormNode New39143763
Title: DB Platform
Definition: 0
NodeLocation: 116,144,1
NodeSize: 92,16
NodeInfo: 1,,,0,,,,130
Original: DB_Platform

Close DB_Syntax_Definition

FormNode Fo1368178387
Title: DB Platform
Definition: 0
NodeLocation: 144,48,1
NodeSize: 120,16
NodeInfo: 1,,,,,,,130,,,,,,0
Original: DB_Platform

Text DB_Te1938603731
Title: Select the DB platform
NodeLocation: 212,40,-1
NodeSize: 204,32
NodeInfo: 1,,,,1,1

Text DB_Te1565310675
Title: Functions to view or modify DB records
NodeLocation: 212,208,-1
NodeSize: 204,128
NodeInfo: 1,,,,1,1

Module DB_Helper_Functions
Title: DB Helper Functions
Author: James Milford~
Lumina Decision Systems
Date: Sat, Feb 3, 2024 2:32 PM
NodeLocation: 568,272,1
NodeSize: 64,24
DiagState: 2,977,97,234,295,17,10
NodeColor: 0,31611,35466
NodeFontColor: 65535,65535,65535

Function DB_Format_Parameters(field; param: ...Optional)
Title: DB Format Parameters
Description: Reformats key parameters to ensure that they array abstract appopriately when used in database functions. The «field» parameter should be a scalar or list of database field names. All other parameters are those that will be reindexed by «field» to align field-and-value pairs~
~
«field»: names of database fields. This can be a scalar or a list.~
«param»: any number of parameter variables that make up field-and-value pairs. Each parameter can be a scalar, a list of same length/order as «field», an array indexed by «field», or any other array.
Definition: LocalIndex fieldList:=[];~
MetaVar fieldHandle;~
LocalIndex fieldDim:=IndexesOf(field);~
IF Size(fieldDim) = 0   {If scalar}~
THEN (~
   fieldList:=[field];~
   fieldHandle:='NA';~
)~
ELSE (   {If list}~
   fieldList:=CopyIndex(field);~
   fieldHandle:=Slice(fieldDim, 1)~
);~
~
LocalIndex returnVal:=[\fieldList];~
FOR p:=Repeated(param) DO (~
   LocalIndex paramDim:=IndexesOf(p);~
   Local selfIndexFlag:=Sum(TextLength(paramDim)=0, paramDim);   {Indicates when param uses an undefined index, eg, [1, 2] input as the param}~
   Local fieldIndexFlag:=PositionInIndex(paramDim, fieldHandle, paramDim);   {Indicates when param is already indexed by field}~
   Local formatParam:=IF Size(paramDim) = 0   {If scalar}~
      THEN AddIndex(p, fieldList)~
      ELSE IF selfIndexFlag   {If undefined index}~
      THEN Slice(CopyIndex(p), @fieldList)~
      ELSE IF fieldIndexFlag   {If indexed by field}~
      THEN (~
         LocalAlias fieldIndex:=fieldHandle;~
         p[@fieldIndex=@fieldList]~
      )~
      ELSE AddIndex(p, fieldList);   {If multi-D, but not indexed by fields} ~
   returnVal:=Concat(returnVal, \formatParam)~
);~
returnVal~
      ~
      ~
       ~
      ~
~
   ~
   ~

NodeLocation: 112,104,1
NodeSize: 64,24
WindState: 2,416,17,1375,840

Function DB_Add_Literal_Quote(param)
Title: Add Literal Quotes
Description: Adds opening/closing quotes to «param», if the parameter is text.
Definition: IF IsText(param) OR IsDateTime(param)~
THEN f"""'{param}'"""~
ELSE param
NodeLocation: 112,160,1
NodeSize: 64,24
WindState: 2,659,347,720,350

Text DB_Te1325861459
Title: Helper Functions
NodeLocation: 112,140,-1
NodeSize: 88,124
NodeInfo: 1,,,,1,1

Function DB_Format_Date_Text(date)
Title: Format Date as Text
Description: Takes a date in the default Analytica format (d-mmm-yyyy) and converts it to a more DB-friendly text string in the format yyyy-MM-dd.
Definition: Local year := DatePart(date, 'Y');~
Local month := DatePart(date, 'MM');~
Local day :=DatePart(date, 'dd');~
f"{year}-{month}-{day}"
NodeLocation: 112,216,1
NodeSize: 64,24
WindState: 2,73,530,720,350

Close DB_Helper_Functions

Text DB_Te787267283
Title: Syntax definitions for DB queries
NodeLocation: 572,172,-1
NodeSize: 148,44
NodeInfo: 1,,,,1,1

Text DB_Te203210451
Title: Supporting logic
NodeLocation: 572,268,-1
NodeSize: 148,44
NodeInfo: 1,,,,1,1

Close Database_library

Close Database_library_demo