luasql has anyone got luaql to work with MS Access 2007?

just wondering if someone successfully connects by odbc in luasql? Can not find much information online.

May 08 '13 19:05 ipstone

I've not tried it……but I did just discover that the team at OpenRestyhttp://openresty.org/ have rewritten their own luasql driverhttps://github.com/agentzh/lua-resty-mysql. Not sure but you might have better luck with that one …. Oh, it seems it's mysql only right now.

Lua seems to (happily) really be gaining popularity, and it's major exposure at this point in my mind seems to be the age & frailty of luasql. I've tried using it with mysql store procedures and (if they have any select statement or out-params), it absolutely won't work.

Good luck! Dewey

From: Isaac Pei <[email protected]mailto:[email protected]> Reply-To: keplerproject/luasql <[email protected]mailto:[email protected]> Date: Wednesday, May 8, 2013 2:49 PM To: keplerproject/luasql <[email protected]mailto:[email protected]> Subject: [luasql] has anyone got luaql to work with MS Access 2007? (#11)

just wondering if someone successfully connects by odbc in luasql? Can not find much information online.

— Reply to this email directly or view it on GitHubhttps://github.com/keplerproject/luasql/issues/11.

May 08 '13 19:05 dgaedcke

thanks for the pointers! yes, so interested in Lua, but comparatively, the library support will need to catch up with python/ruby ...

On Wed, May 8, 2013 at 3:55 PM, Dewey Gaedcke [email protected]:

I've not tried it……but I did just discover that the team at OpenResty< http://openresty.org/> have rewritten their own luasql driver< https://github.com/agentzh/lua-resty-mysql>. Not sure but you might have better luck with that one …. Oh, it seems it's mysql only right now.

Lua seems to (happily) really be gaining popularity, and it's major exposure at this point in my mind seems to be the age & frailty of luasql. I've tried using it with mysql store procedures and (if they have any select statement or out-params), it absolutely won't work.

Good luck! Dewey

From: Isaac Pei <[email protected]mailto:[email protected]>

Reply-To: keplerproject/luasql <[email protected]<mailto: [email protected]>> Date: Wednesday, May 8, 2013 2:49 PM To: keplerproject/luasql <[email protected]<mailto: [email protected]>> Subject: [luasql] has anyone got luaql to work with MS Access 2007? (#11)

just wondering if someone successfully connects by odbc in luasql? Can not find much information online.

— Reply to this email directly or view it on GitHub< https://github.com/keplerproject/luasql/issues/11>.

— Reply to this email directly or view it on GitHubhttps://github.com/keplerproject/luasql/issues/11#issuecomment-17629787 .

Isaac Xin Pei,
- "A word fitly spoken Is like apples of gold in network of silver. "
Proverbs 25:11

May 08 '13 21:05 ipstone

On 08/05/13 20:48, Isaac Pei wrote:

just wondering if someone successfully connects by odbc in luasql? Can not find much information online.

Yes, I use it quiet often. You need to create a User DSN in ODBCAD32[1] and pass that as the DB name when calling luasql.odbc().connect(). (this means you need to run the app under the same user as you created the DSN, which can be a problem sometimes)

I've patched my luasql.odbc code (attached) to also handle having the a full DSN passed to it to avoid having to create the User DSN.

odbc_driver = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}" db_path = [[C:\path\to\my_db.accdb]] env = luasql.odbc() con = env:connect{dsn = odbc_driver..";DBQ="..db_path}

Scott

[1] It's a pain on 64-bit systems. For starters both the 32 and 64 bit version are called ODBCAD32 (the 32-bit one is located in SysWoW64), you need to use the right one for your app. Then there's a problem installing the 32-bit ODBC Access driver if the 64-bit one is already present, for some stupid reason it needs to be installed before the 64-bit. diff --git a/src/ls_odbc.c b/src/ls_odbc.c index 64ca376..34614e3 100644 --- a/src/ls_odbc.c +++ b/src/ls_odbc.c @@ -571,6 +571,112 @@ static int create_connection (lua_State *L, int o, env_data *env, SQLHDBC hdbc) return 1; }

+static int env_table_connect_DSN (lua_State *L) {

env_data *env = (env_data *) getenvironment (L);
const char *sourcename = luaL_checkstring (L, -1);
SQLHDBC hdbc;
char sqlOutBuf[4097];
SQLSMALLINT sqlOutLen;
SQLRETURN ret;

lua_pop(L, 1);

ret = SQLSetEnvAttr (env->henv, SQL_ATTR_ODBC_VERSION,
```
  (void*)SQL_OV_ODBC3, 0);
```
if (error(ret))

  return luasql_faildirect (L, "error setting SQL version.");

/* tries to allocate connection handle */
ret = SQLAllocHandle (hDBC, env->henv, &hdbc);
if (error(ret))

  return luasql_faildirect (L, "connection allocation error.");

/* tries to connect handle */
ret = SQLDriverConnect (hdbc, NULL,
```
  (char *) sourcename, SQL_NTS, 
```

  (char *) sqlOutBuf, 4096, &sqlOutLen,

```
  SQL_DRIVER_NOPROMPT);
```
if (error(ret)) {
```
  ret = fail(L, hDBC, hdbc);
```
```
  SQLFreeHandle(hDBC, hdbc);
```
```
  return ret;
```
}
/* success, return connection object */
ret = create_connection (L, 1, env, hdbc);
if(ret == 1) {

  /\* Add the sqlOutBuf string to the results, for diagnostics */

  lua_pushlstring(L, sqlOutBuf, sqlOutLen);

```
  return 2;
```
}
return ret; +}

+static int env_table_connect_standard (lua_State *L) {

env_data *env = (env_data *) getenvironment (L);
const char *sourcename = luaL_checkstring (L, -1);
const char *username = NULL;
const char *password = NULL;
SQLHDBC hdbc;
SQLRETURN ret;

lua_pop(L, 1);

/* get user */
lua_getfield(L, 2, "user");
if(lua_isstring(L, -1))
```
  username = luaL_checkstring(L, -1);
```
lua_pop(L, 1);

/* get password */
lua_getfield(L, 2, "password");
if(lua_isstring(L, -1))
```
  password = luaL_checkstring(L, -1);
```
lua_pop(L, 1);

/* tries to allocate connection handle */
ret = SQLAllocHandle (hDBC, env->henv, &hdbc);
if (error(ret))

  return luasql_faildirect (L, "connection allocation error.");

/* tries to connect handle */
ret = SQLConnect (hdbc, (char *) sourcename, SQL_NTS,

  (char *) username, SQL_NTS, (char *) password, SQL_NTS);

if (error(ret)) {
```
  ret = fail(L, hDBC, hdbc);
```
```
  SQLFreeHandle(hDBC, hdbc);
```
```
  return ret;
```
}
/* success, return connection object */
return create_connection (L, 1, env, hdbc); +}

+/* +* Creates and returns a connection object +* Lua Input: tab +* tab: table of connection parameters +* "dsn" : A custom DSN +* or +* "source" : The ODBC source DSN +* "user" : The connecting user +* "password" : The connecting user's password +* general params +* "" : ? +* Lua Returns: +* connection object if successfull +* nil and error message otherwise. +/ +static int env_table_connect (lua_State *L) {

lua_getfield(L, 2, "dsn");
if(lua_isstring(L, -1))

  return env_table_connect_DSN(L);        /\* use the custom DSN string */

lua_pop(L, 1);

lua_getfield(L, 2, "source");
if(lua_isstring(L, -1))

  return env_table_connect_standard(L);   /\* standard source [user, password] */

lua_pop(L, 1);

/* failed to get any meaningful connection info*/
return luasql_faildirect (L, "no meaningful connection info given"); +}

/* ** Creates and returns a connection object @@ -581,7 +687,7 @@ static int create_connection (lua_State L, int o, env_data *env, SQLHDBC hdbc) * connection object if successfull ** nil and error message otherwise. */ -static int env_connect (lua_State *L) { +static int env_std_connect (lua_State *L) { env_data *env = (env_data *) getenvironment (L); const char *sourcename = luaL_checkstring (L, 2); const char *username = luaL_optstring (L, 3, NULL); @@ -602,12 +708,27 @@ static int env_connect (lua_State *L) { SQLFreeHandle(hDBC, hdbc); return ret;

}

/* success, return connection object */ return create_connection (L, 1, env, hdbc); }

/* +* Creates and returns a connection object +* Lua Input: see env_std_connect and env_table_connect +* Lua Returns: +* connection object if successfull +* nil and error message otherwise. +/ +static int env_connect (lua_State *L) {

/* have we been given a connection paramater list */
if(lua_istable(L, 2))
```
  return env_table_connect(L);
```
/* just use the standard conection arguments */
return env_std_connect(L); +}

+/* ** Closes an environment object */ static int env_close (lua_State *L) {

May 13 '13 16:05 blumf