Insert data in firebird database

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

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='<mytag>something</mytag>')

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)

<class 'fdb.fbcore.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))

[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 ; !!

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

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 написал:

[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

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

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

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

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?

P. S. If I wont to load some files in window using (control + left button mouse) is it possible?

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

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())

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

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.

[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 

[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:

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

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

[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 Константин Комков <firean...@gmail.com>

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.

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

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

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

<class 'fdb.fbcore.DatabaseError'>(('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=<fdb.fbcore.PreparedStatement object>, operation='SELECT gen_id(genid_test, 0) FROM rdb$database', cursor=<fdb.fbcore.Cursor object>, 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.