Sat Sep 18, 2021 3:40 am
Login Register Lost Password? Contact Us


EMBED MySQL Insert using dataset

Questions around writing code and queries

Fri Jun 24, 2016 12:09 pm Change Time Zone

I have been tasked with writing a dataset to a MySQL database.

The dataset will be quite large millions of records.

I would like to pass the dataset as a parameter to my EMBEDed code, see code below.

I am getting a syntax error.

Can someone help with this syntax?

Thanks!
Chuck


Code: Select all
IMPORT mysql, AI_TransactionLogging;

Layout_DD_TransactionLog := RECORD
     STRING          transaction_id;
      INTEGER       product_id := 22;
      STRING          date_added;
      STRING          service_type;
      STRING          special_billing_id;
      STRING          report_code;
      STRING          report_usage;
      STRING          requestor;
      STRING          reference_number;
      STRING          account_base;
      STRING          account_suffix;
      INTEGER       account_id;
      INTEGER       customer_id;
      STRING          anchor_transaction_id;
      INTEGER       anchor_product_id;
      STRING          full_quote_back;
      STRING          i_date_ordered;
      STRING          i_addr_house_num;
      STRING          i_addr_apt_num;
      STRING          i_addr_line;
      STRING          i_addr_state;
      STRING          i_addr_zip;
      STRING          i_addr_city;
      STRING          i_addr_county;
      STRING          i_addr_country;
      INTEGER       i_addr_type;
      STRING          addr_household_sur_name;
      INTEGER       listed_driver_count;
      INTEGER       confirmed_count;
      INTEGER       not_confirmed_count;
      INTEGER       not_found_count;
      INTEGER       discovered_surname_count;
      INTEGER       discovered_surname_exception_count;
      STRING9       result_format;
      DECIMAL4_1    record_version;
      STRING          processing_status;
      STRING          inquiry_status;
      STRING          inquiry_processing_status;
      INTEGER       billing_type_id;
      DECIMAL18_9 price;
      INTEGER       currency;
      INTEGER       pricing_error_code;
      INTEGER       free;
      STRING          report_options;
      INTEGER       transaction_code;
      STRING          return_node_id;
      STRING          request_node_id;
      INTEGER       order_status_code;
      STRING          product_line;
      INTEGER       login_history_id;
      STRING          ip_address;
      DECIMAL8_4    response_time;
      STRING          esp_method;
      INTEGER       batch_job_id;
      INTEGER       batch_seq_number;
      STRING          is_active;
      STRING          user_added;
      STRING          user_changed;
      STRING          date_changed;
   END;
   
   TestTransaction_DS := DATASET([{'116122R65_CAB', 22, '6/15/2016 12:08:29 PM', 'Z', '', '4293', 'PA', 'HORACE MANN',
               '16167121100007', '990470', 'DDR', 8011322, 99900, '116122R65', 31,
               '17742STSM99                   1MQ3           JXVVBA', '6/10/2016 12:00:00 AM', '318',
               '', 'W 30TH ST', 'MD', '21211', 'BALTIMORE', '', 'USA', 1, 'FUCILE', 1, 0, 1, 0, 0, 0,
               'EDITS', 2.0, 'Z', 'R', 'N', 0, 0.000000000, 0, 0, 0, 0, 0, '', 'B22222000', 100, '', 0,
            '1   0.173.217.9', 0.3570, 'DriverDiscovery', 0, 0, 1, 'vin_tss', '', ''},
            {'116122R66_CAB', 22, '6/15/2016 12:08:51 PM', 'Z', '', '4293', 'PA', 'HORACE MANN',
            '16167121100008', '990300', 'MXC', 6024501, 10057, '116122R66', 31,
            '17742STSM99                   1MQ3           JXVVBA', '6/10/2016 12:00:00 AM', '318',
            '', 'W 30TH ST', 'MD', '21211', 'BALTIMORE', '', 'USA', 1, 'FUCILE', 1, 0, 1, 0, 0, 2,
            'EDITS', 2.0, 'Z', 'C', 'Y', 0, 0.000000000, 0, 0, 0, 0, 0, '', 'B22222000', 100, '', 0,
            '10.173.217.9', 0.3900, 'DriverDiscovery', 0, 0, 1, 'vin_tss', '', ''},
            {'116122R67_CAB', 22, '6/15/2016 12:20:14 PM', 'Z', '', '0069', 'PA', 'USAA-YDD', '16167121100009',
            '502473', '', 5721721, 10087, '116122R67', 24, '1300 D47YFA                   PRH            A5FTEJ',
            '6/15/2016 12:00:00 AM', '1003', '', 'BROOKE RD', 'MD', '20743', 'CAPITOL HEIGHTS', '', 'USA', 1,
            'STANLEY', 1, 0, 0, 1, 0, 0, 'EDITS', 2.0, 'Z', 'R', 'N', 0, 0.000000000, 0, 0, 0, 0, 0, '', 'B22222000',
            100, '', 0, '10.173.217.9', 0.6460, 'DriverDiscovery', 0, 0, 1, 'vin_tss', '', ''}], Layout_DD_TransactionLog);
   
   DATASET(Layout_DD_TransactionLog) addTransactions(DATASET(Layout_DD_TransactionLog) Transaction_DS) := EMBED(
                                                               mysql : user('username'),
                                                                           server('server'),
                                                                           port('port'),
                                                                           database('db'),
                                                                           password('password'))   
      INSERT INTO log_dd.transaction_log
            (transaction_id, product_id, date_added, service_type, special_billing_id, report_code,
            report_usage, requestor, reference_number, account_base, account_suffix, account_id, customer_id,
            anchor_transaction_id, anchor_product_id, full_quote_back, i_date_ordered, i_addr_house_num,
            i_addr_apt_num, i_addr_line, i_addr_state, i_addr_zip, i_addr_city, i_addr_county, i_addr_country,
            i_addr_type, addr_household_sur_name, listed_driver_count, confirmed_count, not_confirmed_count,
            not_found_count, discovered_surname_count, discovered_surname_exception_count, result_format,
            record_version, processing_status, inquiry_status, inquiry_processing_status, billing_type_id,
            price, currency, pricing_error_code, free, transaction_code, return_node_id,
            request_node_id, order_status_code, product_line, login_history_id, ip_address, response_time,
            esp_method, batch_job_id, batch_seq_number, is_active)
            VALUES
            ?;
ENDEMBED;

New_Transaction_DS := addTransactions(TestTransaction_DS);

OUTPUT(New_Transaction_DS, NAMED('New_Transaction_DS'));
chuck.beam
 
Posts: 20
Joined: Mon Dec 21, 2015 2:59 pm

Fri Jun 24, 2016 2:47 pm Change Time Zone

Chuck,

Why not just write the data to a CSV file, despray it, then do a bulk import to the SQL database?

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1593
Joined: Wed Oct 26, 2011 7:40 pm

Fri Jun 24, 2016 2:50 pm Change Time Zone

Hi Richard,

The despray and bulk load is an option, however they wanted me to explorer updating the MySQL database directly from the ECL code.

I have been able to insert records, but not using my dataset.

Do you think this is possible?

Thanks
Chuck
chuck.beam
 
Posts: 20
Joined: Mon Dec 21, 2015 2:59 pm

Fri Jun 24, 2016 3:56 pm Change Time Zone

Chuck,

Given that you said, "millions of records" I wouldn't even consider doing it directly from ECL. If it were onesy-twosey ... :)

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1593
Joined: Wed Oct 26, 2011 7:40 pm

Fri Jun 24, 2016 3:58 pm Change Time Zone

OK, I will go back to them and discuss your suggestion for despray and bulk insert.

Thanks
Chuck
chuck.beam
 
Posts: 20
Joined: Mon Dec 21, 2015 2:59 pm

Mon Jun 27, 2016 12:33 pm Change Time Zone

Hi Richard,

I am trying to do as you suggested, but I have a couple of questions.

First, how do I wrap the STRING fields in single quotes in the OUTPUT and DESPRAYed file?

Second, how do I call the LOAD DATA FILE function from ECL?

My code below fails with the message:
Error: System error: 0: Graph[9], SLAVE #1 [10.194.10.41:6600]: Graph[9], workunitwrite[12]: mysql: This command is not supported in the prepared statement protocol yet, (0, 0), 0,



Code: Select all
DATASET(Layout_DD_TransactionLogOut) LoadData() := EMBED(mysql : user('username'),
server('server'),
port('port'),
database('db'),
password('password'))   
      LOAD DATA INFILE '/data/ActiveInsights_DEVCAB/DD_LOG/TransactionOut.csv'
      INTO TABLE log_dd.transaction_log
      FIELDS TERMINATED BY ','
      LINES TERMINATED BY '\n';
ENDEMBED;

OUTPUT(LoadData(), NAMED('OUT4'));
chuck.beam
 
Posts: 20
Joined: Mon Dec 21, 2015 2:59 pm

Thu Sep 26, 2019 6:11 am Change Time Zone

Hi Team,
How can read dataset in hpcc sql embedded , so rather to create file i can prepare Dynamic insert in SQL embed and upload data

Can you please suggest how to read a dataset in sql and insert in table.
harshdesai
 
Posts: 26
Joined: Fri Feb 23, 2018 8:30 am

Thu Sep 26, 2019 7:22 pm Change Time Zone

Hi harshdesai,

Are you asking about reading data from a MySQL database, or writing data to it?
DSC
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 568
Joined: Tue Oct 18, 2011 4:45 pm

Fri Sep 27, 2019 4:00 am Change Time Zone

Using Embedded Sql ,I intend to pass a dataset and insert same values in SQL tables for Logging perspective.

Regards
Harsh Desai
harshdesai
 
Posts: 26
Joined: Fri Feb 23, 2018 8:30 am

Fri Sep 27, 2019 11:47 am Change Time Zone

Harsh Desai,

Have you considered just using the logging functions in our Standard Library?

HTH,

Richard
rtaylor
Community Advisory Board Member
Community Advisory Board Member
 
Posts: 1593
Joined: Wed Oct 26, 2011 7:40 pm

Next

Return to Programming

Who is online

Users browsing this forum: No registered users and 1 guest

cron