July 01, 2022, 01:25:48 AM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


BindVariable

Started by LarryMc, March 27, 2006, 04:40:52 PM

Previous topic - Next topic

0 Members and 2 Guests are viewing this topic.

LarryMc

This is from the database example.
QuoteAddressBook::InitSelect()
{
string sel;
   sel = "SELECT * FROM addresses";
   IF LEN(m_filter)
      sel += " WHERE " + m_filter;
   hstmt_select = m_db.ExecSQL(sel);
   IF hstmt_select
   {
      m_db.BindVariable(hstmt_select,1,m_id,TYPE_INT);
      m_db.BindVariable(hstmt_select,2,m_fname,TYPE_STRING);
      m_db.BindVariable(hstmt_select,3,m_lname,TYPE_STRING);
      m_db.BindVariable(hstmt_select,4,m_street,TYPE_STRING);
      m_db.BindVariable(hstmt_select,5,m_street2,TYPE_STRING);
      m_db.BindVariable(hstmt_select,6,m_city,TYPE_STRING);
      m_db.BindVariable(hstmt_select,7,m_state,TYPE_STRING);
      m_db.BindVariable(hstmt_select,8,m_zipcode,TYPE_STRING);
      m_db.BindVariable(hstmt_select,9,m_phone,TYPE_STRING);
      m_db.BindVariable(hstmt_select,10,m_email,TYPE_STRING);      
   }
}

is there a way to use a pointer and a loop so that you only have one BindVariable statement? (there was an example in the other language that could do that with pointers)

How would you do it when you had a mix of int, string, and dstring field types?

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Ionic Wind Support Team

A dstring is just a string.  The keyword dstring is only used when defining the string, not when using it so for the database it would be TYPE_STRING.

Open up Aurora, open the example file "database_test.src" and read it.  There's a dynamic binding example included.  Convert your numbers to strings if you only want one binding statement.

Otherwise, just like with using ODBC directly, you'll need a couple.

Ionic Wind Support Team

Ionic Wind Support Team

You can also organize your own data better.  Instead of using discreet variables use a structure.  Then you can bind in a single loop.

struct dbdata
{
int nType;
pointer pData;
}

...

for x = 0;x<numcols;x++
{
   db.BindVariable(hstmt,x+1,p->pData,p->nType);
}


Store the structs in a linked list, or whatever.  If you still can't get it let me know.
Ionic Wind Support Team

LarryMc

what about the 'memo' fields which are longer than 255?

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Ionic Wind Support Team

?.   Just bind a string.

As I tried to explain a DSTRING is a keyword, not a type.  It allows you to dimension a string to larger than 255.  The variable is still a string.  In fact you can create a string dynamicallu, any size, by just using NEW and a BYTE type.

p->pData = new(byte, 10000000);

...after a Get.

print(*(string)p.pData);

Don't make it more complicated in your mind as it is that simple.  A string is just an array of bytes afterall.
Ionic Wind Support Team

Ionic Wind Support Team

Oh and when binding any variable you can specify the size with the optional parameter.  Just look at the database.inc file.  Or your own docs ;)

CDatabase::BindVariable( int hstmt, int column, pointer variable, int type, OPT pointer pReturn = NULL, OPT int cbSize = 255);


So when binding the memo field just give it your allocated size.
Ionic Wind Support Team

LarryMc

LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Rock Ridge Farm (Larry)

Does this only work for string fields - I have mixed data types.

LarryMc

I'm still having problems with fields longer than 255 characters.  I couldn't make it work right in IBPro and I can't make it work right here.

Attached is a modified aurora addressbook example.  It creates 'abook.mdb'.  I made the first 'street' field into a 1000 character field.  The include mdb has a 300 character entry in the 1st record.  But when I run the program I only get back 231 characters. (Which happens to be what I got back in IBPro).

Anyone care to bail me out?


Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Ionic Wind Support Team

In the DoUpdate subroutine, which saves records you still have:

   m_db.BindParameter(hstmt_update,3,m_street,TYPE_STRING,255);

which should be

   m_db.BindParameter(hstmt_update,3,m_street,TYPE_STRING,1000);

In your InitSelect you have:

      m_db.BindVariable(hstmt_select,4,m_street,TYPE_DSTRING);

How many times to I have to tell people that a DSTRING is not a type that can be used anywhere except defining a variable.  The BindParameter function would be binding it as a binary type with 255 bytes (default).  As I explained before you have to tell it the length.  With the optional paramterer

      m_db.BindVariable(hstmt_select,4,m_street,TYPE_STRING, NULL, 1000);


Ionic Wind Support Team

Bruce Peaslee

Quote from: Ionic Wizard on April 02, 2006, 08:36:32 AM
How many times to I have to tell people that a DSTRING is not a type that can be used anywhere except defining a variable.ÂÃ, 

Probably at least once moreÂÃ,  :D
Bruce Peaslee
"Born too loose."
iTired (There's a nap for that.)
Well, I headed for Las Vegas
Only made it out to Needles

LarryMc

April 02, 2006, 10:32:48 AM #11 Last Edit: April 02, 2006, 10:34:51 AM by tlmccaughn
Thanks Paul,
I made your 'simple' corrections and it worked.ÂÃ,  I also made it work in IBPro.ÂÃ,  But when I plug the fix into an IBPro module (non main in project) it doesn't work the IBPro equivalent of this
Quotem_db.BindVariable(hstmt_select,4,m_street,TYPE_STRING, NULL, 1000);
It stops the display of all bound variables and gives me a GF.
If I replace the equiv of the above in IBPro with
QuotedbGetData(hstmt_select,4,street)
after a dbGetFirst/Last/Next/Prev type statement it works fine.ÂÃ,  In my IBPro code street is define as global and initialized in the main module and extern in the module where the statements exists.

The only reason I mention IBPro is that's what I'm more familiar with and I think whatever is forcing me to use the dbGetData in IBPro is a bug and that same bug might exists in Aurora.

But, as you can easily see, I'm not swift enough to set up all the equivalent code in Aurora, YET, to test it.

Larry

PS
Thanks for the moral support Peaslee! :D
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Ionic Wind Support Team

Sorry but I can't help with the IB code.  While the functions/methods have similar names I did things much differently in Aurora, design wise.

That and the fact I am no longer involved with that language in any shape or form. :-\
Ionic Wind Support Team

LarryMc

Understand
But you resolved my problem in IBPro without knowing it anyway.
Quotem_db.BindVariable(hstmt_select,4,m_street,TYPE_STRING, NULL, 1000);
was the key.
Instead of using NULL I had declared a variable (retx) as a pointer.ÂÃ,  What I didn't do was set it to NULL before using it(not use to using pointers). When I plugged the NULL in in place of my variable it all worked fine.

Thanks for the indirect help! Help is help any way you can get it. :D

When I get my code in IBPro all working correctly I'm going to use it as my pattern to convert over to Aurora.ÂÃ,  I'll understand my logic(and it will be correct) so that anything that doesn't work correctly will be due to my lack of understanding of Aurora.

I'm sticking with Aurora unless I get get 'banned' for some of the questions I ask! :D

Larry
LarryMc
Larry McCaughn :)
Author of IWB+, Custom Button Designer library, Custom Chart Designer library, Snippet Manager, IWGrid control library, LM_Image control library

Ionic Wind Support Team

Ionic Wind Support Team