Matthew Sartain Matthew Sartain - 4 years ago 109
SQL Question

Insert an array of tables into one table SQLite C/C++

I made my own database format, and it sadly required too much memory and the size of it got horrendous and upkeep was horrible.

So I'm looking for a way to store an array of a struct that's in an object into a table.

I'm guessing I need to use a blob, but all other options are welcome. An easy way to implement a blob would be helpful as well.

I've attached my saving code and related structures(Updated from my horrible post earlier)

#include "stdafx.h"
#include <string>
#include <stdio.h>
#include <vector>
#include "sqlite3.h"
using namespace std;
struct PriceEntry{

float cardPrice;
string PriceDate;
int Edition;
int Rarity;
};
struct cardEntry{
string cardName;
long pesize;
long gsize;
vector<PriceEntry> cardPrices;
float vThreshold;
int fav;
};


vector<cardEntry> Cards;

void FillCards(){
int i=0;
int j=0;
char z[32]={0};
for(j=0;j<3;j++){
cardEntry tmpStruct;
sprintf(z, "Card Name: %d" , i);
tmpStruct.cardName=z;
tmpStruct.vThreshold=1.00;
tmpStruct.gsize=0;
tmpStruct.fav=1;
for(i=0;i<3;i++){
PriceEntry ss;
ss.cardPrice=i+1;
ss.Edition=i;
ss.Rarity=i-1;

sprintf(z,"This is struct %d", i);

ss.PriceDate=z;
tmpStruct.cardPrices.push_back(ss);
}

tmpStruct.pesize=tmpStruct.cardPrices.size();
Cards.push_back(tmpStruct);
}
}
int SaveCards(){
// Create an int variable for storing the return code for each call
int retval;
int CardCounter=0;
int PriceEntries=0;
char tmpQuery[256]={0};

int q_cnt = 5,q_size = 256;


sqlite3_stmt *stmt;


sqlite3 *handle;


retval = sqlite3_open("sampledb.sqlite3",&handle);

if(retval)
{
printf("Database connection failed\n");
return -1;
}
printf("Connection successful\n");

//char create_table[100] = "CREATE TABLE IF NOT EXISTS users (uname TEXT PRIMARY KEY,pass TEXT NOT NULL,activated INTEGER)";
char create_table[] = "CREATE TABLE IF NOT EXISTS Cards (CardName TEXT, PriceNum NUMERIC, Threshold NUMERIC, Fav NUMERIC);";


retval = sqlite3_exec(handle,create_table,0,0,0);
printf( "could not prepare statemnt: %s\n", sqlite3_errmsg(handle) );
for(CardCounter=0;CardCounter<Cards.size();CardCounter++){
char Query[512]={0};

for(PriceEntries=0;PriceEntries<Cards[CardCounter].cardPrices.size();PriceEntries++){

//Here is where I need to find out the process of storing the vector of PriceEntry for Cards then I can modify this loop to process the data

}

sprintf(Query,"INSERT INTO Cards VALUES('%s', %d, %f, %d)",
Cards[CardCounter].cardName.c_str(),
Cards[CardCounter].pesize,
Cards[CardCounter].vThreshold,
Cards[CardCounter].fav); //My insert command

retval = sqlite3_exec(handle,Query,0,0,0);
if(retval){

printf( "Could not prepare statement: %s\n", sqlite3_errmsg(handle) );

}
}
// Insert first row and second row

sqlite3_close(handle);
return 0;
}


I tried googling but my results didn't suffice.

Answer Source

You have two types here: Cards and PriceEntries. And for each Card there can be many PriceEntries.

You can store Cards in one table, one Card per row. But you're puzzled about how to store the PriceEntries, right?

What you'd normally do here is have a second table for PriceEntries, keyed off a unique column (or columns) of the Cards table. I guess the CardName is unique to each card? Let's go with that. So your PriceEntry table would have a column CardName, followed by columns of PriceEntry information. You'll have a row for each PriceEntry, even if there are duplicates in the CardName column.

The PriceEntry table might look like:

CardName  | Some PE value  | Some other PE value
Ace       | 1 | 1
Ace       | 1 | 5
2         | 2 | 3

and so on. So when you want to find the array of PriceEntries for a card, you'd do

select * from PriceEntry where CardName = 'Ace'

And from the example data above you'd get back 2 rows, which you could shove into an array (if you wanted to).

No need for BLOBs!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download