May 25, 2022, 01:26:11 AM

News:

IonicWind Snippit Manager 2.xx Released!  Install it on a memory stick and take it with you!  With or without IWBasic!


Database question

Started by Rock Ridge Farm (Larry), June 29, 2006, 06:55:58 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

Rock Ridge Farm (Larry)

I have a database with 2 tables.
I update the first table - Insert a record in the second table.
All is ok at this point.
I then try to get the record from the second table and it fails.
Is there something special I should do.

Ionic Wind Support Team

Got some code to show?

Are you trying to reuse the same statement handle without freeing it first?
Ionic Wind Support Team

Rock Ridge Farm (Larry)

Freed the first set of handles.
Used new ones for the second table and freed as completed.
It creates a record but I read it back and it is not correct.
The rec_id (auto number) that is returned on the read is wrong but I get no errors.

Ionic Wind Support Team

Still need to see some code.  Auto numbers are assigned by the database driver. 
Ionic Wind Support Team

Rock Ridge Farm (Larry)

The routine with the error.

sub updstkquote(){
string sel,error,dissym;
dstring cur_symbol[10],cur_tick[5],cur_name[40];
dstring prc_symbol[10],prc_date[18];
double cur_lstprc = 0;
double prc_lstprc = 0;
int cur_id,prc_id,nReturn,sptr,eptr,cnt,prccnt,curprcidx;
int stkstmt_select,stkstmt_update,stkstmt_insert,stkstmt_delete;
int prcstmt_select,prcstmt_update,prcstmt_insert,prcstmt_delete;


//The database object
CDatabase stock_db;

nReturn = stock_db.Connect("Microsoft Access Driver (*.mdb)",GetStartPath() + "Stkkepr.mdb","",0);
IF nReturn = 0 {
MessageBox(0,"Failed to open Database","Error");
return;
}

site = "finance.yahoo.com";
ref = "Stock Keeper";

prccnt = 0;
hopen = InternetOpenA(ref,INTERNET_PRECONFIG,"","",0);
IF hopen {
hconnect = InternetConnectA(hopen,site,80,"","",INTERNET_SERVICE_HTTP,0,0);
IF hconnect != 0 {
sel = "SELECT * FROM stock";
stkstmt_select = stock_db.ExecSQL(sel);
error = stock_db.GetErrorCode(stkstmt_select);
IF(Len(error)) {
messagebox(0,error,"OPEN ERROR");
}
IF stkstmt_select {
while stock_db.Get(stkstmt_select){
stock_db.GetData(stkstmt_select,1,cur_id,TYPE_INT);
stock_db.GetData(stkstmt_select,2,cur_symbol,TYPE_STRING);
stock_db.GetData(stkstmt_select,6,cur_name,TYPE_STRING);
stock_db.GetData(stkstmt_select,14,cur_lstprc,TYPE_DOUBLE);
doc = "d/quotes.txt?s=" + cur_symbol + "&f=sl1n";
hhttp = HttpOpenRequestA(hconnect,"GET",doc,null,"",null,null,0);
IF hhttp {
IF HttpSendRequestA(hhttp,null,0,null,0) {
DO {
InternetReadFile(hhttp,buffer,254,br);
buffer = left$(buffer,br);
IF(br) {
sptr = 1;
eptr = 1;
cnt = 1;
while((buffer[sptr] != ',') AND (buffer[sptr] != null)){
sptr = sptr + 1;
}
sptr = sptr + 2;
eptr = sptr;
while((buffer[sptr] != ',') AND (buffer[sptr] != null)){
cnt = cnt + 1;
sptr = sptr + 1;
}
sptr = sptr + 3;
if (STRTONUM(StrMid(buffer,eptr,cnt)) = cur_lstprc) {
cur_tick = "Even";
} else if (STRTONUM(StrMid(buffer,eptr,cnt)) > cur_lstprc) {
cur_tick = "Up";
} else {
cur_tick = "Down";
}
cur_lstprc = STRTONUM(StrMid(buffer,eptr,cnt));
eptr = sptr;
cnt = 1;
while((buffer[sptr] != '"') AND (buffer[sptr] != null)){
cnt = cnt + 1;
sptr = sptr + 1;
}

sptr = sptr + 1;
cur_name = strmid(buffer,eptr,cnt);
stkstmt_update = NULL;
stkstmt_update = stock_db.PrepareSQL("UPDATE stock SET name=?,lstprc=?,tick=? WHERE id="+NumToStr(cur_id));
stock_db.BindParameter(stkstmt_update,1,cur_name,TYPE_STRING,40);
stock_db.BindParameter(stkstmt_update,2,cur_lstprc,TYPE_DOUBLE,9);
stock_db.BindParameter(stkstmt_update,3,cur_tick,TYPE_STRING,5);
stock_db.Execute(stkstmt_update);
error = stock_db.GetErrorText(stkstmt_update);
IF(Len(error)) {
messagebox(0,error,"WRITE ERROR");
}
stock_db.FreeSQL(stkstmt_update);
Price[prccnt].stkid = cur_id;
Price[prccnt].symbol = cur_symbol;
Price[prccnt].date = today();
Price[prccnt].lstprc = cur_lstprc;
prccnt = prccnt + 1;
}
buffer = "";
} UNTIL br = 0;
}
}
InternetCloseHandle(hhttp);
}
stock_db.FreeSQL(stkstmt_select);
}
InternetCloseHandle(hconnect);
curprcidx = 0;
do {
prcstmt_insert = stock_db.ExecSQL("INSERT INTO hstprc (symbol,prcdt,price) VALUES('bogus','bogus',0)");
error = stock_db.GetErrorText(prcstmt_insert);
IF(Len(error)) {
messagebox(0,error,"PRICE INSERT ERROR");
}
stock_db.FreeSQL(prcstmt_insert);
dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = dissym";
prcstmt_select = stock_db.ExecSQL(sel);
error = stock_db.GetErrorText(prcstmt_select);
IF(Len(error)) {
messagebox(0,error,"PRICE SEL ERROR");
}
stock_db.GetData(prcstmt_select,1,prc_id,TYPE_INT);
error = stock_db.GetErrorText(prcstmt_select);
IF(Len(error)) {
messagebox(0,error,"PRICE GET DATA ERROR");
}
stock_db.FreeSQL(prcstmt_select);
messagebox(0,numtostr(prc_id),"DEBUG");
prcstmt_update = stock_db.PrepareSQL("UPDATE hstprc SET symbol=?,prcdt=?,price=? WHERE id="+NumToStr(prc_id));
stock_db.BindParameter(prcstmt_update,1,Price[curprcidx].symbol,TYPE_STRING,10);
stock_db.BindParameter(prcstmt_update,2,Price[curprcidx].date,TYPE_STRING,25);
stock_db.BindParameter(prcstmt_update,3,Price[curprcidx].lstprc,TYPE_DOUBLE,9);
stock_db.BindParameter(prcstmt_update,4,Price[curprcidx].stkid,TYPE_INT,9);
stock_db.Execute(prcstmt_update);
error = stock_db.GetErrorText(stkstmt_update);
IF(Len(error)) {
messagebox(0,error,"WRITE ERROR");
}
stock_db.FreeSQL(prcstmt_update);
curprcidx = curprcidx + 1;
} UNTIL curprcidx = prccnt;
}
}
stock_db.Disconnect();
InternetCloseHandle(hopen);
return;
}

Ionic Wind Support Team

dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = dissym";

Seems to be your problem.   The select statement is just a string so your querying "dissym" and not "bogus".  The statement should either be:

dissym = "bogus";
sel = "SELECT * FROM hstprc WHERE hstprc.symbol = " + dissym;

or:

sel = "SELECT * FROM hstprc WHERE hstprc.symbol = bogus";
Ionic Wind Support Team

Rock Ridge Farm (Larry)

Changed it - still get error:
   Too few parameters - expected 1.
The records gets created - but the select statement fails.

Ionic Wind Support Team

Ionic Wind Support Team

Rock Ridge Farm (Larry)

sel = "SELECT * FROM hstprc WHERE hstprc.symbol = bogus";

Ionic Wind Support Team

I still think that select statement is wrong.  Should probably be:

sel = "SELECT * FROM hstprc WHERE symbol = 'bogus'";

Vikki would know in an instance ;)
Ionic Wind Support Team

Rock Ridge Farm (Larry)

Had a problem with that as well.
I did  get it to work with the other version:
    sel = "SELECT * FROM hstprc WHERE hstprc.symbol = 'bogus'";
Also had to add:
    stock_db.Get(prcstmt_select);
just before the GetData statement - Duh.
Moving on to my next undiscovered error - thanks for the help.

Vikki

Quote from: Paul Turley on June 30, 2006, 01:28:58 PM
I still think that select statement is wrong.ÂÃ,  Should probably be:

sel = "SELECT * FROM hstprc WHERE symbol = 'bogus'";

Vikki would know in an instance ;)

As usual days late and....ÂÃ,  :)

For strings, SQL needs single quotes around strings. For numbers, no quotes are needed.

Parker

Sorry, I should have known the answer to that one. I learned the hard way with PHP and MySQL. My query strings would never work, until I looked at phpMyAdmin's in detail.