[web2py] Re: Searching for multiple words in SQLFORM.grid
thanks, this is very helpful.
On Friday, March 1, 2013 3:19:22 PM UTC+7, Mandar Vaze wrote:
>
> Hi,
>
> As you all know, SQLFORM.grid search does not support searching for
> multiple words - it throws an error "Invalid Query"
> Searching for multiple words may be a common requirement.
> I looked thru various older posting on this group, but none worked for me
> AS IS (Possiblly because they were old - it worked with whatever was
> current version of web2py at the time of original post) This one came the
> closest : https://groups.google.com/forum/?fromgroups=#!topic/web2py
> /9_1ECdKHKUo
>
> Here is the version that worked for me: (web2py Version 2.3.2 (2012-12-17
> 15:03:30) stable)
>
> #Referred to the web2py implementation of search (gluon/sqlhtml.py:def
> grid())
> #Modified to support multiple words
> def search_query(fields, keywords):
> if isinstance(keywords, (tuple, list)):
> keywords = keywords[0]
> request.vars.keywords = keywords
> key = keywords.strip()
> if key and not '"' in key and not "'" in key:
> SEARCHABLE_TYPES = ('string', 'text', 'list:string')
> words = key.split(' ') if key else []
> filters = []
> for field in fields:
> if field.type in SEARCHABLE_TYPES:
> all_words_filters = []
> for word in words:
> all_words_filters.append(field.contains(word))
> filters.append(reduce(lambda a, b: (a & b),
> all_words_filters))
> parts = filters
> else:
> parts = None
> if parts:
> return reduce(lambda a, b: a | b, parts)
> else:
> return None
>
> Why "&" in the first reduce ? Because if I use "|" then search results are
> too wide. e.g. if I typed "web2py is great" - then using "|" would return
> all the matches containing the word "is" even if "web2py" and "great" are
> missing.
> Off course using "&" isn't fool proof either since it will match a record
> having text "web2py is not great" as well (since all three words are
> present)
>
> One big change from what previous posts having been saying is that I did
> NOT override the search widget. Search widgets works well as is.
> Only difference is that I assigned the above function to "searchable"
> parameter when calling the SQLFORM.grid like
>
> grid = SQLFORM.grid(query,create=True, searchable=search_query) #Use
> other params as required
>
> I came to realize this after I looked at web2py code (gluon/sqlhtml.py)
> where if "searchable" is callable - then it is called. I somehow used to
> think that it is a boolean (True/False)
>
> I hope this helps someone
>
> -Mandar
>
--
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
> #306345<http://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
> #174655<http://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
> #2157<http://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)
>>>>>
>>>>>
>>>>
>>>> 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)')
>>>>
>>>&g
[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)
>>>
>>>
>>
>> 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.
Re: [web2py] Re: Unable to connect to mssql database
Hi Ross,
This is my first time to connect web2py with mssql using pyodbc, Cloud you
please advice me how to set it up. Currently I got error message ('IM002',
'[IM002] [unixODBC][Driver Manager]Data source name not found, and no
default driver specified (0) (SQLDriverConnect)') when I try to connect by
using this command => import pyodbc => pyodbc.connect('.
Thank again
A
On Wednesday, May 23, 2012 1:39:01 AM UTC+7, Ross Peoples wrote:
>
> I have been using MSSQL from Linux for quite some time. I use FreeTDS for
> my driver. This is my connection string:
>
> db = DAL('mssql://user:password@server/db_name?DRIVER={FreeTDS}')
>
> If you need help setting up FreeTDS / unixODBC, let me know. I have some
> notes on how to set this up using a Ubuntu machine.dd
>
--
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.
