Re: UnixODBC
You don't call unixODBC. You setup a data source for unixODBC, then you reference the datasource as if it was a dataserver. I just went through this exercise. The odbc.ini and odbcinst.ini need to have the correct entries for the source of your data. The name that is created in brackets is the name you pass to DBI. Thank you very much, but there are some technical questions... The entry of my odbc.ini is: [ODBC Data Sources] refdb [refdb] ServerDB=refdb ServerNode= Where I have to fill in: dbi:UnixODBC:refdb I don't have any odbcinst.ini, nor I heard of it. I have virtually no documentaion here and SoftwareAG is not responding to my questions. :-( Can you give me an example how to connect to the database via the UnixODBC? $dbh = DBI-connect ( ? ) Thank you very much. Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
RE: [dbi] Re: UnixODBC
On 09-Sep-2004 Christian Stalp wrote: You don't call unixODBC. You setup a data source for unixODBC, then you reference the datasource as if it was a dataserver. I just went through this exercise. The odbc.ini and odbcinst.ini need to have the correct entries for the source of your data. The name that is created in brackets is the name you pass to DBI. Thank you very much, but there are some technical questions... The entry of my odbc.ini is: [ODBC Data Sources] refdb [refdb] ServerDB=refdb ServerNode= This looks like an iODBC driver manager ini file, you are using the unixODBC driver manager aren't you? unixODBC mirrors ODBC in Windows. You need an odbcinst.ini file which defines your ODBC drivers and an odbc.ini file which defines your data sources. e.g. odbcinst.ini [ODBC] TraceFile = /tmp/sql.log Trace = no [OOB] Description = Easysoft ODBC-ODBC Bridge Driver = /usr/local/easysoft/oob/client/libesoobclient.so Setup = /usr/local/easysoft/oob/client/libesoobsetup.so FileUsage = 5 [PostgreSQL] Description = Postgres SQL Driver Driver = /usr/local/easysoft/unixODBC/lib/libodbcpsql.so.2.0.0 FileUsage = 1 CPTimeout = 120 which defines two drivers, OOB and postgres. odbc.ini [mssqlserver] Description = MS SQL Server Driver = OOB Server = xxx.yyy.zzz Port = TargeDSN = test LogonUser = LogonAuth = [postgres] Description = Test to Postgres Driver = PostgreSQL Trace = No TraceFile = /tmp/sql.log Database= test Servername = xxx.yyy.zzz UserName= Password= Port= 5432 Protocol= 6.4 ReadOnly= No RowVersioning = No ShowSystemTables= No ShowOidColumn = No FakeOidIndex= No ConnSettings= which defines two data sources, one using the OOB ODBC driver and the other using the Postgres ODBC driver. Where I have to fill in: dbi:UnixODBC:refdb This should be dbi:ODBC:refdb. I don't have any odbcinst.ini, nor I heard of it. I have virtually no documentaion here and SoftwareAG is not responding to my questions. :-( See the http://www.unixodbc.org where there are examples of odbc.ini and odbcinst.inio files. Can you give me an example how to connect to the database via the UnixODBC? $dbh = DBI-connect ( ? ) May be if I knew what ODBC driver you are using Martin -- Martin J. Evans Easysoft Ltd, UK Development
Re: UnixODBC
Am Dienstag, 7. September 2004 11:09 schrieben Sie: Hello there, one brief question: needs UnixODBC the DBI or is it totally indipendent? Gruss Christian DBI are Perl related. unixODBC it's a C library. Sure, but there is also a perl-Module. http://search.cpan.org/~rkies/UnixODBC-0.32/UnixODBC.pm And so I ask, is there a way to combine these? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
Re: UnixODBC
I tryed to call UnixODBC via DBI. my $data_source = dbi:UnixODBC:refdb; $dbh = DBI-connect ( $data_source, $user, $password ) or die unable to connect !\n; . and got : install_driver(UnixODBC) failed: Can't locate DBD/UnixODBC.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/5.8.3 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/site_perl/5.8.3 /usr/local/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::UnixODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'UnixODBC' isn't right. Available drivers: Adabas, DBM, ExampleP, File, Proxy, Sponge. at ./dbi-test1.pl line 15 But there is a UnixODBC.pm in /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0/ and this directory is part of @INC. you see: [imserv03]/home/STALP perl -e 'printf %d %s\n, $i++, $_ for @INC' 0 /usr/local/lib/perl5/5.8.3/PA-RISC2.0 1 /usr/local/lib/perl5/5.8.3 2 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 3 /usr/local/lib/perl5/site_perl/5.8.3 4 /usr/local/lib/perl5/site_perl 5 . Why perl don't except the module? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de
Re: UnixODBC
You don't call unixODBC. You setup a data source for unixODBC, then you reference the datasource as if it was a dataserver. I just went through this exercise. The odbc.ini and odbcinst.ini need to have the correct entries for the source of your data. The name that is created in brackets is the name you pass to DBI. Christian Stalp wrote on 9/7/2004, 10:21 AM: I tryed to call UnixODBC via DBI. my $data_source = dbi:UnixODBC:refdb; $dbh = DBI-connect ( $data_source, $user, $password ) or die unable to connect !\n; . and got : install_driver(UnixODBC) failed: Can't locate DBD/UnixODBC.pm in @INC (@INC contains: /usr/local/lib/perl5/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/5.8.3 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 /usr/local/lib/perl5/site_perl/5.8.3 /usr/local/lib/perl5/site_perl .) at (eval 1) line 3. Perhaps the DBD::UnixODBC perl module hasn't been fully installed, or perhaps the capitalisation of 'UnixODBC' isn't right. Available drivers: Adabas, DBM, ExampleP, File, Proxy, Sponge. at ./dbi-test1.pl line 15 But there is a UnixODBC.pm in /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0/ and this directory is part of @INC. you see: [imserv03]/home/STALP perl -e 'printf %d %s\n, $i++, $_ for @INC' 0 /usr/local/lib/perl5/5.8.3/PA-RISC2.0 1 /usr/local/lib/perl5/5.8.3 2 /usr/local/lib/perl5/site_perl/5.8.3/PA-RISC2.0 3 /usr/local/lib/perl5/site_perl/5.8.3 4 /usr/local/lib/perl5/site_perl 5 . Why perl don't except the module? Gruss Christian -- Christian Stalp Institut für Medizinische Biometrie, Epidemiologie und Informatik (IMBEI) Obere Zahlbacher Straße 69 55131 Mainz Tel.: 06131/ 17-6852 E-Mail: [EMAIL PROTECTED] Internet: www.imbei.de -- Your Friendly Neighborhood DBA, Chuck
RE: unixODBC
Hello again, I was able to connect to mysql via isql (I'm having a difficult time with postgresql). Do you think that puts unixODBC in the clear and I should look around at other packages as the perpetrators of this seg fault? Or does postgresql hold a significance that I'm not getting? Since then, I've re-installed freetds 0.60 and the Sybase 11.9.2-1 family of rpms, but same results: [root@ns3 sybase]# isql SQL -v wibble Segmentation fault Cheers, Matt -Original Message- From: Nick Gorham [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 2:27 PM To: [EMAIL PROTECTED] Subject: unixODBC Hi, I would try isql first, there are several reasons you may get a seg fault in ODBCConfig all you need is mismatched QT libs. Try and get a postgres connection working. Nick
RE: unixODBC and isql seg fault
Thanks for the help Nick... If anyone from the freetds arena can add some guidance...that would be wonderful. Here is one last odd quirk that I haven't mentionedI am able to connect to the MS SQL 2k server and query without issue via php 4.2.2! The seg fault occurs when running isql from a bash shell and any perl scripts that use DBI-connect(DBI:ODBC:DSN,'USERname', 'PASS') Per Nick Gorham, here are my gdb results: --- (gdb) r -v SQL Starting program: /usr/bin/isql -v SQL [New Thread 8192 (LWP 14857)] Program received signal SIGSEGV, Segmentation fault. [Switching to Thread 8192 (LWP 14857)] 0x4007b8df in uodbc_get_stats () from /usr/lib/libodbc.so.1 (gdb) quit --- I have no idea what the above message means, so if anyone can shed some light...I would appreciate it. For users on the Freetds list, please see the bottom of this email for the start of this email thread. The issue is a basic seg fault when using isql to connect to a MS SQL 2k server. I have freetds0.60, unixODBC 2.2.2-3, and Sybase 11.9 family of rpms installed on a redhat 7.2 linux. Using perl 5.8.0 with DBI and DBD::ODBC (most up to date versions that cpan will allow on 12/03/02). It should be noted that I have another linux box talking with a MS SQL 2K server using freetds-0.53-1, unixODBC-2.1.1-1, perl-5.6.1-26.72.3. That configuration works flawlessly...so others may want to wait a bit longer until they upgrade to those packages. Thanks, Matt Hello again, I was able to connect to mysql via isql (I'm having a difficult time with postgresql). Do you think that puts unixODBC in the clear and I should look around at other packages as the perpetrators of this seg fault? Or does postgresql hold a significance that I'm not getting? No, its going to be a freetds thing I suspect. I would try running isql under gdb gdb isql r -v dsnname and see where it fails I would try asking on the freetds lists. BTW. I have snipped the reply to list. Nick -Original Message- From: Matthew Bartholomew Sent: Tuesday, December 03, 2002 4:23 PM To: 'Nick Gorham' Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: unixODBC Hello again, I was able to connect to mysql via isql (I'm having a difficult time with postgresql). Do you think that puts unixODBC in the clear and I should look around at other packages as the perpetrators of this seg fault? Or does postgresql hold a significance that I'm not getting? Since then, I've re-installed freetds 0.60 and the Sybase 11.9.2-1 family of rpms, but same results: [root@ns3 sybase]# isql SQL -v wibble Segmentation fault Cheers, Matt -Original Message- From: Nick Gorham [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 03, 2002 2:27 PM To: [EMAIL PROTECTED] Subject: unixODBC Hi, I would try isql first, there are several reasons you may get a seg fault in ODBCConfig all you need is mismatched QT libs. Try and get a postgres connection working. Nick --- For those on the freetds list...here is the start of this thread: From: Matthew Bartholomew Sent: Tuesday, December 03, 2002 4:23 PM To: 'Nick Gorham' Cc: '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]'; '[EMAIL PROTECTED]' Subject: RE: unixODBC Not sure what to do from here...any advice would be great. Thanks... -Matt [root@ns3 root]# isql -v Segmentation fault [root@ns3 build]# isql -b Segmentation fault [root@ns3 build]# isql -c Segmentation fault [root@ns3 build]# isql -w Segmentation fault [root@ns3 build]# isql -mn Segmentation fault .you get the idea...isql seg faults on most every command. --version is the only one that doesn't seg fault. I upgrade to unixODBC 2.2.2 in hopes of fixing this, but no avail. Also, I've tried to recompile DBD::ODBC but make test fails...probably because unixodbc is seg faulting when called (see below for that error..and it should be noted that I removed the s from the makefile). Here is what I am running: RedHat 7.2 kernel 2.4.7-10 perl 5.8 unixODBC-2.2.2-3 freetds-0.60-1 glibc-2.2.93-5 DBD::ODBC 0.43 DBI is up to date (Database was generated on Mon, 02 Dec 2002 11:54:12 GMT --- I don't know the version #) ODBCHOME=/usr and the correct DSN settings are in my environment -- odbcinst.ini -- [SQL] Description = v0.52 with protocol v4.2 Driver = /usr/lib/libtdsodbc.so FileUsage = 2 [TXT] Description = Text file driver Driver = /usr/lib/libodbctxt.so Setup = /usr/lib/libodbctxtS.so FileUsage = 2 [PostgreSQL] Description = PostgreSQL driver Driver = /usr/lib/libodbpsql.so Setup = /usr/lib/libodbpsqlS.so FileUsage = 2 -- [root@ns3 DBD-ODBC-0.43]# make test TEST_VERBOSE=1 PERL_DL_NONLAZY=1 /usr/local/bin/perl
RE: unixODBC and DBD::ODBC fails to connect
Make sure the permissions of the web server user allow access to the drives, ini files, etc. I don't know much more abot freeTDS, though, so I'd probably start there to find out what caused the error. Also, since you don't say which version of DBD::ODBC you are using, I know there were some connect fixes which allowed showing better error messages in the latest versions of DBD::ODBC. Also, a quick fix may be to change FreeTDS's LogError in odbc.c to log to a file the specific error messages. You could hack it in quickly and take it out when the problem is resolved. Regards, Jeff I'm sure I'm overlooking something really simple, but I can't find any documentation to help troubleshoot my problem. When running a cgi script that uses DBI and the DBD::ODBC pm, this is error message is deposited into my www error logs. DBI-connect(SQL) failed: [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100 Connecting : [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100. Here are my specs: RedHat 7.2 unixODBC, Freetds, and DBD::ODBC As you can see below, I am able to connect to the database using the isql command. [root@server root]# isql SQL USER PASS +---+ | Connected!| | | | sql-statement | | help [tablename] | | quit | | | +---+ # Here's a sample of my code: $dbh = DBI-connect(dbi:ODBC:SQL, $user, $passwd) or die Connecting : $DBI::errstr\n ; $dbh-do(use $db) or die $dbh-errstr; Thanks for any suggestion and/or help! Matt Bartholomew
RE: unixODBC and DBD::ODBC fails to connect
I've changed the ownership of odbc.ini and odbcinst.ini to my www user, but to no avail. I also have freetds logging all error messages, but nothing is logged to the freetds file when this cgi file is run. When I run isql, freetds logs that transaction with no problem. BTW, here are the version numbers that I'm running: Freetds .60 UnixODBC 2.1.1-1 DBD::ODBC 0.43 DBI-connect(SQL) failed: [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at cgiscript.cgi line 100 Connecting : [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at cgiscript.cgi line 100. -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 26, 2002 9:54 AM To: Matthew Bartholomew; [EMAIL PROTECTED] Subject: RE: unixODBC and DBD::ODBC fails to connect Make sure the permissions of the web server user allow access to the drives, ini files, etc. I don't know much more abot freeTDS, though, so I'd probably start there to find out what caused the error. Also, since you don't say which version of DBD::ODBC you are using, I know there were some connect fixes which allowed showing better error messages in the latest versions of DBD::ODBC. Also, a quick fix may be to change FreeTDS's LogError in odbc.c to log to a file the specific error messages. You could hack it in quickly and take it out when the problem is resolved. Regards, Jeff I'm sure I'm overlooking something really simple, but I can't find any documentation to help troubleshoot my problem. When running a cgi script that uses DBI and the DBD::ODBC pm, this is error message is deposited into my www error logs. DBI-connect(SQL) failed: [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100 Connecting : [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100. Here are my specs: RedHat 7.2 unixODBC, Freetds, and DBD::ODBC As you can see below, I am able to connect to the database using the isql command. [root@server root]# isql SQL USER PASS +---+ | Connected!| | | | sql-statement | | help [tablename] | | quit | | | +---+ # Here's a sample of my code: $dbh = DBI-connect(dbi:ODBC:SQL, $user, $passwd) or die Connecting : $DBI::errstr\n ; $dbh-do(use $db) or die $dbh-errstr; Thanks for any suggestion and/or help! Matt Bartholomew
RE: unixODBC and DBD::ODBC fails to connect
Matthew, You are stomping on shaky ground. The last I tried UnixODBC and FreeTDS was ..53 and I had issues. I haven't tried .60, but I'd suggest doing the following: 1) Login as your www user (if possible) and try isql. 2) adding a logging function in FreeTDS's odbc.c in the LogError function which dumps the errors to a file in /tmp, say. 3) setting the DBI trace level to 8 and running the the script (in the code, before the connect, DBI-trace(8, /tmp/dbitrace.log) works, I think... Regards, Jeff -Original Message- From: Matthew Bartholomew [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 26, 2002 11:38 AM To: 'Jeff Urlwin' Cc: [EMAIL PROTECTED] Subject: RE: unixODBC and DBD::ODBC fails to connect I've changed the ownership of odbc.ini and odbcinst.ini to my www user, but to no avail. I also have freetds logging all error messages, but nothing is logged to the freetds file when this cgi file is run. When I run isql, freetds logs that transaction with no problem. BTW, here are the version numbers that I'm running: Freetds .60 UnixODBC 2.1.1-1 DBD::ODBC 0.43 DBI-connect(SQL) failed: [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at cgiscript.cgi line 100 Connecting : [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at cgiscript.cgi line 100. -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 26, 2002 9:54 AM To: Matthew Bartholomew; [EMAIL PROTECTED] Subject: RE: unixODBC and DBD::ODBC fails to connect Make sure the permissions of the web server user allow access to the drives, ini files, etc. I don't know much more abot freeTDS, though, so I'd probably start there to find out what caused the error. Also, since you don't say which version of DBD::ODBC you are using, I know there were some connect fixes which allowed showing better error messages in the latest versions of DBD::ODBC. Also, a quick fix may be to change FreeTDS's LogError in odbc.c to log to a file the specific error messages. You could hack it in quickly and take it out when the problem is resolved. Regards, Jeff I'm sure I'm overlooking something really simple, but I can't find any documentation to help troubleshoot my problem. When running a cgi script that uses DBI and the DBD::ODBC pm, this is error message is deposited into my www error logs. DBI-connect(SQL) failed: [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100 Connecting : [unixODBC]tds_connect failed (SQL-08001)(DBD: db_login/SQLConnect err=-1) at /cgiscript.cgi line 100. Here are my specs: RedHat 7.2 unixODBC, Freetds, and DBD::ODBC As you can see below, I am able to connect to the database using the isql command. [root@server root]# isql SQL USER PASS +---+ | Connected!| | | | sql-statement | | help [tablename] | | quit | | | +---+ # Here's a sample of my code: $dbh = DBI-connect(dbi:ODBC:SQL, $user, $passwd) or die Connecting : $DBI::errstr\n ; $dbh-do(use $db) or die $dbh-errstr; Thanks for any suggestion and/or help! Matt Bartholomew
RE: [unixODBC-DEV] - Re: String data, right truncation error int
The water is more murky now... On 30-Jan-2002 Jeff Urlwin wrote: Tim, Ok -- that was a big help in describing the problem and letting me understand it. Right now, here's the situation: DBD::ODBC *assumes* that all binds are of type VARCHAR and only when overidden on the bind_col will it actually change that. Your column, in this case, is a SQL_LONGVARCHAR. The current method of handling that is to over-ride the type in the bind call. Yes, that is how I saw it and why I suggested to Tim, to override the parameter type but knowing what he is doing now that is not possible as he does not know the parameter type. However, with SQLColumns support and knowing which table you are inserting into, you can detect it and use code that similar to t/09bind.t and odbctest.pm. It's PAINFUL the first time G. I don't see an easy way for DBD::ODBC to handle all this for you given what I've seen from the drivers I have. HOWEVER: There is code in there to do the work, we just have to allow it to occur. Basically if the SQL_TYPE comes in as 0, then it's currently being set to SQL_VARCHAR. There is code, in dbd_rebind_ph, that checks to see if the sql_type is 0 and performs the check of the column type with SQLDescribeParam. Altogether a good thing, but it makes me nervous as if SQLDescribeParam fails, the the code issues an error which would stop current programs from working. That's the problem from my perspective. It will break, most likely, someone's code. Here's what I propose: - I patch for attempting to use SQLDescribeParam if SQLDescribeParam fails, we revert back to the current assumption of SQL_VARCHAR. Sounds good to me (although see below) so long as we remember that some drivers do not have SQLDescribeParam (e.g. MSAccess/Jet). I notice SQLDescribeParam is called whatever and not after ascertaining if the driver has SQLDescribeParam (there is already one call to SQLGetFunctions to see if the driver has SQLMoreResults - shouldn't this be done for SQLDescribeParam too and perhaps only the first time required and not every time). Right on both, yes, it shouldn't call it every time and it probably won't. Er, well, one time per column of course. What happens in multiple times through the same statement, the sql_type will be non zero and it will not call SQLDescribeParam again for that column. Yes I found the hard way that Access doesn't support it, so if SQLDescribeParam fails, I suck up the error messages (thanks to code from you!) and set the type to SQL_VARCHAR. Unfortunately, there's a more insidious problem: Oracle's ODBC driver (8.1.7) returns VARCHAR for all columns so far. The sad thing is, it then doesn't do the right thing with the date/timestamp field. If you bind with SQL_TIMESTAMP, it works, but if you bind with VARCHAR (as SQLDescribeParam reports), it doesn't work...sigh. I put the tests in t/09bint.t, so it's reproducable. I haven't tried SQL Server yet, but I suspect it's going to support it best of my options. There may be issues, though, with data conversions this way. I'm not sure, but there is a comment in the code indicating there may be an issue with the code. I'll try to test it with the data that I have (Access and Oracle for sure, I'm working on a SQL Server 2000 machine now, too. Is the comment you are referring to: /* XXX This will fail (IM001) on drivers which don't support it. We need to check for this and bind the param as varchars. This will work on many drivers and databases. If the database won't convert a varchar to an int (for example) the user will get an error at execute time but can add an explicit conversion to the SQL: ... where num_field int(?) ... Yep. According to the table in the ODBC 3.0 reference, conversions from SQL_C_CHAR to any other data type should be supported. Except, see my notes above about Oracle and the DATE/Timestamp field. and in a prevoius email Jeff said: Also, I'm missing something (history, etc) here with the TimeStamp parameter issue. The trace should help me greatly. When looking at the OOB log of Tim's script I saw the following call: SQLBindParameter(0x81fe1e8,9,1(Input::),1,12,80,11,0x82048f8,11,0x81ff5e4) Parameter 9 was bound for input as an SQL_C_CHAR(1) to be interpreted as an SQL_VARCHAR(12) with a ColumnSize of 80, DecimalDigits of 11 and BufferLength of 11. What concerned me was the DecimalDigits of 11. According to ODBC spec if the parameter type is SQL_TYPE_TIME, SQL_TYPE_TIMESTAMP, SQL_INTERVAL_XXX (where XXX includes SECOND) the DecimalDigits value sets the Precision field in the IPD. Now, in this case ParameterType is SQL_VARCHAR so I don't think it matters but if you make the proposed change above then SQLDescribeParam will come back in Tim's case with SQL_TYPE_TIMESTAMP in which case DecimalDigits is describing the precision of the
RE: [unixODBC-DEV] - Re: String data, right truncation error int
Tim, Ok -- that was a big help in describing the problem and letting me understand it. Right now, here's the situation: DBD::ODBC *assumes* that all binds are of type VARCHAR and only when overidden on the bind_col will it actually change that. Your column, in this case, is a SQL_LONGVARCHAR. The current method of handling that is to over-ride the type in the bind call. HOWEVER: There is code in there to do the work, we just have to allow it to occur. Basically if the SQL_TYPE comes in as 0, then it's currently being set to SQL_VARCHAR. There is code, in dbd_rebind_ph, that checks to see if the sql_type is 0 and performs the check of the column type with SQLDescribeParam. Altogether a good thing, but it makes me nervous as if SQLDescribeParam fails, the the code issues an error which would stop current programs from working. That's the problem from my perspective. It will break, most likely, someone's code. Here's what I propose: - I patch for attempting to use SQLDescribeParam if SQLDescribeParam fails, we revert back to the current assumption of SQL_VARCHAR. There may be issues, though, with data conversions this way. I'm not sure, but there is a comment in the code indicating there may be an issue with the code. I'll try to test it with the data that I have (Access and Oracle for sure, I'm working on a SQL Server 2000 machine now, too. Any comments? Jeff -Original Message- From: Tim Harsch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 8:35 PM To: Jeff Urlwin; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeff Urlwin Subject: RE: [unixODBC-DEV] - Re: String data, right truncation error int Here it is! and thanks for helping! --- Jeff Urlwin [EMAIL PROTECTED] wrote: Tim, Can you get me a DBI trace (level 9) of the specific run? I'm looking to see how we detected the column after the call to SQLColAttributes. There are lines which will show: col N: some info... SQLDescribeParam %s: SqlType=%s, ColDef=%d Unless I'm missing something: I *think* that we're getting the SQL_VARCHAR from SQLDescribeParam()...not SQL_LONGVARCHAR, in which case the bind will have to be over-ridden in Perl as suggested below. If I'm right, then SQLDescribeParam will show SQLType as VARCHAR, not LONG VARCHAR... Also, I'm missing something (history, etc) here with the TimeStamp parameter issue. The trace should help me greatly. Regards, Jeff -Original Message- From: Tim Harsch [mailto:[EMAIL PROTECTED]] Sent: Tuesday, January 29, 2002 2:00 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; Jeff Urlwin Subject: Re: [unixODBC-DEV] - Re: String data, right truncation error int --- [EMAIL PROTECTED] wrote: On 28-Jan-2002 Tim Harsch wrote: Hi Martin, Sorry to take so long to back to you. I generally work once every two weeks on this project, for a consulting gig I'm doing. Although, I will be here today and tomorrow this week. Below is the log you asked for in your reply to my easysoft.beta.unixodbc post. Also, can you remind me what command to run to determine versions of Server and Client. Thanks. Is your app Perl by any chance? Examining your log file suggests your schema might be something like this: create table timharsch ( sequence_id integer, bio_oject integer, person_id integer, sequence_type varchar(30), sequence_source varchar(30), sequence text, start integer, stop integer, mod_date datetime) Yes? Yes. See previous Reply. I have duplicated your app with some C code and got the same problem. I think you will find the problem is parameter 6. SQLPrepare(0x81fe1e8,0x81fe548,52) SQLPrepare: insert into timtest values ( ?,?,?,?,?,?,?,?,?) cache_param_descriptions(0x81fe1e8) remote sql_num_params()=0 (return parameters=9) P1 datatype=4 size=10 dd=0 nullable=0 P2 datatype=4 size=10 dd=0 nullable=0 P3 datatype=4 size=10 dd=0 nullable=0 P4 datatype=12 size=30 dd=0 nullable=0 P5 datatype=12 size=30 dd=0 nullable=0 P6 datatype=-1 size=2147483647 dd=0 nullable=0 Column 6 is a Text column. P7 datatype=4 size=10 dd=0 nullable=1 P8 datatype=4 size=10 dd=0 nullable=1 P9 datatype=11 size=16 dd=0 nullable=0 snipped stuff SQLBindParameter(0x81fe1e8,6,1(Input::),1,12,9923,9923,0x82051c0,9 923,0x81ff4c 4) ^oobc_expand_desc_recs(0x81fe348,6) ^-oobc_expand_desc_recs()=0x8205080 ^oobc_expand_desc_recs(0x81ff158,6
Re: unixODBC and FreeTDS to MS SQL Server.
Jeff Urlwin wrote: Well, I'm not especially solid on the FreeTDS. I did not realize that FreeTDS supplied an ODBC driver. I thought that FreeTDS was to be used with DBD::Sybase to talk to SQL Server They do have a ODBC driver, but it is basic at the moment, the DBD:Sybase option would be better supported, as the freeTDS coders are not that focused on ODBC. Jeff Hi I am having problems working with the DBD::ODBC driver. I am using unixODBC and FreeTDS connecting to MS SQL Server. They work well together and I have tested it. THe problem comes when I try to use Perl DBD::ODBC (version 0.28). So, I know my DSN entery is ok. This is the code (5 lines) and the Error I get. CAn you please tell me why this may happen? I have tried searching for a week now. #!/usr/bin/perl use DBI; use DBD::ODBC; my $DSN ='driver={myserver};hostname=x.y.x;port=200;database=X;uid=x;pwd=x;'; my $dbh= DBI-connect(dbi::ODBC:$DSN) or die $DBI::errstr\n; If you are using driver={} then it needs to be the entry in odbcinst.ini not in odbc.ini, So I would expect either driver={TDS};... or dsn=myserver;... The error I get is DBI-connect() failed:[unixODBC][Drver Manager] Data source name not found, and no default driver specified (SQL-IM002). I have set the $DBI_DSN variable to dbi:odbc:myserver I know the unixODBC and the free TDS have been installed right. Can you help me with this? Thanks Amit -- Nick Gorham Emacs would be a great operating system if it just had a decent text editor...
RE: unixODBC and FreeTDS to MS SQL Server.
Well, I'm not especially solid on the FreeTDS. I did not realize that FreeTDS supplied an ODBC driver. I thought that FreeTDS was to be used with DBD::Sybase to talk to SQL Server. Jeff Hi I am having problems working with the DBD::ODBC driver. I am using unixODBC and FreeTDS connecting to MS SQL Server. They work well together and I have tested it. THe problem comes when I try to use Perl DBD::ODBC (version 0.28). So, I know my DSN entery is ok. This is the code (5 lines) and the Error I get. CAn you please tell me why this may happen? I have tried searching for a week now. #!/usr/bin/perl use DBI; use DBD::ODBC; my $DSN ='driver={myserver};hostname=x.y.x;port=200;database=X;uid=x;pwd=x;'; my $dbh= DBI-connect(dbi::ODBC:$DSN) or die $DBI::errstr\n; The error I get is DBI-connect() failed:[unixODBC][Drver Manager] Data source name not found, and no default driver specified (SQL-IM002). I have set the $DBI_DSN variable to dbi:odbc:myserver I know the unixODBC and the free TDS have been installed right. Can you help me with this? Thanks Amit
RE: unixODBC and FreeTDS to MS SQL Server.
You have you use version 0.91 of DBD::Sybase to connect to a mysql server. I am using Freetds version 0.51. This setup works fine and no odbc driver is needed just DBD::Sybase. keith _ Keith A. Clay, [EMAIL PROTECTED] Abilene Christian University Lead Programmer, Web Integration and Programming 286 Adams Center for Teaching Excellence (915) 674-2187 (915) 674-2834 (fax) _ ...continue to walk like that One walked... -Original Message- From: Jeff Urlwin [mailto:[EMAIL PROTECTED]] Sent: Friday, October 26, 2001 7:48 PM To: Amit Gandre; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: unixODBC and FreeTDS to MS SQL Server. Well, I'm not especially solid on the FreeTDS. I did not realize that FreeTDS supplied an ODBC driver. I thought that FreeTDS was to be used with DBD::Sybase to talk to SQL Server. Jeff Hi I am having problems working with the DBD::ODBC driver. I am using unixODBC and FreeTDS connecting to MS SQL Server. They work well together and I have tested it. THe problem comes when I try to use Perl DBD::ODBC (version 0.28). So, I know my DSN entery is ok. This is the code (5 lines) and the Error I get. CAn you please tell me why this may happen? I have tried searching for a week now. #!/usr/bin/perl use DBI; use DBD::ODBC; my $DSN ='driver={myserver};hostname=x.y.x;port=200;database=X;uid=x;pwd=x;'; my $dbh= DBI-connect(dbi::ODBC:$DSN) or die $DBI::errstr\n; The error I get is DBI-connect() failed:[unixODBC][Drver Manager] Data source name not found, and no default driver specified (SQL-IM002). I have set the $DBI_DSN variable to dbi:odbc:myserver I know the unixODBC and the free TDS have been installed right. Can you help me with this? Thanks Amit
Re: [unixODBC-DEV] - DBI::ODBC unixODBC timestamp problem
[EMAIL PROTECTED] wrote: denblueuser@dbi:ODBC:SYBTEST select VALUE_DATE from TRADE_BASIC/ VALUE_DATE '2001-01-02 00:00:00.00.' [snip] When I looked with the perl debugger the final chars were \c@. Has anyone else come across this? Is there a work around? Could you provide a ODBC trace of whats going on, I am tempted to suggest it is a driver issue, the driver manager doesn't involve itself with data returned from the driver, but the trace would help verify just what is going wrong. -- Nick Gorham Easysoft Ltd