Bulk insert with numeric data | Weirder problem with the order of data

[vasumur]

@vstavskyi appreciate your help with this Oracle library and it has been very helpful.

I came across this issue while dealing with numeric data in the bulk insert.

The below snippet fails while inserting 2 records
bulk_insert_error.txt

Error : ** (DBConnection.ConnectionError) 'ORA-01722: invalid number\n'

The only change in the below snippet is that I moved the 1st record in the list to the bottom and that makes the bulk inserts to go through fine.

bulk_insert_success.txt

As a workaround, I did apply Jamdb.Oracle.to_list() on the number value converted as a binary so I let Oracle convert into numbers when the inserts go through.

Appreciate if you could help me understand why I am getting the Invalid number error in the first place and why shuffling order of the data addresses it.

Thank you.

[vstavskyi]

DBConnection.Query.encode function must resolve it.

[vasumur]

Not sure I am following you.

In my case I am preparing the statement using the below statement.

   insert_stmt =
      DBConnection.prepare!(conn, %Jamdb.Oracle.Query{
        statement:
          "INSERT INTO #{table_name} (#{column_names_str}) VALUES (#{bind_variables})",
        batch: true
      })

And then passing data to the prepared statement as below.

DBConnection.execute!(conn, insert_stmt, tx_data)

The above tx_data is already using the earlier encoding logic using Jamdb.Oracle.to_list() etc.

Are you saying I should use this encoding on the data as well asd encoding the Jamdb.Oracle.Query?

[vstavskyi]

Forget about Jamdb.Oracle.to_list
Your old code

uuid =
  Enum.map(1..999, fn _x ->
    [String.replace(Ecto.UUID.generate(), "-", ""), "hello", nil]
  end) 

New code

uuid =
  Enum.map(1..999, fn _x ->
   DBConnection.Query.encode(
        %Jamdb.Oracle.Query{},  
        [String.replace(Ecto.UUID.generate(), "-", ""), "hello", nil], 
        [])
  end)

[vasumur]

Just making sure I understand this. In this example here

How do I do this encoding work for the below prepared statement "query" that takes in the bulk "data" which needs encoding?

    DBConnection.execute!(conn, query, data)


DBConnection.transaction(pool, fn conn ->
  query = %Jamdb.Oracle.Query{
    batch: true,
    name: nil,
    statement:
      "INSERT INTO EVEST_PRO (PRODUCT_GUID,PRODUCT_NAME,PRODUCT_STATUS,PRODUCT_PROFILE,TOTAL_AUM,PREVIOUS_TOTAL_AUM,INSTITUTIONAL_AUM,PREVIOUS_INSTITUTIONAL_AUM,BASE_CURRENCY,PRIMARY_EQUITY_CAPITALIZATION,PRIMARY_EQUITY_STYLE_EMPHASIS,MANAGER_PREFERRED_BENCHMARK,ASSET_CLASS_DEFAULT_BENCHMARK,RISK_FREE_INDEX,FIXED_INCOME_STYLE_EMPHASIS,FIXED_INCOME_DURATION_EMPHASIS,ASSET_CLASS,GEOGRAPHIC_FOCUS,PORTFOLIO_MANAGEMENT_STRATEGY, TOTAL_HOLDINGS, INCEPTION_DATE,INVESTMENT_FOCUS,PRODUCT_ID, DATASET_NAME,FIRM_GUID,UNIVERSE_GUID,INSERTED_AT,UPDATED_AT)
                      VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28)"
  }

  try do
    DBConnection.execute!(conn, query, data)
  after
    DBConnection.close(conn, query)
  end
end)

[vstavskyi]

If PRODUCT_GUID, FIRM_GUID,UNIVERSE_GUID columns are raws, then replace "-" with ""

If INSERTED_AT and UPDATED_AT columns are timestamps, then change .... :27, :28 ... to
... to_timestamp(:27,'dd-mon-yy hh:mi:ss AM') , to_timestamp(:28,'dd-mon-yy hh:mi:ss AM') ...

[
'fffe3fcb248c418d9b1047085822270e',
'Fidelity China Region Fund',
'Active',
'https://app.evestment.com//next/ProfilesDetails/ProfilesDialogShell.aspx?p=1529299&f=1002193&t=1&isPM=false&isUE=false&source=CoreAPI&locale=en',
1294.832198,
1608.231699,
1294.832198,
1608.231699,
'US Dollar (USD)',
'All Cap',
'Core',
'MSCI Golden Dragon-GD',
'MSCI Golden Dragon-ND',
'FTSE 3-Month T-Bill',
:null,
:null,
'Equity',
'Greater China',
'Active',
83,
'01-Nov-95',
'Long Only',
1_529_299,
'Asia Pacific',
'6f9f64e45a55482e84b269700f16d3a9',
'd75d8fc6b4af-4d3f97b211234d069d3f',
'03-Jun-22 1:38:46 PM',
'03-Jun-22 1:38:46 PM'
]

VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,
to_timestamp(:27,'dd-mon-yy hh:mi:ss AM'),
to_timestamp(:28,'dd-mon-yy hh:mi:ss AM'))"

[vasumur]

Thanks @vstavskyi . In this case I should still be converting the strings to binary using Jamdb.Oracle.to_list.

This is what I am doing now, got confused as to how to apply the DBConnection.Query.encode in terms of the bulk insert.

[vstavskyi]

For raw column input value must be hex string or binary.

params = [
String.replace(Ecto.UUID.generate(), "-", ""), 
String.replace(Ecto.UUID.generate(), "-", ""), 
NaiveDateTime.utc_now(), 
nil
]
opts = []
params = DBConnection.Query.encode(%Jamdb.Oracle.Query{}, params, opts)    

[
'4e0a1e6f4d544cd4947b10776eb538e0',
'f8948da433c94a2d8210e0299e10cf07',
{{2022, 6, 15}, {5, 57, 44, 427000}},
:null
]

Binary types must be set in opts[:in]

params = [
Ecto.UUID.bingenerate, 
String.replace(Ecto.UUID.generate(), "-", ""), 
NaiveDateTime.utc_now(), 
nil
]
opts = [in: [Ecto.UUID, "UNIVERSE_GUID", "INSERTED_AT", "UPDATED_AT"]]
params = DBConnection.Query.encode(%Jamdb.Oracle.Query{}, params, opts) |> IO.inspect

[
<<233, 128, 116, 167, 36, 18, 68, 89, 183, 241, 196, 214, 161, 207, 200, 135>>,
'69244e5703bd4426a585be13eef6dc16',
{{2022, 6, 15}, {5, 55, 21, 950000}},
:null
]

[vasumur]

Thanks @vstavskyi . All set with this.