ODBC returns none for smalldatetime columns
ODBC returns none for smalldatetime columns on SQL Server 2008 (don't know for other versions)
insert db "select my_sdate from mytable"
copy db
>> [[none] [none] [none]] ;for 3 rows
But it is ok for datetime values.
Same result for ODBC driver "SQL Server" and "SQL Native Driver"
posted by: Endo 24-Jun-2010/7:44:35-7:00
Should I post this as a bug?
posted by: Endo 6-Jul-2010/3:08:49-7:00
recast it
posted by: Graham 6-Jul-2010/22:56:04-7:00
Here is the situation:
create table tmp (d smalldatetime)
insert into tmp values(getdate())
select * from tmp
--> '2010-07-07 10:20:00'
When you read it using R2/View console (2.7.7)
>> db: first open odbc://mytmpDSN
>> insert db "SELECT * FROM tmp"
>> probe copy db
== [[none]]
I tried this on SQL Server 2008 with SQL Server ODBC driver and also SQL Native Driver. Same result.
When the column type is datetime instead of smalldatetime then it works as expected.
posted by: Endo 7-Jul-2010/3:25:51-7:00
Try my opendbx bindings for R2 and see what you get
http://rebol.wik.is/OpenDBX
posted by: Graham 8-Jul-2010/4:18:38-7:00
I coudn't make it work with SQL Server.
r1: odbx_init db: create-db-handle "mssql" "(local)\SQLEXPRESS" "1433"
returns -6 which means,
>> odbx_error db/ptr -6
== "Loading backend library failed"
I don't know why, but it says on its web site it doesn't support date values on SQL Server. So I don't think its gonna work.
posted by: Endo 9-Jul-2010/11:27:48-7:00
the default distro provides about 6 support libraries in a sub directory. Move those dlls to the same directory as rebol. That's why it can't open the backend library.
posted by: Graham 10-Jul-2010/16:45:02-7:00
Since you must be on Windows, you should use odbc instead of mssql for opendbx. If on linux, then you would use freetds to connect to the sql server.
posted by: Graham 10-Jul-2010/16:54:12-7:00
Unfortunately I still can't make it work:
>> probe r1: odbx_init db: create-db-handle "odbc" "(local)\SQLEXPRESS" "1433"
print odbx_error r1
==Invalid handle
posted by: Endo 12-Jul-2010/10:06:57-7:00
That's an improvement. You now have the odbc driver loaded, but it seems you haven't specified a valid DSN so it can't open the database.
posted by: Graham 12-Jul-2010/14:28:46-7:00
DSN? I thought that it is the server name. There is a SQLEXPRESS named instance of SQL server on my PC. So the address should be (local)\SQLEXPRESS. But I also tried (loca), IP address etc.
I'll try with a DSN tomorow.
posted by: Endo 12-Jul-2010/17:44:57-7:00
I would think that if you use the ODBC driver, you have to point to an ODBC DSN.
I downloaded sql server express 2008 but couldn't figure out to create a dsn in the admin control panel.
posted by: Graham 12-Jul-2010/21:39:33-7:00
If you already installed SQL Express its default instance name is SQLEXPRESS. So you need to create a DSN from Control Panel / Administrative Tools / Data Sources (ODBC DSN), type (local)\SQLEXPRESS as Server Name.
You can also try if its working, from OS command prompt:
OSQL.exe -S (local)\SQLEXPRESS -E
1>
I recommend you to download and install SQL Management Studio which is a separate download, and it is free also.
So you can manage you SQL Server and databases more easily.
For example you may want to change the Server Authentication mode to "SQL Server and Windows Authentication mode" to be able to connect username & password. Otherwise you can only connect by a Windows user (Windows Authentication mode)
Second, enable the TCP/IP under SQL Server Configuration -> SQL Server Network Configuration -> Protocols for SQLEXPRESS
Then allow for port 1433 on your Firewall.
posted by: Endo 13-Jul-2010/3:21:24-7:00
Small progress:
r1: odbx_init db: create-db-handle "odbc" "myDSN" ""
print odbx_error r1
== Success
r2: odbx_bind db/ptr "master" "sa" "sa" 0
print odbx_error r2
==Invalid parameter
:(
posted by: Endo 13-Jul-2010/4:49:30-7:00
I got it work, here is the result:
my table scheme is below, it is on SQL Server 2008 Express, my table has new datatypes like date and time:
CREATE TABLE [test] (
a [smalldatetime],
b [datetime],
c [date],
d [time](7)
)
Result of the dbx.r script:
name: a
type: 50
length: 19
value: 2010-07-13 12:04:00
name: b
type: 50
length: 23
value: 2010-07-13 12:03:42
name: c
type: 255
length: 10
value: 2010-07-13
name: d
type: 255
length: 16
value: 12:03:42.2100000
false
Characters read: -13
CLOB data:
It looks it works well for all the types. Just doesn't give the miliseconds for datetime and time values which is mostly not important.
Thanks a lot Graham.
posted by: Endo 13-Jul-2010/5:22:50-7:00
I had SQL management studio already and created a test table. But osql does not connect. I don't know whether the issue is because I have it running as a manually configured service or what. tcp/ip is also enabled. But since you got it working I can remove it now from my system.
I'll add your example to the docs. What were the bind parameters that worked?
posted by: Graham 13-Jul-2010/19:05:10-7:00
Here is my init & bind parameters:
Create a DSN named testDSN to connect your database, select database you've created as default database.
r1: odbx_init db: create-db-handle "odbc" "testDSN" ""
print odbx_error db/ptr r1 ;this should write Success
;dbname, username, password, 0
r2: odbx_bind db/ptr "test" "sa" "sa" 0
print odbx_error db/ptr r2 ;Success
;If you selected "Windows NT Integrated Authentication" in your DSN then this username/password will not be used. But then you have to have rights to connect your SQL (Administrator for ex.)
;If you selected SQL Server Authentication, then you server should be configured as "Mixed Mode Authentication" (which accepts both Windows NT Users and SQL Users)
You can also try your DSN from OS command prompt:
OSQL.exe -D testDSN -Usa -Psa
posted by: Endo 14-Jul-2010/3:12:59-7:00
Good work Endo :)
posted by: Nick 14-Jul-2010/3:26:47-7:00
this looks like the same bind parameters that didn't work! :)
posted by: Graham 14-Jul-2010/5:56:53-7:00
Ok, created a howto in my Jira tracker
http://jira.rebolsource.net:8080/browse/DBX-2
posted by: Graham 14-Jul-2010/6:02:10-7:00
Your DSN was misconfigured may be.
Btw, look at my post above starting with "Small Progress", there is a line:
print odbx_error r2
which looks ok but actually not, odbx_error gets 2 parameters not 1!! So that was the major problem of my failures :)
posted by: Endo 14-Jul-2010/6:37:23-7:00
|