-2

For hours I have been trying to save my python file image to my database table but to no avail due to conversion issue with an error notification of:

Implicit conversion from data type nvarchar to varbinary(max) is not allowed. Use the CONVERT function to run this query.

The table column for photo has a data type of varbinary(max). I have made a research here and found similar posts and comments from members but none could help resolve mine.

How do I convert a string value to bytes in python to enable me save my records in the database? I have tried to use bytes(filename), it didn't work. I also tried:

  base64.encodebytes(filename)

I did that after importing base64, it still didn't work. Then I tried:

 base(filename,'ascii')

it still didn't work.

I want a situation when I upload the photo from the dialogbox and it gets appended to the file, the uploaded photo can be saved in the column reserved for it in the database table and the column has a data type of varbinary(max). The below is my code:

        try:
        fname, _ = QFileDialog.getOpenFileName(self, 'Open', 'c:\\', "image Files (*.jpg *.png)")
        strfn = bytes(fname,'ascii')
        self.pixL.setPixmap(QPixmap(fname).scaled(250, 250))
        self.pixTx.setText(fname)
        self.pixL.setText(strfn)
    except Exception as err:
     print(err)

Here's the insert statement:

             self.cursor.execute(
            "INSERT INTO pixx1(photop, photopath)"
            "VALUES(?,?)",
            #the parameter placholder for pyodbc is the question mark sign (?), whereas that of MySql is %s
            (self.pixL.text(),
             self.pixTx.text())
CCCC
  • 241
  • 2
  • 10
  • just a suggestion: why not uploading the file to some kind of storage on the cloud and just put the link to it in your DB? – Kevin Eaverquepedo Sep 03 '21 at 15:16
  • hello @KevinEaverquepedo, it's a desktop application that doesn't use the internet, so I don't want to use a url for the images. – CCCC Sep 03 '21 at 15:27
  • Then save them in some dir accessible to your server (static files), and add the path to the static file in your DB? – Kevin Eaverquepedo Sep 03 '21 at 15:28
  • that is where the issue is. the images are already in a directory. Please check my codes again. @KevinEaverquepedo – CCCC Sep 03 '21 at 15:32
  • @CEO What is the point of using varbinary for the photopath column if you're only saving filenames? The column data-type should be changed to nvarchar, since the filenames are unicode strings, not binary data. Then there's no need to convert anything. The only reason for using varbinary would be if you wanted the db to store the image data itself (i.e. the file contents). – ekhumoro Sep 03 '21 at 17:17
  • Hello @ekhumoro, it is the image itself I want to store. Do you have a better way I can do that please? – CCCC Sep 03 '21 at 17:43
  • @CEO `img_data = open(fname, 'rb').read()`. That will produce bytes, which pyodbc should automatically convert to varbinary. (NB: do not attempt to convert `fname` to bytes - it should be left as a unicode string). – ekhumoro Sep 03 '21 at 17:56
  • Alright. Let me try it now. I'm grateful. I will give you a feedback @ekhumoro – CCCC Sep 03 '21 at 18:07
  • @CEO PS: you can load the pixmap from the data like this: `pixmap = QPixmap.fromImage(QImage.fromData(img_data))`. – ekhumoro Sep 03 '21 at 18:13
  • I'm using a fileDialog. There's a button to click to open the file dialogbox – CCCC Sep 03 '21 at 18:20
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236744/discussion-between-ceo-and-ekhumoro). – CCCC Sep 03 '21 at 18:52
  • It still doesn't work. It says operand type clash: ntext is incompatible with varbinary – CCCC Sep 03 '21 at 19:21

1 Answers1

0

After almost 10hours of thorough research and critical thinking, I was finally able to resolve it. The error says

  "implicit conversion from data type nvarchar to varbinary(max) 
  is not allowed. use the convert function to run this query."

I tried all the suggestions I was given but Microsoft SQL refused them, insisting the the conversion can't be done implicitly, what I understand by that is that I or the user must do the conversion in the SQL server and take responsibility of it using either CONVERT or CAST.

I decided to CREATE a table afresh just to test the photo upload. The table was given 2 columns only,

   CREATE table pixx2(
        photopath varchar(500),
        photo-p AS CAST(photopath AS varbinary(max))
       )

and the insert statement was:

           insert into pixx2(photopath) value(?),
           (self.pixTx.text())

The pixTx is the filepath text (the string value) of the uploaded photo, when stored in the database, it triggers the photo-p column to receive a copy and saves it in varbinary.

I've achieved what I want. Thanks.

CCCC
  • 241
  • 2
  • 10
  • The return value of `self.pixTx.text()` is a unicode string. It makes no sense whatsoever to cast it to varbinary. Why are you storing a **filepath** as varbinary? If you wanted to store the image data in your db, your code certainly does not achieve that. – ekhumoro Sep 03 '21 at 22:01
  • Mr. @ekhumoro, I created another table. I used insert into self.pixL.text() and also self.pxTx.text() where self.pixL is the QLabel of the pixmap and self.pxTx is the QLineEdit to hold the filepath and display it in the form. I discovered both of them are stored with same value which is the path of the file. I did the conversation inside the database server. Can you tell me the insert statement of the image you're talking about? Let me see that statement. Is it not insert into tablename(x,y) values (?,?),(self.pixTx.text(), self.pixL.text()) I'm to use? I would want to see what you would use – CCCC Sep 03 '21 at 23:13
  • 1
    The value of `self.pixL.text()` is irrelevant. It has nothing to do with the pixmap. If you want to store the filepath and the image data in your db, you should do something like this: `filepath = self.pixTx.text(); imgdata = open(filepath, 'rb').read(); self.cursor.execute("INSERT INTO pixx1(photopath, photop) VALUES(?,?)", (filepath, imgdata))`. This assumes `photopath` is varchar and `photop` is varbinary. And once again: DO NOT try to convert or cast the values in any way before inserting them into the db. – ekhumoro Sep 04 '21 at 00:05
  • If you've done things correctly, it will be possible to fetch the binary image data from the db using a select statement and then create a pixmap directly from it, like this: `pixmap = QPixmap.fromImage(QImage.fromData(imgdata))`. – ekhumoro Sep 04 '21 at 00:16
  • Hello @ekhumoro, I realized you didn't tell me what to use to set the image to the database. After values(?,?)", What about (filepath.text(), img.text()), I would like to see your code for this line. It is very important to me. – CCCC Sep 04 '21 at 07:15
  • 1
    I already gave you the exact code. I have nothing else to add. – ekhumoro Sep 04 '21 at 12:18
  • Hello @ekhumoro, it works fine now. I am very grateful for your patience with me. I used your method. Does it mean if I'm retrieving the data, the image will be automatically uploaded? If yes, then that will be fantastic. – CCCC Sep 04 '21 at 13:12
  • I don't know what you mean by "automatically uploaded". You should test these things for yourself to make sure they work in the way you expect. – ekhumoro Sep 04 '21 at 13:18
  • I use different systems. The one with me presently doesn't have much of my work for now. So on Monday, I will definitely test it in and give you a feedback. I appreciate your guidance – CCCC Sep 04 '21 at 14:06
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/236759/discussion-between-ceo-and-ekhumoro). – CCCC Sep 04 '21 at 15:10
  • Anyway, @ekhumoro, today I tried to retrieve data from the database: python, Microsoft sql, pyodbc module. I used the usual sql statement I've been using before: ```"select from tablename WHERE userID Like ? or surname Like ? or passport Like ?"``` It didn't work. I had to use ```WHERE userID LIKE '%{}%' OR surname LIKE '%{}%' ".format(searchbox,searchbox, searchbox).``` It still didn't work. But when I used only ```SELECT * FROM tablename```, it works but that's not really what I want; and I saw the varbinary data on the qtablewidget cell, not the displayed photo. – CCCC Sep 13 '21 at 18:23