How to acces a database table with BCI?

olgc · Post by **olgc** » Wed Jan 18, 2006 11:03 am

Hi every body,

A ODBC data source is set up and an entry is added into uvodbc.config. How to write the code to access the database with BASIC SQL Client Interface? Can some one there provide a sample code? The code does:

1. Set up a connect to a database,
2. query a table (select ... from ...)
3. return the result

Do we use the following statement to connect to a database? what's connect.env?

status = SQLConnect (connect.env, data.source, logon1, logon2)

Thanks,

kcbland · Post by **kcbland** » Wed Jan 18, 2006 11:12 am

You do realize that the ODBC drivers that ship with DS are only licensed thru the ETL tools, and any direct usage via programming, which includes your DS BASIC programs, is not covered under the licensing agreement? This means you will get 30-day usage warning messages on every query executed, and then it stops working until you purchase those drivers. Of course, if you're using your own separate ODBC drivers, ignore my commentary.

But, what are trying to do and why are you doing it this way? If you're accessing Oracle, SQL-Server, DB2/UDB, Sybase, MySQL, etc, there are command line programs from these databases that can execute SQL scripts and return data to the screen for parsing. Why wouldn't you prefer that method?

olgc · Post by **olgc** » Wed Jan 18, 2006 1:25 pm

We own other your own ODBC drivers.

The reason for BCI ODBC is to build the auto-balancing table. You get the number of records from Datastage and Database, then check these two to see if they are the same.

Once we ran across case these two were different in production.

If you use BCI ODBC, one simple job can do the whole balancing things. Can you suggest better approach?

Thanks,

kcbland · Post by **kcbland** » Wed Jan 18, 2006 1:34 pm

How about use DS API's to extract link metrics for row counts and write them to a text file. Then use a standard job to load that data into a database table. Simple SQL in your target database can compare row counts.

olgc · Post by **olgc** » Wed Jan 18, 2006 5:01 pm

The data you suggested is one of the two in my solution. Another one is read from database. Then compare them to see if they are equal. I had a case, they were different. That cased big problem.

Thanks,

olgc · Post by **olgc** » Thu Jan 19, 2006 9:36 am

Any one does BCI ODBC programming there?

Thanks,

ray.wurlod · Post by **ray.wurlod** » Thu Jan 19, 2006 4:21 pm

Yes, I do, though primarily wearing my UniVerse hat rather than my DataStage hat.

You can find a well-documented example, a function to execute any DML statement, here

olgc · Post by **olgc** » Fri Jan 20, 2006 4:29 pm

Ray,

Oh, Very good documented routine, indeed, thanks very much.

But when I invoke the routine, the error is:

t..Trans (bciExecuteDML): SQL Statement = select count(*) from EDWAPP.T_PARTY_ADDRESS;

t..Trans (bciExecuteDML): SQL Error: SQL State = 37000, DBMS code = -104
[DataStage][SQL Client][ODBC][DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for Windows, UNIX, and Linux]ILLEGAL SYMBOL ;; VALID SYMBOLS ARE WAPP.T_PARTY_ADDRESS

You see the SQL statement is "select count(*) from EDWAPP.T_PARTY_ADDRESS;"

Why "ILLEGAL SYMBOL ;; VALID SYMBOLS ARE WAPP.T_PARTY_ADDRESS"

Thanks again,

chulett · Post by **chulett** » Fri Jan 20, 2006 4:43 pm

I'm asuming it's trying to tell you that the semi-colon at the end of the query is the 'illegal symbol'. Remove it.

ray.wurlod · Post by **ray.wurlod** » Fri Jan 20, 2006 7:41 pm

The database is objecting to EDWAPP.T_PARTY_ADDRESS - the error message is telling you that WAPP.T_PARTY_ADDRESS is a legal identifier.

Please verify that you are using a schema.table combination that is valid.

(I need to add that the routine is working correctly, being able to report this error.

)

chulett · Post by **chulett** » Fri Jan 20, 2006 8:39 pm

Oh, well. Close but no cigar.

olgc · Post by **olgc** » Wed Jan 25, 2006 2:16 pm

No matter it has ';' at the end or not, and the sql is perfect from any other clients or environments, the error is the same from routine. Sometime this illegel, another run another illegal.