[web2py] Re: mssql set identity_insert on
I agree that this is not good practice, but it is the only workable solution that we have found for multi pillar release. We are also heavily using roles, but here the problem is guaranteeing that users have the same ids on all pillars, otherwise we risk users belonging to the wrong groups in production I have found a work around to the insert problem by reserving a range of user records and doing updates instead of inserts; also not a clean solution. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/9e15be13-0b69-4885-a4ae-62d4d03be5ee%40googlegroups.com.
[web2py] Re: mssql set identity_insert on
On Thursday, December 19, 2019 at 2:37:29 PM UTC-8, Davidiam wrote: > > As part of our release process, we need to insert records in the auth > tables using specific id's. On sqlite this is no problem, but in mssql we > get an error saying insert_identity is off. > The solution is to set insert_identity ON, but I am not sure how to do > this using the web2py DAL. Can any give me an code example for this? > We are basically inserting user and group records using ids from a master > table. > > This is the command that I would need to run before doing the inserts : > > SET IDENTITY_INSERT dbo.auth_user ON; > > Thanks, > David > I can't, because I'm using a different DB, but I remember thinking at the time of your earlier post about IDs that ... this is wrong. I think your process should be using *roles*, not *ID*s, and this is what the auth_group table is for. Perhaps if I understood your process, I would agree that relying on specific IDs is the right answer, but it is going to take some effort to convince me. /dps -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/web2py/709105be-e47f-487f-a486-5865fe0e37b2%40googlegroups.com.
[web2py] Re: MSSQL connection
The official book is a good place to start - have you reviewed that yet? http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#Dependencies On Wednesday, December 21, 2016 at 11:13:17 PM UTC-5, Steve Lyle wrote: > > Hi guys. > Newbie (of sorts) here. > 20+ years in programming including Python but not for a couple of years > now. > > But now I have the first time opportunity to build my application in > web2py ~ so not a newbie but yes a web2py newbie. > > Any quick start guides/guide for dummys style for adapting the default > application to connect to MSSQL? > You reference will be appreciated. > > If I remember from prior python work that even though the module is call > '..odbc' is doesn't really use odbc does it?!? > > Thanks. > > -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups "web2py-users" group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: MSSQL and auth.signature
this is a well-known bug . MSSQL doesn't support creating references with cascades that reference something else that cascades (i.e. only one is allowed). There's a workaround : it's all documented here : http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#MSSQL--Microsoft-SQL-Server- On Saturday, May 9, 2015 at 3:06:00 PM UTC+2, Paolo Valleri wrote: that's odd. Can you open an issue on github ( https://github.com/web2py/pydal/issues) with a simple example to reproduce it? I'll have a look in the next few days Paolo On Saturday, May 9, 2015 at 11:54:30 AM UTC+2, Claudinei Fernandes wrote: Hi, Has anyone used auth.signature attribute with MSSQL database? For me it is returning the following error: class 'gluon.contrib.pypyodbc.ProgrammingError' (u'42000', u[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'signature_test_modified_by__constraint' on table 'signature_test' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.) Can anyone help me please? -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: MSSQL and auth.signature
that's odd. Can you open an issue on github (https://github.com/web2py/pydal/issues) with a simple example to reproduce it? I'll have a look in the next few days Paolo On Saturday, May 9, 2015 at 11:54:30 AM UTC+2, Claudinei Fernandes wrote: Hi, Has anyone used auth.signature attribute with MSSQL database? For me it is returning the following error: class 'gluon.contrib.pypyodbc.ProgrammingError' (u'42000', u[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Introducing FOREIGN KEY constraint 'signature_test_modified_by__constraint' on table 'signature_test' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.) Can anyone help me please? -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql connection URGENT!
Thank you for the reply. I found that that problem was why it doesn't accept IP Address instead of Named Instance and fixed the SQL Server configuration to use static IP to connect SQL Server. I can't connect to my servers SQL database via an IP Address http://dba.stackexchange.com/questions/62165/i-cant-connect-to-my-servers-sql-database-via-an-ip-address Problem Solved! On Monday, February 2, 2015 at 1:33:35 PM UTC-6, Massimo Di Pierro wrote: The problem is that since you did not specify a name for the .table files you get default (nobody does). default is a hash of the db uri so if you change it the .table files break. If this is just a change in the name but the tables are already in the database, the you should enable a fake_migration ( http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#migrate--fake_migrate) so that new .table files are created. If this is a new database, you need to enable migrations (I see they are disabled for you). Make sure you do not change the database schema until this is resolved else there is no way for me to tell in which state you db and your .table are. Massimo On Monday, 2 February 2015 08:29:23 UTC-6, Omi Chiba wrote: The below connection statement was working fine and the SQL Servers's instance was HELPDESK/HELPDESK. dbh = DAL(mssql2://username:password@HELPDESK/HS2000CS, migrate_enabled=False) Now, our vendor change the name of instance to HELPDESK/HELPDESK_VM. I tried, dbh = DAL(mssql2://username:password@[HELPDESK/HELPDESK_VM]/HS2000CS, migrate_enabled=False) dbh = DAL(mssql2://username:password@10.30.6.222/HS2000CS, migrate_enabled=False) SQL Server is in our local network and 10.30.6.222 is the local ip address. but both doesn't work. It's in production and I need to fix in a few hours. Please help! -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql connection URGENT!
Thanks!
On Monday, February 2, 2015 at 3:06:05 PM UTC-6, Niphlod wrote:
uhm. Technically having more than one instance of mssql running on the
same ip is possible (multiple named instances) but it involves a totally
different connection handshake.
If you don't specify different port numbers, you can still have them
dynamic but you have to resort to have SQL Server Browser running on the
server and specifying a connection using the odbc connectionstring
notation (mssql://{Driver={SQL
Server};Server=hostname\instancename;..}), that will trigger that new
handshakeeverything works fine, I use it every day.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql connection URGENT!
uhm. Technically having more than one instance of mssql running on the same
ip is possible (multiple named instances) but it involves a totally
different connection handshake.
If you don't specify different port numbers, you can still have them
dynamic but you have to resort to have SQL Server Browser running on the
server and specifying a connection using the odbc connectionstring
notation (mssql://{Driver={SQL
Server};Server=hostname\instancename;..}), that will trigger that new
handshakeeverything works fine, I use it every day.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql connection URGENT!
The problem is that since you did not specify a name for the .table files you get default (nobody does). default is a hash of the db uri so if you change it the .table files break. If this is just a change in the name but the tables are already in the database, the you should enable a fake_migration (http://www.web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#migrate--fake_migrate) so that new .table files are created. If this is a new database, you need to enable migrations (I see they are disabled for you). Make sure you do not change the database schema until this is resolved else there is no way for me to tell in which state you db and your .table are. Massimo On Monday, 2 February 2015 08:29:23 UTC-6, Omi Chiba wrote: The below connection statement was working fine and the SQL Servers's instance was HELPDESK/HELPDESK. dbh = DAL(mssql2://username:password@HELPDESK/HS2000CS, migrate_enabled=False) Now, our vendor change the name of instance to HELPDESK/HELPDESK_VM. I tried, dbh = DAL(mssql2://username:password@[HELPDESK/HELPDESK_VM]/HS2000CS, migrate_enabled=False) dbh = DAL(mssql2://username:password@10.30.6.222/HS2000CS, migrate_enabled=False) SQL Server is in our local network and 10.30.6.222 is the local ip address. but both doesn't work. It's in production and I need to fix in a few hours. Please help! -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
it WORKS now :-) I was playing with it and suddenly it is OK. I dont know
what it was, I believe vs '
these are working definitions:
db.define_table(Mjesta,
Field http://127.0.0.1:8000/examples/global/vars/Field(PostanskiBroj,
integer,notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field(Naziv,requires=
IS_NOT_EMPTY http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY()),
primarykey=[PostanskiBroj],
migrate=False,
format=%(Naziv)s %(PostanskiBroj)s)
db.define_table(Partneri,
Field http://127.0.0.1:8000/examples/global/vars/Field(Sifra,integer,
notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field(Naziv,requires=
IS_NOT_EMPTY http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY()),
Field http://127.0.0.1:8000/examples/global/vars/Field(Mjesto,reference
Mjesta),
primarykey=[Sifra],
migrate=False)
Dana petak, 3. listopada 2014. 20:13:55 UTC+2, korisnik Niphlod napisao je:
what if you don't use requires=IS_EMPTY() ?
On Friday, October 3, 2014 8:52:12 AM UTC+2, Alen Cerovic wrote:
is there any resolution to this, any ideas, or are there plans to make it
work in future?
Dana ponedjeljak, 29. rujna 2014. 12:26:11 UTC+2, korisnik Alen Cerovic
napisao je:
Hi pn, I am expecting dropdown list of Mjesta when I add/edit record in
Partneri
tried putting 'requires' Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj',requires=IS_NOT_EMPTY
http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY*()) *but no
dropdown
Dana nedjelja, 28. rujna 2014. 20:29:50 UTC+2, korisnik pn napisao je:
Can you provide some clarification on what behavior are you expecting
but not seeing when you create the reference? For example, are you able to
create entries in the 'Partneri' table that refer keys not in the 'Mjesta'
table? A quick workaround would be to put in a 'requires' lambda into the
field definition in the Partneri table. This way you can define your own
custom rules.
On Saturday, September 27, 2014 2:51:31 AM UTC-4, Alen Cerovic wrote:
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di
Pierro napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference
PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems
in referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field(
'PostanskiBroj','integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
is there any resolution to this, any ideas, or are there plans to make it
work in future?
Dana ponedjeljak, 29. rujna 2014. 12:26:11 UTC+2, korisnik Alen Cerovic
napisao je:
Hi pn, I am expecting dropdown list of Mjesta when I add/edit record in
Partneri
tried putting 'requires' Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj',requires=IS_NOT_EMPTY
http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY*()) *but no
dropdown
Dana nedjelja, 28. rujna 2014. 20:29:50 UTC+2, korisnik pn napisao je:
Can you provide some clarification on what behavior are you expecting but
not seeing when you create the reference? For example, are you able to
create entries in the 'Partneri' table that refer keys not in the 'Mjesta'
table? A quick workaround would be to put in a 'requires' lambda into the
field definition in the Partneri table. This way you can define your own
custom rules.
On Saturday, September 27, 2014 2:51:31 AM UTC-4, Alen Cerovic wrote:
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di Pierro
napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field(
'PostanskiBroj','integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
what if you don't use requires=IS_EMPTY() ?
On Friday, October 3, 2014 8:52:12 AM UTC+2, Alen Cerovic wrote:
is there any resolution to this, any ideas, or are there plans to make it
work in future?
Dana ponedjeljak, 29. rujna 2014. 12:26:11 UTC+2, korisnik Alen Cerovic
napisao je:
Hi pn, I am expecting dropdown list of Mjesta when I add/edit record in
Partneri
tried putting 'requires' Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj',requires=IS_NOT_EMPTY
http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY*()) *but no
dropdown
Dana nedjelja, 28. rujna 2014. 20:29:50 UTC+2, korisnik pn napisao je:
Can you provide some clarification on what behavior are you expecting
but not seeing when you create the reference? For example, are you able to
create entries in the 'Partneri' table that refer keys not in the 'Mjesta'
table? A quick workaround would be to put in a 'requires' lambda into the
field definition in the Partneri table. This way you can define your own
custom rules.
On Saturday, September 27, 2014 2:51:31 AM UTC-4, Alen Cerovic wrote:
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di Pierro
napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field(
'PostanskiBroj','integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
Hi pn, I am expecting dropdown list of Mjesta when I add/edit record in
Partneri
tried putting 'requires' Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj',requires=IS_NOT_EMPTY
http://127.0.0.1:8000/examples/global/vars/IS_NOT_EMPTY*()) *but no
dropdown
Dana nedjelja, 28. rujna 2014. 20:29:50 UTC+2, korisnik pn napisao je:
Can you provide some clarification on what behavior are you expecting but
not seeing when you create the reference? For example, are you able to
create entries in the 'Partneri' table that refer keys not in the 'Mjesta'
table? A quick workaround would be to put in a 'requires' lambda into the
field definition in the Partneri table. This way you can define your own
custom rules.
On Saturday, September 27, 2014 2:51:31 AM UTC-4, Alen Cerovic wrote:
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di Pierro
napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field(
'PostanskiBroj','integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
Can you provide some clarification on what behavior are you expecting but
not seeing when you create the reference? For example, are you able to
create entries in the 'Partneri' table that refer keys not in the 'Mjesta'
table? A quick workaround would be to put in a 'requires' lambda into the
field definition in the Partneri table. This way you can define your own
custom rules.
On Saturday, September 27, 2014 2:51:31 AM UTC-4, Alen Cerovic wrote:
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di Pierro
napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field('PostanskiBroj'
,'integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field
http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
Is the legacy primary key an integer or even better an auto - incrementing integer? -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
I just asked if the primary key is integer yet the answer was in front of me, except I don't know if it is an identity field. Because your primary key may look like a standard web2py id field, perhaps you can use the 'id' parameter instead of 'primarykey' -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field('PostanskiBroj',
'integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra','integer'
),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql legacy tables reference
tried
'reference Mjesta' - no error but also nothing happens
'reference PostanskiBroj' - Query Not Supported: 'DAL' object has no
attribute 'PostanskiBroj'
I did not dive in source code but is it difficult to make references
between legacy tables reliable. From my perspective it is very important
as many web projects does not start from scratch.
Dana subota, 27. rujna 2014. 08:01:22 UTC+2, korisnik Massimo Di Pierro
napisao je:
'reference Mjesta.PostanskiBroj'* should be *'reference PostanskiBroj'
*but I would not swear by references for legacy keyed tables.*
On Thursday, 25 September 2014 11:58:21 UTC-5, Alen Cerovic wrote:
Hi,
I am totaly new to web2py, actually started yesterday :)
I am trying to work with legacy mssql database and having problems in
referencing two legacy keyed tables
db.define_table('Mjesta',
Field http://127.0.0.1:8000/examples/global/vars/Field('PostanskiBroj',
'integer',notnull=True,unique=True),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
primarykey=['PostanskiBroj'],
migrate=False)
db.define_table('Partneri',
Field http://127.0.0.1:8000/examples/global/vars/Field('Sifra',
'integer'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Naziv'),
Field http://127.0.0.1:8000/examples/global/vars/Field('Mjesto','reference
Mjesta.PostanskiBroj'),
primarykey=['Sifra'],
migrate=False)
no errors but field Partneri.Mjesto does not reference
Mjesta.PostanskiBroj
What am I doing wrong?
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
[web2py] Re: mssql connection
Thank you... / will try feedback ..
:)
On Thursday, November 14, 2013 5:14:12 AM UTC+7, Derek wrote:
Possible...
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Data source name not found and no default
driver specified.
This usually happens in one of the following scenarios:
- you referenced your connection incorrectly (e.g. spelled the DSN
name, or one of the DSN-less string components wrong);
- you referenced a DSN that doesn't exist;
- the user connecting to the DSN or DSN-less connection doesn't have
access to the information stored in the registry (see KB
#306345http://support.microsoft.com/default.aspx/kb/306345
);
- you used an English or localized driver detail for your connection
string when your system is not set up in that language (see KB
#174655http://support.microsoft.com/default.aspx/kb/174655);
or,
- you are missing the connection string entirely (this can happen if
you maintain your connection string in a session variable, and your
sessions aren't working; see Article
#2157http://www.aspfaq.com/show.asp?id=2157
).
http://tutorials.aspfaq.com/8000x-errors/80004005-errors.html
On Tuesday, November 12, 2013 9:03:05 PM UTC-7, Cacpacific O wrote:
Hello Tim,
Thank you for sharing.
I've try as your advice but it still return error message as follow, Is
it possible that there is something wrong with my odbc setup.
:)
db = DAL('mssql://UserName:passw...@203.xxx.xx.xxx\SQLExpress/myDatabase',
lazy_tables=True, pool_size=10)
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
File /home/www-data/web2py/gluon/dal.py, line 7562, in __init__
self._adapter = ADAPTERS[self._dbname](**kwargs)
File /home/www-data/web2py/gluon/dal.py, line 3279, in __init__
if do_connect: self.reconnect()
File /home/www-data/web2py/gluon/dal.py, line 627, in reconnect
self.connection = f()
File /home/www-data/web2py/gluon/dal.py, line 3277, in connector
return self.driver.connect(cnxn,**driver_args)
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not
found, and no default driver specified (0) (SQLDriverConnect)')
On Wednesday, November 13, 2013 5:27:55 AM UTC+7, Tim Richardson wrote:
Here is a working example using server authentication on a server called
win2003, with a sqlserver instance name hcnsql07 and connecting to a
database called hcn using default ports
(I've added a couple of other parameters for performance.
Investigate lazy_tables, it makes a huge difference. )
db_ps_1TE = DAL('mssql://tim:password@win2003\hcnsql07/hcn',
lazy_tables=True, pool_size=10)
On Monday, 11 November 2013 07:10:43 UTC+11, Ariya Owam-aram wrote:
Dear all,
I have to connect mssql database which is locate on another server by
using pyodbc.
It working if connect by using
pyodbc.connect(DNS=dns;UID=user_name;PWD=password)
pyodbc.Connection at 0x391bdc0
But I can't connect with DAL()
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase)
return error as follow
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name
not found, and no default driver specified (0) (SQLDriverConnect)')
As searching from web2py google group, I also try:
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase?Driver={FreeTDS})
return error as follow
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source (0) (SQLDriverConnect)')
---
in my /etc/odbc.ini
[TS]
Description=ODBC Connection via FreeTDS
Driver=FreeTDS
Server=203.xxx.xx.xxx
port=1433
Database=myDatabase
---
in my /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/freetds.conf
### Did not change anything ###
---
in my /etc/freetds/tds.driver.template
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/tds.dsn.template
[DSN_NAME]
Description = Descripton of you DSN connection.
Driver = TDS
Trace = No
Database= myDatabase
Server = 203.xxx.xx.xxx
Port= 1433
TDS_Version = 8.0
Thank you very much
Ariya
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop
[web2py] Re: mssql connection
Possible...
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Driver Manager]Data source name not found and no default
driver specified.
This usually happens in one of the following scenarios:
- you referenced your connection incorrectly (e.g. spelled the DSN name,
or one of the DSN-less string components wrong);
- you referenced a DSN that doesn't exist;
- the user connecting to the DSN or DSN-less connection doesn't have
access to the information stored in the registry (see KB
#306345http://support.microsoft.com/default.aspx/kb/306345
);
- you used an English or localized driver detail for your connection
string when your system is not set up in that language (see KB
#174655http://support.microsoft.com/default.aspx/kb/174655);
or,
- you are missing the connection string entirely (this can happen if you
maintain your connection string in a session variable, and your sessions
aren't working; see Article #2157http://www.aspfaq.com/show.asp?id=2157
).
http://tutorials.aspfaq.com/8000x-errors/80004005-errors.html
On Tuesday, November 12, 2013 9:03:05 PM UTC-7, Cacpacific O wrote:
Hello Tim,
Thank you for sharing.
I've try as your advice but it still return error message as follow, Is it
possible that there is something wrong with my odbc setup.
:)
db = DAL('mssql://UserName:passw...@203.xxx.xx.
javascript:xxx\SQLExpress/myDatabase',
lazy_tables=True, pool_size=10)
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
File /home/www-data/web2py/gluon/dal.py, line 7562, in __init__
self._adapter = ADAPTERS[self._dbname](**kwargs)
File /home/www-data/web2py/gluon/dal.py, line 3279, in __init__
if do_connect: self.reconnect()
File /home/www-data/web2py/gluon/dal.py, line 627, in reconnect
self.connection = f()
File /home/www-data/web2py/gluon/dal.py, line 3277, in connector
return self.driver.connect(cnxn,**driver_args)
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not
found, and no default driver specified (0) (SQLDriverConnect)')
On Wednesday, November 13, 2013 5:27:55 AM UTC+7, Tim Richardson wrote:
Here is a working example using server authentication on a server called
win2003, with a sqlserver instance name hcnsql07 and connecting to a
database called hcn using default ports
(I've added a couple of other parameters for performance.
Investigate lazy_tables, it makes a huge difference. )
db_ps_1TE = DAL('mssql://tim:password@win2003\hcnsql07/hcn',
lazy_tables=True, pool_size=10)
On Monday, 11 November 2013 07:10:43 UTC+11, Ariya Owam-aram wrote:
Dear all,
I have to connect mssql database which is locate on another server by
using pyodbc.
It working if connect by using
pyodbc.connect(DNS=dns;UID=user_name;PWD=password)
pyodbc.Connection at 0x391bdc0
But I can't connect with DAL()
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase)
return error as follow
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name
not found, and no default driver specified (0) (SQLDriverConnect)')
As searching from web2py google group, I also try:
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase?Driver={FreeTDS})
return error as follow
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source (0) (SQLDriverConnect)')
---
in my /etc/odbc.ini
[TS]
Description=ODBC Connection via FreeTDS
Driver=FreeTDS
Server=203.xxx.xx.xxx
port=1433
Database=myDatabase
---
in my /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/freetds.conf
### Did not change anything ###
---
in my /etc/freetds/tds.driver.template
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/tds.dsn.template
[DSN_NAME]
Description = Descripton of you DSN connection.
Driver = TDS
Trace = No
Database= myDatabase
Server = 203.xxx.xx.xxx
Port= 1433
TDS_Version = 8.0
Thank you very much
Ariya
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit
[web2py] Re: mssql connection
@derek: except that he's using unixodbc ^_^ Anyway, googling [unixODBC][Driver Manager]Data source name not found leads you to several hundreds blogposts on the matter. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: mssql connection
Regardless, it's the same message just a different driver manager. I'd say one of the settings being passed just won't work for mssql express, i'd guess 'pooling'. On Wednesday, November 13, 2013 3:25:38 PM UTC-7, Niphlod wrote: @derek: except that he's using unixodbc ^_^ Anyway, googling [unixODBC][Driver Manager]Data source name not found leads you to several hundreds blogposts on the matter. -- Resources: - http://web2py.com - http://web2py.com/book (Documentation) - http://github.com/web2py/web2py (Source code) - https://code.google.com/p/web2py/issues/list (Report Issues) --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: mssql connection
Here is a working example using server authentication on a server called
win2003, with a sqlserver instance name hcnsql07 and connecting to a
database called hcn using default ports
(I've added a couple of other parameters for performance.
Investigate lazy_tables, it makes a huge difference. )
db_ps_1TE = DAL('mssql://tim:password@win2003\hcnsql07/hcn',
lazy_tables=True, pool_size=10)
On Monday, 11 November 2013 07:10:43 UTC+11, Ariya Owam-aram wrote:
Dear all,
I have to connect mssql database which is locate on another server by
using pyodbc.
It working if connect by using
pyodbc.connect(DNS=dns;UID=user_name;PWD=password)
pyodbc.Connection at 0x391bdc0
But I can't connect with DAL()
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase)
return error as follow
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not
found, and no default driver specified (0) (SQLDriverConnect)')
As searching from web2py google group, I also try:
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase?Driver={FreeTDS})
return error as follow
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to connect
to data source (0) (SQLDriverConnect)')
---
in my /etc/odbc.ini
[TS]
Description=ODBC Connection via FreeTDS
Driver=FreeTDS
Server=203.xxx.xx.xxx
port=1433
Database=myDatabase
---
in my /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/freetds.conf
### Did not change anything ###
---
in my /etc/freetds/tds.driver.template
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/tds.dsn.template
[DSN_NAME]
Description = Descripton of you DSN connection.
Driver = TDS
Trace = No
Database= myDatabase
Server = 203.xxx.xx.xxx
Port= 1433
TDS_Version = 8.0
Thank you very much
Ariya
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: mssql connection
Hello Tim,
Thank you for sharing.
I've try as your advice but it still return error message as follow, I
think something wrong with my odbc setup somehow.
:)
db = DAL('mssql://UserName:Password@203.150.29.227\SQLExpress/myDatabase',
lazy_tables=True, pool_size=10)
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
File /home/www-data/web2py/gluon/dal.py, line 7562, in __init__
self._adapter = ADAPTERS[self._dbname](**kwargs)
File /home/www-data/web2py/gluon/dal.py, line 3279, in __init__
if do_connect: self.reconnect()
File /home/www-data/web2py/gluon/dal.py, line 627, in reconnect
self.connection = f()
File /home/www-data/web2py/gluon/dal.py, line 3277, in connector
return self.driver.connect(cnxn,**driver_args)
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name not
found, and no default driver specified (0) (SQLDriverConnect)')
On Wednesday, November 13, 2013 5:27:55 AM UTC+7, Tim Richardson wrote:
Here is a working example using server authentication on a server called
win2003, with a sqlserver instance name hcnsql07 and connecting to a
database called hcn using default ports
(I've added a couple of other parameters for performance.
Investigate lazy_tables, it makes a huge difference. )
db_ps_1TE = DAL('mssql://tim:password@win2003\hcnsql07/hcn',
lazy_tables=True, pool_size=10)
On Monday, 11 November 2013 07:10:43 UTC+11, Ariya Owam-aram wrote:
Dear all,
I have to connect mssql database which is locate on another server by
using pyodbc.
It working if connect by using
pyodbc.connect(DNS=dns;UID=user_name;PWD=password)
pyodbc.Connection at 0x391bdc0
But I can't connect with DAL()
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase)
return error as follow
Error: ('IM002', '[IM002] [unixODBC][Driver Manager]Data source name
not found, and no default driver specified (0) (SQLDriverConnect)')
As searching from web2py google group, I also try:
db =
DAL(mssql://userName:passw...@203.xxx.xx.xxx\SQLExpress,1433/myDatabase?Driver={FreeTDS})
return error as follow
Error: ('08001', '[08001] [unixODBC][FreeTDS][SQL Server]Unable to
connect to data source (0) (SQLDriverConnect)')
---
in my /etc/odbc.ini
[TS]
Description=ODBC Connection via FreeTDS
Driver=FreeTDS
Server=203.xxx.xx.xxx
port=1433
Database=myDatabase
---
in my /etc/odbcinst.ini
[FreeTDS]
Description=FreeTDS
Driver=/usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/freetds.conf
### Did not change anything ###
---
in my /etc/freetds/tds.driver.template
[TDS]
Description = FreeTDS Driver for Linux MSSQL on Win32
Driver = /usr/lib/x86_64-linux-gnu/odbc/libtdsodbc.so
Setup = /usr/lib/x86_64-linux-gnu/odbc/libtdsS.so
---
in my /etc/freetds/tds.dsn.template
[DSN_NAME]
Description = Descripton of you DSN connection.
Driver = TDS
Trace = No
Database= myDatabase
Server = 203.xxx.xx.xxx
Port= 1433
TDS_Version = 8.0
Thank you very much
Ariya
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL connection error
Got it with FreeTDS. Thanks
On Thursday, July 18, 2013 5:26:33 PM UTC-7, Niphlod wrote:
plenty of references around the web it's a PITA to connect to MSSQL
from a linux client but take advices even related to other programming
languages to fix the odbc driver bugs and intricacies, like
http://stackoverflow.com/questions/10728290/mssql-servers-native-odbc-driver-for-linux-and-php-5-4
On Thursday, July 18, 2013 9:50:04 PM UTC+2, greenpoise wrote:
this is my URI
db = DAL('mssql://sa:rms123@192.168.1.40/SQLEXPRESS/HQ')
this is the error I get:
Error: ('01000', [01000] [unixODBC][Driver Manager]Can't open lib 'SQL
Server' : file not found (0) (SQLDriverConnect))
I have pyodbc and all installed.. I tried changing the pool_size to
20..nothing...Tried using the hostname instead of the IP, nothing..
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL connection error
Hi. I have no experience with web2py but it seems it's not able to read some file. Try to use strace command to determine what file is missing then you can search on the net which package contains the missing file. You can try something like strace command -o output.txt or strace -p web2py process id -o output.txt Hope it helps. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL connection error
plenty of references around the web it's a PITA to connect to MSSQL
from a linux client but take advices even related to other programming
languages to fix the odbc driver bugs and intricacies, like
http://stackoverflow.com/questions/10728290/mssql-servers-native-odbc-driver-for-linux-and-php-5-4
On Thursday, July 18, 2013 9:50:04 PM UTC+2, greenpoise wrote:
this is my URI
db = DAL('mssql://sa:rms123@192.168.1.40/SQLEXPRESS/HQ')
this is the error I get:
Error: ('01000', [01000] [unixODBC][Driver Manager]Can't open lib 'SQL
Server' : file not found (0) (SQLDriverConnect))
I have pyodbc and all installed.. I tried changing the pool_size to
20..nothing...Tried using the hostname instead of the IP, nothing..
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL connection error
will try. Thanks!!! On Thursday, July 18, 2013 3:30:09 PM UTC-7, Marcio Andrey Oliveira wrote: Hi. I have no experience with web2py but it seems it's not able to read some file. Try to use strace command to determine what file is missing then you can search on the net which package contains the missing file. You can try something like strace command -o output.txt or strace -p web2py process id -o output.txt Hope it helps. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL connection error
the best answer to that threat is freetds which I actually have had success
from Linux but is it supported by web2py?
On Thursday, July 18, 2013 5:26:33 PM UTC-7, Niphlod wrote:
plenty of references around the web it's a PITA to connect to MSSQL
from a linux client but take advices even related to other programming
languages to fix the odbc driver bugs and intricacies, like
http://stackoverflow.com/questions/10728290/mssql-servers-native-odbc-driver-for-linux-and-php-5-4
On Thursday, July 18, 2013 9:50:04 PM UTC+2, greenpoise wrote:
this is my URI
db = DAL('mssql://sa:rms123@192.168.1.40/SQLEXPRESS/HQ')
this is the error I get:
Error: ('01000', [01000] [unixODBC][Driver Manager]Can't open lib 'SQL
Server' : file not found (0) (SQLDriverConnect))
I have pyodbc and all installed.. I tried changing the pool_size to
20..nothing...Tried using the hostname instead of the IP, nothing..
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I think this is fixed and closed the ticket but can you please double check? On Thursday, 14 March 2013 01:41:25 UTC-5, Marian wrote: Issues created: http://code.google.com/p/web2py/issues/detail?id=1390sort=-id Am Donnerstag, 14. März 2013 00:10:16 UTC+1 schrieb Niphlod: can you please open an issue about this so it gets tracked ? On Wednesday, March 13, 2013 9:39:21 PM UTC+1, Derek wrote: As far as compatibility issues, sqlite, mysql, and postgres all support ISO8601 with an optional T. On Wednesday, March 13, 2013 1:28:30 PM UTC-7, Marian wrote: ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Issues created: http://code.google.com/p/web2py/issues/detail?id=1390sort=-id Am Donnerstag, 14. März 2013 00:10:16 UTC+1 schrieb Niphlod: can you please open an issue about this so it gets tracked ? On Wednesday, March 13, 2013 9:39:21 PM UTC+1, Derek wrote: As far as compatibility issues, sqlite, mysql, and postgres all support ISO8601 with an optional T. On Wednesday, March 13, 2013 1:28:30 PM UTC-7, Marian wrote: ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
seems a casting error: what is the definition on the database of your
auth_events table as stored in the database ?
what is your connection string ?
On Wednesday, March 13, 2013 1:46:43 PM UTC+1, Marian wrote:
Somehow my web2py is not working with my mssql database anymore. I am
pretty sure it worked yesterday. Today it seems every log_event in auth
fails. Even the welcome app fails on register an user.
I dropped all tables and even recreated the database. Nothing works
anymore.
web2py™ Version 2.4.4-stable+timestamp.2013.03.11.20.32.00 Python Python
2.7.2: C:\Python27\python.exe (prefix: C:\Python27) Traceback
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
Traceback (most recent call last):
File D:\web2py\gluon\restricted.py, line 212, in restricted
exec ccode in environment
File D:/web2py/applications/ImageContest/controllers/default.py
http://192.168.77.4:8000/admin/default/edit/ImageContest/controllers/default.py,
line 82, in module
File D:\web2py\gluon\globals.py, line 194, in lambda
self._caller = lambda f: f()
File D:/web2py/applications/ImageContest/controllers/default.py
http://192.168.77.4:8000/admin/default/edit/ImageContest/controllers/default.py,
line 6, in user
def user(): return dict(form=auth())
File D:\web2py\gluon\tools.py, line 1245, in __call__
return getattr(self, args[0])()
File D:\web2py\gluon\tools.py, line 2148, in login
self.log_event(log, user)
File D:\web2py\gluon\tools.py, line 1707, in log_event
origin=origin, user_id=user_id)
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL
Server]Bei der Konvertierung eines char-Datentyps in einen datetime-Datentyp
liegt der datetime-Wert au\xdferhalb des g\xfcltigen Bereichs. (242)
(SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Die
Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
The connection string is:
mssql://USER:PASSWORD@HOST/DATABASE
The auth_events table is the build in table of auth. I did not customize it
but I customized auth_user.
auth.settings.extra_fields['auth_user']=[
Field('image', 'upload', default='', autodelete=True,
requires=IS_EMPTY_OR(IS_IMAGE()), uploadfs=upload_fs),
Field('title', 'string', length=50),
Field('show_address', 'boolean'),
Field('address', 'string', length=200),
Field('show_institute', 'boolean'),
Field('institute', 'string', length=200),
Field('show_phone', 'boolean'),
Field('phone', 'integer'),
Field('show_cv', 'boolean'),
Field('cv', 'text'),
Field('training_newsletter', 'boolean'),
Field('accept_terms', 'boolean', requires=IS_EXPR('bool(value) ==
True')),
]
Using sqlite everthing works fine.
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I was asking for the actual structure of the auth_event table on the
database
On Wednesday, March 13, 2013 2:21:51 PM UTC+1, Marian wrote:
The connection string is:
mssql://USER:PASSWORD@HOST/DATABASE
The auth_events table is the build in table of auth. I did not customize
it but I customized auth_user.
auth.settings.extra_fields['auth_user']=[
Field('image', 'upload', default='', autodelete=True,
requires=IS_EMPTY_OR(IS_IMAGE()), uploadfs=upload_fs),
Field('title', 'string', length=50),
Field('show_address', 'boolean'),
Field('address', 'string', length=200),
Field('show_institute', 'boolean'),
Field('institute', 'string', length=200),
Field('show_phone', 'boolean'),
Field('phone', 'integer'),
Field('show_cv', 'boolean'),
Field('cv', 'text'),
Field('training_newsletter', 'boolean'),
Field('accept_terms', 'boolean', requires=IS_EXPR('bool(value) ==
True')),
]
Using sqlite everthing works fine.
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Ah ok sorry. The structure is like this: id (PS, int, NOT NULL) time_stamp (datetime, NULL) client_ip (varchar(512), NULL) user_id (FS, int, NULL) origin (varchar(512), NULL) description (text, NULL) -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
ok, next step. Are you on windows ? what driver have you available for
mssql ? (you can see as soon as you start web2py.py in the console)
next step: open a shell (web2py.py -M -S theappname)
and do (or try to do)
auth.log_event('something')
db.commit()
db.auth_event.insert(client_ip='bogus', user_id=1, origin='tests',description
='trial1')
db.commit()
request.now
db.auth_event.insert(client_ip='bogus', time_stamp=request.now)
and post the results
On Wednesday, March 13, 2013 2:41:26 PM UTC+1, Marian wrote:
Ah ok sorry. The structure is like this:
id (PS, int, NOT NULL)
time_stamp (datetime, NULL)
client_ip (varchar(512), NULL)
user_id (FS, int, NULL)
origin (varchar(512), NULL)
description (text, NULL)
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I am running on Windows 7 Ent. 64, with Python 2.7.2 x64 as you also can
see in the log. It seems it fails even if I do not insert a timestamp.
Created by Massimo Di Pierro, Copyright 2007-2013
Version 2.4.4-stable+timestamp.2013.03.11.20.32.00
Database drivers available: SQLite(sqlite3), MySQL(pymysql),
PostgreSQL(pg8000),
MSSQL(pyodbc), DB2(pyodbc), Teradata(pyodbc), Ingres(pyodbc), IMAP(imaplib)
WARNING:web2py:import IPython error; use default python shell
Python 2.7.2 (default, Jun 24 2011, 12:22:14) [MSC v.1500 64 bit (AMD64)]
on win
32
Type help, copyright, credits or license for more information.
(InteractiveConsole)
auth.log_event('something')
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\tools.py, line 1707, in log_event
origin=origin, user_id=user_id)
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL
Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegt der datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Die Anweisung wurde beendet. (3621)')
db.commit()
db.auth_event.insert(client_ip='bogus', user_id=1, origin='tests',
description='trial1')
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL
Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegt der datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Die Anweisung wurde beendet. (3621)')
db.commit()
request.now
datetime.datetime(2013, 3, 13, 15, 2, 9, 442000)
db.auth_event.insert(client_ip='bogus', time_stamp=request.now)
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][ODBC SQL Server Driver][SQL
Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegt der datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server
Driver][SQL Server]Die Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
The databse driver is pyodbc-3.0.6-py2.7 -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
ok... one more trial.
import datetime
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
db.auth_event.insert(client_ip='bogus', time_stamp=now)
and . did you try connecting to the database using the odbc syntax ?
db = DAL('mssql://Driver={SQL Server Native Client
10.0};Server=myServerAddress;Database=myDataBase;Uid=myUsername;Pwd=myPassword;')
On Wednesday, March 13, 2013 3:13:38 PM UTC+1, Marian wrote:
The databse driver is pyodbc-3.0.6-py2.7
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Event with odbc syntax it fails like this:
import datetime
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
db.auth_event.insert(client_ip='bogus', time_stamp=now)
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client
10.0][SQL Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegtder datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native
Client 10.0][SQL Server]Die Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
something on the adapter side is terribly wrong... what's the default
language of the user connecting to this instance ?
PS: thanks for the patience.
On Wednesday, March 13, 2013 3:29:51 PM UTC+1, Marian wrote:
Event with odbc syntax it fails like this:
import datetime
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
db.auth_event.insert(client_ip='bogus', time_stamp=now)
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client
10.0][SQL Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegtder datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native
Client 10.0][SQL Server]Die Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I could have answered seeing the message... supposedly German ?
On Wednesday, March 13, 2013 3:36:21 PM UTC+1, Niphlod wrote:
something on the adapter side is terribly wrong... what's the default
language of the user connecting to this instance ?
PS: thanks for the patience.
On Wednesday, March 13, 2013 3:29:51 PM UTC+1, Marian wrote:
Event with odbc syntax it fails like this:
import datetime
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
db.auth_event.insert(client_ip='bogus', time_stamp=now)
Traceback (most recent call last):
File console, line 1, in module
File D:\web2py\gluon\dal.py, line 8443, in insert
ret = self._db._adapter.insert(self, self._listify(fields))
File D:\web2py\gluon\dal.py, line 1201, in insert
raise e
DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client
10.0][SQL Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegtder datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native
Client 10.0][SQL Server]Die Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Oh no thank you for your help. :) The user language is german and encoding of the database is Latin1_General_CI_AS. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
the issue is that it seems that the adapter can't adapt the datetime object to the necessary buffer of commands that are needed to ship to mssql. If you have time you can help testing if connecting outside the DAL the insert works ... cnxn = pyodbc.connect() .. etc etc etc I don't have a python installation at hand, so if you can't figure out the issue, I'll try to reproduce it at home (and I say try because I never had such problems...) On Wednesday, March 13, 2013 3:45:38 PM UTC+1, Marian wrote: Oh no thank you for your help. :) The user language is german and encoding of the database is Latin1_General_CI_AS. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Ok I did a quick test and this time it worked:
import pyodbc
import datetime
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=host;DATABASE=db;UID=user;PWD=pw')
cursor = cnxn.cursor()
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
cursor.execute(insert into auth_event(time_stamp, client_ip, origin,
description) values (?, ?, ?, ?), now, '192.0.0.1', 'origin', 'description'
)
cnxn.commit()
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
what about using now = datetime.datetime.now() ?
On Wednesday, March 13, 2013 4:28:03 PM UTC+1, Marian wrote:
Ok I did a quick test and this time it worked:
import pyodbc
import datetime
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=host;DATABASE=db;UID=user;PWD=pw')
cursor = cnxn.cursor()
now = datetime.datetime(2013, 3, 13, 15, 2, 9)
cursor.execute(insert into auth_event(time_stamp, client_ip, origin,
description) values (?, ?, ?, ?), now, '192.0.0.1', 'origin',
'description')
cnxn.commit()
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
This works also -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
remote testing makes me sick :P
another trial.
now = datetime.datetime.now().isoformat()[:19].replace('T',' ')
On Wednesday, March 13, 2013 4:39:30 PM UTC+1, Marian wrote:
This works also
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
That's why I appreciate your help :) Now there's the DataError:
Traceback (most recent call last):
File D:\Temp\test_pyodbc.py, line 9, in module
cursor.execute(insert into auth_event(time_stamp, client_ip, origin,
description) values (?, ?, ?, ?), now, '192.0.0.1', 'origin', 'description'
)
pyodbc.DataError: ('22007', '[22007] [Microsoft][SQL Server Native Client
10.0][SQL Server]Bei der Konvertierung eines char-Datentyps in einen
datetime-Datentyp liegt der datetime-Wert au\xdferhalb des g\xfcltigen
Bereichs. (242) (SQLExecDirectW); [01000] [Microsoft][SQL Server Native
Client 10.0][SQL Server]Die Anweisung wurde beendet. (3621)')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Maybe this helps from the web2py error ticket: (self=gluon.dal.MSSQLAdapter object, table=Table auth_event (id, time_stamp,client_ip,user_id,origin,description), fields=[(gluon.dal.Field object, 'auth'), (gluon.dal.Field object, datetime.datetime(2013, 3, 13, 16, 46, 4, 117000)), (gluon.dal.Field object, '192.168.77.4'), (gluon.dal .Field object, 1), (gluon.dal.Field object, 'User 1 Logged-in')]) -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
ok. seems that -MM-DD HH:MM:SS is somewhat unrecognizeable by your databasethis is strange because that representation should be universally valid. Let's try with a more specific (just to nail it) now = '2013-03-13 16:46:04' and see if the issue is the same On Wednesday, March 13, 2013 4:59:00 PM UTC+1, Marian wrote: Maybe this helps from the web2py error ticket: (self=gluon.dal.MSSQLAdapter object, table=Table auth_event (id, time_stamp,client_ip,user_id,origin,description), fields=[(gluon.dal. Field object, 'auth'), (gluon.dal.Field object, datetime.datetime(2013, 3, 13, 16, 46, 4, 117000)), (gluon.dal.Field object, '192.168.77.4'), ( gluon.dal.Field object, 1), (gluon.dal.Field object, 'User 1 Logged-in' )]) -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Yes this fails, this is really strange. As said it worked yesterday and it's not a local database server which I restarted or something like that. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I did a patch in the MSSQLAdapter.represent function. This is the only format working for me. But it is obviously not a good way to implement an own dateformat. elif fieldtype == 'datetime': if isinstance(obj, datetime.datetime): obj = obj.isoformat()[:19] elif isinstance(obj, datetime.date): obj = obj.isoformat()[:10]+' 00:00:00' else: obj = str(obj) -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
It probably worked yesterday because yesterday was 3-12 which could be interpreted as December 3rd or March 12th. 3-13 can only be interpreted as march 2013, so perhaps it was reading the date incorrectly? On Wednesday, March 13, 2013 9:16:26 AM UTC-7, Marian wrote: Yes this fails, this is really strange. As said it worked yesterday and it's not a local database server which I restarted or something like that. -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Looks like line 1447 in DAL.py is to blame.
if isinstance(obj, datetime.datetime):
obj = obj.isoformat()[:19].replace('T',' ')
should be:
obj = obj.isoformat()[:19]
That will preserve the 'T'
If you don't need the 'T' (hey, why strip it if it works?)
You should do this:
obj = obj.isoformat(' ')[:19]
If you need the space,
On Wednesday, March 13, 2013 11:53:46 AM UTC-7, Derek wrote:
It probably worked yesterday because yesterday was 3-12 which could be
interpreted as December 3rd or March 12th. 3-13 can only be interpreted as
march 2013, so perhaps it was reading the date incorrectly?
On Wednesday, March 13, 2013 9:16:26 AM UTC-7, Marian wrote:
Yes this fails, this is really strange. As said it worked yesterday and
it's not a local database server which I restarted or something like that.
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Ok that was a good hind. working directly with pyodbc:
Working:
now = datetime.datetime(2013, 3, 12, 16, 46, 4,
117000).isoformat()[:19].replace('T',' ')
Not working:
now = datetime.datetime(2013, 3, 13, 16, 46, 4,
117000).isoformat()[:19].replace('T',' ')
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
import pyodbc
import datetime
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=barney;DATABASE=imagecontest_dev;UID=imagecontest2013;PWD=imagecontest2013'
)
cursor = cnxn.cursor()
now = datetime.datetime(2013, 3, 13, 16, 46, 4, 117000).isoformat()[:19].
replace('T',' ')
cursor.execute(insert into auth_event(time_stamp, client_ip, origin,
description) values (?, ?, ?, ?), now, '192.0.0.1', 'origin', 'description'
)
cnxn.commit()
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Do a trace and see what's actually being passed into that query.
On Wednesday, March 13, 2013 12:45:35 PM UTC-7, Marian wrote:
import pyodbc
import datetime
cnxn = pyodbc.connect('DRIVER={SQL Server Native Client
10.0};SERVER=barney;DATABASE=imagecontest_dev;UID=imagecontest2013;PWD=imagecontest2013'
)
cursor = cnxn.cursor()
now = datetime.datetime(2013, 3, 13, 16, 46, 4, 117000).isoformat()[:19].
replace('T',' ')
cursor.execute(insert into auth_event(time_stamp, client_ip, origin,
description) values (?, ?, ?, ?), now, '192.0.0.1', 'origin',
'description')
cnxn.commit()
--
---
You received this message because you are subscribed to the Google Groups
web2py-users group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
The query is something like this: '2013-03-13 20:59:25' -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
This seems to be ANSI SQL format, which seems to be not language neutral http://www.karaszi.com/sqlserver/info_datetime.asp -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
I don't see any reason why the base adapter can't use the ISO 8601 format with a 'T' in it. On Wednesday, March 13, 2013 1:08:23 PM UTC-7, Marian wrote: This seems to be ANSI SQL format, which seems to be not language neutral http://www.karaszi.com/sqlserver/info_datetime.asp -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
Ok, this page may explain what's going on... http://msdn.microsoft.com/en-us/library/ms180878(v=sql.100).aspx For ANSI SQL date format, it IS DATEFORMAT dependent (we're using a datetime not datetime2) and for 8601 it is NOT DATEFORMAT dependant. MSSQL only recognizes the T containing one as ISO8601. So, you can change the column type to datetime2, or change DAL to add the T back in there. On Wednesday, March 13, 2013 1:28:30 PM UTC-7, Marian wrote: ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
As far as compatibility issues, sqlite, mysql, and postgres all support ISO8601 with an optional T. On Wednesday, March 13, 2013 1:28:30 PM UTC-7, Marian wrote: ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL
can you please open an issue about this so it gets tracked ? On Wednesday, March 13, 2013 9:39:21 PM UTC+1, Derek wrote: As far as compatibility issues, sqlite, mysql, and postgres all support ISO8601 with an optional T. On Wednesday, March 13, 2013 1:28:30 PM UTC-7, Marian wrote: ISO 8601 format seems to be a better format, at least for mssql. Patching this maybe can lead to compatibility issues. I don't know. :( -- --- You received this message because you are subscribed to the Google Groups web2py-users group. To unsubscribe from this group and stop receiving emails from it, send an email to web2py+unsubscr...@googlegroups.com. For more options, visit https://groups.google.com/groups/opt_out.
[web2py] Re: MSSQL datetime conversion issue
did you try those on a fresh app and a fresh database ? Those issue never
rise if tables and values are managed by web2py, they only show up with
wrong migrations, legacy tables, etc . I use datetime fields and MSSQL2008
with no issues at all.
Il giorno mercoledì 16 gennaio 2013 13:26:45 UTC+1, Gottfried Stauffenberg
ha scritto:
I have been trying to test to use Microsoft SQL Server 2008 R1 as a back
end for an web2py application. I have encountered the usual cascading
deletes issue and have solved it by setting the On Delete action to no
action.
However I still do not get the test app running as there seems to be an
issue with the conversion of datetime fields such as the standard
created_on fields to the MSSQL format. I believe it is because
datetime.now() has a higher microsecond precision (6 digits) than MSSQL (3
digits). The tables in MSSQL were all created by web2py, so there cannot be
a migration issue. I cannot even create a new user as the registration
process is using a datetime field in one of the auth tables as well.
In searching the internet I found a similar issue way back in 2009, but
nothing since then. I know the answer was that it is a MSSQL configuration
issue, but I can not change the configuration. Has anybody any idea how I
could maybe truncate the timestamp before the insert statement?
Error Message (in German)
class 'pyodbc.DataError' ('22007', '[22007] [Microsoft][ODBC SQL Server
Driver][SQL Server]Bei der Konvertierung eines varchar-Datentyps in einen
datetime-Datentyp liegt der Wert au\xdferhalb des g\xfcltigen Bereichs.
(242) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Die Anweisung wurde beendet. (3621)')
Database schema besides the usual authentication tables from :
db.define_table('t_currency',
Field('f_name', type='string',
label=T('Name')),
auth.signature,
format='%(f_name)s',
migrate=settings.migrate)
db.define_table('t_currency_archive',db.t_currency,Field('current_record','reference
t_currency',readable=False,writable=False))
db.define_table('t_test',
Field('f_name', type='string',
label=T('Name')),
Field('f_value', type='double',
label=T('Value')),
Field('f_curr', type='reference t_currency',
label=T('Curr')),
auth.signature,
format='%(f_name)s',
migrate=settings.migrate)
db.define_table('t_test_archive',db.t_test,Field('current_record','reference
t_test',readable=False,writable=False))
--
[web2py] Re: MSSQL How to access tables which not created by web2py
Thanks a lot, Bruno.
That really helps, but since my legacy DBs are too much and too
complex (MSSQL Server are only readable to me), I decide to create my
own connections and queries to my MSSQL Server.
Thanks again.
On Jan 18, 3:57 pm, Bruno Rocha rochacbr...@gmail.com wrote:
You need to map the existent table and set migrate=False, also you will
need to specify which field is the 'id' or primary keys, and for all fields
you need to tell the datatype.
msdb.define_table(
'test',
FIeld('cod', 'id'),
Field('field1', datatype),
Field('field2', datatype),
migrate=False
)
look
here:http://web2py.com/books/default/chapter/29/6#Legacy-databases-and-key...
--
Bruno Rocha
[http://rochacbruno.com.br]
Re: [web2py] Re: MSSQL How to access tables which not created by web2py
So you can use: results = msdb.execute_sql(SELECT * FROM table.) -- Bruno Rocha [http://rochacbruno.com.br]
[web2py] Re: MSSQL How to access tables which not created by web2py
Cool, How can i forgot the executesql! thank you very much!!! On Jan 18, 4:19 pm, Bruno Rocha rochacbr...@gmail.com wrote: So you can use: results = msdb.execute_sql(SELECT * FROM table.) -- Bruno Rocha [http://rochacbruno.com.br]
[web2py] Re: mssql connect
Brian: You're the man...said the right thing. I didn't know pyodbc did not ship with web2py. Download and dropped it in the same folder as web2py (don't know if this was mentioned in doc.) Works like a beauty. Thanks all again. On Aug 31, 7:36 pm, Brian M bmere...@gmail.com wrote: What version of python are you using? Have you installed the matching pyodbc version fromhttp://code.google.com/p/pyodbc/? ~Brian
[web2py] Re: mssql connect
Glad you got it working! :)
[web2py] Re: mssql connect
The connection string seems ok but:
- check user and password
- leave out the port (1433 is the default)
- if yo are using the Express version add the instance name e.g.
POPE\\SQLEXPRESS/xPAC
On Aug 31, 2:25 pm, patrick moon pmo...@gmail.com wrote:
I'm using the doc suggested connection string:
db = DAL('mssql://shxxx:shaxxx@POPE:1433/xPAC')
and this is the error I'm getting:
Traceback (most recent call last):
File gluon/restricted.py, line 192, in restricted
File C:/_dev/temp/web2py/applications/welcome/models/db.py, line
18, in module
File gluon/dal.py, line 3981, in __init__
RuntimeError: Failure to connect, tried 5 times:
'NoneType' object has no attribute 'connect'
Appreciate any help
[web2py] Re: mssql connect
Also check to make sure it's loading the MSSQL database driver. You have to run from source, the binary doesn't have it: http://www.web2py.com/book/default/chapter/06#Dependencies
[web2py] Re: mssql connect
Additonal to Denes,
I'm using Microsoft SQL Server 2008 Express and successfully
connection with this.
db = DAL(mssql://UserID:Password@ComputerName\SQLEXPRESS/
DatabaseName)
On Aug 31, 1:52 pm, DenesL denes1...@yahoo.ca wrote:
The connection string seems ok but:
- check user and password
- leave out the port (1433 is the default)
- if yo are using the Express version add the instance name e.g.
POPE\\SQLEXPRESS/xPAC
On Aug 31, 2:25 pm, patrick moon pmo...@gmail.com wrote:
I'm using the doc suggested connection string:
db = DAL('mssql://shxxx:shaxxx@POPE:1433/xPAC')
and this is the error I'm getting:
Traceback (most recent call last):
File gluon/restricted.py, line 192, in restricted
File C:/_dev/temp/web2py/applications/welcome/models/db.py, line
18, in module
File gluon/dal.py, line 3981, in __init__
RuntimeError: Failure to connect, tried 5 times:
'NoneType' object has no attribute 'connect'
Appreciate any help
[web2py] Re: mssql connect
I tried to run from the source but this is what I get. Module use of python25.dll conflicts with this version of Python. Is there a way around this? Thanks On Aug 31, 12:14 pm, Willoughby neil.erik...@gmail.com wrote: Also check to make sure it's loading the MSSQL database driver. You have to run from source, the binary doesn't have it:http://www.web2py.com/book/default/chapter/06#Dependencies
[web2py] Re: mssql connect
What version of python are you using? Have you installed the matching pyodbc version from http://code.google.com/p/pyodbc/? ~Brian
[web2py] Re: MSSQL DAL multiple cascade paths not allowed
I would prefer to leave this as it is for backward compatibility
reasons but you can do Field(, ondelete=no action) correct?
In any case open an issue on google code.
On Aug 17, 2:16 pm, Omi Chiba ochib...@gmail.com wrote:
I was fololwing the web2py book with mssql for my database.
db.define_table('comment',
Field('page_id', db.page),
Field('body', 'text'),
Field('created_on', 'datetime', default=request.now),
Field('created_by', db.auth_user, default=auth.user_id))
will generate
CREATE TABLE comment(
id INT IDENTITY PRIMARY KEY,
page_id INT NULL, CONSTRAINT comment_page_id__constraint FOREIGN
KEY (page_id) REFERENCES page(id) ON DELETE CASCADE,
body TEXT NULL,
created_on DATETIME NULL,
created_by INT NULL, CONSTRAINT comment_created_by__constraint
FOREIGN KEY (created_by) REFERENCES auth_user(id) ON DELETE CASCADE
)
and will cause the error says:
Introducing FOREIGN KEY constraint 'comment_created_by__constraint' on
table 'comment' may cause cycles or multiple cascade paths. Specify ON
DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY
constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
The details for the error is descrived in
herehttp://support.microsoft.com/kb/321843
After I made the change on dal.py it worked fine.
def __init__(
self,
fieldname,
type='string',
length=None,
default=DEFAULT,
required=False,
requires=DEFAULT,
# ondelete='CASCADE',
ondelete='NO ACTION',
Can we make this change as path to avoid the error for mssql ? I'm not
sure if other database has the same problem.
[web2py] Re: MSSQL Field Name: file is not allowed
On Tuesday, August 16, 2011 3:43:41 PM UTC-4, Omi Chiba wrote:
I just want to share my experience which takes hours to figure out. I
was reading web2py book - 03 Overview - An Image Blog, it worked fine
with SQLite and tried to use mssql server on my local PC which is also
the new experience to me...
Replaced the DAL for sqlite with mssql
db = DAL(sqlite://storage.sqlite)
= db = DAL(mssql://ID:PASS@SERVERNAME\SQLEXPRESS/images)
db.define_table('image',
Field('title'),
Field('file', 'upload'))
Then It complains Incorrect syntax near the keyword 'file'.
... the problem was that field name file is reserved word for mssql
server and cannot use it even I run the statement directly on the
server..
CREATE TABLE image(
id INT IDENTITY PRIMARY KEY,
title VARCHAR(512) NULL,
file VARCHAR(512) NULL
);
But it actually allow to create the field called file using new
table wizard.
It's very strange.
The wizard prepends 'f_' before each field name, so it would be 'f_file',
which obviously won't cause an error.
Note, there is a 'check_reserved' argument to help you identify reserved
keywords for each database:
http://web2py.com/book/default/chapter/06#Reserved-Keywords.
Anthony
[web2py] Re: MSSQL to CSV
If you know the mssql table structure, you can create a quick web2py
model:
db=DAL('mssql://...')
db.define_table('yourtable',Field(...),)
print str(db(db.yourtable).select()) # outputs the CSV
On May 18, 3:32 pm, mart msenecal...@gmail.com wrote:
Hi,
there's this MSSQL server running remotely, and I would like to access
it with DAL. So I'm thinking, i should export to CSV then import using
dal.py in something local (like SQLite). Here's the problem... I don't
know how to dump the MSSQL tables to CSV :(
This is a 2005 MS SQL install. Would anyone have a trick? is there
some sort of tool/option/something i can use to say 'dump all tables
to csv' ?
I assume, them i can simply point top those CSV file and import?
Thanks for the help,
Mart :)
[web2py] Re: MSSQL to CSV
ok, that's sounds like a good idea (btw - dal is worth gold!)
Thanks for the help Massimo! :)
On May 18, 4:43 pm, Massimo Di Pierro massimo.dipie...@gmail.com
wrote:
If you know the mssql table structure, you can create a quick web2py
model:
db=DAL('mssql://...')
db.define_table('yourtable',Field(...),)
print str(db(db.yourtable).select()) # outputs the CSV
On May 18, 3:32 pm, mart msenecal...@gmail.com wrote:
Hi,
there's this MSSQL server running remotely, and I would like to access
it with DAL. So I'm thinking, i should export to CSV then import using
dal.py in something local (like SQLite). Here's the problem... I don't
know how to dump the MSSQL tables to CSV :(
This is a 2005 MS SQL install. Would anyone have a trick? is there
some sort of tool/option/something i can use to say 'dump all tables
to csv' ?
I assume, them i can simply point top those CSV file and import?
Thanks for the help,
Mart :)
[web2py] Re: MSSQL to CSV
ERRATA:
db=DAL('mssql://...')
db.define_table('yourtable',Field(...),,migrate=True,fake_migrate=True)
print str(db(db.yourtable).select()) # outputs the CSV
[web2py] Re: MSSQL to CSV
ok, got it! thanks again :)
On May 18, 5:02 pm, Massimo Di Pierro massimo.dipie...@gmail.com
wrote:
ERRATA:
db=DAL('mssql://...')
db.define_table('yourtable',Field(...),,migrate=True,fake_migrate=True)
print str(db(db.yourtable).select()) # outputs the CSV
[web2py] Re: MSSQL to CSV
On May 18, 5:02 pm, Massimo Di Pierro massimo.dipie...@gmail.com
wrote:
db.define_table('yourtable',Field(...),,migrate=True,fake_migrate=True)
why not just ...,migrate=False) ?
what does that combo provide?
[web2py] Re: MSSQL Connection error on MacOS
Bruno, I too have had this problem. It seems as if Mac OS X has some real problems connecting to MSSQL, which isn't too much of a surprise I guess. The way that I develop on Mac OS X may be crazy or over-complicated, but I find it works well for me: I set up a Ubuntu Server virtual machine in Parallels, set it to use Bridged Networking, give it an IP address, then install and configure pyodbc and freetds. Both of those can be installed via aptitude. I've done it a few times, so it only takes me about 10 minutes to set up a web2py installation in a virtual machine. I also develop from multiple Macs, so I have a copy of the virtual machine on each Mac, and I use Dropbox to sync the code between each computer. I've been developing this way for about a year now, starting with Django, and haven't found any problems with it yet. Plus, I am targeting Linux as the production server for my app when it's complete, so if there are any quirks, I'll know about them before putting the app into production. Hope this helps, and if you need a more detailed setup guide, I actually have some instructions I wrote down for setting up and configuring a virtual machine for web2py development. @Annet: Bruno said MSSQL, not MySQL. Everyone seems to get those two confused because of the similar spelling, though.
Re: [web2py] Re: MSSQL Connection error on MacOS
I solved the problem with HomeBrew brew install unixodbc brew install freetds after that you can set up your dsn in Utilities iODBC. Thank you. -- Bruno Rocha [ About me: http://zerp.ly/rochacbruno ] On Thu, Mar 31, 2011 at 10:39 AM, Ross Peoples ross.peop...@gmail.comwrote: Bruno, I too have had this problem. It seems as if Mac OS X has some real problems connecting to MSSQL, which isn't too much of a surprise I guess. The way that I develop on Mac OS X may be crazy or over-complicated, but I find it works well for me: I set up a Ubuntu Server virtual machine in Parallels, set it to use Bridged Networking, give it an IP address, then install and configure pyodbc and freetds. Both of those can be installed via aptitude. I've done it a few times, so it only takes me about 10 minutes to set up a web2py installation in a virtual machine. I also develop from multiple Macs, so I have a copy of the virtual machine on each Mac, and I use Dropbox to sync the code between each computer. I've been developing this way for about a year now, starting with Django, and haven't found any problems with it yet. Plus, I am targeting Linux as the production server for my app when it's complete, so if there are any quirks, I'll know about them before putting the app into production. Hope this helps, and if you need a more detailed setup guide, I actually have some instructions I wrote down for setting up and configuring a virtual machine for web2py development. @Annet: Bruno said MSSQL, not MySQL. Everyone seems to get those two confused because of the similar spelling, though.
Re: [web2py] Re: MSSQL Connection error on MacOS
Glad you were able to figure it out.
[web2py] Re: MSSQL Connection error on MacOS
It seens someone are having same issue woth Pyodbc and sqlalchemy
http://stackoverflow.com/questions/4665078/connect-to-mssql-from-sqlalchemy-by-pyodbc-on-mac-os
So it is not web2py DAL issue, it is Pyodbc issue, anyone aware?
On Tue, Mar 29, 2011 at 12:28 PM, Bruno Rocha rochacbr...@gmail.com wrote:
HI,
I am trying connection to MSSQL using standalone DAL, but I am getting this
error:
Traceback (most recent call last):
File baixar_arquivos.py, line 10, in module
db = DAL('mssql://user1:pass...@servicogente.com.br/coelba')
File /Users/rochacbruno/Dropbox/gente/web2py/gluon/dal.py, line 3665,
in __init__
raise RuntimeError, Failure to connect, tried 5 times:\n%s % error
RuntimeError: Failure to connect, tried 5 times:
('0', '[0] [iODBC][Driver Manager]dlopen({SQL Server}, 6): image
not found (0) (SQLDriverConnectW)')
I installed pyodbc on MacOS X, I dont know how to deal with this error
message.
--
Bruno Rocha
[ About me: http://zerp.ly/rochacbruno ]
[web2py] Re: MSSQL Connection error on MacOS
Hi Bruno, I had the same problem using MySQLdb, I solved it by setting up a my.cnf file in /etc the following way: Log in to OS X using an administrator-level account, open Terminal; cd /usr/local/mysql/support-files/ sudo cp my-huge.cnf /etc/my.cnf and enter your admin password when prompted. I hope this helps you solve the problem. Annet.
[web2py] Re: mssql, login problems
As it doesn't seem possible to change self._uri in SQLDB,
I solved the problem by adding an extra keyword argument (which travels to
SQLDB):
Database_Name = r'mssql://NKCV2
*MSSQL_Login = ';UID=MY_SQL_LOGIN;PWD=MY_PASSWORD'
*My_DB = DAL ( Database_Name, folder = folder, *MSSQL_Login = MSSQL_Login* )
cheers,
Stef
On 05-03-2011 00:34, Stef Mientki wrote:
hello,
With the DAL I created tables in an MSSQL database, which I can access as
long as I login as the
same user.
I created the table with a windows login on my own machine, like this
Database_Name = r'mssql://NKCV2'
My_DB = DAL ( Database_Name, folder = folder )
Now I'm on anther machine, I can not use the windows login, so I use a MSSQL
login
( I found the syntax by trial and error)
*Database_Name = r'mssql://NKCV2;UID=MY_SQL_LOGIN;PWD=MY_PASSWORD'
*My_DB = DAL ( Database_Name, folder = folder )
The login works, I can contact de database,
with the DAL is trying to create new tables (which I'm not allowed, and as
far as know, I don't
need to because they are already there)
File P:\Python26\lib\site-packages\gluon\sql.py, line 1100, in lambda
self._execute = lambda *a, **b: self._cursor.execute(*a, **b)
ProgrammingError: ('42000', [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]CREATE TABLE
permission denied in database 'NKCV2'. (262) (SQLExecDirectW))
Could this be caused, by the DAL creating other filenames to store the table
information ?
Any suggestions to solve this problem ?
thanks,
Stef Mientki
Re: [web2py] Re: MSSQL or MSSQL2
On 14-02-2011 22:17, Massimo Di Pierro wrote: The difference is subtle. as SQLserver uses utf-16, the size will double, am I correct ? thanks, Stef Mientki mssql is the first version of the adapter, the one that everybody uses. It stores data as UTF8 but in ascii. That means the length of the fields is not the number of unicode chars but the length of the utf8 encoded chars. It also uses BIT for booleans. mssql2 uses unicode so length is correct and CHAR(1) (T or F) for booleans. This is newer and less tested. On Feb 14, 1:15 pm, Ross Peoples ross.peop...@gmail.com wrote: I am new to web2py and I just finished reading the book and have started to develop my first application. I recently had the same problem mentioned in this thread:http://groups.google.com/group/web2py/browse_thread/thread/e620c5708f... I was wondering though, what is the difference between using mssql:// and mssql2? I am currently developing an application using am existing database on Microsoft SQL Server 2000 that will eventually be upgraded to Microsoft SQL Server 2008 R2. Thanks
[web2py] Re: MSSQL or MSSQL2
I think so but I cannot say for sure. I am not expert on mssql. On Mar 1, 5:03 pm, Stef Mientki stef.mien...@gmail.com wrote: On 14-02-2011 22:17, Massimo Di Pierro wrote: The difference is subtle. as SQLserver uses utf-16, the size will double, am I correct ? thanks, Stef Mientki mssql is the first version of the adapter, the one that everybody uses. It stores data as UTF8 but in ascii. That means the length of the fields is not the number of unicode chars but the length of the utf8 encoded chars. It also uses BIT for booleans. mssql2 uses unicode so length is correct and CHAR(1) (T or F) for booleans. This is newer and less tested. On Feb 14, 1:15 pm, Ross Peoples ross.peop...@gmail.com wrote: I am new to web2py and I just finished reading the book and have started to develop my first application. I recently had the same problem mentioned in this thread:http://groups.google.com/group/web2py/browse_thread/thread/e620c5708f... I was wondering though, what is the difference between using mssql:// and mssql2? I am currently developing an application using am existing database on Microsoft SQL Server 2000 that will eventually be upgraded to Microsoft SQL Server 2008 R2. Thanks
[web2py] Re: MSSQL or MSSQL2
The difference is subtle. mssql is the first version of the adapter, the one that everybody uses. It stores data as UTF8 but in ascii. That means the length of the fields is not the number of unicode chars but the length of the utf8 encoded chars. It also uses BIT for booleans. mssql2 uses unicode so length is correct and CHAR(1) (T or F) for booleans. This is newer and less tested. On Feb 14, 1:15 pm, Ross Peoples ross.peop...@gmail.com wrote: I am new to web2py and I just finished reading the book and have started to develop my first application. I recently had the same problem mentioned in this thread:http://groups.google.com/group/web2py/browse_thread/thread/e620c5708f... I was wondering though, what is the difference between using mssql:// and mssql2? I am currently developing an application using am existing database on Microsoft SQL Server 2000 that will eventually be upgraded to Microsoft SQL Server 2008 R2. Thanks
[web2py] Re: MSSQL or MSSQL2
Thanks for the clarification. My existing database uses BIT fields, so I will be sticking to mssql for now. On Feb 14, 4:17 pm, Massimo Di Pierro massimo.dipie...@gmail.com wrote: The difference is subtle. mssql is the first version of the adapter, the one that everybody uses. It stores data as UTF8 but in ascii. That means the length of the fields is not the number of unicode chars but the length of the utf8 encoded chars. It also uses BIT for booleans. mssql2 uses unicode so length is correct and CHAR(1) (T or F) for booleans. This is newer and less tested. On Feb 14, 1:15 pm, Ross Peoples ross.peop...@gmail.com wrote: I am new to web2py and I just finished reading the book and have started to develop my first application. I recently had the same problem mentioned in this thread:http://groups.google.com/group/web2py/browse_thread/thread/e620c5708f... I was wondering though, what is the difference between using mssql:// and mssql2? I am currently developing an application using am existing database on Microsoft SQL Server 2000 that will eventually be upgraded to Microsoft SQL Server 2008 R2. Thanks
[web2py] Re: MSSQL Select statements
You cannot this in web2py. perhaps you can create a database view.
On Nov 13, 12:11 pm, Crim crimsonang...@gmail.com wrote:
alright so i got a connection between my MSSql server any web2py. that
has been all sorted out but now im having issues with how to select a
tuple from web2py
in web2py i have this code:
if db(db.User.Email == s).select():
print success
which should check if the users email is in the db
but when i write a select statement for my server its like so:
SELECT TOP 1000 [Email]
,[Name]
,[PIN]
,[Phone]
,[Address]
,[Mileage]
,[MealPref]
FROM [master].[dbo].[User]
notice the dbo? do i have to include this in my select .. (have tried
and it still gives errors)
the error snapshot:
type 'exceptions.KeyError'('user')
thanks,
Crim
[web2py] Re: MSSQL Select statements
what cant i use? the web2py statement?
On Nov 13, 12:26 pm, mdipierro mdipie...@cs.depaul.edu wrote:
You cannot this in web2py. perhaps you can create a database view.
On Nov 13, 12:11 pm, Crim crimsonang...@gmail.com wrote:
alright so i got a connection between my MSSql server any web2py. that
has been all sorted out but now im having issues with how to select a
tuple from web2py
in web2py i have this code:
if db(db.User.Email == s).select():
print success
which should check if the users email is in the db
but when i write a select statement for my server its like so:
SELECT TOP 1000 [Email]
,[Name]
,[PIN]
,[Phone]
,[Address]
,[Mileage]
,[MealPref]
FROM [master].[dbo].[User]
notice the dbo? do i have to include this in my select .. (have tried
and it still gives errors)
the error snapshot:
type 'exceptions.KeyError'('user')
thanks,
Crim
[web2py] Re: MSSQL Select statements
There is no webpy API to generate
FROM [master].[dbo].[User]
only
FROM [User]
On Nov 13, 12:45 pm, Crim crimsonang...@gmail.com wrote:
what cant i use? the web2py statement?
On Nov 13, 12:26 pm, mdipierro mdipie...@cs.depaul.edu wrote:
You cannot this in web2py. perhaps you can create a database view.
On Nov 13, 12:11 pm, Crim crimsonang...@gmail.com wrote:
alright so i got a connection between my MSSql server any web2py. that
has been all sorted out but now im having issues with how to select a
tuple from web2py
in web2py i have this code:
if db(db.User.Email == s).select():
print success
which should check if the users email is in the db
but when i write a select statement for my server its like so:
SELECT TOP 1000 [Email]
,[Name]
,[PIN]
,[Phone]
,[Address]
,[Mileage]
,[MealPref]
FROM [master].[dbo].[User]
notice the dbo? do i have to include this in my select .. (have tried
and it still gives errors)
the error snapshot:
type 'exceptions.KeyError'('user')
thanks,
Crim
[web2py] Re: MSSQL Select statements
im not using web2py to do the second select statement the one FROM
[master].[dbo].[User]
just the:
if db(db.User.Email == s).select():
print success
i connected to my db and want to select the table user and the column
email
On Nov 13, 1:05 pm, mdipierro mdipie...@cs.depaul.edu wrote:
There is no webpy API to generate
FROM [master].[dbo].[User]
only
FROM [User]
On Nov 13, 12:45 pm, Crim crimsonang...@gmail.com wrote:
what cant i use? the web2py statement?
On Nov 13, 12:26 pm, mdipierro mdipie...@cs.depaul.edu wrote:
You cannot this in web2py. perhaps you can create a database view.
On Nov 13, 12:11 pm, Crim crimsonang...@gmail.com wrote:
alright so i got a connection between my MSSql server any web2py. that
has been all sorted out but now im having issues with how to select a
tuple from web2py
in web2py i have this code:
if db(db.User.Email == s).select():
print success
which should check if the users email is in the db
but when i write a select statement for my server its like so:
SELECT TOP 1000 [Email]
,[Name]
,[PIN]
,[Phone]
,[Address]
,[Mileage]
,[MealPref]
FROM [master].[dbo].[User]
notice the dbo? do i have to include this in my select .. (have tried
and it still gives errors)
the error snapshot:
type 'exceptions.KeyError'('user')
thanks,
Crim
[web2py] Re: MSSQL Select statements
thanks for the in-depth reply ^ ^
im using existing tables and i master and another one stuff setup to
do the same thing but this is a school project so im not to picky at
the moment xD but thanks for the info for future use.
Can i do the db(db.user.email == s).select() if i dont instantiate it
in web2py? do i still have to define it? Considering the tables exist
in the MSSql db?
On Nov 13, 2:05 pm, Niphlod niph...@gmail.com wrote:
uhm are you using existing tables or you are using web2py to
create them ??
dbo shouldn't affect at all the query, but a few hints nonetheless :
master is a really nasty place to create tables master db
should be left untouched in web2py you define a database when you
istantiate db object, and all the query done on that db won't be able
to see other databases
that stated, if you have your connection string as
mssql://username:passw...@localhost/master
the query select * from [master].[dbo].[User] where [master].[dbo].
[User]=s should be accomplished doing:
db.define_table('user',
.
Field('email', 'string'),
.
)
and db(db.user.email == s).select()
anyway, I'm not sure (I don't have MSSQL installed to try out, but I
can reply you on monday at work) that there are a few places where
this implementation could not work:
- web2py creates tables and fields with lowercase letters, so User
get selectable only if database is set to be case insensitive
- dbo stands for dbowner and it represent the schema which the
object (in this case, a table) belongs. if your user has not the
db_owner role on the db you won't be able to see it
A few words also on this. Having objects beloging to different schemas
under the same database it's definitely an option, but web2py
(actually the library it uses, and many others like that) can't
choose different schemas... they simply ignore it and the database
defaults to whatever the default is for the user querying the database
itself.
You can have different schemas to separate between permissions on
different objects in the same database, but to avoid headaches it's
always better to create a separate user that owns a single database
and put every data inside it.
If the user in the connection string is db_owner of the database,
select * from dbo.table is perfectly equivalent to select * from
table
If you have any other question please ask, I'll be glad to test it out
on Monday
[web2py] Re: MSSQL Select statements
web2py DAL only sees tables that are defined via the DAL. For raw sql
you can do db.executesql()
On Nov 13, 2:39 pm, Crim crimsonang...@gmail.com wrote:
thanks for the in-depth reply ^ ^
im using existing tables and i master and another one stuff setup to
do the same thing but this is a school project so im not to picky at
the moment xD but thanks for the info for future use.
Can i do the db(db.user.email == s).select() if i dont instantiate it
in web2py? do i still have to define it? Considering the tables exist
in the MSSql db?
On Nov 13, 2:05 pm, Niphlod niph...@gmail.com wrote:
uhm are you using existing tables or you are using web2py to
create them ??
dbo shouldn't affect at all the query, but a few hints nonetheless :
master is a really nasty place to create tables master db
should be left untouched in web2py you define a database when you
istantiate db object, and all the query done on that db won't be able
to see other databases
that stated, if you have your connection string as
mssql://username:passw...@localhost/master
the query select * from [master].[dbo].[User] where [master].[dbo].
[User]=s should be accomplished doing:
db.define_table('user',
.
Field('email', 'string'),
.
)
and db(db.user.email == s).select()
anyway, I'm not sure (I don't have MSSQL installed to try out, but I
can reply you on monday at work) that there are a few places where
this implementation could not work:
- web2py creates tables and fields with lowercase letters, so User
get selectable only if database is set to be case insensitive
- dbo stands for dbowner and it represent the schema which the
object (in this case, a table) belongs. if your user has not the
db_owner role on the db you won't be able to see it
A few words also on this. Having objects beloging to different schemas
under the same database it's definitely an option, but web2py
(actually the library it uses, and many others like that) can't
choose different schemas... they simply ignore it and the database
defaults to whatever the default is for the user querying the database
itself.
You can have different schemas to separate between permissions on
different objects in the same database, but to avoid headaches it's
always better to create a separate user that owns a single database
and put every data inside it.
If the user in the connection string is db_owner of the database,
select * from dbo.table is perfectly equivalent to select * from
table
If you have any other question please ask, I'll be glad to test it out
on Monday
[web2py] Re: MSSQL Select statements
Hi Crim,
if you can use db.user.email that means you have defined the table in
web2py.
As I understand it, as per your previous posts, that table was not
created with web2py so it must be a legacy table.
You can access it depending on two things:
1) If the table has an auto incrementing field similar to web2py's id
field then you just define that field as type 'id' (don't forget to
set migrate=False as per
http://web2py.com/book/default/chapter/06#Legacy-Databases-and-Keyed-Tables).
2) If there is no such field but the table has one or more fields that
are keys you can still access it using a primarykey definition (see
same section in the book as above).
There should be no need to use [dbo] or [master].[dbo] anywhere.
Posting your model would make it easier for others to help you.
Dens.
On Nov 13, 3:39 pm, Crim crimsonang...@gmail.com wrote:
thanks for the in-depth reply ^ ^
im using existing tables and i master and another one stuff setup to
do the same thing but this is a school project so im not to picky at
the moment xD but thanks for the info for future use.
Can i do the db(db.user.email == s).select() if i dont instantiate it
in web2py? do i still have to define it? Considering the tables exist
in the MSSql db?
On Nov 13, 2:05 pm, Niphlod niph...@gmail.com wrote:
uhm are you using existing tables or you are using web2py to
create them ??
dbo shouldn't affect at all the query, but a few hints nonetheless :
master is a really nasty place to create tables master db
should be left untouched in web2py you define a database when you
istantiate db object, and all the query done on that db won't be able
to see other databases
that stated, if you have your connection string as
mssql://username:passw...@localhost/master
the query select * from [master].[dbo].[User] where [master].[dbo].
[User]=s should be accomplished doing:
db.define_table('user',
.
Field('email', 'string'),
.
)
and db(db.user.email == s).select()
anyway, I'm not sure (I don't have MSSQL installed to try out, but I
can reply you on monday at work) that there are a few places where
this implementation could not work:
- web2py creates tables and fields with lowercase letters, so User
get selectable only if database is set to be case insensitive
- dbo stands for dbowner and it represent the schema which the
object (in this case, a table) belongs. if your user has not the
db_owner role on the db you won't be able to see it
A few words also on this. Having objects beloging to different schemas
under the same database it's definitely an option, but web2py
(actually the library it uses, and many others like that) can't
choose different schemas... they simply ignore it and the database
defaults to whatever the default is for the user querying the database
itself.
You can have different schemas to separate between permissions on
different objects in the same database, but to avoid headaches it's
always better to create a separate user that owns a single database
and put every data inside it.
If the user in the connection string is db_owner of the database,
select * from dbo.table is perfectly equivalent to select * from
table
If you have any other question please ask, I'll be glad to test it out
on Monday
