Store QVD loop failing in multiple ODBC connections

[Anonymous]

Hi

I have a scenario , where my qvw has 3 ODBC connections in it, here I implemented for loop for  storing to qvd's and dropping tables.

But the problem is  the for loop is not storing any qvds as it is entering to the next ODBC connection,

pls suggest..

SUB STORETABLES

FOR i =0 to NoOfTables()-1

LET vTabName =TableName(0);

STORE $(vTabName) into $(vTabName).qvd (qvd);

DROP Table $(vTabName);

NEXT i

END SUB

ODBC1 Conn;

load * from ODBC1;

ODBC2 Conn;

Load * from ODBC2;

John

[jagan · Accepted solution]

Hi,

Try like this, it is working, no need of macro you can directly use For loop

Table1:

LOAD

*

INLINE [

A,B

1,2

];

Table2:

LOAD

*

INLINE [

A,B,C

1,2, 3

];

FOR i =0 to NoOfTables()-1

  LET vTabName =TableName(0);

  STORE $(vTabName) into $(vTabName).qvd (qvd);

  DROP Table $(vTabName);

NEXT i

Regards,

Jagan.

[jagan]

Hi,

Please find attached file for solution.

Regards,

Jagan.

[Anonymous]

Here none of these tables getting exported to QVD.

When I put comment on CALL, everything is normal where I can see both tables getting exported  from multiple databases.

John

[Anonymous]

Thanks Jagan for the reply, I have no problem when I have single database connection, the problem  is occuring when connecting to multiple databse connections in single qvw file.

As Peter & Marcus said the database connection is getting lost from the 1st database, when moving to the 2nd ODBC connection.

what i want is  qvd's(of 1st ODBC connection ) should get  created before moving to second ODBC connection, this would solve the problem,

John

[cesaraccardi]

Hi John,

What if instead of using a SUB routine you move the above lines to the the last tab of the script:

FOR i =0 to NoOfTables()-1

LET vTabName =TableName(0);

STORE $(vTabName) into $(vTabName).qvd (qvd);

DROP Table $(vTabName);

NEXT i

Does it work?

Cesar

[marcus_sommer]

I think you should try what Peter suggested to see if the sql will be executed and the two tables exists. Maybe you need a 32-bit connection or if the data-structure from both tables are the same they will be automatically concatenated ... Further, do you use any ERRORMODE within this script?

Another possibility is of course to use a loop which contained the sql + storing + dropping. Especially if you have larger loads or you want use statements like exists() you might need to drop tables as early as possible.

- Marcus

[Gabriel]

Hi,

You have to call the SUB() after each database connection. Because what you are doing is connecting to different database and loading, so call the subroutine after each connection and loading

[Anonymous]

Thanks Jagan,Cesar, Marcus & peter  its working as per your suggestions,

I kept the for loop without sub routine  in the last tab and its working.

Here I didn't even wrote any disconnect statement for each ODBC connection, still worked fine with no issues.

Thanks once again.

John