Manip SQLITE pas light !

Bonjour,

Je crois que je tourne en rond, merci de jeter un oeil avisé ...

Je voudrais exploiter une base SQLITE existante que j'ai rapatrié dans data/data/monPackage/databases/ en m'inspirant de ça : http://www.reigndesign.com/blog/usin...-applications/

Voici la sous-classe de SQLiteOpenHelper

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
 
package org.BddWpApp.riAndroid;
 
 
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
 
 
public class WpBdd extends SQLiteOpenHelper
{
	private static String DB_PATH = "/data/data/org.BddWpApp.riAndroid/databases/";
 
    private static String DB_NAME = "WP02-BddSqliteOK.db";
 
    private static SQLiteDatabase myDataBase; 
 
    private final Context myContext;
 
    // constructeur
	public WpBdd(Context context ) //, String name, CursorFactory factory,int version)
	{
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }
 
	@Override
	public void onCreate(SQLiteDatabase arg0) {
		// TODO Auto-generated method stub
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase arg0, int arg1, int arg2) {
		// TODO Auto-generated method stub
	}
 
	public static void openDB() 
	{
		//Open the database
    	String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
	}
}

Puis l'activité :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
 
package org.BddWpApp.riAndroid;
 
import android.app.Activity;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
 
public class MainTestBdd extends Activity
{
	private EditText fieldSearch ;
    private TextView showCode ;
    private Button bSearch ;
    boolean flag = true ;
    SQLiteDatabase wpBdd ;
 
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
 
        SQLiteOpenHelper wpBddHelper = new WpBdd(this) ; // this est le contexte. On crée une instance de WpBdd (dérivé de SQLiteOpenHelper)
        wpBdd = wpBddHelper.getReadableDatabase(); // on récupère la base de donnée
 
        fieldSearch = (EditText)findViewById(R.id.fieldSearch);
        showCode = (TextView)findViewById(R.id.showCode);
 
        bSearch = (Button)findViewById(R.id.bSearch);
        bSearch.setOnClickListener(new Button.OnClickListener()
    	{ public void onClick (View v) { lanceReq(); }});
    }
 
    private void lanceReq() throws SQLException // on exploite pas la base pour l'instant ....
	{
    	if (flag)
    	{
    		WpBdd.openDB() ;
    		String strSearch = "LFOU" ; // en dur pour le test et ça plante ligne suivante !
    		Cursor ret = wpBdd.rawQuery("SELECT _id , Code , LatE6 , LonE6 FROM WP WHERE Code=" + strSearch + ";", null); // <<<<<< ça plante ICI !!!!
    		if (ret != null)
        	{
        		ret.moveToFirst(); 
        	}
        	flag = false ;
    		showCode.setText("Open");
    	}
    	else
    	{
    		wpBdd.close() ;
    		flag = true ;
    		showCode.setText("Close");
    	}	
 
	}
}

Pour l'essai le but est de faire une requette (qui fonctionne par aileurs) permettant de retrouver un enregistrement. Le code ne révoit pas l'exploitation, avant, il vaudrait mieux trouver pourquoi le RawQuery plante...
Bien sûr ça plante (sinon je ne vous le soumettrais pas !) et voilà ce que dit le LogCat :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
 
03-04 19:53:49.270: DEBUG/AndroidRuntime(1473): >>>>>>>>>>>>>> AndroidRuntime START <<<<<<<<<<<<<<
03-04 19:53:49.280: DEBUG/AndroidRuntime(1473): CheckJNI is ON
03-04 19:53:49.539: DEBUG/AndroidRuntime(1473): --- registering native functions ---
03-04 19:53:49.939: DEBUG/ddm-heap(1473): Got feature list request
03-04 19:53:50.490: DEBUG/PackageParser(52): Scanning package: /data/app/vmdl27422.tmp
03-04 19:53:50.599: INFO/PackageManager(52): Removing non-system package:org.BddWpApp.riAndroid
03-04 19:53:50.609: DEBUG/PackageManager(52): Removing package org.BddWpApp.riAndroid
03-04 19:53:50.609: DEBUG/PackageManager(52):   Activities: org.BddWpApp.riAndroid.MainTestBdd
03-04 19:53:50.719: DEBUG/PackageManager(52): Scanning package org.BddWpApp.riAndroid
03-04 19:53:50.729: INFO/PackageManager(52): /data/app/vmdl27422.tmp changed; unpacking
03-04 19:53:50.749: DEBUG/installd(32): DexInv: --- BEGIN '/data/app/vmdl27422.tmp' ---
03-04 19:53:51.020: DEBUG/dalvikvm(1479): DexOpt: load 46ms, verify 61ms, opt 3ms
03-04 19:53:51.040: DEBUG/installd(32): DexInv: --- END '/data/app/vmdl27422.tmp' (success) ---
03-04 19:53:51.040: DEBUG/PackageManager(52):   Activities: org.BddWpApp.riAndroid.MainTestBdd
03-04 19:53:51.060: DEBUG/ActivityManager(52): Uninstalling process org.BddWpApp.riAndroid
03-04 19:53:51.170: INFO/installd(32): move /data/dalvik-cache/data@app@vmdl27422.tmp@classes.dex -> /data/dalvik-cache/data@app@org.BddWpApp.riAndroid.apk@classes.dex
03-04 19:53:51.190: DEBUG/PackageManager(52): New package installed in /data/app/org.BddWpApp.riAndroid.apk
03-04 19:53:51.320: DEBUG/AndroidRuntime(1473): Shutting down VM
03-04 19:53:51.330: DEBUG/dalvikvm(1473): DestroyJavaVM waiting for non-daemon threads to exit
03-04 19:53:51.340: DEBUG/dalvikvm(1473): DestroyJavaVM shutting VM down
03-04 19:53:51.340: DEBUG/dalvikvm(1473): HeapWorker thread shutting down
03-04 19:53:51.350: DEBUG/dalvikvm(1473): HeapWorker thread has shut down
03-04 19:53:51.350: DEBUG/jdwp(1473): JDWP shutting down net...
03-04 19:53:51.350: INFO/dalvikvm(1473): Debugger has detached; object registry had 1 entries
03-04 19:53:51.360: DEBUG/dalvikvm(1473): VM cleaning up
03-04 19:53:51.390: ERROR/AndroidRuntime(1473): ERROR: thread attach failed
03-04 19:53:51.420: DEBUG/ActivityManager(52): Uninstalling process org.BddWpApp.riAndroid
03-04 19:53:51.450: DEBUG/dalvikvm(1473): LinearAlloc 0x0 used 623012 of 5242880 (11%)
03-04 19:53:51.610: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f0700e5
03-04 19:53:51.610: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f020031
03-04 19:53:51.610: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f020030
03-04 19:53:51.619: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f050000
03-04 19:53:51.649: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f060001
03-04 19:53:51.669: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f060000
03-04 19:53:51.969: DEBUG/dalvikvm(101): GC freed 2707 objects / 159680 bytes in 329ms
03-04 19:53:52.210: DEBUG/dalvikvm(52): GC freed 9215 objects / 601512 bytes in 376ms
03-04 19:53:52.539: DEBUG/dalvikvm(52): GC freed 1144 objects / 49408 bytes in 262ms
03-04 19:53:52.569: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f0700e5
03-04 19:53:52.569: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f020031
03-04 19:53:52.569: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f020030
03-04 19:53:52.579: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f050000
03-04 19:53:52.599: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f060001
03-04 19:53:52.619: WARN/ResourceType(52): Resources don't contain package for resource number 0x7f060000
03-04 19:53:53.370: DEBUG/AndroidRuntime(1484): >>>>>>>>>>>>>> AndroidRuntime START <<<<<<<<<<<<<<
03-04 19:53:53.400: DEBUG/AndroidRuntime(1484): CheckJNI is ON
03-04 19:53:53.739: DEBUG/AndroidRuntime(1484): --- registering native functions ---
03-04 19:53:54.140: DEBUG/ddm-heap(1484): Got feature list request
03-04 19:53:54.669: INFO/ActivityManager(52): Starting activity: Intent { flg=0x10000000 cmp=org.BddWpApp.riAndroid/.MainTestBdd }
03-04 19:53:54.759: INFO/ActivityManager(52): Start proc org.BddWpApp.riAndroid for activity org.BddWpApp.riAndroid/.MainTestBdd: pid=1492 uid=10030 gids={}
03-04 19:53:54.769: DEBUG/AndroidRuntime(1484): Shutting down VM
03-04 19:53:54.779: DEBUG/dalvikvm(1484): DestroyJavaVM waiting for non-daemon threads to exit
03-04 19:53:54.789: DEBUG/dalvikvm(1484): DestroyJavaVM shutting VM down
03-04 19:53:54.799: DEBUG/dalvikvm(1484): HeapWorker thread shutting down
03-04 19:53:54.799: DEBUG/dalvikvm(1484): HeapWorker thread has shut down
03-04 19:53:54.799: DEBUG/jdwp(1484): JDWP shutting down net...
03-04 19:53:54.810: INFO/dalvikvm(1484): Debugger has detached; object registry had 1 entries
03-04 19:53:54.829: DEBUG/dalvikvm(1484): VM cleaning up
03-04 19:53:54.859: ERROR/AndroidRuntime(1484): ERROR: thread attach failed
03-04 19:53:54.939: DEBUG/dalvikvm(1484): LinearAlloc 0x0 used 638596 of 5242880 (12%)
03-04 19:53:55.170: DEBUG/ddm-heap(1492): Got feature list request
03-04 19:53:55.969: INFO/ActivityManager(52): Displayed activity org.BddWpApp.riAndroid/.MainTestBdd: 1226 ms (total 1226 ms)
03-04 19:54:05.169: DEBUG/dalvikvm(101): GC freed 2724 objects / 159976 bytes in 115ms
03-04 19:54:10.139: DEBUG/dalvikvm(208): GC freed 43 objects / 2080 bytes in 79ms
03-04 19:54:27.910: WARN/SQLiteDirectCursorDriver(1492): Found SQL string that ends in ; -- SELECT _id , Code , LatE6 , LonE6 FROM WP WHERE Code=LFOU;
03-04 19:54:27.930: DEBUG/AndroidRuntime(1492): Shutting down VM
03-04 19:54:27.930: WARN/dalvikvm(1492): threadid=3: thread exiting with uncaught exception (group=0x4001b188)
03-04 19:54:27.940: ERROR/AndroidRuntime(1492): Uncaught handler: thread main exiting due to uncaught exception
03-04 19:54:27.960: ERROR/AndroidRuntime(1492): android.database.sqlite.SQLiteException: no such column: LFOU: , while compiling: SELECT _id , Code , LatE6 , LonE6 FROM WP WHERE Code=LFOU
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteProgram.native_compile(Native Method)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteProgram.compile(SQLiteProgram.java:110)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:49)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:49)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1220)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1193)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at org.BddWpApp.riAndroid.MainTestBdd.lanceReq(MainTestBdd.java:46)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at org.BddWpApp.riAndroid.MainTestBdd.access$0(MainTestBdd.java:40)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at org.BddWpApp.riAndroid.MainTestBdd$1.onClick(MainTestBdd.java:37)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.View.performClick(View.java:2364)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.View.onTouchEvent(View.java:4179)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.widget.TextView.onTouchEvent(TextView.java:6540)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.View.dispatchTouchEvent(View.java:3709)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.ViewGroup.dispatchTouchEvent(ViewGroup.java:884)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at com.android.internal.policy.impl.PhoneWindow$DecorView.superDispatchTouchEvent(PhoneWindow.java:1659)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at com.android.internal.policy.impl.PhoneWindow.superDispatchTouchEvent(PhoneWindow.java:1107)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.app.Activity.dispatchTouchEvent(Activity.java:2061)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at com.android.internal.policy.impl.PhoneWindow$DecorView.dispatchTouchEvent(PhoneWindow.java:1643)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.view.ViewRoot.handleMessage(ViewRoot.java:1691)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.os.Handler.dispatchMessage(Handler.java:99)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.os.Looper.loop(Looper.java:123)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at android.app.ActivityThread.main(ActivityThread.java:4363)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at java.lang.reflect.Method.invokeNative(Native Method)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at java.lang.reflect.Method.invoke(Method.java:521)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:860)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:618)
03-04 19:54:27.960: ERROR/AndroidRuntime(1492):     at dalvik.system.NativeStart.main(Native Method)
03-04 19:54:27.989: INFO/Process(52): Sending signal. PID: 1492 SIG: 3
03-04 19:54:28.000: INFO/dalvikvm(1492): threadid=7: reacting to signal 3
03-04 19:54:28.050: INFO/dalvikvm(1492): Wrote stack trace to '/data/anr/traces.txt'
03-04 19:54:30.509: INFO/Process(1492): Sending signal. PID: 1492 SIG: 9
03-04 19:54:30.529: INFO/WindowManager(52): WIN DEATH: Window{43cdd438 org.BddWpApp.riAndroid/org.BddWpApp.riAndroid.MainTestBdd paused=false}
03-04 19:54:30.539: INFO/ActivityManager(52): Process org.BddWpApp.riAndroid (pid 1492) has died.
03-04 19:54:30.679: INFO/UsageStats(52): Unexpected resume of com.android.launcher while already resumed in org.BddWpApp.riAndroid
03-04 19:54:30.699: WARN/InputManagerService(52): Got RemoteException sending setActive(false) notification to pid 1492 uid 10030
03-04 19:54:30.799: ERROR/gralloc(52): [unregister] handle 0x48bd90 still locked (state=40000001)

Merci par avance si vous voyez mon erreur ....

Salut,

Au hazard :

Code:

Cursor ret = wpBdd.rawQuery("SELECT _id , Code , LatE6 , LonE6 FROM WP WHERE Code='" + strSearch + "';", null); // <<<<<< ça plante ICI !!!!

(rajout de ' autour de ton mot recherché)

Merci MrDuChnok,

Je suis reparti de zéro en m'inspirant et élaguant l'exemple Google du NotePad, ici : http://developer.android.com/intl/fr...tepad-ex2.html

Il est très pédagogique, en anglais of course.

Et ça marche ! Je ne crie pas trop victoire, je sais que le curseur issu de la requête n'est pas vide et je m'attaque à l'exploitation des données sélectionnées ...
Cela ne veut pas dire que j'ai tout compris, mais ne plus avoir de plantage permet d'avancer pas à pas en comprenant ce qui se passe.

Quand cela sera exploitable par d'autres "rameurs" dans mon genre je ferai un post avec le code qui ne plante pas !!!

Le hasard fait bien les choses MrDuChnok !!

En effet, il faut mettre les valeurs alpha qui servent aux recherches entre ' : par exemple 'LFOU'.

C'est vrai aussi quand on calcule la chaine de recherche SQL, par exemple ;
Editable airp = fieldSearch.getText() ;
strReq ="SELECT _id , Code , LatE6 , LonE6 " +
"FROM WP WHERE Code = '" + airp + "';";

Ce n'est pas très visible, mais il y a un ' juste avant le " situé avant +airp + et un ' juste après le " dans ce qui suit +airp +. Le contenu de la variable airp se trouve ainsi encadrée par 2 '

Quand on réfléchi un peu, .... c'est normal !

Ouais, je me suis déjà fait avoir avec ce genre de chose, d'où mon conseil ;)

C'est normal quand on enregistre les String dans une table on emploi ' '.

Mais 2 ans plus tard, une meilleure réponse est encore d'utiliser la méthode SelectionArgs de RawQuery, qui résoud les problèmes d'injection en plus du problème initial de guillemets :
Code:

1 2 p_query = "select * from mytable where name_field = ?"; mDb.rawQuery(p_query, new String[] { uvalue });