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"

Should I post this as a bug?

recast it

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.

Try my opendbx bindings for R2 and see what you get http://rebol.wik.is/OpenDBX

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.

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.

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.

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

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.

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.

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.

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.

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 :(

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.

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?

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

Good work Endo :)

this looks like the same bind parameters that didn't work! :)

Ok, created a howto in my Jira tracker http://jira.rebolsource.net:8080/browse/DBX-2

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 :)