How to acces a database table with BCI?
Moderators: chulett, rschirm, roy
How to acces a database table with BCI?
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,
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,
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?
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?
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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,
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,
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
You can find a well-documented example, a function to execute any DML statement, here
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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,
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,
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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. )
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. )
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.