How-To working with external data sources MSSQL

[HONIG2012]

hi guys,

i currently trying to get a costumer ms sql db into WFA - i tried to find something like a guide or how steps but couldn't find anything appropriate.

so what are the steps to get this stuff work

what i've already done is:

- created a datasrouce type SQL (under Designer) with newly create scheme

- created datasource (under Execution) > Execution completes successfull

and now? what's next?

i tried to create a dictionary entry but i cannot see whether its filled up or not - so how i can proceed??

is there any documentation on such tasks (i think they are very common).

thanks AJ

[sinhaa]

AJ,

     Acquiring a Datasource in WFA needs some good amount of planning to be done as what all do you want to acquire from the external DB ( MS sql in your case ). This decision will be based on what all data you want to use for resource selection for your workflow executions. So  as you can see this needs some planning to get it to full strength. 

So setps to start with:

1. Create your dictionary  on a new scheme. This will create a New Database. Dictionary are like DB tables, rows in dictionaries will be the columns in that table. So decide what all columns you need to acquire from MS sql.

2. Create a Datasource Type, Method SQL, Driver: MS SQL Jdbc driver 3.0. You might also have to mention your SID/Database name as present in MS SQl server. 

3. Create Cache queries on your Data Source Type. They are SQL queries with select methods which will be executed against the MS SQL server and the result data will be obtained and filled in WFA DB into the dictionary your created. You need to create Cache queries for all your dictionay items which you want to acquire. by default all will be enables to be acquired.

4.  Create a New Data Source with the given DataSource Type as in step 2. Provide connection details like Host IP, port, credentials of your MS SQL. Run acquistion.

5. You are done.

See some certified Datasource like OCUM6.0 ( Mysql server) etc. to see how it works.

@i tried to create a dictionary entry but i cannot see whether its filled up or not - so how i can proceed??

-------

WFA DB is mysql server. So you can connect to it using any mysql client like SQLYog, DB vizualizer, SQL workbench etc using credentials wfa/Wfa123. See WFA developer's guide/Instllation Guide for information on this. You can see the Data acquired in your DB by the acquistion.

 sinhaa

[HONIG2012]

sinhaa,

thanks a lot for your reply - it really was very helpful - it's working now and it seems i haven't created a cache query which caused the scheme to be empty - i assumed i can make direct sql queries when not enabling caching - but as i understood correctly now i always have to cache external information first within wfa db.

some experience i want to share with you:

1. i cannot create point 3 before point 4 as you suggested - by trying to save the cache query it tells me "datesource doesn't exist" - so i did "4" first and then created "3"

2. creating a scheme which has to many digits leads to an error during acquisition "specified key was too long; max key length is 3072 bytes" (even if i don't create a cache query first) - so although it let's me create the scheme with 10 letters (10lettters in my case- all smal letters, no numbers, no special characters) it seems there is a limitation when it tries to aquire data into that scheme - shortening the scheme name fixes this problem

3. as soon as the scheme is created also a column "id" is created with AI and PK options in mysql db in background - the problem comes up within the cache select query - i also had to select this id-column although it only should be used by wfa and not to be an imported column - if i only select my columns i get "number of cacheable attributes doesn't match with the number of columns selected in the cache query"

so i think 2. and 3. are bugs

today i tried the same with 3.0RC1 (before it had 2.2.1RC1) - same results - also i want to mention that upgrading was not possible what was quite frustrating - i backuped and restored the db, but if there where new workflows, commands etc. in 3.0 they're lost from my install now....

hope this helps to make WFA a better product - thanks for help

AJ

[sinhaa]

AJ,

       Glad it worked for you. Some points to clarify:

@1. i cannot create point 3 before point 4 as you suggested - by trying to save the cache query it tells me "datesource doesn't exist" - so i did "4" first and then created "3"

---

You can Save but you can't Test. Attempting to Test gives this error. Testing needs connection info which is only available when a DataSource is created.

@2. creating a scheme which has to many digits leads to an error during acquisition "specified key was too long; max key length is 3072 bytes" (even if i don't create a cache query first) - so although it let's me create the scheme with 10 letters (10lettters in my case- all smal letters, no numbers, no special characters) it seems there is a limitation when it tries to aquire data into that scheme - shortening the scheme name fixes this problem

-----

I don't think WFA limits this, but its a MYsql limit for a DB ( scheme ) name. What scheme name were you trying to give.

@3. as soon as the scheme is created also a column "id" is created with AI and PK options in mysql db in background - the problem comes up within the cache select query - i also had to select this id-column although it only should be used by wfa and not to be an imported column - if i only select my columns i get "number of cacheable attributes doesn't match with the number of columns selected in the cache query"

----------

This is expected behaviour. A DB entry will have an ID as a column and WFA acquistion is a DB entry creation.

[HONIG2012]

well also the save button tries to test the select state - i don't know why but i also noticed this behavior in select queries for user inputs - the first dialog box may be quit with ok but the workflow at all may not be saved if the select query is invalid.

mysql has created the scheme  - i saw it through workbench - i tried to reproduce but where not able to do so - seems like this error is gone now

i understand that there's an automated id for wfa but why do i have to import this id by the cache query? - e.g. my mssql db haven't had such an entry - so i would like to import the data and the ID column should be autoincremented by the mysql db since i will not use these interal wfa id's - you know what i mean?

thanks for assistance again.

AJ

[sinhaa]

Hello AJ,

@well also the save button tries to test the select state - i don't know why but i also noticed this behavior in select queries for user inputs - the first dialog box may be quit with ok but the workflow at all may not be saved if the select query is invalid.

-----

Queries written at Cache queries and those at User-Inputs are not the same behaviour wise. The former is meant to run on the external DB and hence need connnection info to Test and hance do NOT run Testing of query before save. You can type any blah at a cahe query and it will get saved. Only Test will fail if a DataSource is created which provides for running the query against the external DB and validating.

But User-Input queries are always going to be executed on the local WFA mysql db and hence it makes sense to validate the query before saving.

@i understand that there's an automated id for wfa but why do i have to import this id by the cache query? - e.g. my mssql db haven't had such an entry - so i would like to import the data and the ID column should be autoincremented by the mysql db since i will not use these interal wfa id's - you know what i mean?

-----

There are reasons for it. I will want to keep the ID in the external DB same in the WFA local DB due to referance to other tables. Auto-Increment in this case will cause a complete DB catastrophy if the IDs get changed.