0

I'm trying to update some code I have to use Binding Instead of native Queries.

I'm using the the ETL model to load this data.

It all starts with me creating a table with all columns defined as Text.

Create Table TableA ( Col1 Text, Col2 Text);

I then populate the table using native queries like,

INSERT INTO TABLEA ('A', '2');
INSERT INTO TABLEA ('B', 'Bad_VALUE_Not_INT');

I then create a real data table 

 Create Table RealTableA ( Col1 Text, Col2 Integer);

And then insert the values into the real table and Delete ones with the wrong datatype as explained here

INSERT INTO RealTableA SELECT * FROM TABLEA;

DELETE FROM RealTableA WHERE typeof(col2) != 'integer'

This works great when I wrote the queries and submitted them natively. But I now want to Bind Variables to a statement instead of using Native Queries as explained here

So I changed the code to use sqlite3_bind_text with a prepared_statement.

Now SQLite thinks every value in col2 is really 'text' when I query using typeof(col2) this is erasing EVERYTHING.

I find this strange because of the example of type affinity SQLite has here.

How do I fix this without rewriting all my ETL queries after the initial load?

I found a binding function called sqlite3_bind_value who's definition sounds like it will treat the values bound to it as numeric which is what I want. The problem is it takes a SQLite3_value* and I can't for the life of me figure out how to make text values from a file into that.

Ideas? Am I way off with this approach?

Also why is it when I bind values they get a hard 'TEXT' type designation but when I insert into a 'TEXT' column it allows some wiggle room for other types?

Note: I'm using the C/C++ interface with Visual Studios 2010 and I'm on version 3.7.13 of SQLite.

Community
  • 1
  • 1
Dan
  • 2,625
  • 7
  • 39
  • 52

1 Answers1

1

sqlite3_bind_value works only with such values that you get from SQLite, which happens only with parameters of user-defined functions.

To bind other types than text, use the appropriate sqlite3_bind_xxx function, e.g., sqlite3_bind_int.

CL.
  • 173,858
  • 17
  • 217
  • 259
  • I'm aware of the other bind functions but I'm in a situation where I have to store everything I'm given which makes storing it as text logical. Then after I do that I can go through and take the valid data to actually process with. So there is no bind that works like the default column type Numaric for data you have? – Dan Feb 11 '14 at 23:45
  • Appears that I had the size of the text value wrong and once I used strlen() on it white space filler disappeared and everything went back to normal. I'm giving you the answer because it's when I reread the bind documentation you linked that I noticed I'd done it wrong. – Dan Feb 12 '14 at 02:49