Android Question Fail to SELECT an image from a SQLite file

[AndyChang]

I have a problem :

1) I have a SQLite Data file

File name: NailBasicData.sqlite3 Table Name: NailBasic

Data structure is:

col1: itemno TEXT primary key

col2: image BLOB

2) I want to read an image which itemno = (a String Variable item(0))

Here (in the test program) I set a value to item(0) = “FMAS-2101”

3) The error msg : no such column: itemno (code 1): while compiling: SELECT …

Please show me how to correct my code.

Andy

 

[AndyChang]

I have a problem :

1) I have a SQLite Data file

File name: Nail.sqlite3 Table Name: BasicData

Data structure is:

col1: itemno TEXT primary key

col2: image BLOB

2) I want to read an image which itemno = (a String Variable item(0))

Here (in the test program) I set a value to item(0) = “FMAS-2101”

3) The error msg : no such column: itemno (code 1): while compiling: SELECT …

Please show me how to correct my code.

 

[KMatle]

1. Please no double posts!

2. The error message says: "No such column FMASxxxx". Just check the correct syntax.

 

[AndyChang]

Dear Kmatle
1. The 1st post was too large about 8.5MB so I change the file size and post again.
2. The error msg is funny. I use "itemno" as the column name in code but the error message appear no such column "FMAS-2010"
3. I don't know how to correct it.
Andy

 

[klaus]

I had a look at your problem.
1. The error you show in the pictures is not from the project in post#2 !?
The error you show is raised by a query, but there is no query in your test project !?
2. In your test project you try to access the DB from a folder on the PC !?
How do you expect the Android device access a folder on the PC !?
3. The file names and table names are different in post#1 and post#2 !?
In the database in your test project there is no table nor column with the names you give.

You need to read the chapter about SQLite in the B4A User's Guide.

I leave it up to you, with the comments given above, to find the correct way to solve the problem.

Done correctly it works:

 

[AndyChang]

Thanks Klaus
1. Because the SQLite data file in the first post was too large, so I create a new file with a new file name. Then I change the file name and table name to meet the second file in the code. That is why I post again.
2. I have an APP project which is developed now by using B4A as the developer tool.
3. In this project, I will build a big SQL database in the Cloud which contain thousands of nails' item_number and their images(Each image size is about 20-30 KB)
4. I am now working with the user interface part. I am happy that it is very efficient to use B4A to develop this part.
5. Another team is working with creating the nails item_NBR and their image
6. By the end of this month, a big database will be created in the Cloud. And I have to retrieve the data from that database.
7. So I create this test program to do a trial test.
8. { 2. In your test project you try to access the DB from a folder on the PC !?
How do you expect the Android device access a folder on the PC !? }
Am I do a wrong test about retrieving database ? I suppose that if this test passed, then it is easy for me to read database from Cloud? Am I wrong?
9. Could you guide me a way to do the test?
Andy

 

[klaus]

Attached you find the test project I used.
I copied the database to File.DirInternal to be able to use it.
I have never tryed to access a database in the cloud.
You must search in the forum.
Perhaps you will need the RemoteDatabaseConnector.

 

[AndyChang]

Dear Klaus
I have check the code you send to me Test21.Zip It is an old question and has been solved already.
Please download the new one which I upload again in this post. It is a different requirement.
I want to Query the image data which is belongs to itemno(0). I use the code below but fail.

Cursor1 = SQL1.ExecQuery("SELECT image FROM " & Tablename &" WHERE itemno = "& item(0) )

Please help me.

Andy

 

[klaus]

To get help, you MUST provide us with working projects showing the problem !
The project in post#8 doesn't work !
I don't want, each time you post a new question, to first need to modify your project to make it run !

 

[AndyChang]

Dear Klaus
I have find a way to solve the problem by just change the column name back to my original name.
By now, I am a little bit worry about my project for B4A will sometimes generate a very strange error happened during I develop the program.
With 100% same program, but just change the database's column name, the program will get error. It make me crazy.
Now I have to go on my work on the Project. Hope it will go smooth on the remaining develop stage.
Andy

 

[OliverA]

By now, I am a little bit worry about my project for B4A will sometimes generate a very strange error happened during I develop the program.
With 100% same program, but just change the database's column name, the program will get error. It make me crazy

I hope you realize this has absolutely nothing to do with B4A in general. Changing a column name of a database without changing your code that accesses that column is going to give you problems, or are you expecting the column name change to propagate to your B4A code? Through the SQL library you can access Android's underlying SQLite implementation. You then can use SQL statements to access the data. SQL is separate from B4A and efficient use of SQL is another learned skill (besides being able to program in B4A). These SQL statements are not somehow dynamically linked to your database that allows you to rename columns and have that change automatically reflected in your SQL statements. You, as a programmer/database manager, have to adjust your SQL statements accordingly to any adjustments that you make to the database. This has nothing to do with the fitness of B4A and "strange" errors you may be getting when accessing databases via B4A.

3. In this project, I will build a big SQL database in the Cloud which contain thousands of nails' item_number and their images(Each image size is about 20-30 KB)

I hope you realize you'll need additional libraries that allow you access to this SQL database if you think about using SQL access via your Android app. The only SQL database that is supported out of box is Android's SQLite implementation.

If you do think about implementing direct SQL access to your cloud database, you may want to look at code like this

Cursor1 = SQL1.ExecQuery("SELECT image FROM " & Tablename &" WHERE itemno = "& item(0) )

and familiarize yourself with SQL injection, especially if either "Tablename" or "item(0)" are user supplied values.

 

[AndyChang]

Thanks for your patient to give me so many suggestions.
As a very new and less experience programmer in APP, I believe that it is the pressure of this project make me nervous.
During the last one month period, I get so many help from Forums. In Fact, I still have confidence that I can finish my 1st project by using B4A.
I will go on my project. Hope one month later I can show you all a very nice APP application in the Nail field.
Andy