Getting Database Information via ODBC

Hi,
In my PB 6.5 application it is possible to select any ODBC datasource (I
get the information from the registry), now I want also to see all
tables or views of the selected database. Now my questions:
1. Is there an generall way (database independent) to get this
information?
2. Must I use the ODBC32.dll, if yes, how?
3. Has every ODBC database the same system-tables, if yes, how they
call?
4. What about system stored procedures, is that a solution for my
problem?

Thanks and greetings to Lothar Matth�us
Michael

0
Michael
6/16/1999 10:07:55 AM
sybase.powerscript 7704 articles. 0 followers. Follow

4 Replies
317 Views

Similar Articles

[PageSpeed] 8

On Wed, 16 Jun 1999 12:07:55 +0200,
 in powersoft.public.powerbuilder.powerscript
Michael Tauber <mtauber@milestone.de> wrote: 
>In my PB 6.5 application it is possible to select any ODBC datasource (I
>get the information from the registry), now I want also to see all
>tables or views of the selected database. Now my questions:
>1. Is there an generall way (database independent) to get this
>information?

Not that I'm aware of.  The closest I've been able to come is that if I
know that the PB development environment is there, I look for the PB catalog
tables.

>4. What about system stored procedures, is that a solution for my
>problem?

Determining what the specific method is for each database, and developing a
specific routine for each one.  Sorry.....


---
Bruce Armstrong [TeamSybase]            | Romac/Source International
mailto:Bruce.Armstrong@teamsybase.com   | mailto:jobs@sourcela.com
                                        | http://www.romac-source.com

Preach the gospel at all times. If necessary, use words. [Francis of Assisi]
http://www.kidbrothers.org                http://www.fccwc.org
http://www.harvest.org/knowgod/index.htm

-----------== Posted via the PFCGuide Web Newsreader ==----------
http://www.pfcguide.com/_newsgroups/group_list.asp
0
Bruce
6/16/1999 4:16:30 PM
Michael Tauber wrote:

> Hi,
> In my PB 6.5 application it is possible to select any ODBC datasource (I
> get the information from the registry), now I want also to see all
> tables or views of the selected database. Now my questions:
> 1. Is there an generall way (database independent) to get this
> information?

You can do this for ODBC via the ODBC API making basically the same calls
that PowerBuilder does in the development environment.  This isn't a project
you'd embark upon lightly though.

>
> 2. Must I use the ODBC32.dll, if yes, how?

When you connect with a PowerBuilder transaction object, you can use the
DBHandle() function to get an ODBC connection handle.  That handle is an
argument to many ODBC API calls.  For a table list for instance, you'd call
the API function SQLTables and process the information it returns.  The key
here is familiarity with the ODBC API.  There's actually some examples on
calling ODBC API functions in the PowerBUilder on-line help.

>
> 3. Has every ODBC database the same system-tables, if yes, how they
> call?

No they don't.  The ODBC API provides the standardization, so a client app
calls the API function SQLTables and the ODBC driver implementation know how
to get that information from the DBMS-specific system tables.

>
> 4. What about system stored procedures, is that a solution for my
> problem?
>

Nope, not all ODBC datasources have them and they, like the system tables,
will be DBMS dependent.

>
> Thanks and greetings to Lothar Matth�us
> Michael




0
Jim
6/16/1999 5:00:12 PM
Who the hell is Lothar Matthaeus???

Michael Tauber schrieb:

> Hi,
> In my PB 6.5 application it is possible to select any ODBC datasource (I
> get the information from the registry), now I want also to see all
> tables or views of the selected database. Now my questions:
> 1. Is there an generall way (database independent) to get this
> information?
> 2. Must I use the ODBC32.dll, if yes, how?
> 3. Has every ODBC database the same system-tables, if yes, how they
> call?
> 4. What about system stored procedures, is that a solution for my
> problem?
>
> Thanks and greetings to Lothar Matth�us
> Michael

--
Bitte beachten Sie unsere neue Adresse und Rufnummer:

Template Software GmbH
Gerhard Engel
Frankfurter Stra�e 233
D-63263 Neu-Isenburg

Phone: +49 (0) 6102-4390-0
Fax: +49 (0) 6102-4390-99
E-Mail: gengel@template.de
WWW: http://www.template.de

Besuchen Sie uns auf der CeBit 99 Halle 3 Stand A12
"Template Software ----- Enterprise Integration is our profession"


0
Gerhard
6/17/1999 10:50:04 AM
Michael

Each database has its own system catalogue and there is minimal consistency
between databases from different vendors.  However, the ODBC API
(ODBC32.DLL) has standard functions that abstract access to the system
catalogues (SQLTables, SQLColumns, SQLForeignKeys, SQLPrimaryKeys etc.),
although not all functions are supported by all ODBC drivers.  It is
possible to do this in PowerBuilder, but it is definitely not trivial.
PowerBuilder provides you with the ODBC handle, which allows you to call
functions in the ODBC API.

hdbc = sqlca.DBHandle()

I have developed code (on an object inherited from a standard transaction
object) that queries the ODBC driver to obtain additional information about
the current ODBC connection.  This should point you in the right direction:

ODBC function declarations:

// ODBC function to get information on a connection
FUNCTION integer SQLGetConnectOption(Long hdbc, uint fOption, ref ulong
pvParam) LIBRARY "ODBC32.DLL"
FUNCTION integer SQLGetConnectOption(Long hdbc, uint fOption, ref string
spvParam) LIBRARY "ODBC32.DLL"

// ODBC function to configure a connection
FUNCTION integer SQLSetConnectOption(Long hdbc, uint fOption, long pvParam)
LIBRARY "ODBC32.DLL"
FUNCTION integer SQLSetConnectOption(Long hdbc, uint fOption, ref string
spvParam) LIBRARY "ODBC32.DLL"

// ODBC function to return info about driver and connection
FUNCTION integer SQLGetInfo(Long hdbc, uint fInfoType, ref string
rgbInfoValue, int cbInfoValueMax, ref long pcbInfoValue) LIBRARY
"ODBC32.DLL"

// ODBC installer function to get parameters from ODBC.INI or the registry
FUNCTION ulong SQLGetPrivateProfileString(string lpAppName, string
lpKeyName, string lpDefault, ref string lpReturnedString, ulong nSize,
string lpFileName) LIBRARY "ODBCCP32.DLL"

Instance Variables:

// ODBC connection handle
Long hdbc = 0

Constant Values:

// ODBC return codes
Constant integer SQL_INVALID_HANDLE = -2
Constant integer SQL_ERROR  = -1
Constant integer SQL_SUCCESS  = 0
Constant integer SQL_SUCCESS_WITH_INFO = 1
Constant integer SQL_NO_DATA_FOUND = 100

// Selected SQLGetInfo codes
Constant uint SQL_DATA_SOURCE_NAME = 2
Constant uint SQL_DRIVER_NAME = 6
Constant uint SQL_DRIVER_VER = 7
Constant uint SQL_ODBC_VER  = 10
Constant uint SQL_SERVER_NAME = 13
Constant uint SQL_DATABASE_NAME = 16
Constant uint SQL_DBMS_NAME  = 17
Constant uint SQL_DBMS_VER  = 18
Constant uint SQL_OWNER_TERM = 39
Constant uint SQL_USER_NAME  = 47
Constant uint           SQL_DRIVER_ODBC_VER    = 77

// Selected SQLSetConnectionOption codes
Constant uint SQL_ACCESS_MODE = 101
Constant uint SQL_AUTOCOMMIT = 102
Constant uint SQL_OPT_TRACE  = 104
Constant uint SQL_OPT_TRACEFILE = 105
Constant uint SQL_CURRENT_QUALIFIER = 109

// Constant values for SQL_AUTOCOMMIT
Constant uint SQL_AUTOCOMMIT_OFF = 0
Constant uint SQL_AUTOCOMMIT_ON = 1

// Constant values for SQL_TRACE
Constant uint SQL_OPT_TRACE_OFF = 0
Constant uint SQL_OPT_TRACE_ON = 1


Function to retrieve ODBC connection info from the ODBC API


Function uf_get_odbc_connection_option( uint fInfoType ) RETURNS String

// fInfoType can be any of the SQLGetInfo codes defined above

Integer  RETCODE

Integer  cbInfoValueMax = 128   // Max buffer length
String  sInfoValue
String  sResult

//
// Setup a string to return the value into
//
sInfoValue = Space( cbInfoValueMax )

//
// Call the ODBC SQLGetConnectOption function to retrieve the required info
//
RETCODE = SQLGetConnectOption( hdbc , fInfoType , sInfoValue )

//
// If successful then return the driver option, otherwise return an empty
string
//
CHOOSE CASE RETCODE
 CASE SQL_SUCCESS_WITH_INFO, SQL_SUCCESS
  sResult = sInfoValue
 CASE SQL_ERROR
  sResult = "*SQL error*"
 CASE SQL_INVALID_HANDLE
  sResult = "*Invalid handle*"
 CASE SQL_NO_DATA_FOUND
  sResult = "*No data*"
 CASE ELSE
  sResult = "***"
END CHOOSE

RETURN sResult


Are you sure you want to get into this?



Dave M



Michael Tauber wrote in message <3767777A.45B0D1F1@milestone.de>...
>Hi,
>In my PB 6.5 application it is possible to select any ODBC datasource (I
>get the information from the registry), now I want also to see all
>tables or views of the selected database. Now my questions:
>1. Is there an generall way (database independent) to get this
>information?
>2. Must I use the ODBC32.dll, if yes, how?
>3. Has every ODBC database the same system-tables, if yes, how they
>call?
>4. What about system stored procedures, is that a solution for my
>problem?
>
>Thanks and greetings to Lothar Matth�us
>Michael
>


0
Dave
6/22/1999 12:17:27 AM
Reply:

Similar Artilces:

Powerbuilder 8: cant get primary key information from ms-access via ODBC
Hi, using Powerbuilder 6.5 it is possible to get the primary key information of tables create in MS Access via the odbc driver (also the odbc doesnt support this function). We just changed to Powerbuilder 8 and these functionality is gone. It is no longer possible to get the primary key information, I can only see an index called 'primarykey'. What has changed between 6.5 and 8? Is there any chance to get the primary key information with Powerbuilder 8 like 6.5 did? TH ...

How to get database information
Hello everyone I am using ASE 11.5.1 on Sun 2.6 . I wonder what is the sql-syntax to get the following information : 1 to retrieve all the tables info including columns, column-type, length, creator, nulls or not-nulls, in a database. 2 to retrieve who is granted to access database,on what tables, what is the authority given. 3 to retrieve what is the index & its fields created for each table in a database Would appreciate that you can help. thanks. sp_help <table_name> go sp_helprotect <object_name> go sp_helpindex <table_name> go sp_he...

getting information from the database to the webpage
Hello,I am an experienced PHP programmer starting at C#.I am rebuilding an old site from PHP to ASP.NET and I have a database with portfolio's. In my site I finally have an overview with the porfolio using following code:1 korebritsTableAdapters.portfolioTableAdapter portfolioAdapter = 2 new korebritsTableAdapters.portfolioTableAdapter(); 3 korebrits.portfolioDataTable products; 4 products = portfolioAdapter.GetPortfolio(); 5 6 string classname; 7 string html = ""; 8 string detailUrl; 9 int aantalPerRij = 3; 1...

textbox get the information from the database~help~
Label1.Text=Session["user"].ToString (); string strConn="server=MAGICNIKI;uid=yinwun;pwd=;database=caspnet"; string selCmd="select * from croinfo where user= '"+Label1.Text+"'"; SqlConnection myConnection=new SqlConnection (); myConnection.ConnectionString =strConn; myConnection.Open (); SqlDataAdapter da=new SqlDataAdapter (selCmd,myConnection); DataSet ds=new DataSet (); da.Fill(ds,"croinfo"); DataRow dr; dr=ds.Tables["croinfo"].Rows[0]; string company = (strin...

Getting information from multi database/schema
Hi there, Could anyone tell me how to select tables from multi database/schema to do a reverse engineering from PowerDesigner 7? I have done that in PowerDesigner 6 from Oracle 8i, but not in PD7. PD7 can only select items from ONE database at one time. In my situation, there are relationships defined for tables from different database/schema. Hence, the relationships linked between database's did not show up on the diagram generated from reverse engineering. Thanks in advance. Best Regards, PDv6 never could do multi-database modeling. Are you using ODBC or scr...

how could i get ODBC-profiles for a specific database ??
hello i'm using powerbuilder 9.02 ; and i need to select on application-start a odbc-database-profile from a specific-databasedriver (e.g. ASA9....) how could i get the odbc-profiles from the user-dsn oder system-dsn on the local machine for a spec.database-driver? have anyone an example or a short help for my problem? many - many thank's in advance!!!! Michael One way is to query the registry at HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources. You should also be able to probe using the ODBC API to get this information. "M.Erlinger" &l...

Get DB Settings via ODBC
Hello, I am interested in getting SQLAnywhere 5.5 database switches (EngineName, Start, etc.) via ODBC using VC++. I can create these using SQLConfigDataSource(), but can't figure out how to retrieve them. Any ideas? Thanks, Derek Price These are stored in the registry so you can use APIs that manipulate the registry to read the values. For system DSNs go to: hkey_local_machine\Software\ODBC\ODBC.ini Replace hkey_local_machine with hkey_current_user for user DSNs. /ck <Derek_Price> wrote in message news:7B85A0534F0E470F00521B5A85256A88.00521B6885256A88@...

Slow paradox database via ODBC
I have an application that uses a Paradox database back end, stored on an NSS volume on a Netware 6SP3 server. Clients are XP with client 4.90 (with and without SP1) The application itself has no problems accessing the database, but when I try to insert the data into an Excel document via MSQuery and an ODBC datasource pointing at the database it goes at a snail's pace. It might take half an hour just to view the list of fields within the table, and a similar amount to then import the data although the column preview field works instantly having got the list of fields. And if in ...

get database profile window in powerscript
hi all, What iam trying to do in my application is to let the user connect to any d/b that he/she wants to do ( using odbc drivers). For this purpose i cannot have the database profile already created or use any predefined ini...the application should call the d/b profile setup wherein the person can create and connect to the d/b. is this possible ?? tia Dinu ---== Posted via the PFCGuide Web Newsreader ==--- http://www.pfcguide.com/_newsgroups/group_list.asp you cannot call this screen from your exe, but the only thing you have to do is to populate the properties of your tra...

How to put blob in database via ODBC?
I'm trying to use a LONG BINARY-column to insert a file in SQL-Anywhere 5.5.0.2. Now I would like to store (and read) the file in the column. The only thing a found is some C-code in the help-file (Example for SET statement): EXEC SQL BEGIN DECLARE SECTION DECL_BINARY(5000) buffer; .... <etc>. How can I use this code via the ODBC-driver to store a file in the database? TIA, -- Alexander Beeren Nordined Technische Automatisering bv Alexander_Beeren@compuserve.com Check out the ODBC function calls SQLPutData and SQLGetData Jason Hinsperger Product Quality Engin...

pfc_n_tr
hi when my application connect to database - after the user-input from user-name and password, i connect to database - if the user-name or the password is incorrect, the odbc-propertie-window from the odbc-setup become open. i want to check a return-value from the connect and print a messagebox, and than i end the application - how could i opress the odbc-setup-propertie-window????? have anyone an idea? thank's in advance Michael E. There is a ConnectOption DBParm parameter that you set to tell ODBC not to show the dialog. On 8 Jun 2004 07:15:43 -0700, "M.E...

Get DB Settings via ODBC
Hello! I am interested in getting SQLAnywhere 5.5 database switches (EngineName, Start, etc.) via ODBC using VC++. I can create these using SQLConfigDataSource(), but can't figure out how to retrieve them. Any ideas? Thanks, Derek One options would be to get it from the brute force/raw level with SQLGetPrivateProfileString() You'll need to be precise about the DSN and the Connection Param your looking for but it works (at least with the ASA 7 odbc driver) for me with: szSection = 'ASA 7.0 Sample' szKey = 'ENG' (or sz...

Get information about login from database handle
Hi, Is it possible to get information from the database handle returned when opening a connection, in particular I would like to get the username used to open the connection to the database. For example: $dbh = DBI->connect($dsn, "john", "password") then after this, anywhere I can access the $dbh variable, or it's contents, I can say something like: GetUser($dbh); and this would equal "john". thanks in advance for any help. garry Why would you want to do that. Don't you already have that info when logging a user in? ...

How to get information from a database and use it into a webform
hi, i am doing this project and i want to know how to get the information in a database and pass it on to a webform and i have two webforms and the first one is a form where it asked your name and two password textbox(one for PASSWORD and another one for PASSWORD again) then the information that a user passed in will appear in the database and then i need to pas the information into another webform. Please help me. ok Just store the values which have to be passed in session and show it in the other form. or just retrieve the values from the database and show. Devendra naik ...

Web resources about - Getting Database Information via ODBC - sybase.powerscript

Information - Wikipedia, the free encyclopedia
Information (shortened as info or info. ) is that which informs, i.e. an answer to a question, as well as that from which knowledge and data ...

InformationWeek – Latest Information Technology (IT) news & IT articles - Technology news - IT magazine ...
InformationWeek provides latest Information technology (IT) news online & articles and publishes latest tech news & IT magazine for Indian IT ...

Queensland weather: Latest storm warning information
PARTS of Queensland were hit by severe storms, with golf-ball-size hail in the Granite Belt Monday afternoon.

Magnetic protein may provide animals with navigation information
What do monarch butterflies, salmon, lobsters, bats, mole rats, and marine nudibranch mollusks have in common? As I'm sure you already knew, ...

Security researchers have discovered more information on how the Sony hackers managed to stay undetected ...
More details of the hack that wiped out Sony Pictures' computer systems and resulted in thousands of internal emails and documents leaking online ...

Russian plane crash: $50M reward for information - CNN.com
... plane crash in Sinai, Egypt, last month was caused by a bomb, the Russian FSB chief says, and Russia offers a $50 million reward for information. ...

The Federal Reserve Leaks Price Sensitive Information Enabling Stock Market Profits
When people say that the insiders get to make all the money in the financial markets we usually pooh pooh such conspiracy theories. For a start ...

Transpose focuses on business customers, starts charging for its information management platform
... business customers. The service began in 2014 as a note-taking application called KustomNote, but it has since grown into a more holistic information ...

UN Urges Release of All Information on Hammarskjold Death
UN urges all countries to release information on 1961 crash that killed UN chief Hammarskjold

Jenny McCarthy: Knowing co-star Charlie Sheen was HIV+ would have been 'valuable information'
Jenny McCarthy is not throwing her support behind Charlie Sheen in light of his revelation this week that he was diagnosed as HIV-positive four ...

Resources last updated: 11/26/2015 8:08:14 PM