Hi,
I've been using web2py for a bit but haven't delved into the dal
before. I decided to give it a try with a new project and I'm
encountering some difficulty.
I am using MS SQL Server 2008 and pyodbc for database connectivity
btw.
Okay, so I have three problems, any of which could be due to my
ignorance so please bear with me :-)
1)
I need my database text fields to be nvarchar and wanted to use the
mssql2 driver to achieve this since it defines strings as nvarchar
rather than varchar.
I put this into my db.py:
db = SQLDB('mssql2://[user]:[pass]@(local)\\[DB INSTANCE]/[DB NAME]',
check_reserved=['mssql'])
db.define_table("games",
Field("name", "string", length=50),
Field("description", "string", length=4000),
Field("categoryID", "integer"),
)
But when I execute the page I get "ProgrammingError: ('The SQL
contains 0 parameter markers, but 1 parameters were supplied',
'HY000')"
If I remove the "utf8" parameter from MSSQL2Adapter's log_execute
statement (execute is not overwritten in MSSQLAdapter) then it goes
through fine. Does anyone know what's up with this?
2)
I also want my "description" field to be nvarchar(MAX) and I see no
way to do this. Defining length as "MAX" puts it at INFINITY which
mssql doesn't like and the "TEXT" field type does not suit my unicode
needs.
3)
When I'm altering my tables there appears to be some problem with
casing with my case-sensitive DB. In the example above, if I change
db.py to remove the categoryID column I get the following error:
ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server
Driver][SQL Server]ALTER TABLE DROP COLUMN failed because column
'categoryid' does not exist in table 'games2'. (4924)
(SQLExecDirectW)")
Which indicates that web2py attempts to drop categoryid even though I
defined the column name as categoryID.
Lastly, I would like to have more freedom in defining db datatypes and
I'm wondering whether that's possible to do without changing dal.py.
For example, I would like to use mssql's datetime2 type as well as
tinyint and smallint.
Thanks in advance
