[web2py] Re: Insert data in firebird database

[Константин Комков]

Nico de Groot and Raul Monares, thank you! I tested loading files less 64kb 
and thats files was loaded. On my work we will use folder "uploads" for 
store files.
But I don't understan why operation INSERT is working not in all cases.
There are two tables 'test' and 'bin_files'. Both tables have generators 
and triggers. When I trying insert somthing in 'test' I have error.
*Error snapshot*
(('Error while preparing SQL 
statement:\n- SQLCODE: -104\n- invalid request BLR at offset 46\n- 
generator GENID_TEST is not defined', -104, 335544343))
*Function argument list*

(self=, operation='SELECT 
gen_id(genid_test, 0) FROM rdb$database', cursor=, internal=True)
tables.py
db_xml.define_table('test',
Field('id', 'integer'),
Field('XYZ'),
Field('IOP'),
primarykey=['id'],
migrate=False
)
db_xml.define_table('bin_files',
Field('id','integer'),
Field('PARENT'),
Field('SRC_FILENAME'),
Field('DATA', 'blob'),
primarykey=['id','PARENT'],
migrate=False
)
default.py
db_xml.test.insert(XYZ='fzxf', IOP='dfsds')
After that:
db_xml.executesql("""INSERT INTO test (XYZ,IOP) VALUES 
('{0}','{1}')""".format('string1', 'string2'))
I see in database string1 and string2.
When I insert something in 'bin_files' all OK.





-- 
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.

Re: [web2py] Re: Insert data in firebird database

[Raul Monares]

The problem with the Firebird adapter is that it inserts blobs as base64 
strings directly in an INSERT statement instead of using the stream 
mechanism that Firebird provides. There's a reported bug about 
this: https://github.com/web2py/web2py/issues/666. At this time, there's no 
workaround.


On Monday, May 14, 2018 at 8:56:43 AM UTC-6, Nico de Groot wrote:
>
> I added blob to the fieldname because in my code DAL checks for reserved 
> SQL names. DATA is not allowed in some databases 
>
> Op zo 13 mei 2018 om 11:39 schreef Константин Комков  >
>
>> Nico de Groot, thank you for example and attention! I already have crud 
>> form and that form work. It load files in folder 'uploads'. I want to save 
>> that files in database now. I sure that 
>> stream=open(filepath + '\\' + i.file, 'rb')
>> and
>> stream.read()
>> made a long base64 string because I saw it on site. Then I decoded it and 
>> got picture.
>> Also I tried to save that file as usual SQL request in Firebird and 
>> web2py interfaces, where field DATA conteined string in base64. Like that:
>> INSERT INTO bin_files (PARENT, SRC_FILENAME, DATA) VALUES 
>> (33,'name.jpg','very 
>> very long base64 string').
>> After that Firebird has hanged. Web2py given an error message.
>> Just in case, are there between names 'DATA' and 'DATAblob'?
>> Yesterday and today I can't test all again because it's day off.
>> Raul Monares, thank you too! I cheked file that contain string in base64 
>> and it's sise 440kb. It's more then size of file in the beginning (name.jpg 
>> - 322kb). What should I do in that case, after all blob field can store 
>> large files.
>>
>> -- 
>> 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 a topic in the 
>> Google Groups "web2py-users" group.
>> To unsubscribe from this topic, visit 
>> https://groups.google.com/d/topic/web2py/_MVVu9PF0xk/unsubscribe.
>> To unsubscribe from this group and all its topics, send an email to 
>> web2py+un...@googlegroups.com .
>> For more options, visit https://groups.google.com/d/optout.
>>
>

-- 
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.

Re: [web2py] Re: Insert data in firebird database

[Nico de Groot]

I added blob to the fieldname because in my code DAL checks for reserved
SQL names. DATA is not allowed in some databases

Op zo 13 mei 2018 om 11:39 schreef Константин Комков 

> Nico de Groot, thank you for example and attention! I already have crud
> form and that form work. It load files in folder 'uploads'. I want to save
> that files in database now. I sure that
> stream=open(filepath + '\\' + i.file, 'rb')
> and
> stream.read()
> made a long base64 string because I saw it on site. Then I decoded it and
> got picture.
> Also I tried to save that file as usual SQL request in Firebird and web2py
> interfaces, where field DATA conteined string in base64. Like that:
> INSERT INTO bin_files (PARENT, SRC_FILENAME, DATA) VALUES (33,'name.jpg','very
> very long base64 string').
> After that Firebird has hanged. Web2py given an error message.
> Just in case, are there between names 'DATA' and 'DATAblob'?
> Yesterday and today I can't test all again because it's day off.
> Raul Monares, thank you too! I cheked file that contain string in base64
> and it's sise 440kb. It's more then size of file in the beginning (name.jpg
> - 322kb). What should I do in that case, after all blob field can store
> large files.
>
> --
> 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 a topic in the
> Google Groups "web2py-users" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/web2py/_MVVu9PF0xk/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to
> web2py+unsubscr...@googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.
>

-- 
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: Insert data in firebird database

[Константин Комков]

Nico de Groot, thank you for example and attention! I already have crud 
form and that form work. It load files in folder 'uploads'. I want to save 
that files in database now. I sure that 
stream=open(filepath + '\\' + i.file, 'rb')
and
stream.read()
made a long base64 string because I saw it on site. Then I decoded it and 
got picture.
Also I tried to save that file as usual SQL request in Firebird and web2py 
interfaces, where field DATA conteined string in base64. Like that:
INSERT INTO bin_files (PARENT, SRC_FILENAME, DATA) VALUES (33,'name.jpg','very 
very long base64 string').
After that Firebird has hanged. Web2py given an error message.
Just in case, are there between names 'DATA' and 'DATAblob'?
Yesterday and today I can't test all again because it's day off.
Raul Monares, thank you too! I cheked file that contain string in base64 
and it's sise 440kb. It's more then size of file in the beginning (name.jpg 
- 322kb). What should I do in that case, after all blob field can store 
large files.

-- 
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: Insert data in firebird database

[Raul Monares]

Take into account that the Firebird DAL adapter has a 64kb limit for 
inserting blobs. 

On Friday, April 27, 2018 at 4:27:48 AM UTC-6, Константин Комков wrote:
>
> Hello! I'm trying add data in my table.
> tables.py
> db_xml.define_table('xml_files',
> Field('F'),
> Field('I'), 
> Field('O'),
> Field('IS_IMPORTED'),
> Field('XML_FILE'),
> format='%(id)s %(F)s %(I)s %(O)s %(IS_IMPORTED)s %(XML_FILE)s',
> migrate=False)
> default.py
>
> db_xml.xml_files.insert(F='Castle', I='Rick' O='Middlename' IS_IMPORTED='0' 
> XML_FILE='something')
>
> DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- 
> invalid request BLR at offset 51\n- generator GENID_XML_FILES is not 
> defined', -104, 335544343)
>
> (('Error while preparing SQL statement:\n- 
> SQLCODE: -104\n- invalid request BLR at offset 51\n- generator 
> GENID_XML_FILES is not defined', -104, 335544343))
>
>
> 
>
>
>

-- 
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: Insert data in firebird database

[Nico de Groot]

 Minimal example using web2py DAL as a script. I'm not using firebird but 
your example with the string shows that the storage in a blob is working in 
firebird (Note that a python string is saved as binary data in a blob). In 
a webapp, move code to model file and to a controller, or use a form to 
upload an image, see 
https://stackoverflow.com/questions/34999878/upload-image-in-web2py-to-database-from-default-controller-following-image-blog
  


from gluon import DAL,Field 

def testdb(): 
uri = 'sqlite://storage.sqlite'  # start of model code 

try: 
db_xml = DAL(uri, 
 folder='test', 
 check_reserved=['all'], 
 migrate=True, 
 fake_migrate=False, 
 migrate_enabled=True, 
 fake_migrate_all=False) 

except Exception as e: 
print "database {} could not be opened. {}".format(uri, e) 

db_xml.define_table('bin_files', 
Field('DATAblob', 'blob'), 
migrate=True 
   ) 
# controller code 
imgstream = open(u'/users/ncdegroot/pictures/pasfotos/photo.jpg', 'rb') 
bin_data = imgstream.read() 
stored_img = db_xml.bin_files.insert(DATAblob=bin_data) 
db_xml.commit() # needed in a script 
retrieved_img = db_xml(db_xml.bin_files.id==stored_img).select().first() 


assert retrieved_img.DATAblob == bin_data # the storage and retrieval 
worked 


if __name__ == "__main__": 
testdb()


Nico 

On Friday, April 27, 2018 at 12:27:48 PM UTC+2, Константин Комков wrote:
>
> Hello! I'm trying add data in my table.
> tables.py
> db_xml.define_table('xml_files',
> Field('F'),
> Field('I'), 
> Field('O'),
> Field('IS_IMPORTED'),
> Field('XML_FILE'),
> format='%(id)s %(F)s %(I)s %(O)s %(IS_IMPORTED)s %(XML_FILE)s',
> migrate=False)
> default.py
>
> db_xml.xml_files.insert(F='Castle', I='Rick' O='Middlename' IS_IMPORTED='0' 
> XML_FILE='something')
>
> DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- 
> invalid request BLR at offset 51\n- generator GENID_XML_FILES is not 
> defined', -104, 335544343)
>
> (('Error while preparing SQL statement:\n- 
> SQLCODE: -104\n- invalid request BLR at offset 51\n- generator 
> GENID_XML_FILES is not defined', -104, 335544343))
>
>
> 
>
>
>

-- 
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: Insert data in firebird database

[Константин Комков]

That 'example' isn't work too(((. Can anyone advice how can I insert data 
in blob field.
There are one interesting thing. This code is working.
db_xml.define_table('bin_files',
Field('id','integer'),
Field('PARENT'),
Field('SRC_FILENAME'),
Field('DATA', 'blob'),
primarykey=['id','PARENT'],
migrate=False
   )

load_img = db_xml.bin_files.insert(PARENT=entrant_id[0][0],SRC_FILENAME=i.
file,DATA='something')
After this transaction I see 'c29tZXRoaW5n' in database - great!
But when I trying load file it's don't work!
filepath = os.path.join(request.folder,'uploads')
img = db_app(db_app.doc_images.person=='{0} {1} {2}'.format(session.abit_f,
session.abit_i,session.abit_o)).select()
for i in img:
stream=open(filepath + '\\' + i.file, 'rb')
load_img = db_xml.bin_files.insert(PARENT=entrant_id[0][0],SRC_FILENAME=
i.file,DATA=stream.read())
Maybe anyone using Firebird 2.5 or have the same problem? 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: Insert data in firebird database

[Константин Комков]

I found only one working example.
tables.py
db_xml.define_table('bin_files',
Field('id','integer'),
Field('PARENT'),
Field('SRC_FILENAME'),
Field('DATA', 'blob'),
primarykey=['id','PARENT'],
migrate=False
   )
default.py
img = db_app(db_app.doc_images.person=='{0} {1} 
{2}'.format(session.abit_f,session.abit_i,session.abit_o)).select(db_app.doc_images.file)
filepath = os.path.join(request.folder,'uploads')
for i in img:
stream=open(filepath + '\\' + i.file, 'rb')
s=base64.encodestring(stream.read())
db_xml.executesql("""INSERT INTO bin_files 
(PARENT,SRC_FILENAME,DATA) VALUES 
({0},'{1}','{2}')""".format(entrant_id[0][0],i.file,s))
db_xml.commit()
I don't know how can I create the generator and trigger in web2py for using 
insert like this.
db_xml.bin_files.insert(PARENT=entrant_id[0][0],SRC_FILENAME=i.file,DATA=stream.read())



-- 
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: Insert data in firebird database

[Константин Комков]

I found example:
tables.py
db_xml.define_table('test',
Field('NAME', 'string'),
Field('FILE', 'string')
)
default.py
db_xml.test.insert(NAME='something', FILE='somthing')
But it's not working, becouse generator not defined. In database I created 
generator and trigger. Raul Monares, can you describe it detailed or give 
example?

-- 
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: Insert data in firebird database

[Константин Комков]

Can you make example please. How I shoud use another type fore some fields 
at the same time? What type is correct: list:string, reference , 
list:reference 
?

-- 
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: Insert data in firebird database

[Nico de Groot]

You don’t need to use sqlexecute(), insert can handle multiple fields. But you 
do need to separate them with comma. In the field definitions you need to add 
the field type otherwise the default type is assumed (string). See the DAL 
documentation for details.

Nico

-- 
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: Insert data in firebird database

[Константин Комков]

Thank you, as I understand web2py function INSERT have only one argument 
and if I want to pass 2 or more variables I should use something else?
I have read about executesql and use it for inserting some variables like 
that: db.executesql('INSERT INTO table (var1, var2, var3) VALUES (1, 2, 3)')

суббота, 28 апреля 2018 г., 19:21:29 UTC+3 пользователь Raul Monares 
написал:
>
> If web2py didn't create the generator and trigger, you can do it manually 
> with this DDL
>
> CREATE GENERATOR GENID_XML_FILES;
>
> SET TERM !! ;
> CREATE TRIGGER XML_FILES_BI FOR XML_FILES
> ACTIVE BEFORE INSERT POSITION 0
> AS
> DECLARE VARIABLE tmp DECIMAL(18,0);
> BEGIN
>   IF (NEW.ID IS NULL) THEN
> NEW.ID = GEN_ID(GENID_XML_FILES, 1);
>   ELSE
>   BEGIN
> tmp = GEN_ID(GENID_XML_FILES, 0);
> if (tmp < new.ID) then
>   tmp = GEN_ID(GENID_XML_FILES, new.ID-tmp);
>   END
> END!!
> SET TERM ; !!
>
>
> On Friday, April 27, 2018 at 4:27:48 AM UTC-6, Константин Комков wrote:
>>
>> Hello! I'm trying add data in my table.
>> tables.py
>> db_xml.define_table('xml_files',
>> Field('F'),
>> Field('I'), 
>> Field('O'),
>> Field('IS_IMPORTED'),
>> Field('XML_FILE'),
>> format='%(id)s %(F)s %(I)s %(O)s %(IS_IMPORTED)s %(XML_FILE)s',
>> migrate=False)
>> default.py
>>
>> db_xml.xml_files.insert(F='Castle', I='Rick' O='Middlename' IS_IMPORTED='0' 
>> XML_FILE='something')
>>
>> DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- 
>> invalid request BLR at offset 51\n- generator GENID_XML_FILES is not 
>> defined', -104, 335544343)
>>
>> (('Error while preparing SQL statement:\n- 
>> SQLCODE: -104\n- invalid request BLR at offset 51\n- generator 
>> GENID_XML_FILES is not defined', -104, 335544343))
>>
>>
>> 
>>
>>
>>

-- 
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: Insert data in firebird database

[Raul Monares]

If web2py didn't create the generator and trigger, you can do it manually 
with this DDL

CREATE GENERATOR GENID_XML_FILES;

SET TERM !! ;
CREATE TRIGGER XML_FILES_BI FOR XML_FILES
ACTIVE BEFORE INSERT POSITION 0
AS
DECLARE VARIABLE tmp DECIMAL(18,0);
BEGIN
  IF (NEW.ID IS NULL) THEN
NEW.ID = GEN_ID(GENID_XML_FILES, 1);
  ELSE
  BEGIN
tmp = GEN_ID(GENID_XML_FILES, 0);
if (tmp < new.ID) then
  tmp = GEN_ID(GENID_XML_FILES, new.ID-tmp);
  END
END!!
SET TERM ; !!


On Friday, April 27, 2018 at 4:27:48 AM UTC-6, Константин Комков wrote:
>
> Hello! I'm trying add data in my table.
> tables.py
> db_xml.define_table('xml_files',
> Field('F'),
> Field('I'), 
> Field('O'),
> Field('IS_IMPORTED'),
> Field('XML_FILE'),
> format='%(id)s %(F)s %(I)s %(O)s %(IS_IMPORTED)s %(XML_FILE)s',
> migrate=False)
> default.py
>
> db_xml.xml_files.insert(F='Castle', I='Rick' O='Middlename' IS_IMPORTED='0' 
> XML_FILE='something')
>
> DatabaseError: ('Error while preparing SQL statement:\n- SQLCODE: -104\n- 
> invalid request BLR at offset 51\n- generator GENID_XML_FILES is not 
> defined', -104, 335544343)
>
> (('Error while preparing SQL statement:\n- 
> SQLCODE: -104\n- invalid request BLR at offset 51\n- generator 
> GENID_XML_FILES is not defined', -104, 335544343))
>
>
> 
>
>
>

-- 
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.