iodbc-list — Miscellaneous iODBC-related discussion

Re: [Iodbc-list] libodbcinst.so.2 link to lib*i*odbcinst.so.2 ?

[Profpatsch <ma...@pr...>]

On 16-12-01 12:07pm, Hugh Williams wrote:
> Hi
> 
> Yes, you can simply rename as they are compatible …

Very nice!

Maybe you want to have symlinks in your upstream distribution as well;
there are already links for libodbc.so anyway.

-- 
Proudly written in Mutt with Vim on NixOS.
Q: Why is this email five sentences or less?
A: http://five.sentenc.es
May take up to five days to read your message. If it’s urgent, call me.

Re: [Iodbc-list] libodbcinst.so.2 link to lib*i*odbcinst.so.2 ?

[Hugh W. <hwi...@op...>]

Hi

Yes, you can simply rename as they are compatible …

Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc.      //              http://www.openlinksw.com/
Weblog   -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter  -- http://twitter.com/OpenLink
Google+  -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

> On 30 Nov 2016, at 20:00, Profpatsch <ma...@pr...> wrote:
> 
> Would it be correct to create a symlink from iodbcinst to odbcinst?
> aka are the ABIs compatible?
> 
> I’m asking because the (sadly unfree) Microsoft ODBC driver
> has libodbcinst.so.2 in its ELF header, but I’d like to use
> iodbc instead of unixODBC (which is kind of awkward and undocumented).
> 
> -- 
> Proudly written in Mutt with Vim on NixOS.
> Q: Why is this email five sentences or less?
> A: http://five.sentenc.es
> May take up to five days to read your message. If it’s urgent, call me.
> 
> ------------------------------------------------------------------------------
> _______________________________________________
> Iodbc-list mailing list
> Iod...@li...
> https://lists.sourceforge.net/lists/listinfo/iodbc-list

[Iodbc-list] libodbcinst.so.2 link to lib*i*odbcinst.so.2 ?

[Profpatsch <ma...@pr...>]

Would it be correct to create a symlink from iodbcinst to odbcinst?
aka are the ABIs compatible?

I’m asking because the (sadly unfree) Microsoft ODBC driver
has libodbcinst.so.2 in its ELF header, but I’d like to use
iodbc instead of unixODBC (which is kind of awkward and undocumented).

-- 
Proudly written in Mutt with Vim on NixOS.
Q: Why is this email five sentences or less?
A: http://five.sentenc.es
May take up to five days to read your message. If it’s urgent, call me.

[Iodbc-list] iodbc + Firebird 2.5 + Firebird ODBC driver cuts SQL queries on the first cyrillic character

[Maxim S. <smy...@gm...>]

Hello!
We have a use case where the client connects to the Firebird database in
WIN1251 character set through Oracle HS. Both server and client are running
under Linux enviroments supporting russian locales.
So we have such link: SQL Query => Oracle 12c => Oracle database gateways
=> Linux odbc (unixODBC or iODBC) => Firebird ODBC => Firebird database in
WIN1251.

When we try to execute statement with cyrillic letters SQL eror is
generated. Firebird trace files show that ODBC cuts the query on the first
russian letter.
E.g. for "select * from test where value = 'Русские буквы' " we have a log
record "select * from test where value = ' ".

Using iodbctest and iodbstestw shows the same results.
Firebird ODBC driver can be linked with unixODBC library too, and it seems
to work fine on the use case, but we would like to see both libraries
working fine.

I have seen previous bug reports and got the idea that libiodbc represents
multibyte characters in wchar_t while unixODBC uses UCS2. So, I am sure it
is the main problem.
Does anyone have any suggestions on how I can fix it? Perhaps, I can set
some more appropriate value of HS_NLS_NCHAR in my oracle init*.ora file,
but I didn't find one and I doubt it cat help because iodbctest(w) don't
work either?

-- 
Thank you!
Smyatkin Maxim, Red Soft Corporation.
https://www.linkedin.com/in/smyatkin

[Iodbc-list] iODBC hangs while calling MySQL driver

[Kristján R. <k_r...@si...>]

Hi
I'm trying to get the MySQL ODBC driver working on AIX 7 with iODBC 3.52.7. When I run the 'iodbcetest' program iODBC hangs in functon _iodbcdm_driver while calling the macro CALL_DRIVER at line 1210 in connect.c. The trace file makes it as far as entering the function SQLDriverConnectW.

Does anybody have a clue why this might be happening?

Vive valeque,
Kristján Rúnarsson

[Iodbc-list] TNS ----> ODBC

[Mehdi A <meh...@gm...>]

Hi;

Is anyone interested to develop a TNS Listener backing to ODBC? (contract)

Please drop e-mail mehdiaf110@gmail....

Re: [Iodbc-list] Oracle InstantClient Configuration

[Ted T. Jr <tth...@op...>]

Hi, Scott --

On Jan 2, 2013, at 11:47 AM, Scott Dudley wrote:
> I've been asked to test my application against both unixODBC-2.3.1 and libiodbc-3.52.8 using Oracle's instantclient_11_2 ODBC driver.
> 
> I have a working odbcinst.ini and odbc.ini (/etc) working with unixODBC.
> 
> First execution of iodbctest revealed that I needed to add the section [ODBC Data Sources] to the top of my odbc.init.  The "?" then listed the DSN however, all attempts to connect using it fail with a sigsegv:
> 
> Program received signal SIGSEGV, Segmentation fault.
> [Switching to Thread -135227712 (LWP 3556)]
> 0xf7ccbc10 in bcoSQLSetStmtOption () from /usr/local/telmast/instantclient_11_2/libsqora.so.11.1
> (gdb) bt
> #0  0xf7ccbc10 in bcoSQLSetStmtOption () from /usr/local/telmast/instantclient_11_2/libsqora.so.11.1
> #1  0xf7ce36c3 in bccSQLSetStmtOption () from /usr/local/telmast/instantclient_11_2/libsqora.so.11.1
> #2  0xf7ce1839 in SQLSetConnectAttrW () from /usr/local/telmast/instantclient_11_2/libsqora.so.11.1
> #3  0x08052805 in _iodbcdm_SetConnectOption_init ()
> #4  0x08054402 in _iodbcdm_driverload ()
> #5  0x08057123 in SQLDriverConnect_Internal ()
> #6  0x080579eb in SQLDriverConnect ()
> #7  0x08049722 in ODBC_Connect ()
> #8  0x0804a912 in main ()
> (gdb)
> 
> Any pointers on configuration for use with InstantClient?
> 
> Thanks.

DSN definition should be identical -- as this is specific to
the *driver* not to the driver manager.

The need for [ODBC Data Sources] is based on the ODBC specification,
and this stanza should be present regardless of driver manager.

For best results, odbcinst.ini and odbc.ini should be targeted
by the environment variables $ODBCINSTINI and $ODBCINI -- even
if they're at one of the default locations, as in your case.

Please do confirm that all libraries and components are matching
in bitness and processor architecture.  It may be useful to 
confirm the OS on which you're working as well.

If the above doesn't bring resolution, we'll need to review your
complete odbc.ini and odbcinst.ini files.  You can post them to 
the list, start a thread on our public discussion forums --

   <http://boards.openlinksw.com/support/viewforum.php?f=32>,

-- or log a confidential (and free) support case --

   <http://support.openlinksw.com/support/online-support.vsp>

Regards,

Ted
OpenLink Support


> -- 
>  
> Scott Dudley | Senior Developer
> 
> Telesoft | 1661 E. Camelback Road, Suite 300 | Phoenix, AZ 85016
> P: 602.308.1115 | F: 602.308.1300 | W: www.telesoft.com

--
A: Yes.                      http://www.guckes.net/faq/attribution.html
| Q: Are you sure?
| | A: Because it reverses the logical flow of conversation.
| | | Q: Why is top posting frowned upon?

Ted Thibodeau, Jr.           //               voice +1-781-273-0900 x32
Senior Support & Evangelism  //        mailto:tth...@op...
                             //              http://twitter.com/TallTed
OpenLink Software, Inc.      //              http://www.openlinksw.com/
         10 Burlington Mall Road, Suite 265, Burlington MA 01803
     Weblog   -- http://www.openlinksw.com/blogs/
     LinkedIn -- http://www.linkedin.com/company/openlink-software/
     Twitter  -- http://twitter.com/OpenLink
     Google+  -- http://plus.google.com/100570109519069333827/
     Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

[Iodbc-list] Oracle InstantClient Configuration

[Scott D. <sc...@te...>]

I've been asked to test my application against both unixODBC-2.3.1 and 
libiodbc-3.52.8 using Oracle's instantclient_11_2 ODBC driver.

I have a working odbcinst.ini and odbc.ini (/etc) working with unixODBC.

First execution of iodbctest revealed that I needed to add the section 
[ODBC Data Sources] to the top of my odbc.init.  The "?" then listed the 
DSN however, all attempts to connect using it fail with a sigsegv:

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread -135227712 (LWP 3556)]
0xf7ccbc10 in bcoSQLSetStmtOption () from 
/usr/local/telmast/instantclient_11_2/libsqora.so.11.1
(gdb) bt
#0  0xf7ccbc10 in bcoSQLSetStmtOption () from 
/usr/local/telmast/instantclient_11_2/libsqora.so.11.1
#1  0xf7ce36c3 in bccSQLSetStmtOption () from 
/usr/local/telmast/instantclient_11_2/libsqora.so.11.1
#2  0xf7ce1839 in SQLSetConnectAttrW () from 
/usr/local/telmast/instantclient_11_2/libsqora.so.11.1
#3  0x08052805 in _iodbcdm_SetConnectOption_init ()
#4  0x08054402 in _iodbcdm_driverload ()
#5  0x08057123 in SQLDriverConnect_Internal ()
#6  0x080579eb in SQLDriverConnect ()
#7  0x08049722 in ODBC_Connect ()
#8  0x0804a912 in main ()
(gdb)

Any pointers on configuration for use with InstantClient?

Thanks.

-- 

Scott Dudley | Senior Developer

Telesoft | 1661 E. Camelback Road, Suite 300 | Phoenix, AZ 85016
P: 602.308.1115 | F: 602.308.1300 | W: www.telesoft.com 
<http://www.telesoft.com/?utm_source=signature&utm_medium=email&utm_campaign=2012>

TEM Edge Blog 
<http://www.telesoft.com/blog?utm_source=signature&utm_medium=email&utm_campaign=2012> 
| LinkedIn <http://www.linkedin.com/company/telesoft-corp.> | Twitter 
<http://www.twitter.com/_Telesoft>

[Iodbc-list] problem after build libiodbc-3.52.8 and psqlodbc-09.01.0200

[Maziar N. <mna...@gm...>]

i try to use ODBC in centos 6.3 x64 and postgresql 9.1.3 .
as soon as I remember ,odbc.ini and odbcinst.ini  are created when I use 
unixodbc .
how ever  when I follow these steps :
----ibiodbc-3.52.8.tar.gz
./configure --with-libpq=/usr/pgsql-9.1/bin/pg_config --enable-pthreads
        --with-iodbc=/usr/local/bin/iodbc- config  --sysconfdir=/etc
Make
Make install
----psqlodbc-09.01.0200.tar.gz
./configure --with-libpq=/usr/pgsql-9.1/bin/pg_config --with-iodbc
Make
Make install

I think these packages installed properly, however two files (iodbc.ini 
and odbcinst.ini) is not created.
Is it normal?
I must create this file by hand?

Re: [Iodbc-list] Difference between database, catalog and schema?

[Ted T. Jr <tth...@op...>]

Hi, Maury --

I'm hoping to add a bit of clarity to what I see as rather murky
in Tim's notes below...


On Jun 22, 2012, at 12:41 PM, Tim Haynes wrote:
> On 06/22/2012 02:16 PM, Maury Markowitz wrote:
>> I've looked over the documents on MS's site on ODBC, but I'm still confused about this. Is there any*real*  difference between these sources, or are they, in the end, synonyms? IE, if I ask for the SQL_DATABASE_NAME, will I fail to get a name if I'm connected to a "catalog"? Or if I use SQL_MAXIMUM_CATALOG_NAME_LENGTH, will I get garbage if I'm connected to a "database"?
> 
> http://msdn.microsoft.com/en-us/library/windows/desktop/ms711688(v=vs.85).aspx 
> says:
> 
>> Database also refers to a particular collection of data, such as a collection of Xbase files in a directory or a database on SQL Server. It is generally equivalent to the term catalog, used elsewhere in this manual, or the term qualifier in earlier versions of ODBC.
> 
> What I understand:
> 
> RDBMS: an engine that serves up multiple databases; sometimes `database' 
> abused as synonym

This discussion departs from both SQL and ODBC spec, as well as 
from common usage (which itself departs from the above specs).

The following are according to SQL:2008 (the latest published 
standard, freely accessible only in a near-to-final draft, 
<http://bit.ly/Mvw3Su>) --

An RDBMS "instance" == a running binary (might be a cluster of
same, running in concert), which hosts zero or more "catalogs".
Oracle has a one-to-one "instance" to "catalog" ratio, but other 
DBMS engines support other ratios.

"catalog" == a collection of zero or more "schema".  These
schema are generally not inherently inter-related within the 
catalog -- but users/DBA may design relationships which are
then outside the bounds of DBMS-enforced "referential integrity".

"schema" == a collection of zero or more "tables" and "views"
and "persistent stored modules" (a/k/a "stored procedures"),
which also describes the structures of (column data types and 
sizes, value uniqueness and other restrictions, nullability, 
etc.) and relationships between those objects (primary keys, 
foreign keys, etc.).  DBMS engines typically enforce such
rules and relationships *within each schema*, maintaining 
"referential integrity".


Many of these terms are used differently in docs for specific 
RDBMS (Oracle, Microsoft SQL Server, MySQL, DB2, SQL Anywhere, 
PostgreSQL, Progress/OpenEdge, Sybase ASE, etc.) and their APIs.

These terms are also used somewhat differently in the ODBC spec
and in Microsoft's documentation of same ... but the overall
containing structure and four-part-naming of 
catalog.schema.table.column remains.

Note that when you get into ODBC, SQLGetInfo() has a number of 
related arguments, which may now make more sense given the 
structure described above --

   SQL_MAX_CATALOG_NAME_LEN
   SQL_MAX_SCHEMA_NAME_LEN
   SQL_MAX_TABLE_NAME_LEN
   SQL_MAX_COLUMN_NAME_LEN

> instance: normally one per network listener port, a query-handler 
> serving multiple catalogs that may be running or quiescent all together
> 
> catalog: a collection or space in which tables and views might live

I think the above definition of "catalog" is more suited to 
"schema"... and I would not generally recommend using the 
overloaded term "database" (which may refer to the DBMS engine,
a catalog hosted by that engine, a schema within that catalog,
etc.) as its below-defined synonym for "catalog". 

> database:
> (1) syn. catalog;
> (2) any generalized store of data, hence permitting conflation of the 
> term with the RDBMS level (amongst others such as no-sql or OODB and 
> their implementations)
> 
> schema: a blueprint for describing structure, "there's a table 'foo' 
> with columns of types..."
> 
> Additionally, the schema may be stored in / presented as a catalog, 

... except that here "catalog" is not used in its primitive sense 
as in the four-part-naming, but in the very general sense of "a 
list of names and associated descriptions"...

> known as INFORMATION_SCHEMA - ie you can do things like
>    select * from DB.INFORMATION_SCHEMA.TABLES ;
> and it lists all tables in the system.
> 
> Oracle (and perhaps Sybase?) differ slightly regarding their 
> interpretation of schemas.
> 
> When a cheap web-hosting company offers you a `mysql database' they 
> probably mean a catalog(/database.1) in one instance, access governed by 
> ACL on the catalog, and if you want to install both wordpress *and* 
> phpBB then you better put prefixes on your tables...
> 
> 
> SQLGetInfo(SQL_DATABASE_NAME) is defined as returning the RDBMS 
> name(+version); if you're connected, you have one but it'll be a string 
> such as "SQL Server".

To be (hopefully) clearer -- SQLGetInfo(SQL_DATABASE_NAME) is not 
related to catalog/schema/table at all.  

SQLGetInfo(SQL_DATABASE_NAME) is tied to the engine *hosting/
serving* those data structures -- e.g., "MySQL 3.0.14" or 
"PosgreSQL" or "Oracle 8i" or "ORACLE", etc.


> SQLGetInfo(SQL_MAXIMUM_CATALOG_NAME_LENGTH) is defined as returning the 
> maximum length of a catalog name - mostly so you know what size string 
> to allocate, either ~18 (SQL-89) or ~128 (SQL-92).

Note that this is the length of the first segment, in the four-
part naming above -- so the full string size to be allocated (if 
you're holding it as the full four-part) may be rather larger --

   [*18].[*18].[*18].[*18] = 18*4 + 3 = 75  <-- SQL-89

   [*128].[*128].[*128].[*128] = 128*4 + 3 = 515  <-- SQL:2008


Hopefully helpful...

Ted


> So, no gibberish - but you're allowed to gibber a bit :)
> 
> HTH,
> 
> ~Tim

--
A: Yes.                      http://www.guckes.net/faq/attribution.html
| Q: Are you sure?
| | A: Because it reverses the logical flow of conversation.
| | | Q: Why is top posting frowned upon?

Ted Thibodeau, Jr.           //               voice +1-781-273-0900 x32
Senior Support & Evangelism  //        mailto:tth...@op...
                             //              http://twitter.com/TallTed
OpenLink Software, Inc.      //              http://www.openlinksw.com/
         10 Burlington Mall Road, Suite 265, Burlington MA 01803
     Weblog   -- http://www.openlinksw.com/blogs/
     LinkedIn -- http://www.linkedin.com/company/openlink-software/
     Twitter  -- http://twitter.com/OpenLink
     Google+  -- http://plus.google.com/100570109519069333827/
     Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

Re: [Iodbc-list] Difference between database, catalog and schema?

[Tim H. <th...@op...>]

On 06/22/2012 02:16 PM, Maury Markowitz wrote:
> I've looked over the documents on MS's site on ODBC, but I'm still confused about this. Is there any*real*  difference between these sources, or are they, in the end, synonyms? IE, if I ask for the SQL_DATABASE_NAME, will I fail to get a name if I'm connected to a "catalog"? Or if I use SQL_MAXIMUM_CATALOG_NAME_LENGTH, will I get garbage if I'm connected to a "database"?

http://msdn.microsoft.com/en-us/library/windows/desktop/ms711688(v=vs.85).aspx 
says:

> Database also refers to a particular collection of data, such as a collection of Xbase files in a directory or a database on SQL Server. It is generally equivalent to the term catalog, used elsewhere in this manual, or the term qualifier in earlier versions of ODBC.

What I understand:

RDBMS: an engine that serves up multiple databases; sometimes `database' 
abused as synonym

instance: normally one per network listener port, a query-handler 
serving multiple catalogs that may be running or quiescent all together

catalog: a collection or space in which tables and views might live

database:
(1) syn. catalog;
(2) any generalized store of data, hence permitting conflation of the 
term with the RDBMS level (amongst others such as no-sql or OODB and 
their implementations)

schema: a blueprint for describing structure, "there's a table 'foo' 
with columns of types..."

Additionally, the schema may be stored in / presented as a catalog, 
known as INFORMATION_SCHEMA - ie you can do things like
    select * from DB.INFORMATION_SCHEMA.TABLES ;
and it lists all tables in the system.

Oracle (and perhaps Sybase?) differ slightly regarding their 
interpretation of schemas.

When a cheap web-hosting company offers you a `mysql database' they 
probably mean a catalog(/database.1) in one instance, access governed by 
ACL on the catalog, and if you want to install both wordpress *and* 
phpBB then you better put prefixes on your tables...


SQLGetInfo(SQL_DATABASE_NAME) is defined as returning the RDBMS 
name(+version); if you're connected, you have one but it'll be a string 
such as "SQL Server".

SQLGetInfo(SQL_MAXIMUM_CATALOG_NAME_LENGTH) is defined as returning the 
maximum length of a catalog name - mostly so you know what size string 
to allocate, either ~18 (SQL-89) or ~128 (SQL-92).

So, no gibberish - but you're allowed to gibber a bit :)

HTH,

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>

[Iodbc-list] Difference between database, catalog and schema?

[Maury M. <mau...@gm...>]

I've looked over the documents on MS's site on ODBC, but I'm still confused about this. Is there any *real* difference between these sources, or are they, in the end, synonyms? IE, if I ask for the SQL_DATABASE_NAME, will I fail to get a name if I'm connected to a "catalog"? Or if I use SQL_MAXIMUM_CATALOG_NAME_LENGTH, will I get garbage if I'm connected to a "database"?

Re: [Iodbc-list] Adding some UI features

[Tim H. <th...@op...>]

On 06/20/2012 03:24 PM, Maury Markowitz wrote:
>> >I think you want SQLDescribeCol() on a resultset.
> I thought so too, but that*always*  returns "SQLCHAR" from what I can tell, regardless of the encoding.
>
> This might be at the driver level...

It may well be the driver.

By way of comparison, with our Virtuoso ODBC driver (Unicode):
create table timtest(id integer, str nvarchar)	-- note nvarchar
insert....
select * from timtest
... and the iodbc trace shows:

> iodbctest       7F98A6B02700 EXIT  SQLDescribeCol with return code 0 (SQL_SUCCESS)
>                 SQLHSTMT          0x14b1670
>                 SQLUSMALLINT      2
>                 SQLCHAR         * 0x7fffdefc21e0
>                                   | str                                      |
>                 SQLSMALLINT       50
>                 SQLSMALLINT     * 0x0
>                 SQLSMALLINT     * 0x7fffdefc20c2 (SQL_WVARCHAR)
>                 SQLULEN         * 0x7fffdefc20c8 (4072)
>                 SQLSMALLINT     * 0x7fffdefc20c4 (0)
>                 SQLSMALLINT     * 0x7fffdefc20c6 (SQL_NULLABLE)

HTH,

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>

Re: [Iodbc-list] Adding some UI features

[Maury M. <mau...@gm...>]

On 2012-06-20, at 4:08 AM, Tim Haynes wrote:
>> Another issue that's come up is accented characters, which we can translate, but only if we know about it. So, is there a way to check easily? Do I do this on a column-by-column basis or is there a connection-level flag I can default to?
> 
> I think you want SQLDescribeCol() on a resultset.

I thought so too, but that *always* returns "SQLCHAR" from what I can tell, regardless of the encoding.

This might be at the driver level...

Re: [Iodbc-list] Adding some UI features

[Tim H. <th...@op...>]

On 06/19/2012 09:04 PM, Maury Markowitz wrote:
> I've had some success with my little iODBC project on the Mac, and I'm now successfully returning data from all sorts of sources from MySQL to Firebird. Now I'd like to improve my GUI before sending out a major revision. So...
>
> One of the things I'd like to do is is display a simplified login screen. As Tim noted earlier, I can leave all of this up to the driver, but many drivers on the Mac don't have a setup lib included - MySQL for instance, an apparent oversight on the build. So our app has it's own login screen.
>
> However, this means that the username and password are empty - we don't read them from the DSN. Parsing the DSN directly seems sub-optimal... is there a recommended way of returning these directly from the driver?

I don't know of a way offhand, although there are folks better qualified 
to state so authoritatively than I am.

Random thought: it transpires there's a SQLBrowseConnect() function... 
might that help?

> Another issue that's come up is accented characters, which we can translate, but only if we know about it. So, is there a way to check easily? Do I do this on a column-by-column basis or is there a connection-level flag I can default to?

I think you want SQLDescribeCol() on a resultset.

HTH!

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>

[Iodbc-list] Adding some UI features

[Maury M. <mau...@gm...>]

I've had some success with my little iODBC project on the Mac, and I'm now successfully returning data from all sorts of sources from MySQL to Firebird. Now I'd like to improve my GUI before sending out a major revision. So...

One of the things I'd like to do is is display a simplified login screen. As Tim noted earlier, I can leave all of this up to the driver, but many drivers on the Mac don't have a setup lib included - MySQL for instance, an apparent oversight on the build. So our app has it's own login screen.

However, this means that the username and password are empty - we don't read them from the DSN. Parsing the DSN directly seems sub-optimal... is there a recommended way of returning these directly from the driver?

Another issue that's come up is accented characters, which we can translate, but only if we know about it. So, is there a way to check easily? Do I do this on a column-by-column basis or is there a connection-level flag I can default to?

Re: [Iodbc-list] Proper flag for read-only sources?

[Hugh W. <hwi...@op...>]

Hi Maury,

SQL_ATTR_ACCESS_MODE and  SQL_MODE_READ_ONLY are defined in the “sqlext.h” header file shipped with iODBC.

Best Regards
Hugh Williams
Professional Services
OpenLink Software, Inc.      //              http://www.openlinksw.com/
Weblog   -- http://www.openlinksw.com/blogs/
LinkedIn -- http://www.linkedin.com/company/openlink-software/
Twitter  -- http://twitter.com/OpenLink
Google+  -- http://plus.google.com/100570109519069333827/
Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

On 16 Jun 2012, at 14:17, Maury Markowitz wrote:

> According to the MS documentation, I can SQLGetConnectAttr to see if SQL_ATTR_ACCESS_MODE is SQL_MODE_READ_ONLY. However, in iODBC's sql.h the only thing that seems to be similar is SQL_DATA_SOURCE_READ_ONLY. Am I correct in thinking these are the same?
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and 
> threat landscape has changed and how IT managers can respond. Discussions 
> will include endpoint security, mobile security and the latest in malware 
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> _______________________________________________
> Iodbc-list mailing list
> Iod...@li...
> https://lists.sourceforge.net/lists/listinfo/iodbc-list

[Iodbc-list] Proper flag for read-only sources?

[Maury M. <mau...@gm...>]

According to the MS documentation, I can SQLGetConnectAttr to see if SQL_ATTR_ACCESS_MODE is SQL_MODE_READ_ONLY. However, in iODBC's sql.h the only thing that seems to be similar is SQL_DATA_SOURCE_READ_ONLY. Am I correct in thinking these are the same?

Re: [Iodbc-list] Understanding server vs. database in ODBC terms

[Maury M. <mau...@gm...>]

First off, my apologies for posting back outside the list, I didn't notice that I was sending to people's emails.

Tim, your hint about SQLGetConnectAttr is working wonderfully. I've pushed this up the UI stack so that the selected catalog appears in main window for quick reference.

Indeed, the selection of the catalog on login is a problem... I'm connecting to a public MySQL over the internet as anon, and it definitely is not setting a default catalog. This causes downstream problems when it tries to get the (non-existant) schema.

With this new code I'm able to check for this case and return a nicely formatted error. A little bit of debugging output goes a long way...

Re: [Iodbc-list] Understanding server vs. database in ODBC terms

[Ted T. Jr <tth...@op...>]

Hi, Maury --

On Jun 13, 2012, at 11:28 AM, Maury Markowitz wrote:

> I'm using iODBC as installed on OSX. I'm looking over some sample code in ODBCkit (3rd party) and come across a problem. I'm trying to better understand the nature of issue...
> 
> When one opens a connection to a database server, say MSSQL, you do not have to select a *database* within it. One can do that with USE, or in the case of ODBC, by using one of the available flags in the DSN. But what if you don't? You still have a valid *connection*, but you aren't talking to an actual DB... right? Is that legal under ODBC?

The "server" typically refers the DBMS engine instance, which may host multiple "catalogs" and/or "schemas" either (or neither) of which the DBMS engine may consider to be a "database".

When you connect to the "server", you typically connect to the *default* "catalog" or "schema" or "database", as set by the DBMS server for the user as whom you've connected.

This default may be DBMS admin configurable, on many possible bases.

The "USE ..." SQL statement changes the "catalog" or "schema" or "database" which subsequent statements address.


> The code in question assumes such a connection to the DB exists if a connection to the server exists. It then goes on and attempts to read the schema of the DB, which fails. I'd like to fix this, but I see problems...
> 
> Does ODBC maintain some sort of state indicating whether it is connected to a DB (in the sense above)?
> 
> If not, is there an easy way to tell if you are connected to a DB?
> 
> Does this state change when the user selects a different DB?
> 
> If not, does the same basic check allow one to notice such a change?
> 
> Thanks!


If I'm understanding you correctly, I think the issue(s) you're hitting is(are) DBMS- or possibly client-application-specific, and not tied to ODBC per se.  And I think the info I gave above may obviate these lower questions...

Of course, more details of what you're doing and your resultant experiences may change my understanding.

Be seeing you,

Ted


--
A: Yes.                      http://www.guckes.net/faq/attribution.html
| Q: Are you sure?
| | A: Because it reverses the logical flow of conversation.
| | | Q: Why is top posting frowned upon?

Ted Thibodeau, Jr.           //               voice +1-781-273-0900 x32
Senior Support & Evangelism  //        mailto:tth...@op...
                             //              http://twitter.com/TallTed
OpenLink Software, Inc.      //              http://www.openlinksw.com/
         10 Burlington Mall Road, Suite 265, Burlington MA 01803
     Weblog   -- http://www.openlinksw.com/blogs/
     LinkedIn -- http://www.linkedin.com/company/openlink-software/
     Twitter  -- http://twitter.com/OpenLink
     Google+  -- http://plus.google.com/100570109519069333827/
     Facebook -- http://www.facebook.com/OpenLinkSoftware
Universal Data Access, Integration, and Management Technology Providers

Re: [Iodbc-list] Understanding server vs. database in ODBC terms

[Tim H. <th...@op...>]

On 06/13/2012 04:28 PM, Maury Markowitz wrote:
> I'm using iODBC as installed on OSX. I'm looking over some sample code in ODBCkit (3rd party) and come across a problem. I'm trying to better understand the nature of issue...
>
> When one opens a connection to a database server, say MSSQL, you do not have to select a *database* within it. One can do that with USE, or in the case of ODBC, by using one of the available flags in the DSN. But what if you don't? You still have a valid *connection*, but you aren't talking to an actual DB... right? Is that legal under ODBC?

You create a connection with certain parameters, normally including the 
database name, many of which are optional, all of which may be stored in 
the DSN or specified in the connect-string to complement the DSN.

If you don't specify a database in the connect-string nor in the DSN, if 
you call SQLDriverConnect() to connect, the driver might prompt the user 
for missing parameters; otherwise you stand a fair chance of the 
connection being rejected or a default database assumed (RDBMS-dependent).

> The code in question assumes such a connection to the DB exists if a connection to the server exists. It then goes on and attempts to read the schema of the DB, which fails. I'd like to fix this, but I see problems...
>
> Does ODBC maintain some sort of state indicating whether it is connected to a DB (in the sense above)?

If you have a connection handle (via SQLAllocConnect() or 
SQLAllocHandle()) on which you have successfully run SQLConnect() or 
SQLDriverConnect(), then you're connected; the first you'll know about 
it is a subsequent API call will fail if you cease to be connected.

> If not, is there an easy way to tell if you are connected to a DB?
 > Does this state change when the user selects a different DB?
 >
 > If not, does the same basic check allow one to notice such a change?

SQLGetInfo(SQL_DATABASE_NAME)  (ODBC 1.x+) or
SQLGetConnectAttr(SQL_ATTR_CURRENT_CATALOG)  (ODBC 3.x+)

If you get a SQL_SUCCESS from that, you are connected, and you know to 
what database(/catalog) into the bargain.

HTH,

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>

[Iodbc-list] Understanding server vs. database in ODBC terms

[Maury M. <mau...@gm...>]

I'm using iODBC as installed on OSX. I'm looking over some sample code in ODBCkit (3rd party) and come across a problem. I'm trying to better understand the nature of issue...

When one opens a connection to a database server, say MSSQL, you do not have to select a *database* within it. One can do that with USE, or in the case of ODBC, by using one of the available flags in the DSN. But what if you don't? You still have a valid *connection*, but you aren't talking to an actual DB... right? Is that legal under ODBC?

The code in question assumes such a connection to the DB exists if a connection to the server exists. It then goes on and attempts to read the schema of the DB, which fails. I'd like to fix this, but I see problems...

Does ODBC maintain some sort of state indicating whether it is connected to a DB (in the sense above)?

If not, is there an easy way to tell if you are connected to a DB?

Does this state change when the user selects a different DB?

If not, does the same basic check allow one to notice such a change?

Thanks!

Re: [Iodbc-list] question about sql syntax thru odbc connection...

[Tim H. <th...@op...>]

On 05/21/2012 03:51 PM, Patrick van Kleef wrote:
> Hi,
>
>>
>> We are writing an SQL client that needs to be able to execute queries against any RDBMS.
>>
>> As far as **connecting** to the database server, ODBC clearly helps wrap this into a single, cross-db API.
>>
>> Now I'm left wondering about the SQL syntax which is used when passed thru an ODBC connection.
>>
>> If we're connected **via ODBC** to Oracle, do we use Oracle's SQL language implementation?
>>
>> Likewise, if we're connected **via ODBC** to MySQL, do we use MySQL's SQL language implementation?
>>
>> etc...
>>
>>
>> ... OR... does iODBC accept only SQL queries that conform to one of the SQL standards: SQL99, SQL2003, SQL2008, SQL2011?  (working out the idiosyncrasies of the particular RDBMS SQL language itself)
>>
>
>
> No, the iODBC Driver Manager as well as the Microsoft Driver Manager on windows, do not limit you to a dumbed down subset of the SQL syntax that all databases can automatically handle. So when you are connected to Oracle, you CAN use the full Oracle syntax including Oracle Stored Procedures. Ditto for Informix, DB/2, Microsoft SQLServer etc.
>
> It does provide you with a load of SQLGetTypeInfo and SQLGetInfo calls to see what kind of syntax the driver you are connected to claims to handle.
>
> These SQLGetInfo calls can be used inside your application to make database specific code that can switch between various syntaxes. However when you code sensibly, you will find that most SELECT and INSERT statements can be written in such a way to be almost completely transparently.

Just as an addition to the above: while all SQL is passed-through 
transparently, you also gain abstractions in the form of ODBC escape 
syntax for date/time types,
{d 'yyyy-mm-dd'},
{ts 'yyyy-mm-dd hh:MM:ss[.ffffff]'}
and procedure calling,
{call some_proc()},
that the driver will translate to native syntaxes for you as well.

HTH,

~Tim
-- 
Tim Haynes
Product Development Consultant
OpenLink Software
<http://www.openlinksw.com/>
<http://twitter.com/openlink>

Re: [Iodbc-list] question about sql syntax thru odbc connection...

[Patrick v. K. <pk...@op...>]

Hi,

> 
> We are writing an SQL client that needs to be able to execute queries against any RDBMS.
> 
> As far as **connecting** to the database server, ODBC clearly helps wrap this into a single, cross-db API.
> 
> Now I'm left wondering about the SQL syntax which is used when passed thru an ODBC connection.
> 
> If we're connected **via ODBC** to Oracle, do we use Oracle's SQL language implementation?
> 
> Likewise, if we're connected **via ODBC** to MySQL, do we use MySQL's SQL language implementation?
> 
> etc...
> 
> 
> ... OR... does iODBC accept only SQL queries that conform to one of the SQL standards: SQL99, SQL2003, SQL2008, SQL2011?  (working out the idiosyncrasies of the particular RDBMS SQL language itself)
> 


No, the iODBC Driver Manager as well as the Microsoft Driver Manager on windows, do not limit you to a dumbed down subset of the SQL syntax that all databases can automatically handle. So when you are connected to Oracle, you CAN use the full Oracle syntax including Oracle Stored Procedures. Ditto for Informix, DB/2, Microsoft SQLServer etc.

It does provide you with a load of SQLGetTypeInfo and SQLGetInfo calls to see what kind of syntax the driver you are connected to claims to handle.

These SQLGetInfo calls can be used inside your application to make database specific code that can switch between various syntaxes. However when you code sensibly, you will find that most SELECT and INSERT statements can be written in such a way to be almost completely transparently.



Patrick
---
iODBC Maintainer

[Iodbc-list] question about sql syntax thru odbc connection...

[<iod...@dy...>]

We are writing an SQL client that needs to be able to execute queries against any RDBMS.

As far as **connecting** to the database server, ODBC clearly helps wrap this into a single, cross-db API.

Now I'm left wondering about the SQL syntax which is used when passed thru an ODBC connection.

If we're connected **via ODBC** to Oracle, do we use Oracle's SQL language implementation?

Likewise, if we're connected **via ODBC** to MySQL, do we use MySQL's SQL language implementation?

etc...


... OR... does iODBC accept only SQL queries that conform to one of the SQL standards: SQL99, SQL2003, SQL2008, SQL2011?  (working out the idiosyncrasies of the particular RDBMS SQL language itself)


Thanks.