www.digitalmars.com         C & C++   DMDScript  

digitalmars.D.learn - A lot of people want to use D,but they only know MS SQL Server,what

reply " FrankLike" <1150015857 qq.com> writes:
Hello,everyone:

A lot of people want to use D,but they only know MS SQL 
Server,what will help them to Learn D?

So lots of people want to use D,who can help them?
They want to connect MS SQL Server in D,then they will connect 
other DataBase,
because it's a good idea that nice thing come from the small and 
familiar things always.
Sql driver or ODBC driver, for win7 in D,
who can help them?

Thank you,everyone.
Apr 14 2014
next sibling parent reply "Dejan Lekic" <dejan.lekic gmail.com> writes:
On Monday, 14 April 2014 at 15:21:33 UTC, FrankLike wrote:

 Hello,everyone:

 A lot of people want to use D,but they only know MS SQL 
 Server,what will help them to Learn D?

 So lots of people want to use D,who can help them?
 They want to connect MS SQL Server in D,then they will connect 
 other DataBase,
 because it's a good idea that nice thing come from the small 
 and familiar things always.
 Sql driver or ODBC driver, for win7 in D,
 who can help them?

 Thank you,everyone.


First thing a D programmer MUST do is

1) To port FreeTDS to D, or write a binding/wrapper for it 
(should not be too difficult).

or

2) Use ODBC directly, or maybe also write some wrapper around it.

or

3) Implement D connector to MS SQL server directly (I would 
advise against that, such project would probably be 10x bigger 
than the DMD project itself)

My advice - use ODBC, it is the fastest way you may connect to 
the SQL server, and you already have everything you need for 
that. :)

Regards
Apr 14 2014
next sibling parent " FrankLike" <1150015857 qq.com> writes:
 First thing a D programmer MUST do is

 1) To port FreeTDS to D, or write a binding/wrapper for it 
 (should not be too difficult).

 or

 2) Use ODBC directly, or maybe also write some wrapper around 
 it.

 or

 3) Implement D connector to MS SQL server directly (I would 
 advise against that, such project would probably be 10x bigger 
 than the DMD project itself)

 My advice - use ODBC, it is the fastest way you may connect to 
 the SQL server, and you already have everything you need for 
 that. :)

 Regards


Thank you.

1)  I have used the  binding/wrapper for FreeTDS,I have tested , 
but can't get the OMF lib,and can't get the ctlib's dll,so  no 
end.
2) I test the ODBC32 ON WIN7,but not have the right OMF lib, I 
test the implib ,
but do not get the right OMF lib ,then I not get any thing.

Maybe my implib tool is error? The implib tool's created time is 
2000-05-05,I know it's old,but I can't get any lastest.

Hope you can help to me,
Thank again.
Apr 14 2014
prev sibling parent reply " FrankLike" <1150015857 qq.com> writes:
 My advice - use ODBC, it is the fastest way you may connect to 
 the SQL server, and you already have everything you need for 
 that. :)

 Regards


I have test the d\dmd2\windows\lib\odbc32.lib,the size is 4.5kb,
I test it by test.d(build :dmd test.d)
but find the error:
Error 42:Symbol Undefined _SQLFreeHandle 8
Error 42:Symbol Undefined _SQLSetEnvAttr 16
Error 42:Symbol Undefined _SQLAllocHandle 12
Error 42:Symbol Undefined _SQLGetDiagRec 32
-- errorlevel 4

build mssql.lib :dmd -lib mssql.d database.d -Itrunk/win32/sql.d 
-Itrunk/win32/sqlext.d -IODBC32.lib -Iwin32.lib
build test.exe  :dmd test.d
-----------------------------module test.d--------------------
module test;
import std.stdio;
import arsd.mssql;
pragma(lib,"mssql");
void main() {
	try{
	//auto db = new MsSql("Driver={SQL 
Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes");
	auto db = new MsSql("Driver={SQL Server Native Client 
10.0};Server=127.0.0.1;Database=test;Trusted_Connection=Yes");

	//db.query("INSERT INTO users (id, name) values (30, 'hello 
mang')");
        db.query("SELECT top 10 * FROM testa");
	
	}
	catch(Exception e){
		auto f = new File("err.text","w");
		scope(exit)f.close();
		f.writeln(e.info);
		
	}
}
--------------------------------------------module 
mssql.d-----------

// NOTE: I haven't even tried to use this for a test yet!
// It's probably godawful, if it works at all.

module arsd.mssql;
pragma(lib, "odbc32");

public import arsd.database;

import std.string;
import std.exception;

import win32.sql;
import win32.sqlext;

class MsSql : Database {
	// dbname = name  is probably the most common connection string
	this(string connectionString) {
		SQLAllocHandle(SQL_HANDLE_ENV, cast(void*)SQL_NULL_HANDLE, 
&env);
		enforce(env !is null);
		scope(failure)
			SQLFreeHandle(SQL_HANDLE_ENV, env);
		SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, cast(void *) 
SQL_OV_ODBC3, 0);
		SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
		scope(failure)
			SQLFreeHandle(SQL_HANDLE_DBC, conn);
		enforce(conn !is null);

		auto ret = SQLDriverConnect(
			conn, null, cast(ubyte*)connectionString.ptr, SQL_NTS,
			null, 0, null,
			SQL_DRIVER_NOPROMPT );

		if ((ret != SQL_SUCCESS_WITH_INFO) && (ret != SQL_SUCCESS))
			throw new DatabaseException("Unable to connect to ODBC object: 
" ~ getSQLError(SQL_HANDLE_DBC, conn)); // FIXME: print error

		//query("SET NAMES 'utf8'"); // D does everything with utf8
	}

	~this() {
		SQLDisconnect(conn);
		SQLFreeHandle(SQL_HANDLE_DBC, conn);
		SQLFreeHandle(SQL_HANDLE_ENV, env);
	}

	override void startTransaction() {
		query("START TRANSACTION");
	}

	ResultSet queryImpl(string sql, Variant[] args...) {
		sql = escapedVariants(this, sql, args);

		// this is passed to MsSqlResult to control
		SQLHSTMT statement;
		auto returned = SQLAllocHandle(SQL_HANDLE_STMT, conn, 
&statement);

		enforce(returned == SQL_SUCCESS);

		returned = SQLExecDirect(statement, cast(ubyte*)sql.ptr, 
SQL_NTS);
		if(returned != SQL_SUCCESS)
			throw new DatabaseException(error());

		return new MsSqlResult(statement);
	}

	string escape(string sqlData) { // FIXME
		return ""; //FIX ME
		//return ret.replace("'", "''");
	}


	string error() {
		return null; // FIXME
	}

	private:
		SQLHENV env;
		SQLHDBC conn;
}

class MsSqlResult : ResultSet {
	// name for associative array to result index
	int getFieldIndex(string field) {
		if(mapping is null)
			makeFieldMapping();
		if (field !in mapping)
			return -1;
		return mapping[field];
	}


	string[] fieldNames() {
		if(mapping is null)
			makeFieldMapping();
		return columnNames;
	}

	// this is a range that can offer other ranges to access it
	bool empty() {
		return isEmpty;
	}

	Row front() {
		return row;
	}

	void popFront() {
		if(!isEmpty)
			fetchNext;
	}

	int length()
	{
		return 1; //FIX ME
	}
	
	this(SQLHSTMT statement) {
		this.statement = statement;

		SQLSMALLINT info;
		SQLNumResultCols(statement, &info);
		numFields = info;

		fetchNext();
	}

	~this() {
		SQLFreeHandle(SQL_HANDLE_STMT, statement);
	}

	private:
		SQLHSTMT statement;
		int[string] mapping;
		string[] columnNames;
		int numFields;

		bool isEmpty;

		Row row;

		void fetchNext() {
			if(isEmpty)
				return;

			if(SQLFetch(statement) == SQL_SUCCESS) {
				Row r;
				r.resultSet = this;
				string[] row;

				SQLLEN ptr;

				for(int i = 0; i < numFields; i++) {
					string a;

					more:
				        SQLCHAR buf[255];
					if(SQLGetData(statement, cast(ushort)(i+1), SQL_CHAR, 
buf.ptr, 255, &ptr) != SQL_SUCCESS)
						throw new DatabaseException("get data: " ~ 
getSQLError(SQL_HANDLE_STMT, statement));

					assert(ptr != SQL_NO_TOTAL);
					if(ptr == SQL_NULL_DATA)
						a = null;
					else {
						a ~= cast(string) buf[0 .. ptr > 255 ? 255 : ptr].idup;
						ptr -= ptr > 255 ? 255 : ptr;
						if(ptr)
							goto more;
					}
					row ~= a;
				}

				r.row = row;
				this.row = r;
			} else {
				isEmpty = true;
			}
		}

		void makeFieldMapping() {
			for(int i = 0; i < numFields; i++) {
				SQLSMALLINT len;
				SQLCHAR[255] buf;
				auto ret = SQLDescribeCol(statement,
					cast(ushort)(i+1),
					cast(ubyte*)buf.ptr,
					255,
					&len,
					null, null, null, null);
				if (ret != SQL_SUCCESS)
					throw new DatabaseException("Field mapping error: " ~ 
getSQLError(SQL_HANDLE_STMT, statement));
				
				string a = cast(string) buf[0 .. len].idup;

				columnNames ~= a;
				mapping[a] = i;
			}

		}
}

private string getSQLError(short handletype, SQLHANDLE handle)
{
	char sqlstate[32];
	char message[256];
	SQLINTEGER nativeerror=0;
	SQLSMALLINT textlen=0;			
	auto ret = SQLGetDiagRec(handletype, handle, 1,
			cast(ubyte*)sqlstate.ptr,
			cast(int*)&nativeerror,
			cast(ubyte*)message.ptr,
			256,
			&textlen);

	return message.idup;
}

/*
import std.stdio;
void main() {
	//auto db = new MsSql("Driver={SQL 
Server};Server=<host>[\\<optional-instance-name>]>;Database=dbtest;Trusted_Connection=Yes");
	auto db = new MsSql("Driver={SQL Server Native Client 
10.0};Server=<host>[\\<optional-instance-name>];Database=dbtest;Trusted_Connection=Yes")

	db.query("INSERT INTO users (id, name) values (30, 'hello 
mang')");

	foreach(line; db.query("SELECT * FROM users")) {
		writeln(line[0], line["name"]);
	}
}
*/

---------------------------------------------module database.d's 
part-------------------------------
module arsd.database;

public import std.variant;
import std.string;

import core.vararg;

interface Database {
	/// Actually implements the query for the database. The query() 
method
	/// below might be easier to use.
	ResultSet queryImpl(string sql, Variant[] args...);

	/// Escapes data for inclusion into an sql string literal
	string escape(string sqlData);

	/// query to start a transaction, only here because sqlite is 
apparently different in syntax...
	void startTransaction();

	// FIXME: this would be better as a template, but can't because 
it is an interface

	/// Just executes a query. It supports placeholders for 
parameters
	/// by using ? in the sql string. NOTE: it only accepts string, 
int, long, and null types.
	/// Others will fail runtime asserts.
	final ResultSet query(T...)(string sql, T t) {
		Variant[] args;
		foreach(arg; t) {
			Variant a;
			static if(__traits(compiles, a = arg))
				a = arg;
			else
				a = to!string(t);
			args ~= a;
		}
		return queryImpl(sql, args);
	}
	version(none)
	final ResultSet query(string sql, ...) {
		Variant[] args;
		foreach(arg; _arguments) {
			string a;
			if(arg == typeid(string) || arg == typeid(immutable(string)) 
|| arg == typeid(const(string)))
				a = va_arg!string(_argptr);
			else if (arg == typeid(int) || arg == typeid(immutable(int)) 
|| arg == typeid(const(int))) {
				auto e = va_arg!int(_argptr);
				a = to!string(e);
			} else if (arg == typeid(uint) || arg == 
typeid(immutable(uint)) || arg == typeid(const(uint))) {
				auto e = va_arg!uint(_argptr);
				a = to!string(e);
			} else if (arg == typeid(immutable(char))) {
				auto e = va_arg!char(_argptr);
				a = to!string(e);
			} else if (arg == typeid(long) || arg == typeid(const(long)) 
|| arg == typeid(immutable(long))) {
				auto e = va_arg!long(_argptr);
				a = to!string(e);
			} else if (arg == typeid(ulong) || arg == typeid(const(ulong)) 
|| arg == typeid(immutable(ulong))) {
				auto e = va_arg!ulong(_argptr);
				a = to!string(e);
			} else if (arg == typeid(null)) {
				a = null;
			} else assert(0, "invalid type " ~ arg.toString() );

			args ~= Variant(a);
		}

		return queryImpl(sql, args);
	}
}
import std.stdio;

Ret queryOneColumn(Ret, string file = __FILE__, size_t line = 
__LINE__, T...)(Database db, string sql, T t) {
	auto res = db.query(sql, t);
	if(res.empty)
		throw new Exception("no row in result", file, line);
	auto row = res.front;
	return to!Ret(row[0]);
----------------------------------------not end--------


If you have some time,please help me,Thank you.
Apr 14 2014
parent reply "FrankLike" <1150015857 qq.com> writes:
On Monday, 14 April 2014 at 17:13:56 UTC, FrankLike wrote:

 My advice - use ODBC, it is the fastest way you may connect to 
 the SQL server, and you already have everything you need for 
 that. :)

 Regards


 I have test the d\dmd2\windows\lib\odbc32.lib,the size is 4.5kb,
 I test it by test.d(build :dmd test.d)
 but find the error:
 Error 42:Symbol Undefined _SQLFreeHandle 8
 Error 42:Symbol Undefined _SQLSetEnvAttr 16
 Error 42:Symbol Undefined _SQLAllocHandle 12
 Error 42:Symbol Undefined _SQLGetDiagRec 32
 -- errorlevel 4


  I have fixed the errors.
The exe file only 210kb,it works very good.

Where the errors is ?
In the odbc32.def file.
must set the all used function names.
such as:
  _SQLFreeHandle 8 	= SQLFreeHandle

Ok.
Frank.
May 01 2014
parent "Regan Heath" <regan netmail.co.nz> writes:
On Thu, 01 May 2014 09:56:49 +0100, FrankLike <1150015857 qq.com> wrote:


 On Monday, 14 April 2014 at 17:13:56 UTC, FrankLike wrote:

 My advice - use ODBC, it is the fastest way you may connect to the SQL  
 server, and you already have everything you need for that. :)

 Regards


 I have test the d\dmd2\windows\lib\odbc32.lib,the size is 4.5kb,
 I test it by test.d(build :dmd test.d)
 but find the error:
 Error 42:Symbol Undefined _SQLFreeHandle 8
 Error 42:Symbol Undefined _SQLSetEnvAttr 16
 Error 42:Symbol Undefined _SQLAllocHandle 12
 Error 42:Symbol Undefined _SQLGetDiagRec 32
 -- errorlevel 4


   I have fixed the errors.
 The exe file only 210kb,it works very good.

 Where the errors is ?
 In the odbc32.def file.
 must set the all used function names.
 such as:
   _SQLFreeHandle 8 	= SQLFreeHandle


That's interesting.

Those functions are _stdcall, so should be exported from the lib as  
_<func> <N>.

How did you declare them in arsd.mssql?

You should use extern(Windows) e.g.

extern(Windows) SQLRETURN SQLFreeHandle(SQLSMALLINT HandleType, SQLHANDLE  
Handle);

The extern(Windows) tells DMD to look for _stdcall.
extern(C) tells it to look for _cdecl.

The difference boils down to who is responsible for cleaning up the stack  
after a function call.  _stdcall assumes the callee will cleanup the  
stack, _cdecl assumes the caller will.

R

-- 
Using Opera's revolutionary email client: http://www.opera.com/mail/
May 01 2014
prev sibling parent reply "Rikki Cattermole" <alphaglosined gmail.com> writes:
On Monday, 14 April 2014 at 15:21:33 UTC, FrankLike wrote:

 Hello,everyone:

 A lot of people want to use D,but they only know MS SQL 
 Server,what will help them to Learn D?

 So lots of people want to use D,who can help them?
 They want to connect MS SQL Server in D,then they will connect 
 other DataBase,
 because it's a good idea that nice thing come from the small 
 and familiar things always.
 Sql driver or ODBC driver, for win7 in D,
 who can help them?

 Thank you,everyone.


There is another option. Using OpenDBX[0]. My binding here[1].
Its not exactly tested but since OpenDBX is a c library there
shouldn't be any issues as long as you can grab the appropriate
shared library version.

[0] http://www.linuxnetworks.de/doc/index.php/OpenDBX/Support
[1] https://github.com/rikkimax/Derelict_Extras---OpenDBX
Apr 14 2014
parent " FrankLike" <1150015857 qq.com> writes:
 There is another option. Using OpenDBX[0]. My binding here[1].
 Its not exactly tested but since OpenDBX is a c library there
 shouldn't be any issues as long as you can grab the appropriate
 shared library version.

 [0] http://www.linuxnetworks.de/doc/index.php/OpenDBX/Support
 [1] https://github.com/rikkimax/Derelict_Extras---OpenDBX


Hi,Rikki Cattermole,
Thank you,very much.
It's a very good work.

Frank.
Apr 15 2014