On 26/04/2009 11:28 AM, Gene wrote:
> You are exactly right John...that is indeed what the code looks
> like...except we have over 25 columns (it's a flat table).
I presume that you are referring to this:
"""
Write a script that loops around doing 'select * from mytable where
rowid = ?' on a connection to your corrupt database and doing 'insert
into mytable values(?,?,?,?,? etc etc etc)' on a connection to your
clean database.
"""
If that's correct, then surely the only maintenance you need to do to
the above when an extra column is added to your table is to add an extra
two characters ',?' to the insert statement ... you don't even have to
do that e.g.
# Python
TABLE_NAME = "mytable"
NUMBER_OF_COLS = 25
question_marks = ",".join("?" * NUMBER_OF_COLS)
insert_sql = "insert into %s values (%s)" % (TABLE_NAME, question_marks)
AND the output from the select should be able to be pumped straight into
the insert with no changes at all.
AND there might even be a pragma or suchlike that will enable you to
easily find the number of columns on the fly in your script ...
>
> We've already fixed the bad code, but there are some customers who have old
> versions...it didn't break very often with the old code, but it does still
> did.
>
> I haven't tried a select Min or max on the row id but a select count(*)
> returns an error...that's how I know I need to do the row by row recovery
> method. Select * from mytable also returns an error.
>
> The tables usually have tens of thousands of rows, sometimes over a couple
> hundred thousand but that's rare.
>
> What seems to work is that I do a select * from myTable where rowId = 'X'
> incing X until I get an error. After I get the error, every row higher then
> X also returns an error. So as soon as I get an error, I stop trying to
> recover more rows.
Does this usually mean that you are able to recover almost all of the rows?
> The pull and bind code is just ugly and we don't update our 'recovery
> utility' as quickly as we make changes to the database so it tends to get
> out of data. That's all.
I don't understand what is "the pull and bind" code and why you would
need anything other that what I've outlined.
Cheers,
John
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
