Fetching Data from External Account(MySql)

[sibyj2188]

Hi All,

Using an external account I have connected ACC with MySql workbench. whenever I have to fetch a small amount of data it works fine but it does not work if data is huge takes a longer amount of time which is not a feasible solution I believe.

so to overcome above problem what I did is I created the schema using the external account which is an exact real-time copy of my SQL table. Now, whenever I try to fetch data from created schema ACM shows the following two errors:

1. WDB-200001 SQL statement 'DROP TABLE IF EXISTS wkf22092800_128_1' could not be executed.

2. MYS-230000 MySQL error 1290: The MySQL server is running with the --read-only option so it cannot execute this statement.

Note: I have connected SQL using read-only access credentials. but since my requirement only to fetch data I don't feel I need a write access credentials.

I found one alternative for the above problem but it is incomplete.

So what I did is, I used a script to fetch data from created external account schema.

and the script is:

var query = xtk.queryDef.create(  <queryDef schema="Table_Name" operation="select">
    <select>
    <node expr="@Column1"/>
    </select>
    <where>
      <condition boolOperator="AND" expr="ToDate(@Column2) = '2019-05-06'"/>
    </where>
  </queryDef>
  )
var set = query.ExecuteQuery()
for each(var row in set)
{
  logInfo("USER ID : " + row.@Column1);
  vars.Customer_Id = +row.@Column1;
}

Using the above script I was able to display data in log info but again that is not a solution.
So, I want to store all rows into a single column using an enrichment activity.

Any help would be appreciated.

[Jonathon_wodnicki]

Hi,

What is the question?

Thanks,

-Jon

[sibyj2188]

Hi,

Firstly thank you much for your time.

So, I want to fetch data from MySql.

for which I have created the external account and I have been able to pull out data using RDBMS activity but it takes a huge amount of time whenever I have to pull a larger amount of data say above 50K.

So what I did is I created a schema using the external account (which I have configured for MySql) but whenever I try to fetch data from that schema using a query activity it shows following error:

1. WDB-200001 SQL statement 'DROP TABLE IF EXISTS wkf22092800_128_1' could not be executed.

2. MYS-230000 MySQL error 1290: The MySQL server is running with the --read-only option so it cannot execute this statement.

though I have read-only access for MySql and I presume that is the sufficient right in order to pull data since I am not manipulating any tables.
I hope you got my question now.

please let me know in case further information required.

[Jonathon_wodnicki]

Hi,

Create a tablespace with write permission for the temp tables, then configure the external account to use it.

Thanks,

-Jon

[sibyj2188]

Hi,

Thank you so much for your replies.

[pvdamme]

Hi,

When creating a data schema for an FDA table, Adobe Campaign needs to be able to create temporary tables on that external database. Therefore you need to have the write permissions on that database.

If you want to read data from an FDA table without the write access requirement, your only option is to use the data load (RDBMS) activity in a workflow. You can't use data schema's

Kind regards,